Search code examples
mysqlload-data-infileinto-outfile

Round Trip INTO OUTFILE, Excel Editing, and LOAD DATA INFILE without corruption

I have been trying to export a mysql table with INTO OUTFILE to CSV for editing in Excel and then inserting back into mysql using LOAD DATA INFILE.

I can export ok, and even reinsert the unedited file. However, if I open in Excel make changes and then save things get screwed up. I select "Yes" when it asks if I'm sure I want to export in CSV, but even if I try and reopen the file in Excel it comes up with invalid format.

I am wondering what the proper settings "FIELDS ESCAPED BY, TERMINATED, etc." would be for each command to ensure compatibility with Excel.

Thanks,

Solution

  • You should be able to do:

    SELECT < > FROM <blah>
INTO OUTFILE '<filename>' 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

    You can also do:

    SELECT < > FROM <blah>
INTO OUTFILE '<filename>'
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

    You can also use '|' as delimiter and import the file as delimited.