Convert excel 2003 spreadsheet xml to any format xls or xlsx or ods

dansei · June 27, 2020, 4:10pm

Team,

I have a 2003 excel spreadheet excel.

I need to convert this eventually to excel for parsing.

I have looked at many links and have tried to run soffice cmds.

The xml file can be opened manually bia libreoffice calc and saved to another format.

HOwever, I need to do this automatically via headless for data processing

Any ideas here?

LInks I referrred to

https://cgit.freedesktop.org/libreoffice/core/tree/filter/source/config/fragments/filters

cmds I have run, all render an empty spreadsheet.

MS Excel 97
soffice --headless --convert-to ooxml:“Calc Office Open XML” prefcardPositionExample.xml

soffice --headless --convert-to ods prefcardPositionExample.xml

soffice --headless --convert-to xls prefcardPositionExample.xml

soffice --headless --convert-to xlsx prefcardPositionExample.xml

prefcardPositionExample.xls

erAck · June 27, 2020, 4:25pm

So what does the file contain? Anything that would require the GUI?

dansei · June 27, 2020, 4:28pm

trying to attach the file now.

dansei · June 27, 2020, 4:31pm

I added the file in the last line of the original post.

Not sure why is says fakepath and I had to change the extenstion to xls in order for it to be uploaded.

It is xml.

anon73440385 · June 27, 2020, 5:00pm

Which operating system are your working on?

`libreoffice6.4 --convert-to ods prefcardPositionExample.xml` provides the following result for me: [prefcardPositionExample.ods|attachment](upload://uBSS1eHzc1oepQtrqyoCkVE7xxu.ods) on Linux openSUSE 15.1 using LibreOffice 6.4.4.2 (from TDF packages), which isn't empty at least (can't comment on the content). All other conversions provide a file with content as well.

dansei · June 27, 2020, 5:09pm

I am running the below

$ soffice --version
LibreOffice 6.0.7.3 00m0(Build:3)

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.4 LTS
Release: 18.04
Codename: bionic

dansei · June 27, 2020, 5:12pm

I suppose I could run your stack in docker ( or try to upgrade my libreoffice )
to do the parsing and export it to a storage location.

dansei · June 27, 2020, 5:12pm

Your output looks good.

dansei · June 27, 2020, 5:50pm

I updated my libreoffice and still get blank file output

$ libreoffice --version
LibreOffice 6.4.4.2 40(Build:2)

(base) chris@chris-Precision-5530:~/Downloads$ libreoffice --version
LibreOffice 6.4.4.2 40(Build:2)

$ soffice --convert-to ods prefcardPositionExample.xml
convert /home/chris/Downloads/prefcardPositionExample.xml → /home/chris/Downloads/prefcardPositionExample.ods using filter : calc8
Overwriting: /home/chris/Downloads/prefcardPositionExample.ods

anon73440385 · June 28, 2020, 2:35pm

Could reproduce your problem on Ubuntu 18.04 - regardless of using LibreOffice from base repos (6.0.7), PPA repos (6.4.4.2) or TDF packages (6.4.4). The resulting .ods file shows no content.

In addition: Same problem seems to appear in Ubuntu 20.04 (tested LibreOffice 6.4.3.2 from base package repository). I tend to assume a general problem on Ubuntu itself.

On Ubuntu can’t even load the .xml file into Calc. Hence it seems the problem to start at import already.

erAck · June 30, 2020, 4:29pm

Hooray for Ubuntu.

The attached XML is the awkward Excel 2003 XML file format to be read either (old way) by an even more dreaded XSLT filter to import to LibreOffice, or (new way) using liborcus. Seems Ubuntu lacks a piece to make that work. It’s odd that even the TDF provided build fails on Ubuntu.

anon73440385 · June 30, 2020, 5:23pm

Hello,

the following command works for me on Ubuntu 18.04 and 20.04

soffice --headless --infilter="MS Excel 2003 XML" --convert-to ods prefcardPositionExample.xml

(Due to luck only, in my earlier tests also tried option --infilter but using the filter name as it appears in file picker dialog, which is Microsoft Excel 2003 XML and this hadn’t any effect")

In addition it is completely unclear to me,

when we need to add a filter explicitly on opening a file
how the file type detection works based on which properties of a file
which are valid filter names (the only reliable source for such information seems to be the source code - may be here: filter/source/config/fragments/filters - core - Gitiles)

Tested with LibreOffice (Ubuntu 18.04)

Version: 6.0.7.3, Build-ID: 1:6.0.7-0ubuntu0.18.04.10
CPU-Threads: 4; BS: Linux 5.3; UI-Render: Standard; VCL: gtk3; 
Gebietsschema: de-DE (de_DE.UTF-8); Calc: group

Tested with LibreOffice (Ubuntu 20.04)

Version: 6.4.3.2, Build-ID: 1:6.4.3-0ubuntu0.20.04.1
CPU-Threads: 1; BS: Linux 5.4; UI-Render: Standard; VCL: gtk3; 
Gebietsschema: de-DE (de_DE.UTF-8); UI-Sprache: de-DE, Calc: threaded

Hope that helps.

dansei · June 30, 2020, 6:10pm

Team,

this worked 100% on my platform

$ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 18.04.4 LTS Release: 18.04 Codename: bionic

:~/Downloads$ soffice --headless --infilter=“MS Excel 2003 XML” --convert-to ods prefcardPositionExample.xml
~/Downloads$ soffice --headless --infilter=“MS Excel 2003 XML” --convert-to xls prefcardPositionExample.xml

anon73440385 · July 1, 2020, 10:08am

Team,

who is Team (sounds like your are working at IBM)?

dansei · July 1, 2020, 12:48pm

Opaque, Thanks for your response and I appreciate all responses from all above. Thank you and Thank you all