2

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,

christian
  • 2,279
  • 4
  • 31
  • 42
  • If it helps, I have all types of data, dollar amounts, decimal values with percentage, etc. The table to reinsert is comprised of varchar fields. – christian Jan 21 '12 at 01:30
  • This appears to be very similiar to [8031193](http://stackoverflow.com/questions/8031193) – arober11 Dec 02 '12 at 23:06

1 Answers1

0

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.

Karlson
  • 2,958
  • 1
  • 21
  • 48
  • Thanks for the response. That cuts off anywhere I have a comma and replaces the remainder of the field with '\'. I can add ENCLOSED BY '"' ... that gets the data to excel fine, but when I edit and save it messes up on the LOAD DATA INFILE. – christian Jan 21 '12 at 03:10
  • @christian Do you have tabs in the columns? – Karlson Jan 21 '12 at 03:12
  • Excel doesn't seem to support the tab option. I'm trying to allow users to easily edit their data so trying to avoid extra steps. – christian Jan 21 '12 at 03:27
  • @christian Excel allows import of a text file that is delimited by something else you can also try just making it an .xls file and try to open it. – Karlson Jan 21 '12 at 03:29
  • I'm sure there is a way to import with another delimiter, but I'm just trying to make things as simple as possible for the user. I am able to export to csv ok, so I think I am going to try and use fgetcsv instead to import the data. I am processing after the fact, so the insertion speed is not as big a factor as the export speed. – christian Jan 21 '12 at 05:04
  • No, from MYSQL database. I can export many records in seconds with INTO OUTFILE. – christian Jan 21 '12 at 05:35
  • @christian The delimiter won't change the speed of export. – Karlson Jan 21 '12 at 05:42