Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Friends don't let friends export to CSV

kaveland.no

Post reply on HN
Print view View original

441–450 of 459 posts

Re: Friends don't let friends export to CSV

#441

Post by reportgunner »

unsupp0rted wrote: post #400
reportgunner wrote: post #389

Earlier quoted context omitted.

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 sa…

Wow! I had no idea you could set data format on legacy text columns during import. I had thought the column preview was just that- not a selectable radio button that you can then apply column data formatting to. Thanks for the instructions! It's cumbersome as heck, but it's better than nothing.

You can also access (pretty much) the same dialog via Data -> Text to Columns but you need to have some data already pasted in Excel.

Re: Friends don't let friends export to CSV

#442

Post by mrozbarry »

Use the right data format for the right data. CSV can be imported into basically any spreadsheet, which can make it appealing, but it doesn't mean it's always a good option.

If you want csv, considering a normalization step. For instance, make sure numbers have no commas and a "." decimal place. Probably quote all strings. Ensure you have a header row.

Probably don't reach for a CSV if:

- You have long text blobs with special characters (ie quotes, new lines, etc.)

- You can't normalize the data for some reason (ie some columns have formulas instead of specific data)

- You know that every user will always convert it to another format or import it

Re: Friends don't let friends export to CSV

#443

Post by cryptonector »

rkaveland wrote: post #261

Author here. I see now that the title is too controversial, I should have toned that down. As I mention in the conclusion, if you're giving parquet files to your user and all they want to know is how to turn it into Excel/CSV, you should just give them Excel/CSV. It is, after all, what end users often want. I'm going to edit the intro to make the same point there. If you're exporting files for machine consumption, pl…

> I see now that the title is too controversial, I should have toned that down.

Sometimes a click-baity title is what you need to get a decent conversation/debate going. Considering how many comments this thread got, I'd say you achieved that even if sparking a lengthy HN thread had never been your intent.

Re: Friends don't let friends export to CSV

#444

Post by resource_waste »

alexvoda wrote: post #438
resource_waste wrote: post #386

Earlier quoted context omitted.

Whatever situation got you into the "Power" universe was bad. Warning you about M$, you will soon be an enterprise dev.

I am not in that universe, on the contrary I try to stay as far away as possible. And I agree with you. However I think the everything-done-in-OLD-Excel universe is worse. Some people will be terminally stuck in Excel but at least they can use new Excel capabilities instead of being stuck with the Excel of 20 years ago. So why remain stuck importing CSVs into Excel when you can use Power Query to import Parquet. Why…

Sorry man, I can't think of a case where I'd import a CSV into excel, but have the skill level to use powerquery and import parquet.

Like, if you are going to use power query, why not just python? At least this way you arent going to get nailed into a legacy hellhole.

Re: Friends don't let friends export to CSV

#445

Post by zachmu »

askvictor wrote: post #271
TimTheTinker wrote: post #204

Earlier quoted context omitted.

I think his arguments apply more closely to SQLite databases. They're not directly human readable, but boy are there a lot of tools for working with them.

We have a use case where we effectively need to have a relational database, but in git. The database doesn't change much, but when it does, references between tables may need to be updated. But we need to easily be able to see diffs between different versions. We're trying an SQLite DB, with exports to CSV as part of CI - the CSV files are human-readable and diff'able. It's also worth noting that SQLite can ingest CS…

Somebody already said this, but we built exactly this and it's called Dolt.

https://github.com/dolthub/dolt

Would love to hear how it addresses your use case or falls short.

Re: Friends don't let friends export to CSV

#446

Post by smcin »

culebron21 wrote: post #412
smcin wrote: post #358

Earlier quoted context omitted.

> for geospatial data... GeoPackage was "the Format of the Future" 8 years ago What's the current consensus? Can you link to a summary article? (Some still say GeoPackage is: https://mapscaping.com/shapefiles-vs-geopackage/ )

I'd say compared to Shapefile, it is indeed better in every aspect (to begin with, shp has 8-character column names limit). For some kinds of data and operations GPKG is superior to other geo-formats. Like 1) store a lot of data, but retreive within an area (you can set an arbitrary polygon as a filter with GDAL driver, IIRC), 2) append/delete/modify and have the data indexed -- with CSV here you'll have to just repr…

What's the one-line on that: there is consensus that Shapefile is on its way out but no consensus on its successor, not GeoPackage or anything else? Where can we simply see as of today, what % of geospatial files in use are Shapefile, GeoPackage et al??

(I tried to estimate from references to formats on https://gis.stackexchange.com/ but it just gave me a headache.)

Re: Friends don't let friends export to CSV

#447

Post by outop »

nmz wrote: post #396
outop wrote: post #216

Earlier quoted context omitted.

The vast majority of CSVs do not have strings which include either quotes or newlines. No CSV I have ever encountered has comments.

Google contacts does.

It seems that Google generally try and find ways of making exporting their data to use in other services as painful as possible.

Re: Friends don't let friends export to CSV

#448

Post by outop »

eviks wrote: post #236
outop wrote: post #216

Earlier quoted context omitted.

The vast majority of CSVs do not have strings which include either quotes or newlines. No CSV I have ever encountered has comments.

So you're fine with a lot of bugs in the case of a vast minority?

Well, most code that loads CSVs is intended to work with certain files from certain sources, and not with all the CSVs that have ever existed.

So yes, I am happy with code that works for a subset of files. There are thousands of applications which work with CSVs and they all do exactly this.

Re: Friends don't let friends export to CSV

#449

Post by averms »

SAI_Peregrinus wrote: post #380
fredguth wrote: post #25

Earlier quoted context omitted.

What is the encoding of the text file? UTF8, windows-1252? What is the decimal delimiter “.”, “,”? Most csv users don’t even know they have to be aware of all of these differences.

The main issue is that "CSV" isn't one format with a single schema. It's one format with thousands of schemas and no way to communicate them. Every program picks its own schema for CSVs it produces, some even change the schema depending on various factors (e.g. the presence or absence of a header row). RFC 4180 provides a (mostly) unambiguous format for writing CSVs, but because it discards the (implied) schema it's…

> RFC 4180 provides a (mostly) unambiguous format for writing CSVs,

What are the ambiguities in RFC 4180?

Re: Friends don't let friends export to CSV

#450

Post by eviks »

outop wrote: post #448
eviks wrote: post #236

Earlier quoted context omitted.

So you're fine with a lot of bugs in the case of a vast minority?

Well, most code that loads CSVs is intended to work with certain files from certain sources, and not with all the CSVs that have ever existed. So yes, I am happy with code that works for a subset of files. There are thousands of applications which work with CSVs and they all do exactly this.

and those thousands of apps bug because they exist in a reality where few CSVs from certain sources can have different formats, not in the fantasy world where intention to work with certain files becomes a binding specification
Post reply on HN