2

I'm trying to create a feature in my Content Management System in which users can upload a CSV file which is then parsed and the data is put in a MySQL database. To do this I use a file input and this SQL query.

$sql = "LOAD DATA LOCAL INFILE '".$_FILES["file"]["tmp_name"]."'
            INTO TABLE persons
            FIELDS TERMINATED BY ';'
            LINES TERMINATED BY '\r'
            (id, name, email, contacts)";

This works perfectly for a .csv file I created on my computer, but not al CSV files have fields that are terminated by a semicolon and have lines that are terminated with a \r. Now I want this query to work for all .csv files. Otherwise this feature won't work well enough to be implemented in my CMS. Is there any way I can make this work for .csv files which have other field and line endings? (e.g \n, \r\n, ,)

  • There are different types of CSV.. So unless you let the user decide how fields are seperated and and how lines are seperated, there is no way of knowing.. – Naruto Nov 12 '14 at 11:04

1 Answers1

6

Try LINES TERMINATED BY '\r\n'

This will work for files ending lines with '\r\n', but also with '\r' and '\n'. From the MySQL docs:

The FIELDS TERMINATED BY, LINES STARTING BY, and LINES TERMINATED BY values can be more than one character. For example, to write lines that are terminated by carriage return/linefeed pairs, or to read a file containing such lines, specify a LINES TERMINATED BY '\r\n' clause.

[EDITED]

You also need to add \r\n to the clause FIELDS TERMINATED BY in order to tell the parser not to include the end-of-lines symbols in the last field of the first line. So

FIELDS TERMINATED BY ';, \t\r\n'

will work in most cases.

Al_
  • 1,481
  • 1
  • 11
  • 22
  • 1
    Thanks for your answer! Both solutions you give don't seem to work though. Any idea why that is? –  Nov 12 '14 at 12:05
  • You are right. I've amended the answer. I think the problem is in the clause `FIELDS TERMINATED BY` – Al_ Nov 12 '14 at 12:57
  • Sadly, this doesn't seem to work either. Thanks for your effort though! –  Nov 12 '14 at 13:01
  • Well, this solution will work well with files of the type '\r\n' and '\n' (which are the majority). Problem is with files of the type '\r'. I'd suggest to get rid of '\r', for example with `dos2unix` (system command) before running the sql `LOAD DATA` – Al_ Nov 12 '14 at 14:14