5 ms·

Excel is useless with csv and utf-8. when I need to export to csv, which I regularly do, I always use Libre Office.

douche 10y ago
I refuse to open CSVs in Excel, because so often perfectly valid CSV gets butchered by it. It's easier to just use Notepad++.
- supergreg 10y ago
  A nice weekend project would be a converter from CSV to xslx or SpreadsheetML [0] [0] https://en.wikipedia.org/wiki/SpreadsheetML https://en.wikipedia.org/wiki/SpreadsheetML
m_mueller 10y ago
It actually does have CSV and UTF-8, but finding the correct settings is very hidden. I'm always amazed at how unable Microsoft is to improve the base of Excel and instead just keeps piling UI layers on top to hide the ugly places.
SonOfLilit 10y ago
Surprisingly, Excel can import CSV from UTF8 with BOM (at least new versions of Excel). However, be careful, as Ctrl+S will save it in some arbitrary codepage and you'll probably end up with a document like: ID,Name 1,??? ???? 2,Kevin Dub?is so take care to always save such documents as .xmlx. (If you convert them to your local codepage and import that - when at all relevant - it will be saved in the local codepage, so less risk of data loss.)
- masklinn 10y ago
  > Surprisingly, Excel can import CSV from UTF8 with BOM (at least new versions of Excel). A BOM is abnormal and not recommended in UTF-8, it's basically a shitty MS hack: the BOM is necessary to detect endianness differences between the document and the host system, endianness has no impact impact on UTF-8. And as freak_nl above notes, Excel uses locale-dependent field separators, so in some countries/locales it will try to import "CSV" with semicolon separators.
Freak_NL 10y ago
Excel is quite often useless with CSV, even if its contents are pure ASCII. Whether or not Excel will open a standard CSV file delimited with comma's (as per RFC 4180) properly also depends on the locale of the computer running Excel. So if you run Excel on Windows with a Dutch locale, it will by default fail to open the CSV normally and jam all its contents in one cell, because it expects a semi-colon as delimiter instead of a comma. To open standard CSV you have to import the file instead. Someone at some point decided that the Dutch use semi-colons instead of commas to delimit tabulated data, and decided to apply this logic to CSV files as well. I would like to know the history behind such a decision! Switch your locale to English US, and it opens normally. LibreOffice just opens the CSV and asks the user to confirm the delimiter and what not regardless of locale.
- pieter_mj 10y ago
  Importing csv is very messy in excel. A VBA import routine will always run in the US locale. However, csv files containing dates formatted according to other locales will have their days and months switched as long as the day is not bigger than 12.
- jsproc 10y ago
  Yes, this is so horrible. As a workaround I sometimes put "sep=," on the first line of the spreadsheet. This forces Excel to use the comma as a separator, regardless of the locale. The downside of course is that every other tool on the planet shows the extra row, so it's not very useful.
  - ptman 10y ago
    I suffer from the reverse problem. I need to ingest CSV files produced by excel in various locales. Or is there some generally available alternative to excel that would produce valid RFC CSVs?
    - Piskvorrr 10y ago
      There is no such thing as a valid CSV - even the spec is borked, and nobody follows it anyway. If you have a choice, XLSX is a lesser evil.

I refuse to open CSVs in Excel, because so often perfectly valid CSV gets butchered by it. It's easier to just use Notepad++.

supergreg 10y ago
A nice weekend project would be a converter from CSV to xslx or SpreadsheetML [0] [0] https://en.wikipedia.org/wiki/SpreadsheetML https://en.wikipedia.org/wiki/SpreadsheetML

m_mueller 10y ago

It actually does have CSV and UTF-8, but finding the correct settings is very hidden. I'm always amazed at how unable Microsoft is to improve the base of Excel and instead just keeps piling UI layers on top to hide the ugly places.

SonOfLilit 10y ago

Surprisingly, Excel can import CSV from UTF8 with BOM (at least new versions of Excel). However, be careful, as Ctrl+S will save it in some arbitrary codepage and you'll probably end up with a document like: ID,Name 1,??? ???? 2,Kevin Dub?is so take care to always save such documents as .xmlx. (If you convert them to your local codepage and import that - when at all relevant - it will be saved in the local codepage, so less risk of data loss.)

masklinn 10y ago
> Surprisingly, Excel can import CSV from UTF8 with BOM (at least new versions of Excel). A BOM is abnormal and not recommended in UTF-8, it's basically a shitty MS hack: the BOM is necessary to detect endianness differences between the document and the host system, endianness has no impact impact on UTF-8. And as freak_nl above notes, Excel uses locale-dependent field separators, so in some countries/locales it will try to import "CSV" with semicolon separators.

Freak_NL 10y ago

Excel is quite often useless with CSV, even if its contents are pure ASCII. Whether or not Excel will open a standard CSV file delimited with comma's (as per RFC 4180) properly also depends on the locale of the computer running Excel. So if you run Excel on Windows with a Dutch locale, it will by default fail to open the CSV normally and jam all its contents in one cell, because it expects a semi-colon as delimiter instead of a comma. To open standard CSV you have to import the file instead. Someone at some point decided that the Dutch use semi-colons instead of commas to delimit tabulated data, and decided to apply this logic to CSV files as well. I would like to know the history behind such a decision! Switch your locale to English US, and it opens normally. LibreOffice just opens the CSV and asks the user to confirm the delimiter and what not regardless of locale.

pieter_mj 10y ago
Importing csv is very messy in excel. A VBA import routine will always run in the US locale. However, csv files containing dates formatted according to other locales will have their days and months switched as long as the day is not bigger than 12.
jsproc 10y ago
Yes, this is so horrible. As a workaround I sometimes put "sep=," on the first line of the spreadsheet. This forces Excel to use the comma as a separator, regardless of the locale. The downside of course is that every other tool on the planet shows the extra row, so it's not very useful.
- ptman 10y ago
  I suffer from the reverse problem. I need to ingest CSV files produced by excel in various locales. Or is there some generally available alternative to excel that would produce valid RFC CSVs?
  - Piskvorrr 10y ago
    There is no such thing as a valid CSV - even the spec is borked, and nobody follows it anyway. If you have a choice, XLSX is a lesser evil.