Plurrrr

a tumblelog
08 Apr 2019

Importing a CSV into MySQL

I helped Paul out with the problem of importing a CSV file created in OpenOffice on Windows into an existing table on MySQL. After some reading I came up with a command similar to:

LOAD DATA LOCAL INFILE
"file.csv"
INTO TABLE tablename
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Note the LINES TERMINATED value, which must be '\r\n' if the file was created on Windows. If on Linux, use just '\n'.

Don't forget the LOCAL otherwise MySQL can't execute the command if running with the --secure-file-priv option.

less and Unicode

To have less work correctly with UTF-8 files use:

LESSCHARSET='utf-8' less file.txt