#1
  1. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,353
    Rep Power
    62

    Load data infile question


    Hey, guys. I get data from a vendor in the format of

    zipcode|city,state|their id

    I'd like to use a load data infile statement to insert the data into a table that's

    zipcode
    city
    state
    vendor_id
    country

    Without the state field, I could just do

    load data local infile /directory/file.csv replace into table my_table
    fields terminated by '|'
    (zipcode, city, vendor_id)
    set country='United States'

    since for this particular file, the country is always the United States. The problem is that I have to split the "city,state" field. Now since I'm doing this in php, I could just modify the file by replacing commas by pipes, but I was wondering if load data infile is able to handle this. Any help would be appreciated.
    Correspondence chess
    nothingbutchess.com
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    you could try something like this (untested)

    Code:
    load data local infile /directory/file.csv 
    replace into table my_table fields terminated by '|' 
    (zipcode, @city, vendor_id) 
    set city=select substring_index(@city,'.',1) 
    set state=select substring_index(@city,'.',-1) 
    set country='United States'

    Comments on this post

    • colpaarm agrees : Thanks for setting me in the right direction.
  4. #3
  5. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,353
    Rep Power
    62
    I had to make some modifications, but you set me in the right direction. The following does it

    load data local infile /directory/file.csv replace into table my_table
    fields terminated by '|'
    (zipcode, @city, vendor_id)
    set city=trim(substring_index(@city, ',', 1)),
    state=trim(substring_index(@city, ',', -1)),
    country='United States'";

    Thanks a ton, TonyF123.
    Correspondence chess
    nothingbutchess.com

IMN logo majestic logo threadwatch logo seochat tools logo