News for hackers. Stuff that matters.
Re:Excel and SQL by dergachev on Thursday May 23, 2013 @06:31PM (#5759949)
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!

Re:Excel and SQL by Someone on Thursday May 23, 2013 @07:09PM (#5760147)
"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.

Re:Excel and SQL by Groxx on Friday May 24, 2013 @03:13AM (#5761593)

  $ 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.
Re:Excel and SQL by redman25 on Thursday May 23, 2013 @07:08PM (#5760141)
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.
Re:Excel and SQL by kyllo on Thursday May 23, 2013 @08:33PM (#5760508)
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.
Re:Excel and SQL by Glyptodon on Thursday May 23, 2013 @08:02PM (#5760375)
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.
Re:Excel and SQL by mtdewcmu on Thursday May 23, 2013 @09:48PM (#5760787)
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.