Hacker News Reader

Comment by byroot | Hacker News Reader

I don't think I ever seen an Excel / CSV import implementation that wasn't a huge mess.

Excel imports with Perl did pretty well for me. I was pretty careful on insisting on some rules for the sheet data and enforced them strictly with decent debugging info for the users.

I still think Robustness principle[1] is a croc and strictly controlling inputs is one key to happiness. It also, frankly, helps your users in the long run by giving them exactly what they want and it actually cuts down on the amount of thought they have to put into it. Chaos and disappointment do not make a good user experience.

1) https://en.wikipedia.org/wiki/Robustness_principle

Ruby has had good ETL libraries for a long time. In my opinion, our product team was too lenient concerning the format of the Excel files. Asking customers to fill out a template spreadsheet to submit to our system, rather than letting them submit any old XLS file they happen to have on their computer, would have gone a long way towards simplifying the problem space.

Do you mean Excel to CSV? Or a CSV importer?

I totally agree about Excel importing, but CSV is trivial, no? Here is an Erlang version I happened to write yesterday:

  lists:map(
    fun(Row) -> string:tokens(Row, [SepChar]) end,
    string:tokens(InputStr, "\n")
  ).

EDIT: I know this version won't support escaped separator/newline characters, but I made it for a specific use case in which I knew that would not occur. Adding that functionality would make it a little messier, but still not too bad.

EDIT2: Thanks for the interesting comments! Not so trivial after all!

Perhaps a more accurate version of what I was attempting to say above is that 'it is often (not always) easy to build a CSV parser to interact with one specific program'. The four line version above works perfectly for reading the type of files I designed it for. If you want to work with human created, or more complex variants of CSV, all bets are off.

You need a lot more than that to handle CSV in the wild (quoting, Unicode, line termination, etc.) but the real killer I see is when it's edited by humans. The special cases for errors and inconsistencies will add up quickly; in some cases you may be able to reject invalid data but you may not have that option or an easy way to tell whether any particular value is wrong.

Excel takes that, adds some fun things like people using color and formatting to store data, and things like Excel auto-corrupting values which look like dates and may not have been noticed before you do something with the data.

That is very interesting, thanks! I hadn't thought about Unicode or tolerating human error. Although the times I have worked with it have been when it is a transport medium between two computer programs.

That's definitely a less-aggravating situation by far. I've had a lot of cases where a significant amount of specialist human time was in a spreadsheet and it's really made me wish there was an Excel-for-data which acknowledges how many people are using it for semi-structured data like this.

Like Airtable?

I know of at least one company whose entire business is handling this stuff. They find growing companies as they hit critical mass and need to move their Excel data into a real database. The product is just "Your data is hideous and was entered by hand without validation or formatting; it'll never convert and it'll be wrong when it does. We can help."

They handle all kinds of theory and technical stuff, like normalization and processing Excel-corrupted dates. But they also handle a lot of easy-but-agonizing tasks like regularizing single quotes into apostrophes, which crop as soon as you let humans enter free-form data.

Could you share the company name?

I'll try to remember. I ran into them at a career fair a few years ago, so it's not leaping to mind, but it seemed like they had good software and a great market niche.

I used to use Google Refine (now OpenRefine [0]) for this. It lets you load up the data and then apply rules to see if they are mostly correct. It doesn't get you all the way, but it is better than going blind on manually revising a huge Excel "database".

[0] http://openrefine.org/

What do you use now?

Let's not forget Japan Post's CSV for all the Japanese Address data that contains some lines that are line-wrapped, that is, one record spans two or more lines in the CSV file. A line-wrapped CSV... I just can't even.

That's why ASCII was designed with record and field separators. Unfortunately, it's not used (de facto) for delimited files.

> but CSV is trivial, no?

You can define a narrow subset or version of CSV that is trivial, but that doesn't reflect what one finds in the wild as "CSV", which was not systematically defined or described until well after many mutually incompatible things by that name.wdre well established.

I'm not talking about parsing. It's a mess in it's own right of course (encoding, line terminators, etc as others mentioned).

