Exporting table to excell results in data not in proper col.

I have a table with about 10 fields. I want to export it so that it can be read by Excell with all the fields in their proper columns. I tried it and some of the data is shifted one column to the right (e.g., the data for "City" appears in the column for "State" and that shifts all the remaining data also.). The strange thing is that this occurs for some records and not for others. I thought that some of the fields might have misplaced comma, since the downloaded data is csv, but on inspection no such item is found.

Do you have any idea how I can rectify the problem?

Thanks

Dan Landis
danl@hawaii.edu

With the correct line and field terminators while exporting you should not run into that problem. Please try using
- the semicolon as field terminator
- double quote as field encloser
- \r\n as line terminator

I wish that could've worked; it did not. I simply got each record with the fields bunched together with quotes around each field, but not in the appropriate columns...

Any other ideas?

Dan

It'd be way easier to find out what's wrong if we could just, well, see it.

Useful data you could provide:
[list:92664a978d]
[*:92664a978d]A SQL dump with the table definition and, at least, the INSERT statement for the problematic record
[*:92664a978d]The resulting CSV file
[*:92664a978d]Your computer regional settings (list separator, decimal character... or at least the country, so we can figure out)
[/list:u:92664a978d]

How would I attach the requested files to this message?

Also, the total data table has close to 50 fields, I am only selecting 10...I would rather not dump the entire table since there is sensitive information in there. I can send you the 10 fields in the csv format with both comma and : as separators. The computer resides in the US if that helps.

Dan

How would I attach the requested files to this message?

It's all plain text, just open in notepad, select all, Ctrl+C, go to forum and Ctrl+V.

We don't need or want your whole database, just paste a small sample so we can figure out what's wrong. Put yourself in our shoes: how can we know what's wrong if we can't see any actual data?

Anyway, here come some ideas:
[list:4805632162]
[*:4805632162]If your computer has US regional settings you must probably use comma as field separator rather than semicolon.
[*:4805632162]If the data itself includes the special characters used to separate columns and rows (such as quotes, comas, line feeds...) you'll probably face problems with CSV since HeidiSQL won't escape them (esp. quotes). In that case, you may get better results exporting as HTML.
[*:4805632162]Last but not least, don't discard the possibility that it's eventually an Excel issue: that program has never been specially good importing text data ;-)
[/list:u:4805632162]

While I like Heidi very much, there is another program SQLyog that does, in fact, download data to Excell and keeps the fields in the appropriate columns. SQLyog has an option for download EXCELLXLM which seems to do the trick.

Dan