Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

How to export mostly text fields from mysql to CSV and import to MS-EXCEL , keeping fields aligned.

Avatar of rleyba828
rleyba828Flag for Australia asked on
PHP
11 Comments1 Solution7004 ViewsLast Modified:
Hi Team,

  I'm just trying to find an elegant way to script an export to csv for one of my mysql tables whose fields are mostly text, some of which are "varchar" and "longtext" field type.

My problem is that in the fields that are multiline (since they contain paragraphs), when I use the command below:

 SELECT * INTO OUTFILE '/tmp/tabledump.csv' FIELDS TERMINATED BY '|'  ENCLOSED BY '"' LINES TERMINATED BY '\N' FROM ChangeRecord

...once I import it to MS Excel, (even if I do it manually by doing a data import, specify delimiter, date format, etc), then excel gets confused with the fields which contain line returns and puts these multi line fields into separate cells, thus totally destroying the format of the table.

*However, if I do it from phpmyadmin, then I do "export" --> "CSV for MS Excel"  --> "Save as file"...then "go"....phpmyadmin allows me to open the table directly into excel with all the text fields intact.  Even text fields containing multiple paragraphs STAY within one cell, thus preserving the entire data table structure and all fields and row align perfectly.

Is there a workaround to the export utility in CLI in mysql so I can do what the phpmyadmin does correctly?  Alternatively, is there a command line for phpmyadmin that allows me to do the export CSV for MS excel option?

Thanks very much.