6 ms·
Real-world CSV files generally contain some or all of the following horrors: - some strings enclosed in speechmarks, but some not - empty fields - speechmark
by codeulike 8y ago
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?
- setr 8y agoAll 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)
- codeulike 8y agoYes, 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.
- Dylan16807 8y agoThey do? Commas and quotes are the two basic features of CSV, so it seems very strange to forget to implement half.
- 83457 8y agoThere 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.
- 83457 8y agoAnd 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.
- deleted 8y ago[deleted]
- em500 8y agoIf 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-and-delimiters-in-csv-files-with-excel https://stackoverflow.com/questions/43273976/escaping-quotes...)
- badcircle 8y agoPowerShell will clean up a gross CSV: Import-CSV .\file.csv | Export-CSV .\file.csv -NoTypeInformation -Encoding UTF8
- barrkel 8y agoMore 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.
- harelba 8y agoHi, 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.
- barrkel 8y agoThe 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).
- snazz 8y agoFor those reasons I much prefer tab-delimited files. Does anyone know if Q supports that?
- harelba 8y agoq supports any kind of input and output delimiter (-d <input-delim> and -D <output-delim> respectively). harelba (creator of q)
- Avshalom 8y agoWithout 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.
- samatman 8y agoJust this weekend was filtering commas out of unquoted dollar values in a CSV