Consider Using CSV

Post by MrJohz » Sat, Dec 10, 2022, 9:31 PM UTC

zem wrote: post #69

chaps wrote: post #66

Earlier quoted context omitted.

Oh yes. CSVs are deceptively challenging especially if your use-case is from excel files to csv. Excel will happily convert a worksheet to csv, but it's a naive conversation. Headers that start on line 3, multi-line headers, inconsistent column counts, etc. It adds up really quickly!

I've also run into issues where I wrote some code that worked with csv input, and told users they could just export their data from excel. turns out excel doesn't export in utf-8 by default, we had some weird issues until we figured that out.

Post by hermitcrab » Sat, Dec 10, 2022, 10:11 PM UTC

elcritch wrote: post #31

fatneckbeardz wrote: post #24

Earlier quoted context omitted.

but which delimiter. if you choose pipe ok, now you have to make sure nobody typed a pipe into the input field or spreadsheet, and you cannot store unix commands if you choose tab, ok, now people will get confused when they try to edit the text file to replace tabs with spaces, and now you have trouble putting code snippets into data fields because they have tabs. this is the problem and it's why xml/json exist. in m…

Well the obvious solution would be ASCII 0x1D (Group Separator)! Accept, no one actually uses those ASCII characters. Kind of bums me out that UNIX basically skipped out on them.

Post by wenc » Sat, Dec 10, 2022, 10:18 PM UTC

scrollaway wrote: post #30

CharlesW wrote: post #10

Earlier quoted context omitted.

Yes, I feel like this would've been more helpful generalized as "Consider DSV" (delimiter-separated values) than CSV specifically, because of the interop issues that often come up. I'd have also mentioned using Parquet.

Parquet has the opposite problem of CSV though. It's so complex to work with, that unless you're specifically in data science, it's both unheard of and unusable. To read a parquet file in Python, you need Apache Arrow and Pandas. And literally the second result for "parquet python libraries" is an article titled "How To Read Parquet Files In Python Without a Distributed Cluster". I remember dealing with Parquet file…

    import duckdb
    df = duckdb.query("select * from 'a.parquet'")

    vd a.parquet

   import pandas as pd

   # output to CSV
   pd.read_parquet("a.parquet").to_csv("a.csv") 

   # output to JSON (choose from any number of orientations)
   pd.read_parquet("a.parquet").to_json(orient="table")

   # read rows
   for row in pd.read_parquet("a.parquet").itertuples():
       print(row)

Post by wenc » Sat, Dec 10, 2022, 10:23 PM UTC

fbdab103 wrote: post #34

scrollaway wrote: post #30

Earlier quoted context omitted.

Parquet has the opposite problem of CSV though. It's so complex to work with, that unless you're specifically in data science, it's both unheard of and unusable. To read a parquet file in Python, you need Apache Arrow and Pandas. And literally the second result for "parquet python libraries" is an article titled "How To Read Parquet Files In Python Without a Distributed Cluster". I remember dealing with Parquet file…

I want to use parquet more frequently, but it creates new problems that do not exist if I dump to CSV. Last I looked, there were not any good GUIs that would let someone quickly browse the data. Now it is just a blob lacking introspection. CSV has issues, but it is universal.

Post by hermitcrab » Sat, Dec 10, 2022, 10:27 PM UTC

_dain_ wrote: post #88

hermitcrab wrote: post #82

Earlier quoted context omitted.

Yes, CSV is superior to JSON for tabular data, but has it's own issues. One issue is that the standard is not consistently applied. Another is the approach to escaping means that it is hard to parse a CSV file with multiple threads. You have to parse the entire file before you can be sure which " characters escape other characters. I wrote an article about tabular formats and their strengths and weaknesses here: http…

>CSV is superior to JSON for tabular data Can't you just do this? { "columns": ["col1", "col2", "col3"], "data": [ [1, 2, 3], [4, 5, 6], [7, 8, 9] ] } That's valid JSON but it's human-readable and human-editable rows of comma-separated data, just like CSV.