I'm talking about the actual conversion from tabular data to relational. Most of the applications I've worked on had this in one form or another.

So you end up with users downloading an export of their data in CSV, editing it in Excel in various ways, and then reimporting it in the application.

Every company I worked for, this kind of feature was always in the top 3 in term of support load.

Ah right, yes, I can imagine that would be extremely messy!

"Relational" means "tabular". (A "relation" in relational theory is a table with a name, fields with names and types, and the data in the table.)

A "relationship" in an ER diagram maps to a "reference" in relational theory. This is part of the type safety/domain system of RDBMSs.

If these concepts are muddled, SQL will never quite make sense :)

> "Relational" means "tabular".

Relational database can be expressed in tabular form, but tabular data is not necessarily relational.

> (A "relation" in relational theory is a table with a name, fields with names and types, and the data in the table.)

A relation is a system of one or more functions (in the mathematical sense) each of which has a domain that is a candidate key of the relation and a range that is the composite of the non-key attributes.

Interesting definition. Do you have a source for it. It seems ambiguous.

From the Wikipedia article on relational databases, subsection relational model. "This model organizes data into one or more tables (or "relations") of columns and rows, with a unique key identifying each row. Rows are also called records or tuples."

CSV is trivial? You may have missed this:

    http://tburette.github.io/blog/2014/05/25/so-you-want-to-write-your-own-CSV-code/

:-)

This is interesting, and as the other commenters have pointed out, creating a parser for /all/ variations of CSV can be very tricky.

But your code doesn't even handle the trivial case.

    "She said, \"Hello, world!\""

You can drop the "meh, I know I didn't handle all the complicated cases" act.

We all recognize the classic developer I-could-build-that-in-a-weekend hubris when we see it. :)

Hi,

Thanks for your thoughts. As I have stated elsewhere, the code handles all of the cases I needed it to handle, due to the stability of the input file format (which was emitted from another program). I don't see that this should be too hard to believe.

I also said in my second edit, on the top line, 'Not so trivial after all!'. If I was putting on some kind of act, wouldn't that have been dropping it? Further, I noted in my first edit, before I had received any replies, that I 'know this version won't support escaped separator/newline characters', so I am not sure what you were trying to add with your example?

I think that my central point (and I totally accept that I didn't express this well) is that depending on the specifications of your program, the required CSV parser /can be/ very short. When one compares this to other data exchange formats, for example JSON, it is clear that the barrier to /entry/ is much lower. The shortest JSON parser I could find with a cursory look was 200 lines of C.

I totally appreciate that to write a CSV parser that works for all cases would be extremely longwinded. It has been interesting to hear other people's experiences and opinions about that. But the fact remains true that /in some cases/, depending on the requirements of the program, the parser can be very short.

> We all recognize the classic developer I-could-build-that-in-a-weekend hubris when we see it. :)

It is funny you should say this. I needed the CSV parser because I thought it would be fun and interesting to see if I could build an anti-malware tool in a week (I am taking a malware detection class at the moment, I wanted it done before the next lecture). I did not expect I would be able to have anything good working in that time, but by the early hours of the next morning I had a perfectly functional anti-malware tool. It can use ClamAV signatures (so it can detect everything(?) that ClamAV can), runs in parallel, has a nice text console with DSL, and is fast enough (processing 210k small files in ~5 minutes, checking against ~60k sigs). It is about 650 lines of Erlang (including comments). I am saying this not to boast(!), but to make the point that I greatly underestimated how productive I could be, beat my expectations by many fold, then people comment about my hubris online the next day. It is funny how life goes!

Thanks,

Sam

Your edit is delightful.

"No, honest guys, I knew CSV was more complicated. I just didn't need to make my code safe."

Here's a csv parser in Erlang that actually attempts all that trivial stuff:

https://github.com/rcouch/ecsv/blob/master/src/ecsv_parser.e...

That's a lot more code than yours. And the notes even say it's not tolerant of badly formed CSVs.

I submitted that edit before the post had any replies.

Also, I did try to make clear that the given code was created 'for a specific use case in which I knew' that the format of the input files was tightly defined.

Reply on news.ycombinator.com