3 ms·

I've had to deal with CSV data and Excel a lot in my career, and I learned one trick (sorry) a few years back that has made my life so much better: Here's a sc

by sbarre 9y ago

I've had to deal with CSV data and Excel a lot in my career, and I learned one trick (sorry) a few years back that has made my life so much better: Here's a scenario I bet many people have faced: Export an Excel sheet to CSV that has high-ASCII characters in it like accents. The export gets mangled when you load it into code or a text editor after. You eventually just upload it to Google Sheets and export it from there instead. It works but it's a pain. Instead of exporting it as a CSV from Excel, export it as a UTF-16 TXT, which is basically a TSV file. That will correctly preserve all the character encoding. I can't promise this will work 100% of the time but it has resolved many many encoding issues going to/from Excel.

masklinn 9y ago
My one trick there is to just not ask for CSV, and import Excel files directly. Most languages have libraries to read basic (non-formula) excel files, they work fine, and provide richer data models than CSV (though somewhat risky as you have to deal with formattings and finding e.g. numbers where you expected strings and the other way around).
- purple-again 9y ago
  This is unacceptable in large business settings today. A list of assets alone can be tens of millions of lines. A list of transactions? Oh boy time to break out Rust if you want your database to be finished this month.
  - masklinn 9y ago
    > This is unacceptable in large business settings today. A list of assets alone can be tens of millions of lines. A list of transactions? The original comment specifically mentioned excel. Excel can not work on "tens of millions of lines" and your objection is thus irrelevant. If your data does not come from excel, there is obviously no reason to make it go through excel. There is also no reason to make it go through CSV, make it go through an actual serialisation format which is not mostly unspecified garbage since you're programmatically serialising it for interchange and hopefully not expecting human edition of the intermediate megabytes of data dump.
  - maxerickson 9y ago
    Do those things start out as an Excel file? Because that was the suggestion, to avoid Excel->CSV headaches by importing Excel files directly. I don't think they meant to always use Excel for interchange.
  - ryanbrunner 9y ago
    Generally speaking though, the areas where the sorts of issues that the article talks about come up is from user-specified CSV mostly loaded from Excel. When you're dealing with enormous amounts of data coming from another (automated) system, you can often make assumptions that wouldn't be practical with user-supplied CSV, like assuming a particular encoding, delimiter, etc.
chubot 9y ago
Yes his other article said this too: https://donatstudios.com/CSV-An-Encoding-Nightmare https://donatstudios.com/CSV-An-Encoding-Nightmare As of this writing, there exists a single usable CSV format that Microsoft Excel can both read and write safely across platforms. Tab delimited UTF-16LE with leading Byte Order Mark. I work with CSVs in R, but I don't work with Excel that much. Thanks for the useful tip.

by sbarre 9y ago

masklinn 9y ago

My one trick there is to just not ask for CSV, and import Excel files directly. Most languages have libraries to read basic (non-formula) excel files, they work fine, and provide richer data models than CSV (though somewhat risky as you have to deal with formattings and finding e.g. numbers where you expected strings and the other way around).

purple-again 9y ago
This is unacceptable in large business settings today. A list of assets alone can be tens of millions of lines. A list of transactions? Oh boy time to break out Rust if you want your database to be finished this month.
- masklinn 9y ago
  > This is unacceptable in large business settings today. A list of assets alone can be tens of millions of lines. A list of transactions? The original comment specifically mentioned excel. Excel can not work on "tens of millions of lines" and your objection is thus irrelevant. If your data does not come from excel, there is obviously no reason to make it go through excel. There is also no reason to make it go through CSV, make it go through an actual serialisation format which is not mostly unspecified garbage since you're programmatically serialising it for interchange and hopefully not expecting human edition of the intermediate megabytes of data dump.
- maxerickson 9y ago
  Do those things start out as an Excel file? Because that was the suggestion, to avoid Excel->CSV headaches by importing Excel files directly. I don't think they meant to always use Excel for interchange.
- ryanbrunner 9y ago
  Generally speaking though, the areas where the sorts of issues that the article talks about come up is from user-specified CSV mostly loaded from Excel. When you're dealing with enormous amounts of data coming from another (automated) system, you can often make assumptions that wouldn't be practical with user-supplied CSV, like assuming a particular encoding, delimiter, etc.

chubot 9y ago

Yes his other article said this too: https://donatstudios.com/CSV-An-Encoding-Nightmare https://donatstudios.com/CSV-An-Encoding-Nightmare As of this writing, there exists a single usable CSV format that Microsoft Excel can both read and write safely across platforms. Tab delimited UTF-16LE with leading Byte Order Mark. I work with CSVs in R, but I don't work with Excel that much. Thanks for the useful tip.