Convert multi-sheet ods to csv files

users
#1

Hi,

I have 50 or so spreadsheets comprising of 6 sheets of information. I need to export this data to csv format. I thought I had found the solution by using the unoconv program (unoconv -f csv FileName.ods . Unfortunately it will only export the first sheet. How do I get the remaining pages of information extracted without manually opening and exporting the data?

Thanks,

Vytas

#2

Am 30.03.2012 23:36, Vytas Janusauskas wrote:

Hi,

I have 50 or so spreadsheets comprising of 6 sheets of information. I
need to export this data to csv format. I thought I had found the
solution by using the unoconv program (unoconv -f csv FileName.ods .
Unfortunately it will only export the first sheet. How do I get the
remaining pages of information extracted without manually opening and
exporting the data?

Thanks,

Vytas

http://www.linuxjournal.com/content/convert-spreadsheets-csv-files-python-and-pyuno-part-1v2

#3

Hi Andreas

Any chance there is a similar python script that works under Windows?

I have found a DotNet 4 program
(http://www.softinterface.com/Convert-XLS\Features\Convert-XLS-To-CSV.htm)
that does that but since I already have LibreOffice installed it would be
great if I could use it instead of having to install another program AND
DotNet 4

Thanks!

#4

Am 01.04.2012 20:16, Pedro wrote:

Hi Andreas

Any chance there is a similar python script that works under Windows?

Python is platform independent. I don't see any reason why that particular script would not run with the Python runtime shipped with your LibreOffice.
C:\<donno>\LibreOffice3.5\program\python.exe convert.py file.ods

#5

Andreas Säger wrote

Python is platform independent. I don't see any reason why that
particular script would not run with the Python runtime shipped with
your LibreOffice.

Yes, I know how to run python scripts :wink:
But I get

Traceback (most recent call last):
  File "C:\temp\ssconverter.py", line 14, in ?
    import ooutils
  File "C:\temp\ooutils.py", line 36, in ?
    import uno
ImportError: No module named uno

I have edited oopaths to include '/progra~1/libreo~1.5/program' and
'/Program Files/LibreOffice 3.5/program' but no luck...

Help?

#6

Am 01.04.2012 23:26, Pedro wrote:

Andreas Säger wrote

Python is platform independent. I don't see any reason why that
particular script would not run with the Python runtime shipped with
your LibreOffice.

Yes, I know how to run python scripts :wink:
But I get

Traceback (most recent call last):
   File "C:\temp\ssconverter.py", line 14, in ?
     import ooutils
   File "C:\temp\ooutils.py", line 36, in ?
     import uno
ImportError: No module named uno

IMHO, this indicates that you are not running the LibO Python which certainly has the module named uno. Each and every extension and macro imports uno.

#7

Andreas Säger wrote

IMHO, this indicates that you are not running the LibO Python which
certainly has the module named uno. Each and every extension and macro
imports uno.

Excellent tip. In fact it was running another Python version needed for
ArcGis. I just removed it but the Python version included with LibreOffice
fails to execute. It reports "This application has failed to start because
python26.dll was not Found. Re-installing the application may fix this
problem."

In fact i can only find python.exe. I have uninstalled and reinstalled LO
3.5.2rc2 but it still doesn't work.

Any ideas?

#8

I could run the script on a Windows box calling the full pathname of
Python.exe script.py input.ods
It complained about something else but it found all the modules to import.

#9

Andreas Säger wrote

I could run the script on a Windows box calling the full pathname of
Python.exe script.py input.ods
It complained about something else but it found all the modules to import.

I can run it too and without any error messages (you need to specify
output.csv)
But there is no output file :frowning:

#10

I found out that it does work. But it opens and displays the file first,
which is exactly what I didn't want to happen. The problem is that I'm
working with 100.000+ lines xls documents and because they are heavily
formatted (don't ask! :slight_smile: ) it takes ages to open even on a powerful machine.

Because the python script worked in command line mode I assumed (wrongly)
that it would operate in headless mode.

Thank you for your help. Back to the DotNet program :frowning:

#11

Andreas Säger wrote

IMHO, this indicates that you are not running the LibO Python which
certainly has the module named uno. Each and every extension and macro
imports uno.

Excellent tip. In fact it was running another Python version needed for
ArcGis. I just removed it but the Python version included with LibreOffice
fails to execute. It reports "This application has failed to start because
python26.dll was not Found. Re-installing the application may fix this
problem."

Why not just install Python6? You can have multiple versions installed
(I have both 5 & 6).

In fact i can only find python.exe. I have uninstalled and reinstalled LO
3.5.2rc2 but it still doesn't work.

...

#12

Am 05.04.2012 23:43, NoOp wrote:

Why not just install Python6? You can have multiple versions installed
(I have both 5& 6).

The Python runtime with the UNO module needs to be compiled with the exact same compiler as the office suite.

#13

Ah. Got it. Thanks Andreas.

#14

FYI

As an alternative Gnumeric makes it very easy to extract individual sheets
to csv (even on Windows).

Reading this gives me yet another reason to learn Python.

Cheers, John

#15

Am 03.04.2012 11:54, Pedro wrote:

I found out that it does work. But it opens and displays the file first,
which is exactly what I didn't want to happen. The problem is that I'm
working with 100.000+ lines xls documents and because they are heavily
formatted (don't ask! :slight_smile: ) it takes ages to open even on a powerful machine.

xls supports no more than 65536 rows and 256 columns.

#16

Am 24.04.2012 01:21, jjjk wrote:

FYI

As an alternative Gnumeric makes it very easy to extract individual sheets
to csv (even on Windows).

Reading this gives me yet another reason to learn Python.

Cheers, John

--
View this message in context: http://nabble.documentfoundation.org/Convert-multi-sheet-ods-to-csv-files-tp3872138p3933937.html
Sent from the Users mailing list archive at Nabble.com.

Better you learn not to use spreadsheets for database tasks.

#17

Andreas Säger wrote

The problem is that I'm
working with 100.000+ lines xls documents and because they are heavily
formatted (don't ask! :slight_smile: ) it takes ages to open even on a powerful
machine.

xls supports no more than 65536 rows and 256 columns.

Yes, on each sheet.

#18

Hi John

jjjk wrote

As an alternative Gnumeric makes it very easy to extract individual sheets
to csv (even on Windows).

Thank you very much for the tip. I never knew Gnumeric could do that! And in
headless mode :slight_smile:

Best regards,
Pedro