Importing CSV to MySQL using LOAD DATA INFILE, Simple right?

[AndyUK]

Just when you think something is going to be simple, it turns out to be a right bum

So I have a CSV file (link below if you are interested) and I’m trying to import this into a MySQL database. Now the easiest way I could think of would be to use LOAD DATA INFILE as shown below…

LOAD DATA INFILE '/home/shopify/shopify_all_products.csv'
INTO TABLE csv_temp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

All works fine until line 2481 when this happens…

hat-glasses-and-moustache-print-racerback-tank-top,Hat, Glasses and Moustache Print Racerback Tank Top ,

There are only 2 fields shown here but because Hat, has been used, MySQL is treating it as 3. What’s more annoying is that some commas are escaped with 's, and some are placed within " ". Why they couldn’t be consistent I don’t know

Actual CSV file I am importing…
http://fashiondropshippers.com/public-feeds/shopify/shopify_all_products.csv

So my question is, is there a way to import this without having to parse it line by line?

[fretburner]

You could try adding an ‘escaped by’ clause for the fields line:

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY "\\"

[AndyUK]

$query = <<<eof
    LOAD DATA INFILE '/home/shopify/fds/shopify_all_products.csv'
    INTO TABLE fds_csv
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY '\'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (handle,title,vendor,type,tags,published,option1_name,option1_value,option2_name,option2_value,variant_sku,variant_grams,variant_inventory_tracker,variant_inventory_policy,variant_fulfillment_service,variant_price,variant_compare_at_price,variant_requires_shipping,variant_taxable,variant_weight_unit,image_src,image_alt_text,gift_card,variant_image_1,variant_image_2,variant_image_3,variant_image_4,variant_image_5,variant_image_6,variant_image_7,variant_image_8,variant_image_9,variant_image_10,body_html)
eof;

right syntax to use near ‘’ IGNORE 1 LINES

[fretburner]

@AndyUK

ESCAPED BY ''

It needs to be a double backslash ('\\').

Backslash is the MySQL escape character within strings in SQL
statements, so to specify a literal backslash, you must specify
two backslashes for the value to be interpreted as a single
backslash.

[AndyUK]

Is that necessary if I’m using $query = <<< eof ?

Having said that, I have tried it and it fails around line 1024

The only way I can get it to work it replace replace , with ## (or whatever other symbol not used within the file), then import the file. When I come to export it from MySQL I can then replace the ## with , again.

Any reason why I should do it like this?