0

First time reader but first time posting here.

I am trying export a MySQL table to a .csv file. This works totally fine:

SELECT * INTO OUTFILE '/tmp/0405151831.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
ESCAPED BY '\\' 
LINES TERMINATED BY '\n' 
FROM temp;

So exporting isn't the issue at all. But when I go to open in Excel, the escape character '\' shows up with all special characters in the spreadsheet.

I'm learning with a full list of 20k+ items provided by a plumbing supplier (they sent me spread sheets and I have been saving them as .csv, importing and exporting with MySQL to fill tables and to dick around on my own). Their list contains a plethora of ", ', * and all the other crap that may normally need escaping in basically each line.

I tried to get just get rid of the ENCLOSED BY and ESCAPED BY statements but guess what? There's plenty of commas in there too.

How do I keep the escape character from being exported into the .csv?

jrb805
  • 1
  • 1
  • Suggestion: Import it into mysql again instead of in excel and then use an ODBC driver to connect to mysql from Excel – Norbert Apr 06 '15 at 01:57
  • Oh yeah. I also tried opening in Libra Calc and the same thing happens so it isn't an issue caused by the programs to my knowledge. – jrb805 Apr 06 '15 at 01:58
  • No, when loading into excel (of librecalc or any), you would have to write/apply functions to get rid of the encoding. When importing back into a mysql database, you can use the read file code of mysql to do the same with way less effort (plus it is really quick, and you do not have limits on the number of records (your possible next issue)). – Norbert Apr 06 '15 at 02:01
  • Sorry. I was typing that first comment before I even saw that someone commented. Wasn't expecting a response so quick =) I will check it out in a little while and will get back to you. – jrb805 Apr 06 '15 at 02:06
  • Did that work? Yes it did. I also just brain farted because they also offer .csv format to download which alleviates the main reason I asked the question in the first place. If I use their csv files and not the excel files, I can import and export excluding the enclosed and escaped options and it works as intended. I guess I was comparing apples to oranges here. – jrb805 Apr 06 '15 at 03:48

0 Answers0