distal-attribute
distal-attribute
distal-attribute
distal-attribute

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

User, date Message
Written by landisd
6 years ago
Category: Import/Export
7 posts since Sun, 06 May 07
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
Written by ansgar
6 years ago
5023 posts since Fri, 07 Apr 06
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
Written by landisd
6 years ago
7 posts since Sun, 06 May 07
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
Written by kalvaro
6 years ago
595 posts since Thu, 29 Nov 07
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]
Written by landisd
6 years ago
7 posts since Sun, 06 May 07
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
Written by kalvaro
6 years ago
595 posts since Thu, 29 Nov 07

landisd wrote: 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]
Written by landisd
6 years ago
7 posts since Sun, 06 May 07
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
 

Please login to leave a reply, or register at first.