Multiple Possibilities for LINES INTERRUPTED AND FIELDS DISCONTINUED - MySQL
I am trying to create a function in my content management system where users can upload a CSV file, which is then parsed and the data is put into a MySQL database. For this I am using 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 fine for a file .csv
I created on my computer, but not all CSV files have fields that end with a semicolon and have lines that end with a character \r
. Now I want this query to work for all files .csv
. Otherwise, this feature won't work well enough to be implemented in my CMS. Can I make this work for files .csv
that have different fields and lines? (e.g. \ n, \ r \ n,)
source to share
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:
PLANNED FIELDS, LINES STARTING AND LINE TERMINATED values can be more than one character. For example, to write lines that end with carriage return / carriage return pairs, or to read a file containing such lines, specify the LINES TERMINATED BY '\ r \ n' clause.
[Changed]
You also need to add \r\n
to the clause FIELDS TERMINATED BY
to tell the parser not to include end-of-line characters in the last field of the first line. So
FIELDS TERMINATED BY ';, \t\r\n'
will work in most cases.
source to share