[Solved] Convert sheet in spreadsheet to csv

[Aquarina]

Hi,
I need to access the values of cells c3:g10 in the third sheet (named "calcs") of a spreadsheet file (named "calculations.ods") from bash on linux.
Any hope?
I tried:
$ libreoffice --convert-to csv calculations.ods
but only the first page was exported with a strange character set.
:crazy:

[John_Ha]

You need to activate Sheet 3. Manually,

1. Go to Sheet 3.
2. File > Save as ..., and choose csv.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[Aquarina]

[quote="John_Ha"]1. Go to Sheet 3.
2. File > Save as ..., and choose csv.

I meant to do it from the command line because I need to automate a lot of processes.

[John_Ha]

We crossed.

You need to find a way to activate/select Sheet 3.

You may be better posting in Macros and UNO Forum.

Pitonyak's macro book may help with a workaround of running a macro

Code: Select all

6.5.3. Select a Cell
Click on a cell to select the cell. Although the cursor is not displayed in the cell, the cell is
selected. Use the arrow keys to “move the cursor” and select a different sell. The behavior is
the same. In OOo 2.4, the following macro used to select the entire cell so that the cell was
highlighted. The behavior appears to have changed in OOo 3.0.
Listing 6.13: Select a single cell.
Dim oCell
Dim oSheet
REM Get the first sheet.
oSheet = ThisComponent.getSheets().getByIndex(0)
REM Get cell A2
oCell = oSheet.GetCellbyPosition( 0, 1 )
REM Move the selection to cell A2
ThisComponent.CurrentController.Select(oCell)

and

Code: Select all

6.18.1. Copy entire sheet to a new document
The following macro copies the contents of a given sheet into a newly created of a second document.
'Author: Stephan Wunderlich [stephan.wunderlich@sun.com]
Sub CopySpreadsheet
Dim doc1
Dim doc2
doc1 = ThisComponent
selectSheetByName(doc1, "Sheet2")
dispatchURL(doc1,".uno:SelectAll")
dispatchURL(doc1,".uno:Copy")
doc2 = StarDesktop.loadComponentFromUrl("private:factory/scalc" , _
"_blank",0,dimArray())
doc2.getSheets().insertNewByName("inserted",0)
selectSheetByName(doc2, "inserted")
dispatchURL(doc2,".uno:Paste")
End Sub
Sub selectSheetByName(oDoc, sheetName)
oDoc.getCurrentController.select(oDoc.getSheets().getByName(sheetName))
End Sub
Sub dispatchURL(oDoc, aURL)
Dim noProps()
Dim URL As New com.sun.star.util.URL
Dim frame
Dim transf
Dim disp
frame = oDoc.getCurrentController().getFrame()
URL.Complete = aURL
transf = createUnoService("com.sun.star.util.URLTransformer")
transf.parseStrict(URL)
disp = frame.queryDispatch(URL, "", _
com.sun.star.frame.FrameSearchFlag.SELF _
OR com.sun.star.frame.FrameSearchFlag.CHILDREN)
disp.dispatch(URL, noProps())
End Sub

[Aquarina]

I'll do that. Thanks.
In the meantime, if someone knows a better solution...
Maybe some command line tool outside of libreoffice?

[John_Ha]

Do a google search - I found lots of stuff including the LO Developers Guide - Spreadsheet Examples and Starting LibreOffice Software With Parameters

[Aquarina]

I did lots of lookups, but since I am not a programmer (except for bash), I never looked for anything like macros.
Did try to read Pythoniac's tutorials at some time... only to give up after a while! :-(

[John_Ha]

Does the --accept={UNO} parameter do what you want? I assume you write the UNO commands and they are sent to LO.

Notifies LibreOffice software that upon the creation of "UNO Acceptor Threads", a "UNO Accept String" will be used.

UNO-URL is string the such kind uno:connection-type,params;protocol-name,params;ObjectName.

More information is found in LibreOffice Developer's Guide.

Did try to read Pythoniac's tutorials at some time... only to give up after a while!

Me too!! UNO seems to me like IBM's JCL. No-one ever writes JCL from scratch - they find something close enough and hack it.

[Aquarina]

No-one ever writes JCL from scratch - they find something close enough and hack it.

Lol! If I ever try to learn JCL I'll use this as the first line on my notebook.

[jrkrideau]

I'll do that. Thanks.
In the meantime, if someone knows a better solution...
Maybe some command line tool outside of libreoffice?

I might have a way to semi-automate the process in another program but it is stretching my not-exactly-great skills.

Is the layout of your data always consistent? In every file that you want to convert the data is always Sheet 3 : c3:g10 ?

Are the files named consistently, something like File1.ods, File2.ods, etc., or all are in a separate folder with no other .ods files?

Do you want the data in separate .csv files or in one .csv file at the end of the day?

