CODEWITHSUNDEEP
mysqlexcelcsvphpmyadmin
Carl Paulsen
Carl Paulsen

Reputation: 11

phpmyadmin export CSV to excel drops data

I'm using MySQL with XAMPP and using phpmyadmin to extract data from tables. If I choose to export to CSV, the data appears to be fine. But when I select to export using the "CSV for MS Excel" option, I'm losing some data in the export file. Settings are the same in both cases.

Specifically, if a field has a comma in it, it appears that at least sometimes the data after the comma is dropped. Note that the comma is contained in quotes with other text in the standard CSV format, so the comma should not be seen as a field delimiter. The data after the comma within the field is dropped, but in addition, data in fields that follow the field with the comma are also dropped, but not necessarily for the entire record.

So, let's say record 2 has a comma in a text-based field in column C, such as "big spender, nice guy." What goes into Column C in Excel is "big spender" with ", nice guy" being dropped. In addition, Columns D, E, F and G may also lose their data. But in some cases it appears that later columns (perhaps H, I, J and K) may have the correct data in them. I'm not suggesting it always loses data for any specific number of columns, just that some number seem to lose data but at times later columns start having data again in the correct column.

I can't see a clear pattern to what gets dropped and what doesn't, just that what I describe above happened yesterday in a data set I'm using. Note I can see the complete data in the SQL table, and if I use the straight CSV export, it appears that no data is lost.

Could this be a bug? I've searched for known bugs and found none. FYI, I'm using Excel in Office 2007 on a Windows 7 machine. The original data source is SugarCRM.

Thanks so much.

Upvotes: 1

Views: 902

Answers (1)

O. Jones
O. Jones

Reputation: 108706

Open up the CSV file phpmyadmin made for you with a text editor, not with Excel. Find the offending row (the one with big spender, nice guy in it). Look to see whether it looks like this

"whatever","whatever","big spender, nice guy", 123, 456

or

whatever,whatever,big spender, nice guy, 123, 456

If it's the second one your columns aren't delimited properly. CSV is deceptively hard to get right because of this, and because of the possibility of this kind of text string:

Joe said, "O'Meara is a big spender and a nice guy!"

You may wish to try exporting your data in a tab-delimited rather than comma-delimited file to overcome this. You can do this by specifying ordinary, not Excel-style, CSV. Then specify 

\t

where it ask you for "Columns separated with:".

Excel will be able to figure this out as it reads it.

Upvotes: 1

Related Questions