Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 3 of 3
  1. #1
    Junior Member
    Join Date
    Dec 2004
    Posts
    5
    Member #
    8447
    Hi,

    Im wondering if anyone out there as done something similar to what im 'bout to do or has some idea on how to tackle this problem.

    I have a csv file containing values to be import into a database (mysql). The values in the csv file could contain values for country names (ie America, Australia etc), name, phone etc.

    Lets say that these values needs to be matched into a contacts table containing the fields (name, phone, city, zip, country). It would be trivial to match for values that are names, and phone numbers for instance, however the country field in the contacts table would typically be an id (country_id) which reference another table (country_table).

    So, the challenge is to find out if there exists a country name "America" in the country_table before i could insert it into the contacts table.

    The problem with this is, if there are 200 records to be imported, then it becomes inefficient to do a search in the country_table for each records.

    Does anyone out here know a solution to this? My approach could be wrong and if anyone know of a solution please do let me know.

    Thank you heaps.

    Sing tran

  2.  

  3. #2
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    So you're saying that you want to import only those records that are for a valid country?

    You could do this in a 3-stage approach...
    a) Import CSV into staging table (eg. csv_load_temp)
    b) do an update
    Code:
    UPDATE csv_load_temp a
    SET country_id = ( SELECT b.country_id
                       FROM   country b
                       WHERE  b.country_name = a.country_name ) 
    WHERE EXISTS ( SELECT 1
                   FROM   country c
                   WHERE  c.country_name = a.country_name ) ;
    c) Copy records from csv_load_temp into your contacts table where the country_id IS NOT NULL

    Does rely on you having an up-to-date version of MySQL which supports sub-queries, but I think most do these days...

    Just an idea.

    Sean

  4. #3
    Junior Member
    Join Date
    Dec 2004
    Posts
    5
    Member #
    8447
    Hi Sean, thanks for your reply. I will try that out using temporary table of type heap.

    Sing Tran


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 05:05 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com