It is weird to say both that "CSV files have terrible compression" and then that the proposed format, Apache Parquet, has "Really good compression properties, competitive with .csv.gz". I think what's meant here is that csv compresses really well but you loose the ability to "seek" inside the file.
Friends don't let friends export to CSV
381–390 of 459 posts
Re: Friends don't let friends export to CSV
#382"There's a better way" - "just" write your application in Java or Python, import Thrift, zstandard and boost, do some compiling - and presto, you can now export a very complicated file format you didn't really need which you hope your users (who all undoubtedly have Java and Python and Thrift and whatnot) will be able to read.
CSV does not deserve the hate.
Re: Friends don't let friends export to CSV
#383Earlier quoted context omitted.
And then you get a business analyst at your client going "I just hit export in our internal tool, what's this delimiter that you're asking me about in the upload form? Google Sheets doesn't ask me to tell them that"
And how do you fix the “some analyst” problem? Is there a better format that reduces this problem?
JSON? RON? Protobuf? Cap'n'Proto? Anything with more types than "string" which is all CSV has, and with an unambiguous data encoding. Preferably also a way to transmit the schema, since all of these formats (including CSV) have a schema but don't necessarily include it in the output.
About half the problems with CSV are due to encoding ambiguities, the other half are due to schema mismatches.
Re: Friends don't let friends export to CSV
#384Earlier quoted context omitted.
excel now has a prompt so you can tell it to not convert stuff automatically
Gasp. Big news. I do not recall ever seeing this, so I wonder if $JOB is running some hilariously outdated version for compatibility with a load bearing VBA script.
Automatic Data Conversion toggle was only added in the past ~year: https://insider.microsoft365.com/en-us/blog/control-data-con...
Re: Friends don't let friends export to CSV
#385new line seperated json "JSONL"
Re: Friends don't let friends export to CSV
#386Earlier quoted context omitted.
> in the real world the spec is essentially "Can you import it to Excel?" And the answer to that is always no. You will it think it's yes because it works for you, but when you send it to someone who has a different Excel version or simply different regional settings, it won't work. The recipient will first have to figure out what dialect you used to export.
On the other hand you can now use Power Query to import perquet data into Excel.
Whatever situation got you into the "Power" universe was bad.
Warning you about M$, you will soon be an enterprise dev.
Re: Friends don't let friends export to CSV
#387Earlier quoted context omitted.
To me this criticism feels excessive. It feels like the author is describing their frustrations with internal usage of CSVs - there's no mention of customers and non-technical stakeholders at all. I think it goes without saying that Parquet files and other non-human-readable formats are a nonstarter when working with external stakeholders and the last paragraph makes that clear - if the end-user wants CSV, give them…
I deal with gig size csvs all the time and don’t have any performance issues. These aren’t huge files, but decent sized. And most are just a few megs and only thousands to millions of records. Csv is not very performant, but it doesn’t matter for these use cases. I’ll also add that I’m not working with the csvs, they are just I/o. So any memory issues are handled by the load process. I certainly don’t use csvs for my…
That may be your experience, but certainly not a universal experience (and apparently not the author's, either). In my experience, it's pretty easy to have CSVs (or Parquet files, or whatever) that are tens or hundreds of GBs in size. The space savings from a more modern file format are significant, as is the convenience of being able to specify and download/open only a subset of rows or columns over the network. Most of us don't have workstations with 50GB of RAM, because it's far more cost-effective to use a Cloud VM if you only occasionally need that much memory.
That being said, the real point here is that folks blindly use CSVs for internal-facing processes even though there's no particular reason to, and they have plenty of drawbacks. If you're just building some kind of ETL pipeline why wouldn't you use Parquet? It isn't as if you're opening stuff in Excel.
Re: Friends don't let friends export to CSV
#388Here is a crazy idea: So csv itself is abiguous, but as a convention we could encode the options in the file name. E.g data.uchq.csv means utf8, comma-separated, with header, quoted.
[deleted]
Re: Friends don't let friends export to CSV
#389I wish I could get Excel to stop converting Product UPCs to scientific notation when opening CSVs. Also some UPCs start with 0 Worst is when Excel saves the scientific notation back to the CSV, overwriting the correct number.
1. Open a blank workbook
2. Enable the legacy Text Import Wizard as per [0]
3. Go to Data -> Get Data -> Legacy Wizards -> From Text (Legacy)
4. Set config based on your CSV file, typically select "Delimited" and "My data has headers" enabled
5. Click Next and pick the delimiter, typically "Comma"
6. Click Next and click the columns with UPCs, select "Text" in the "Column data format" area
7. Click Finish
(I'm not saying this is great, just sharing how to do it in case you don't know)
[0] https://professor-excel.com/import-csv-text-files-excel/
edit: you can also set up a PowerQuery query that will always open some CSV at some path and apply this config, but I don't want to have anything to do with PowerQuery, sorry.
Re: Friends don't let friends export to CSV
#390Earlier quoted context omitted.
Now all your numbers are strings
It's a text file. All your numbers were already strings. Nothing has changed.
But now all our strings might be numbers! We now have to parse every quoted string, and we can no longer represent numbers as text.
unquoted input:
0, 10, "Text", "123"
unambiguous output:
(Num) 0, (Num) 10, (Text) Text, (Text) 123
quoted input:
"0", "10", "Text", "123"
output :
(Num) 0, (Num) 10, (Text) Text, (Num) 123