I am trying to break out if good ol’ crappy bourne shell scripting so I am trying a few scripting languages that people are obsessed with. I briefly tried Python, but it was too unfamiliar to me and I wasn’t getting anything done. Now I am trying Ruby.
At Polytechnic, the course scheduling is done using using Excel spreadsheets that are emailed around. This… um… leaves a lot to be desired. So I figure the best way to shed some light on this process is to start scripting the existing ways of working to show people that computers and make things better.
Since I am working with Excel spreadsheets, I should be directly scripting Excel directly. But I am on a Mac, and the required win32ole lib is not there by default. So I have converted the Excel doc to Comma-Separated Value (CSV) format, since it’s a widely understood format.
But it wasn’t that easy. Apparently, Excel considers commas within double quotes to be escaped, while Ruby’s CSV lib freaks out when it encounters this. Here’s an example:
DM ,6043,A,4,,MEDIA ORGANIZATION,”Skelton, Carl”,,3:00 PM
I got this lovely stacktrace from Ruby:
/usr/lib/ruby/1.8/csv.rb:639:in `get_row’: CSV::IllegalFormatError (CSV::IllegalFormatError)
from /usr/lib/ruby/1.8/csv.rb:556:in `each’
from /usr/lib/ruby/1.8/csv.rb:531:in `parse’
from /usr/lib/ruby/1.8/csv.rb:311:in `open_reader’
from /usr/lib/ruby/1.8/csv.rb:85:in `open’
from /Users/hans/scripts/csv-to-ical.rb:27
Removing the ” double quotes fixed that:
DM ,6043,A,4,,MEDIA ORGANIZATION,Skelton, Carl,,3:00 PM
But that leaves a unpleasant taste in my mouth. I thought Ruby was supposed to be amazingly easy and productive. This is not a good sign… Unfortunately, the fun is not over. I removed all ” double quotes, yet I still get the same stacktrace error. Hmm…
it seems to be related to the text format, but that’s strange. As far as I can tell, the text files are in Mac format. It would naturally be better as a UNIX format. But this doesn’t strike me as something that should trip up a CSV parser. Marg… so I converted the Mac OS X linefeeds to UNIX style, and it works. Here’s the line of perl I used:
perl -p -e ’s/\r/\n/g’ unix.csv
Another option is to tell Ruby to expect Mac linefeeds (\r):
CSV.open(‘macosx.csv’, ‘r’, ‘\r’) do |row|
From what I read, Ruby automatically deals with UNIX linefeeds (\n) and Windows linefeeds (\r\n), but apparently not Mac linefeeds (\r). This blog post covers the issue and has some example code snippets:
http://railsforum.com/viewtopic.php?pid=13154
Actually, after all this, I was wrong about the ” quotes. Ruby handles them fine. It was all about the linefeeds \r crap…
