[Solved] Convert sheet in spreadsheet to csv

Java, C++, C#, Delphi, ??? - Using the UNO bridges

[Solved] Convert sheet in spreadsheet to csv

Postby Aquarina » Mon Jan 25, 2021 9:25 pm

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:
Last edited by Aquarina on Thu Jan 28, 2021 10:43 am, edited 1 time in total.
User avatar
Aquarina
 
Posts: 50
Joined: Sat Oct 24, 2009 5:15 am

Re: Convert a sheet in a spreasheet file to csv

Postby John_Ha » Mon Jan 25, 2021 9:27 pm

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.
Last edited by John_Ha on Mon Jan 25, 2021 9:33 pm, edited 1 time in total.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8338
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Convert a sheet in a spreasheet file to csv

Postby Aquarina » Mon Jan 25, 2021 9:31 pm

[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.
User avatar
Aquarina
 
Posts: 50
Joined: Sat Oct 24, 2009 5:15 am

Re: Convert a sheet in a spreasheet file to csv

Postby John_Ha » Mon Jan 25, 2021 9:34 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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
Last edited by John_Ha on Mon Jan 25, 2021 9:40 pm, edited 1 time in total.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8338
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Convert a sheet in a spreasheet file to csv

Postby Aquarina » Mon Jan 25, 2021 9:38 pm

I'll do that. Thanks.
In the meantime, if someone knows a better solution...
Maybe some command line tool outside of libreoffice?
User avatar
Aquarina
 
Posts: 50
Joined: Sat Oct 24, 2009 5:15 am

Re: Convert a sheet in a spreasheet file to csv

Postby John_Ha » Mon Jan 25, 2021 9:46 pm

Do a google search - I found lots of stuff including the LO Developers Guide - Spreadsheet Examples and Starting LibreOffice Software With Parameters
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8338
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Convert a sheet in a spreasheet file to csv

Postby Aquarina » Mon Jan 25, 2021 9:50 pm

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! :-(
User avatar
Aquarina
 
Posts: 50
Joined: Sat Oct 24, 2009 5:15 am

Re: Convert a sheet in a spreasheet file to csv

Postby John_Ha » Mon Jan 25, 2021 9:53 pm

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.


Aquarina wrote: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.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8338
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Convert a sheet in a spreasheet file to csv

Postby Aquarina » Mon Jan 25, 2021 10:02 pm

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.
User avatar
Aquarina
 
Posts: 50
Joined: Sat Oct 24, 2009 5:15 am

Re: Convert a sheet in a spreasheet file to csv

Postby jrkrideau » Tue Jan 26, 2021 12:32 am

Aquarina wrote: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.
LibreOffice 6.4.7.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3808
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Convert a sheet in a spreasheet file to csv

Postby John_Ha » Tue Jan 26, 2021 1:16 am

Aquarina wrote: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.

Clipboard.png
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8338
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Convert a sheet in a spreasheet file to csv

Postby MrProgrammer » Tue Jan 26, 2021 2:31 am

John_Ha wrote: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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3964
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Convert a sheet in a spreasheet file to csv

Postby jrkrideau » Tue Jan 26, 2021 5:24 pm

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.
LibreOffice 6.4.7.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3808
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Convert a sheet in a spreasheet file to csv

Postby Villeroy » Tue Jan 26, 2021 10:34 pm

Aquarina wrote: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:

A byte of Python would help. It is possible to write some Python code, callable from bash, connecting to a running office.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29576
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a sheet in a spreasheet file to csv

Postby Mountaineer » Thu Jan 28, 2021 9:50 am

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


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.
OpenOffice 3.1 on Windows Vista
Mountaineer
 
Posts: 66
Joined: Sun Sep 06, 2020 8:27 am

Re: [Solved] Convert a sheet in a spreasheet file to csv

Postby Aquarina » Thu Jan 28, 2021 10:51 am

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.
User avatar
Aquarina
 
Posts: 50
Joined: Sat Oct 24, 2009 5:15 am


Return to External Programs

Who is online

Users browsing this forum: No registered users and 3 guests