Broken OpenOffice Java config

I've recently been working on a little Rails app that manages foreign exchange handling, and prints reports. I'm converting an existing Excel based app that does the same thing, and I needed to get data from a spreadsheet to use as test data. So I fired up OpenOffice.org Spreadsheet, it loaded it fine, and even had KDE file dialogs which is nice. I looked for an option the export to CSV (comma separated values), but there were only options for PDF and xhtml.

I thought, no problem I'll try xhtml and munge the output to get it into my test database. When I tried the export command I got a dialog with this text:

Oh dear, I have a 'defective' Java environment apparently. So I looked under the suggested menu option and found that I only have gcj on my machine. So fine, just apt-get install Sun's JRE and I'll be good to go. I installed the 105 Mb sun-java5-jdk package, and tried again. The new Sun runtime was in the options dialog so the Spreadsheet knew about it, and I selected it. When I tried the export again I got exactly the same message. How useless. Why doesn't the dialog tell me which version of Java is correct? Why does a large C/C++ based app need Java to do something simple like export to xhtml anyway? Where are all the other export options like CSV? Maybe a link in the dialog to somewhere with a downloadable compatible runtime might be handy.

So I gave up on OpenOffice and tried KSpread instead. It installed really fast, is only a couple of Mb, and has a whole boatload of export options including CSV. So that's one less user for OpenOffice and one more for KSpread.

The extracted data looks like this, a line of headings followed by lines of data:

"Fecha","Divisa","TipoES","Total Mani","Cambio","Contra Valor","C. Aplicada","Total ",,,
"2007-03-01","NZD","S","0","0.02404048","0","0","0","540.79069152",,"0.42687773"
"2007-03-02","GBP","S","400",,,"0.0644285","0.50857182",,,
"2007-03-02","L.V.","S","11,941","1.61071244","19,233.51724304","1.92335172","15.18214032",,,
...

I initially thought I'd just use the split method with comma as separator, on each line to get the values, but there were two problems with that. Firstly, I got strings with the double quotes in the them for each field, and worse there were commas in some of the numeric fields which messed up the split. After a little thought I came up with a cunning plan - just convert each line to executable ruby code and eval it, and the ruby interpreter can handle the parsing.

File.open("movimientos_detalles.csv") do |file|
  while line = file.gets
    line.chomp!
    line.gsub!(/(,)(?=,)/, '\1 nil')
    line.sub!(/^,/, 'nil, ')
    line.sub!(/,$/, ', nil')
    fields = eval "[#{line}]"
...
end
end

Contiguous commas in the CSV line are changed into nils separated by commas - '(?=,)' means look ahead for a comma, but don't count it as part of the match. Then add square brackets to create a ruby Array declaration and eval - perfect! Anyhow, the fun of doing that made up for all the hassle with failing to get OpenOffice to work.

-- Senor Dale Cerveza