Hacker News Reader

Comment by dergachev | Hacker News Reader

Excel can easily export CSV files, which are easy to parse. However, standard Linux tools aren't great for working with CSVs, especially ones with multi-line content (eg exports from a CMS).

As an experiment, I wrote a quick ruby script called csv2sqlite which parses one more CSV files (and their headers), and automatically populates an SQLite database based on the CSV.

If you have a CSV and want to easily know how many records it has, or to filter or join these records, it can be just a matter of running something like following:

ruby ~/csv2sqlite/csv2sqlite.rb baby-names-10.csv --output babynames.db

sqlite3 babynames.db "SELECT * FROM baby_names_10 WHERE percent > .05;"

Hope it helps you!

View on HN

"Excel can easily export CSV files, which are easy to parse"

'CSV' and 'easy to parse' do not go together that well http://en.wikipedia.org/wiki/Comma-separated_values#Toward_s... also is instructive:

Nevertheless, RFC 4180 is an effort to formalize CSV. It defines the MIME type "text/csv", and CSV files that follow its rules should be very widely portable.

[...]

Each record "should" contain the same number of comma-separated fields.

[...]

Fields containing a line-break, double-quote, and/or commas should be quoted.

[...]

The format is simple and can be processed by most programs that claim to read CSV files.

  $ irb
  >> require 'csv'
  # => true
  >> CSV.parse(file)

^ works every time I've tried it. Systems which export mangled data to CSV probably do so elsewhere, so CSV isn't special there, and CSV is really really simple to escape well enough that any decent parser won't have any problems at all.

Excel's CSV export has a lot to be desired. From what I've tried, it doesn't export data in Unicode and exports non-standard CSV that many parsers choke on.

Excel exports CSV in UTF-16 with byte order mark (BOM). Very annoying if you're sending the data to another application that is expecting UTF-8.

I've had pretty straightforward success with Python's built in CSV parsing. Not sure what you mean by "Linux tools aren't great for working with CSVs" exactly. I guess maybe Python's not a "Linux tool," but it's sure easy to use for quick stuff from the shell.

I imagine he means things like awk, sed, sort, etc., that are standard unix tools. The newer versions of gawk have extensions that are designed to help handle CSV files, but, unfortunately, Arnold Robbins opted to make it "some assembly required." Given the fiddlyness of parsing CSV files and their ubiquity, hard-coding a CSV mode probably would have been a better solution.

Reply on news.ycombinator.com