How to prevent `ssconvert` recalculating Excel file before conversion?

Question

I am trying to convert the .xlsx file http://www.eia.gov/forecasts/steo/archives/mar14_base.xlsx into a .csv, but it seems that the .xlsx contains formulae that link to a local file that I don't have (the creator of the file must have forgotten to paste as values instead of as formula) so each time I use ssconvert it tries to recalculate the formula, which fails hence I can't get the data:

ssconvert --export-type=Gnumeric_stf:stf_assistant  -O "locale=C
format=automatic separator=, eol=unix sheet='3atab'" "STEO_m.xlsx"
"text.csv"

triggers the following message: (and the values inside the .csv are missing)

(/usr/bin/ssconvert:14771): GLib-GObject-WARNING **: g_object_set_valist: object class 'SheetObjectImage' has no property named `style' '7etab'!BM7 :
'VLOOKUP($A7,[1]oracle_allbbb!$2:$89130,Dates!BM$12+1,FALSE)' Invalid expression

I have seen that there is also a --recalc argument in your ssconvert function but actually I want to do the opposite!

ssconvert --recalc=FALSE --export-type=Gnumeric_stf:stf_assistant -O "locale=C format=automatic separator=, eol=unix sheet='3atab'" "STEO_m.xlsx" "text.csv"

Is there any piece of advice you could give me to find a solution here?

Propylite

I can't open the file in Open Office. Are you sure that this particular file is on the level? Have you tried other files? I've had success with other files using xlsx package. – Leprose

It seems it works with any other xlsx file indeed. On my side I managed to open this xlsx with Libroffice Cal. But it doesn't read properly when calling the gnumeric command line. Apparently they forgot to format properly the file this time, and they left a formula in each cell that link to a document not available. (this other document was on their local environment). I think we need here to tell gnumeric not to recompute the formula before converting to csv. I am still trying to find if there is such argument... – Propylite

ssconvert (called by gnumeric) has an argument --recalc but we want the other way round... – Propylite

I wonder if it would be possible to call OO through command line and ask it to save file as a csv? – Leprose

the R package 'gnumeric' is exactly doing that, but with gnumeric instead of OO: it converts the xlsx into a csv using gnumeric command 'ssconvert', then uses read.csv. See man.cx/ssconvert(1) The last time I checked, gnumeric had the most advanced command line function, but indeed theoretically we could do the same with OO or LibreOffice, provided they have the right command line tools. – Propylite

See #10557860 for all methods to convert xlsx to csv – Propylite

Answer 1

Apparently it was a bug. It is fixed here.

Propylite