Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Excel csv export

anet's profile image anet posted 2 years ago in Import/Export Permalink

Hi,

I am using the latest version of Heidi (9.5.0.5196). I exported a database selection as excel csv. Excel (MS Office 365) does not recognised it correctly and does not display the data in columns but instead the full line in one cell. The export is comma-separated where Excel expected semikolon-separated data. Adding "sep=," manually in a text editor to the csv file makes Excel show the file like expected. With a previous version of Heidi it worked fine (9.3.something)!

Two questions arise for me:

  1. Why has the default separator for Excel export changed.

  2. Why is the dropdown selection disabled where I can choose the separator in case of Excel export?

To 2) I have to add, that I also tried to export as "Delimited text" but then I get problems with return characters that are available in some data base entries. This I come around when using " as encloser (default blank). But I think the Excel export should work right away and I should not need to try different setting until it works.

Thanks anet

ansgar's profile image ansgar posted 2 years ago Permalink

According to this article on Wikipedia, Excel expects a specific field separator:

  • when pasting some text into Excel, Excel expects a tab character (\t)
  • when loading a .csv file in Excel:
    • if your system wide decimal separator is comma (,) then Excel expects semicolon (;)
    • for all other decimal separators, Excel expects a comma
  1. this logic in HeidiSQL has not changed since 2011. See this thread for discussion on the last changes. And see the version history of the relevant code file.

  2. the dropdown is disabled for Excel because if the user would break it if he changes the control characters. There is no alternative to the above logic in my experience.

Also, the field encloser (" for Excel) is doubled if it occurs in any field data. That's a form of escaping. For example a field containing foo"bar is exported as "foo""bar"

If you want some specific format you need to select "Delimited text", not "Excel CSV".

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