Correct LINES TERMINATED BY when importing uploaded CSV into MySQL table from PHP

Question

I’m having trouble identifying how my CSV’s lines are terminated. I’m using the following PHP code to load data from a CSV into my DB.

mysql_query('LOAD DATA LOCAL INFILE "temp/test.csv"
             INTO TABLE test
             FIELDS TERMINATED BY ","
             LINES TERMINATED BY "///"
             IGNORE 1 LINES (one, two, three, four, five)');

Now when I use LINES TERMINATED BY "///", the data is put into my DB properly, except for the fact that the 1st column on each row is “a return” followed by the cell data. That’s not what I want, I want the return to disappear.

I think that the sequence of last column cell, ///, return and first column cell in the CSV is split by /// and should be split by /// + return. I should change the LINES TERMINATED BY. I’ve tried \n, \r, \r\n, \n\r. After a while I tried <br> and knew I needed help.

I’m using MS Office on a Mac and saving my CSV as Comma Separated Values (.csv) (sample). I’ve also tried the mentioned LINES TERMINATED BY with MS-DOS Comma Separated and Windows Comma Separated files.

Result of SELECT HEX(one) FROM test after running my query:

Array
(
    [0] => 0D6131
)
Array
(
    [0] => 0D6132
)
Array
(
    [0] => 0D6133
)

Solution

Correct query

Your CSV has rows delimited by three slashes and carriage return. Just use LINES TERMINATED BY "///\r" in your query and it should work. At least it works for me on my MySQL 5.1.49.

LOAD DATA LOCAL INFILE "temp/test.csv"
INTO TABLE test
FIELDS TERMINATED BY ","
LINES TERMINATED BY "///\r"
IGNORE 1 LINES (one, two, three, four, five)

Debugging unprintable characters

When in doubt about what the unprintable characters are, you can always look at hexdump and decode the characters manually. Usually these characters are ASCII. On *NIXes, see man ascii, otherwise look the table up on Internet (e.g. http://man-ascii.com/).

In MySQL, hexdump of a string is obtained by HEX() function application, e.g.

SELECT HEX(one) FROM test

In POSIX shell, hexdump is generated using

od -t x1 temp/test.csv

If you do not want to be that hardcore, for common whitespace it is enough to convert it to C escape sequences (e.g. \r for carriage return) using

od -c temp/test.csv

Maybe even simpler (but generally harder to explain) approach to identifying EOL type is just using the text editor of your choice and letting it tell you. CSV is a plain text format (in contrast to binary format). E.g. in Vim open the file and run se ff?. This will show you fileformat=<EOL-type> where <EOL-type> is

• unix for \n
• dos for \r\n
• mac for \r

Beware multiple escaping levels

Notice that at the beginning of my answer I wrote just the query, not PHP command to execute it. In SQL, some characters are special and need to be escaped to be taken literally (quote inside string), other gain their special meaning through escaping (n in \n). In PHP, the SQL query must be placed in string, where another level of escaping takes place (and for different characters!). Another level of escaping comes e.g. when you call PHP from shell as php -r '…'.

If you want to be sure that you got the escaping right, print the query before sending it to database.

$query = '…';
print $query;
#$result = mysql_query($query);

mysql API was deprecated in PHP 5.5, removed in PHP 7.0

The old mysql API (mysql_* functions) was deprecated in PHP 5.5 and removed in PHP 7.0, it will not be available in future versions. Consult the PHP manual for choice of MySQL API. See also other relevant answers.