Parsing a tab delimited file

[sanjay manchiganti]

Hello All -

I'm using the SmarterCSV gem to parse a tab delimited .txt file with a header, which works great in a basic scenario. 

The issues I'm running into is when trying to parse a tab delimited file which has some formulas in it.  The file itself works great when we manually try to open it in Excel or Open Office, but while parsing it using a parser is when I'm running into issues.  I tried using the spreadsheet gem to open and parse it, but since it is a .txt file the Excel processor complains about OLE elements not being present. 

So, in short, for Excel processor the file is not a Excel file, but for a tab delimited processor the formulas are not good.

A sample entry in the file is below

=if(c2=c1,  b2- b1,"")    2647256    1810023108         26472564    2    05/01/13    00:04:20    PCssE S LN   OCBBN RF EL 337    PC10 5TH HOUSE S LN         W RES              WTR5               1.00000    6020
=if(c3=c2,  b3- b2,"")    2649479    1810023108         26494795    2    05/01/13    23:54:56    PC10 5TH E S LN   OCEAN RE FL 33097    PC0 5TH HO S LN         W RES              WTR5               1.00000    6020

Any suggestions / ideas on how to solve this issue is highly appreciated.

Thanks,
Sanjay.

[Robert Di Marco]

That's crazy.  What did you do to offend the Greek god of Demented Data Formats that you are so cursed with this?  I wonder how this was even generated.

Anyhow, I don't think the spreadsheet gem or its ilk will be able to deal well with this, so a couple of options that pop to my mind:

1) Is the only formula you need to deal with the example in column 1?  If the logic of the columns is consistent (e.g. always looking to see if the current row value in the 3rd position equals the previous row's value in that position then do something), you could just do that manually in Ruby

2) If OpenOffice/Excel can handle it, can you run a pre-processing macro that automates the import of the files in to Excel/OpenOffice and then saving them in to a more manageable format.

3) Write your own parser :)

--
You received this message because you are subscribed to the Google Groups "Philly.rb" group.
To unsubscribe from this group and stop receiving emails from it, send an email to phillyrb+u...@googlegroups.com.
To post to this group, send email to phil...@googlegroups.com.
Visit this group at http://groups.google.com/group/phillyrb?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Maurício Linhares]

It would be simpler if you could provide a piece of the real file
(like uploading a piece of the file somewhere) since this this one you
have pasted in your email doesn't look like it was tab separated.

Also, the "" at the formulas make this CSV file invalid, as they
should have been escaped. In any case, if the file is really separated
by tabs and always looks like this one you have pasted, you can easily
get away by parsing it manually:

lines = []

IO.foreach( "formulas.csv" ) do |line|
lines << line.split('\t').map { |v| v.strip }
end

puts "lines are:"
lines.each do |line|
puts lines.join(" ! ")
end


-
Maurício Linhares
http://mauricio.github.io/ - http://twitter.com/#!/mauriciojr