Carson Carson - 5 months ago 27
PHP Question

Line break issue from CSV to MySQL

I am importing a .csv file into MySQL and everything works fine, except the line breaks that are in the file.

One of my .csv rows looks like this:

42,E-A-R™ Classic™ Earplugs,ear,images/ear/classic.jpg,5%,"Proven size, shape, and foam
3M's most popular earplug
Corded and uncorded in a variety of individual packs
NRR 29 dB / CSA Class AL",312-1201,,"E-A-R™ Classic™ Uncorded Earplugs, in Poly Bag",310-1001,,E-A-R™ Classic™ Uncorded Earplugs in Pillow Pack,311-1101,,"E-A-R™ Classic™ Corded Earplugs, in Poly Bag"


The sixth field over should break into a new line when called, but it doesn't. When importing the .csv I select Lines terminated by \r. I have tried \n and auto but no luck.

Weird thing is, the field looks correct in the database with all of the appropriate breaks. If I manually go in to insert the line breaks in PHPmyadmin it prints correctly. Each field is set to UTF-8 as well.

Any ideas on this? Thanks.

edit: here is the MySQL statement

LOAD DATA LOCAL INFILE '/tmp/php89FC0F' REPLACE INTO TABLE `ohes_flyer_products`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r'

Answer

maybe you could use fgetcsv to parse each csv line into an array and then dump that array into the database?

something along the lines of

$fd = fopen($csvfile, "r");
while ($line = fgetcsv($fd))
{
    $sql = sprintf("INSERT INTO tablename (...) VALUES ('%s', ...)", $line[0], ...);
    $res = mysql_query($sql);
}

note 1: code not ready for production, check SQL injections!

note 2: please, use prepared statements as using them will speed the thing a lot (or make one multi-row insert statement).

note 3: wrap all in a transaction.

Comments