May I ask what the overall reason for doing this? I ask because it may be easier to approach the problem from another angle.

[John_Ha]

Lol! If I ever try to learn JCL I'll use this as the first line on my notebook.

The unofficial (written by an employee) and extremely funny IBM Jargon and General Computing Dictionary - Tenth Edition defines JCL as below, and clarifies it by also defining command language.

[MrProgrammer]

No-one ever writes JCL from scratch - they find something close enough and hack it.

I wrote tens of thousands of lines of JCL, since I was a mainframe programmer/analyst for over three decades. Of course I would borrow from other JCL I had written when possible, but it was often easier to just create anew instead of searching for a good match. JCL is rather wordy, especially before improvements were made in the mid-1980s, but it is not difficult. Here is some of my JCL, which had to be created from scratch as no one else had done anything remotely similar.

Though JCL may seem awful, it helps to understand the context. The machines for which it was designed had, by modern standards, tiny amounts of memory, say 256K (or even 32K), and slow processors. Thus simplicity — for the operating system, not for the programmer — was very important. This pushed more work onto the people who created the JCL, but that was OK because the machine's resources were so valuable. Programmers were less expensive than computers. There was no "virtual storage". That ¼ megabyte had to hold the operating system, the program, and its data. And typically this was the only computer which the organization had. Times were quite different then.

Mainframes today still use JCL for two reasons:
• It is quite flexible in managing the machine's resources.
• Downward compatibility is regarded as very important in that environment. Things developed 20 or more years ago, should work without any change today. An assembler program written in the mid-1970s for 24-bit hardware will run just fine on a modern 64-bit mainframe. I know this from personal experience.

