[Solved] Convert sheet (not first) in spreadsheet to csv

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
Aquarina
Posts: 58
Joined: Sat Oct 24, 2009 5:15 am

[Solved] Convert sheet (not first) in spreadsheet to csv

Post by 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.
I posted this in the calc forum but was advised to post it in here, so I'll change it a little bit:
What I really need is to insert the values I calculated into a sqlite database (linked to an opendocument database via odbc). So if you instead know of a way to make the spreadsheet do that...
Last edited by RoryOF on Thu Jan 28, 2021 10:51 am, edited 2 times in total.
Reason: Added [Solved] [RoryOF, Moderator]
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a sheet (not the first) in a spreasheet file to

Post by Villeroy »

Forget about csv. There is a way to copy sheet data into a registered database. Hit F4 for the data source window, expand the registered database, drag the cell range onto the "Tables" icon, enter name of target table, choose "Append data" and if your data include column headers or not. If you click [Next], you can adjust the column order if required.
I don't know why one can not drag the cells onto the right table.
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: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a sheet (not the first) in a spreasheet file to

Post by Villeroy »

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

Re: Convert a sheet (not the first) in a spreasheet file to

Post by Aquarina »

Forget about csv. There is a way to copy sheet data into a registered database.
:oops: That will do it for now. Thanks. Never knew I could do that - I guess I've been trying to drag to the correct sheet all this time!
I would still rather have a way to access the data from the command line because that would be a way to automate a lot of tasks (maybe even unattended).
^- Is this proper English?!
Isn't it strange that there is no command-line tool to access opendocument data?!
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a sheet (not the first) in a spreasheet file to

Post by Villeroy »

Aquarina wrote:Isn't it strange that there is no command-line tool to access opendocument data?!
It's a zip file. Content.xml contains the raw data and formulas. Unzip content.xml and parse.

https://odftoolkit.org/odfdom/index.html
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
Aquarina
Posts: 58
Joined: Sat Oct 24, 2009 5:15 am

Re: Convert a sheet (not the first) in a spreasheet file to

Post by Aquarina »

I Tried to add [Solved] to the start of the subject line, but couldn't. It only allowed me to add one character.
Changed the icon though.
Although the solution might not be what I intended, Villeroy's solution is probably the best solution given the circumstances.
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: [Solved] Convert sheet (not first) in spreadsheet to csv

Post by John_Ha »

Aquarina wrote:What I really need is to ...
Ah! A classic XY problem. You want to do X but you cannot. You come up with a solution Y which you find does not quite work and is probably not a good idea. You then ask for help to fix Y.

It is always far better to say "I want to do X ..." and ask for help on X. That way all options can be considered without getting drawn down the possible dead end of Y. Villeroy came up trumps with his first post: Forget about csv.

And no JCL either :super:
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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a sheet (not the first) in a spreasheet file to

Post by Villeroy »

Villeroy wrote:Forget about csv. There is a way to copy sheet data into a registered database. Hit F4 for the data source window, expand the registered database, drag the cell range onto the "Tables" icon, enter name of target table, choose "Append data" and if your data include column headers or not. If you click [Next], you can adjust the column order if required.
I don't know why one can not drag the cells onto the right table.
Yep, this works without explicitly opening the database in a separate window. If you have the database document open anyway, you can copy the cell range and paste it directly onto the table icon. You get the same import dialog but with the correct name of table and option "Append data" preselected. From the data source window you get to the database window quickly via right-click on database icon > "Edit database ..." These parts of the office GUI are slightly inconsistent and irritating.
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
Post Reply