Hacker News Reader

Hacker News

Comment by codeulike | Hacker News Reader

Full thread

codeulike

Real-world CSV files generally contain some or all of the following horrors:

- some strings enclosed in speechmarks, but some not

- empty fields

- speechmarks within strings

- commas within strings

- carriage returns within strings

How does Q do up against a CSV file with those traits?

View on HN

All of your “horrors” seem...correct? Its comma delimited, so anything that is between two commas should be parsed without issue; if it’s a string with a comma in it, and unquoted, you simply have a broken csv file. If its quoted, than anything until the next (unescaped) quote is fine, including commas

Unless you’re trying to parse csv files with regexes, none of those should be difficult, or even unexpected, to handls with a PEG parser, or any equivalent device

Ofc if you’re accepting ambiguity then its just arbitrary how you handle it, but none of your examples afaict present any ambiguity (I’m assuming strings are either quoted or unquoted, with the former primarily allowing commas/newlines in strings; escaping exists as well; comma delimited columns, newline delimited rows)

Yes, it would be valid CSV. I suppose my point is that naive attempts to roll-your-own CSV parsers tend to fail on the points I listed. Hopefully Q does not do that.

They do? Commas and quotes are the two basic features of CSV, so it seems very strange to forget to implement half.

There is a lot of inconsistency out there. I have seen csv files saved in Excel not be import-able by Access because the latter doesn't handle breaks in fields correctly. I've seen csvs saved from various systems such as sql mngmnt studio grid view and wufoo exports not generate csv correctly. There are many lazy attempts at csv generators out there that just throw breaks between records and commas between fields and call it a day.

And even if they do wrap all fields in double quotes it is very common to forget to escape double quotes in fields, then it depends on the parser as to whether it can determine the proper structure of the record.

If you need to read to import CSV from someone else, there are tons of ambiguities. Do you interpret an empty field as an empty string or a NULL? If you've treated a column of unquoted digits as numbers so far, do you parse the first row with a non-number in that column as a NaN, NULL or string? If string, do you reinterpret all the previous column values as strings?

Many people are not in the position to just return the file to the client/boss and tell them they have a "broken csv file". (They'll tell you they saved it in Excel and it reads back fine, so the problem must be on your end. E.g.: https://stackoverflow.com/questions/43273976/escaping-quotes...)

More interesting: any kind of delimiter, including chars from utf8 and windows-1252, and you need to detect encoding too. And CSV embedded in CSV, a result of flattening an XML source. And fixed width files, not CSV but where you see CSV you may need to support. And let's not get into date parsing or other typed data, and type inference over sample files.

Hi, q's creator here,

Any kind of input/output delimiter is supported (-d <delim> and -D <delim>), and also multiple encodings (-e <encoding>). Also, q performs automatic type inference over the actual data.

Encoding autodetection and fixed width files are not supported though.

The company I work for also does delimiter autodetection, quote character inference (from a limited set), and encoding inference (which is mostly limited to utf8 / windows-1252 / iso-8859-15, but it can't reliably differentiate latter two).

Without diving into the source code I can only say Q pops up a couple times a year in either as posts or in cli recommendation threads so I suspect it's at least reasonably robust.

For those reasons I much prefer tab-delimited files. Does anyone know if Q supports that?

q supports any kind of input and output delimiter (-d <input-delim> and -D <output-delim> respectively).