//*-----------------------------------------------------------------*//         
//*            BEFORE SUBMITTING THIS JOB, EXIT ISPF AND ISSUE      *//         
//*            THE "TSOLIB DEACT" AND "FREE ALL" COMMANDS           *//         
//*-----------------------------------------------------------------*//         
//* SEE DETAILED DOCUMENTATION IN \\FILES\SHARE\CORPORATE\MIS\      *//         
//* COMPUTER SERVICES\AS400 S390 STORAGE SYSTEMS\PUBLIC\S390\       *//         
//* SAPR3 DOCUMENTATION\USING FTPGROUP TO COPY BETWEEN SYSPLEXS.TXT *//         
//*-----------------------------------------------------------------*//         
//         SET TARGET=TWQ0                LPAR WHERE DATA WILL BE SENT          
//         SET PDS=B68778.DATA            DATASET WITH 4 MEMBERS BELOW          
//         SET NETRC=FTPNETRC             MEMBER WITH USER ID                  
//         SET DUMP=FTPDUMP               B68778.**                             
//         SET RESTORE=FTPREST            **                                    
//         SET JOBCARD=FTPJOB             MEMBER WITH JOB STATEMENT             
//         SET WORK=BASIS.B68778.ADRDSSU  OUTPUT FROM DF/DSS DUMP               
//         SET MEGS=50                    SIZE OF OUTPUT IN MEGS                
//*-----------------------------------------------------------------*//         
//*            DELETE WORK DATASET IF IT EXISTS                     *//         
//*-----------------------------------------------------------------*//         
//#0      EXEC PGM=IKJEFT01,REGION=6M,PARM='%FTPGRPR0 &WORK'                    
//SYSEXEC   DD DISP=SHR,DSN=SYS6.AMBER.DATA                                     
//SYSTSIN   DD DUMMY                                                            
//SYSTSPRT  DD SYSOUT=*                                                         
//*-----------------------------------------------------------------*//         
//*            DUMP DATASET GROUP TO DISK                           *//         
//*-----------------------------------------------------------------*//         
//#1        IF RC = 0 THEN                                                      
//#1      EXEC PGM=ADRDSSU,REGION=6M,TIME=NOLIMIT                               
//SYSIN     DD DISP=SHR,DSN=&PDS(&DUMP)                                         
//SYSPRINT  DD SYSOUT=*                                                         
//DATA      DD DISP=(NEW,PASS),DSN=&WORK,UNIT=SYSDA,                            
//             AVGREC=M,SPACE=(1,(&MEGS,&MEGS),RLSE),                           
//             DSORG=PS,RECFM=U,BLKSIZE=27998                                   
//*-----------------------------------------------------------------*//         
//*            CREATE PARAMETERS FOR UTILITIES                      *//         
//*-----------------------------------------------------------------*//         
//#2        IF RC = 0 THEN                                                      
//#2      EXEC PGM=IKJEFT01,REGION=6M,PARM='%FTPGRPR1'                          
//SYSEXEC   DD DISP=SHR,DSN=SYS6.AMBER.DATA                                     
//SYSTSIN   DD DUMMY                                                            
//SYSTSPRT  DD SYSOUT=*                                                         
//RESTORE   DD DISP=SHR,DSN=&PDS(&RESTORE)                                      
//JOBCARD   DD DISP=SHR,DSN=&PDS(&JOBCARD)                                      
//DATA      DD DISP=(OLD,CATLG),DSN=*.#1.DATA                                   
//FTPDEL    DD DISP=(NEW,PASS),UNIT=VIO,AVGREC=U,SPACE=(80,80),                 
//             DSORG=PS,RECFM=FB,LRECL=80                                       
//FTPPUT    DD DISP=(NEW,PASS),UNIT=VIO,AVGREC=U,SPACE=(80,80),                 
//             DSORG=PS,RECFM=FB,LRECL=80                                       
//FTPSUB    DD DISP=(NEW,PASS),UNIT=VIO,AVGREC=U,SPACE=(80,80),                 
//             DSORG=PS,RECFM=FB,LRECL=80                                       
//JOB       DD DISP=(NEW,PASS),UNIT=VIO,AVGREC=U,SPACE=(80,80),                 
//             DSORG=PS,RECFM=FB,LRECL=80                                       
//*-----------------------------------------------------------------*//         
//*            DELETE DATA FROM TARGET IF IT EXISTS                 *//         
//*-----------------------------------------------------------------*//         
//#3        IF RC = 0 THEN                                                      
//#3      EXEC PGM=FTP,REGION=6M,TIME=NOLIMIT,                                  
//             PARM='&TARGET (TIMEOUT 600'                                      
//INPUT     DD DISP=(OLD,DELETE),DSN=*.#2.FTPDEL        FB/80                   
//NETRC     DD DISP=SHR,DSN=&PDS(&NETRC)                FB/80                   
//OUTPUT    DD SYSOUT=*                                 FB/160                  
//SYSPRINT  DD SYSOUT=*                                                         
//*-----------------------------------------------------------------*//         
//*            SEND DATA TO TARGET NODE                             *//         
//*-----------------------------------------------------------------*//         
//#4        IF RC = 0 THEN                                                      
//#4      EXEC PGM=FTP,REGION=6M,TIME=NOLIMIT,                                  
//             PARM='&TARGET (EXIT TIMEOUT 600'                                 
//INPUT     DD DISP=(OLD,DELETE),DSN=*.#2.FTPPUT        FB/80                   
//NETRC     DD DISP=SHR,DSN=&PDS(&NETRC)                FB/80                   
//OUTPUT    DD SYSOUT=*                                 FB/160                  
//DATA      DD DISP=(OLD,DELETE),DSN=&WORK                                      
//SYSPRINT  DD SYSOUT=*                                                         
//*-----------------------------------------------------------------*//         
//*            SUBMIT JOB TO RESTORE DATA ON TARGET NODE            *//         
//*-----------------------------------------------------------------*//         
//#5        IF RC = 0 THEN                                                      
//#5      EXEC PGM=FTP,REGION=6M,TIME=NOLIMIT,                                  
//             PARM='&TARGET (EXIT TIMEOUT 600'                                 
//INPUT     DD DISP=(OLD,DELETE),DSN=*.#2.FTPSUB        FB/80                   
//NETRC     DD DISP=SHR,DSN=&PDS(&NETRC)                FB/80                   
//JOB       DD DISP=(OLD,DELETE),DSN=*.#2.JOB           FB/80                   
//OUTPUT    DD SYSOUT=*                                 FB/160                  
//SYSPRINT  DD SYSOUT=*                                                         
//#5     ENDIF RC = 0                                                           
//#4     ENDIF RC = 0                                                           
//#3     ENDIF RC = 0                                                           
//#2     ENDIF RC = 0                                                           
//#1     ENDIF RC = 0

[jrkrideau]

I thought that there was only one original JCL program that had been handed down like sacred scriptures. I am in awe of someone who could write their own.

[Villeroy]

Hi,
I need to access the values of cells c3:g10 in the third sheet (named "calcs") of a spreadsheet file (named "calculations.ods") from bash on linux.
Any hope?
I tried:
$ libreoffice --convert-to csv calculations.ods
but only the first page was exported with a strange character set.

A byte of Python would help. It is possible to write some Python code, callable from bash, connecting to a running office.

[Mountaineer]

...
$ libreoffice --convert-to csv calculations.ods
but only the first page was exported with a strange character set.

There are options to the filter - as shown here:

https://ask.libreoffice.org/en/question ... mand-line/

I'd Try to insert a new first sheet which contains only the desired data and export/convert this page.

As csv can't have multiple pages you are bound to one page.

J.

PS: On the long run Villeroys Suggestion to ask a running calc for the data by python would be most flexible.

[Aquarina]

I Have marked this thread as solved in spite not being solved per se because the followup that happened here gives the best possible solution so far.
Thanks for all the help.
Long live JCL :-D.