Search code examples
mysqlcsvdelimiterend-of-line

MySQL LOAD DATA INFILE: works, but unpredictable line terminator

MySQL has a nice CSV import function LOAD DATA INFILE.

I have a large dataset that needs to be imported from CSV on a regular basis, so this feature is exactly what I need. I've got a working script that imports my data perfectly.

.....except.... I don't know in advance what the end-of-line terminator will be.

My SQL code currently looks something like this:

LOAD DATA INFILE '{fileName}'
 INTO TABLE {importTable}
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
( {fieldList} );

This works great for some import files.

However, the import data is coming from multiple sources. Some of them have the \n terminator; others have \r\n. I can't predict which one I'll have.

Is there a way using LOAD DATA INFILE to specify that my lines may be terminated with either \n or \r\n? How do I deal with this?

Solution

  • I'd just pre-process it. A global search/replace to change \r\n to \n done from a command line tool as part of the import process should be simple and performant.