mysql export data to csv

Question

I am using mysql **select into outfile.." export data to csv file, but in the mysql table there is a field of type "text" with some html content in the field.

The content contains double quote(") and comma(,), so when I dump the data to csv,the text field will be spit to many fields when I open the csv file with office excel.

Some data in my table is just as following:

<div id="designer-txt-right">
                     <h2>TECHNICAL INFORMATION</h2>
                     <ul>
    <li>
        <strong><span lang="IT">Internal frame</span>: </strong><br>
        <span lang="IT">tubular steel and steel profiles</span></li>
    <li>
        <strong><span lang="IT">internal frame upholstery</span>: </strong><br>
        <span lang="IT"><span lang="IT">Bayfit® (Bayer®) flexible cold shaped polyurethane foam, cover in water repellent polyester fibre</span></span></li>
    <li>
        <strong><span lang="IT">Feet and fixing elements</span>: </strong><br>
        <span lang="IT"><span lang="IT">thermoplastic material</span></span></li>
    <li>
        <strong>COVER: </strong><br>
        FABRIC (<span lang="IT">limited categories)</span></li>
 </ul>
 <br>
                 </div>

My export sql statement:

SELECT * INTO OUTFILE '/tmp/filename.csv'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'  LINES TERMINATED BY '\r\n' FROM product;

there's various escape/terminator options for that, but you shouldn't be using csv anyways. csv is NOT intended to handle arbitrary text, especially when the text itself contains CSV metacharacters. csv should be a last-resort format, not your go-to never-fail. — Marc B
@James I edit the question, and the statement added to the question. — tudouya
whats the use of a text/blob field in a csv file? you can better avoid that, also you can store those in your file system using a stored proc/ program by keeping a link to each row in your csv as file name. — Adarsh Gangadharan
@AdarshGangadharan, yeah,you are right.I don't really need the text field in csv file.so there is no question — tudouya

Joshua Dannemann Joshua Dannemann · Accepted Answer · 2015-10-26T17:04:02

First check the content of the generated file in a text editor and make sure that the strings containing quotes and commas are in fact surrounded by quotation marks. I have sneaking suspicion that the file is generated correctly; however, the HTML content within it is sure to cause problems if you view the file in Excel. The reason why is that the program, like many MS Office programs, sees HTML and then it tries to parse it as such. This may cause more than a few problems viewing the file in Excel and there is not a good workaround for it. If you have other programs at your disposal that can use view the file, try it in those instead. You can also validate the content using CSV lint: http://csvlint.io/

mysql export data to csv

1 Answers