Exporting an SQL table to CSV with commas escaped?

Question

I have a large MySQL table (8-9k records, I'm using phpMyAdmin) with some values that are descriptions, with commas.

I need to Export the SQL table into CSV form to use on a new platform and I can't for the life of me solve this issue:

When there are commas in certain fields, the data gets shoved to the next column and things get out of order.

I JUST NEED the EXACT table in MySQL to become a CSV. Any tips, ideas?

Thanks.

Answer 1

Use this query:

SELECT * INTO OUTFILE 'filename.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM [tablename]

Source: http://www.wausita.com/2011/02/mysql-export-csv/

Answer 2

Simply go to the database's Export tab. Be sure to enclose your fields. This way values are wrapped in " to avoid erroneous commas.

Otherwise, you can use mysqldump from the command line.

Or you can export data with a query:

SELECT *
FROM table
INTO OUTFILE '/tmp/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Answer 3

My solution was exporting as an .xlsx from MySQL. The columns were rendered perfectly. Using several combinations of enclosing/escaping character was to no avail.

I then saved the .xlsx as a .csv and it was fine.

Thanks for your time guys.