4 ms·

Am I insane here, or does Excel generally do a very good job of handling almost all CSV files. I don't really see a need for a metadata file, nor would I ever

by 41209 5y ago

Am I insane here, or does Excel generally do a very good job of handling almost all CSV files. I don't really see a need for a metadata file, nor would I ever see Excel or other tools accepting it. The main problem is adoption, CSV isn't perfect but it's what we have. Now if you wrote this as a member of the Excel team at Microsoft, and then Excel had the option of exporting CSV files with a metadata file, then I'd be a bit more excited.

Tagbert 5y ago
Excel has some major problems with CSV ingestion though I don’t think that this proposal will address those problems. Here are a couple of cases that I run into frequently: * Excel is very aggressive about forcing type conversion based on its own assumptions. It will convert strings to dates or numbers, even if data is lost in the process. It will ignore quotes to convert long numeric IDs into scientific notation which truncates the ID unrecoverable. * Excel cannot deal with quoted strings containing line breaks. It treats them as separate records and you get truncated records and partial records on separate rows.
- mark-r 5y ago
  My two favorite problems with Excel automatic type conversions: Zip codes that lose leading zeros. Gene names that get converted to dates. The names of some genes were recently changed because too many databases were being corrupted by researchers using Excel to read CSV files: https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
IanCal 5y ago
Excel does an atrocious job of handling CSV files. It regularly alters data, messes up encodings and either can't or couldn't (I haven't checked in a few years) open CSV files that start with a capital letter I. Source: dealing with CSV files people exported from Excel and the horrors that flowed from there.
- swader999 5y ago
  I've donated hours of my life to resolving excel corrupted csv files. They haven't been satisfying hours.
  - IanCal 5y ago
    Same. If it helps, the python package ftfy can fix a lot of encoding issues.
    - swader999 5y ago
      It's tempting to consider different languages...
- bitwize 5y ago
  > either can't or couldn't (I haven't checked in a few years) open CSV files that start with a capital letter I. It can't do this because it confuses such files with files in SYLK format, which was YET ANOTHER attempt to standardize spreadsheet data interchange, dating from the 80s.
  - IanCal 5y ago
    What I find incredible about this is that it decides that the file ending ".CSV" must not be a CSV file but SYLK. Then loading it as SYLK fails, and it doesn't then try and load it as a CSV file instead.
- 41209 5y ago
  Does any widely used application do it better. I always view CSV as a lowest common denominator, of course more precise formats exist, but not everyone can use those. Csvs normally get the job done, but like anything else you need to know it's limitations. Something like a basic phone book should work, your scientific data, with dozens upon dozens of floating point numbers may not work.
  - capeterson 5y ago
isoprophlex 5y ago
No, excel does about the worst possible job of handling CSVs. You can't even hope to keep a file intact upon opening...
VenTatsu 5y ago
Excel does a fairly bad job when moving data between two computers that aren't configured the same way, which kind of defeats the purpose of using a data interchange format. Where I work we have offices in the US, and in Europe where installing a localized version of windows will swap ',' and '.' when used as the group and decimal separator. Excel when loading a value 100,002 in the US will see one hundred thousand and two, in some parts of Europe it will see one hundred and 2 thousandths. Character set handing can be just as bad, there is no good way to get Excel to auto open a CSV file as UTF-8 that won't break every other CSV parser in existence. The only cross platform option is ASCII. Excel will happily load your local OS encoding, likely some variant of ISO-8859, but any other encoding requires jumping through hoops.
awild 5y ago
Excel will strip leading zeros of your data, even when you're escaping it, it will also assume that that single cell of a Column of dot-separated floats is in fact a date. Excel is in fact so good at detecting formats that even if you construct an xls fill it with ids which partially start with zeros, properly mark them as strings, it will still nag you at every single cell of your file that there is something fishy about the file.
- pbreit 5y ago
  The stripping of leading zeroes is dreadful when working with zip codes and SSNs.
pbreit 5y ago
You are insane :-). Excel has the most inexplicably horrific handling of long number strings that it is borderline unusable. https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10374741-stop-excel-from-changing-large-numbers-actually https://excel.uservoice.com/forums/304921-excel-for-windows-...
jpeloquin 5y ago
> Am I insane here, or does Excel generally do a very good job of handling almost all CSV files. If the user does Data > From Text/CSV > Transform > use PowerQuery to set the first row as the header, I think it's true that Excel does a good job. It provides the basics at least: configurable charset and column type detection. When the user re-saves to CSV, I'm not aware of any way to configure the output (e.g., force quotation of text content), but that's sort of ok. The easy path—double clicking on a CSV file or using File > Open—is where all the weird auto-conversion of values happens. But other posts have covered that part.

by 41209 5y ago

Tagbert 5y ago

Excel has some major problems with CSV ingestion though I don’t think that this proposal will address those problems. Here are a couple of cases that I run into frequently: * Excel is very aggressive about forcing type conversion based on its own assumptions. It will convert strings to dates or numbers, even if data is lost in the process. It will ignore quotes to convert long numeric IDs into scientific notation which truncates the ID unrecoverable. * Excel cannot deal with quoted strings containing line breaks. It treats them as separate records and you get truncated records and partial records on separate rows.

mark-r 5y ago
My two favorite problems with Excel automatic type conversions: Zip codes that lose leading zeros. Gene names that get converted to dates. The names of some genes were recently changed because too many databases were being corrupted by researchers using Excel to read CSV files: https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

IanCal 5y ago

Excel does an atrocious job of handling CSV files. It regularly alters data, messes up encodings and either can't or couldn't (I haven't checked in a few years) open CSV files that start with a capital letter I. Source: dealing with CSV files people exported from Excel and the horrors that flowed from there.

swader999 5y ago
I've donated hours of my life to resolving excel corrupted csv files. They haven't been satisfying hours.
- IanCal 5y ago
  Same. If it helps, the python package ftfy can fix a lot of encoding issues.
  - swader999 5y ago
    It's tempting to consider different languages...
bitwize 5y ago
> either can't or couldn't (I haven't checked in a few years) open CSV files that start with a capital letter I. It can't do this because it confuses such files with files in SYLK format, which was YET ANOTHER attempt to standardize spreadsheet data interchange, dating from the 80s.
- IanCal 5y ago
  What I find incredible about this is that it decides that the file ending ".CSV" must not be a CSV file but SYLK. Then loading it as SYLK fails, and it doesn't then try and load it as a CSV file instead.
41209 5y ago
Does any widely used application do it better. I always view CSV as a lowest common denominator, of course more precise formats exist, but not everyone can use those. Csvs normally get the job done, but like anything else you need to know it's limitations. Something like a basic phone book should work, your scientific data, with dozens upon dozens of floating point numbers may not work.
- capeterson 5y ago

isoprophlex 5y ago

No, excel does about the worst possible job of handling CSVs. You can't even hope to keep a file intact upon opening...

VenTatsu 5y ago

Excel does a fairly bad job when moving data between two computers that aren't configured the same way, which kind of defeats the purpose of using a data interchange format. Where I work we have offices in the US, and in Europe where installing a localized version of windows will swap ',' and '.' when used as the group and decimal separator. Excel when loading a value 100,002 in the US will see one hundred thousand and two, in some parts of Europe it will see one hundred and 2 thousandths. Character set handing can be just as bad, there is no good way to get Excel to auto open a CSV file as UTF-8 that won't break every other CSV parser in existence. The only cross platform option is ASCII. Excel will happily load your local OS encoding, likely some variant of ISO-8859, but any other encoding requires jumping through hoops.

awild 5y ago

Excel will strip leading zeros of your data, even when you're escaping it, it will also assume that that single cell of a Column of dot-separated floats is in fact a date. Excel is in fact so good at detecting formats that even if you construct an xls fill it with ids which partially start with zeros, properly mark them as strings, it will still nag you at every single cell of your file that there is something fishy about the file.

pbreit 5y ago
The stripping of leading zeroes is dreadful when working with zip codes and SSNs.

pbreit 5y ago

You are insane :-). Excel has the most inexplicably horrific handling of long number strings that it is borderline unusable. https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10374741-stop-excel-from-changing-large-numbers-actually https://excel.uservoice.com/forums/304921-excel-for-windows-...

jpeloquin 5y ago

> Am I insane here, or does Excel generally do a very good job of handling almost all CSV files. If the user does Data > From Text/CSV > Transform > use PowerQuery to set the first row as the header, I think it's true that Excel does a good job. It provides the basics at least: configurable charset and column type detection. When the user re-saves to CSV, I'm not aware of any way to configure the output (e.g., force quotation of text content), but that's sort of ok. The easy path—double clicking on a CSV file or using File > Open—is where all the weird auto-conversion of values happens. But other posts have covered that part.