Bug 135762 - Export all sheets as CSV
Summary: Export all sheets as CSV
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard: target:7.3.0 target:7.2.0.2
Keywords:
Depends on:
Blocks: CSV-Export
  Show dependency treegraph
 
Reported: 2020-08-14 13:29 UTC by J22Gim
Modified: 2021-09-21 21:26 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description J22Gim 2020-08-14 13:29:07 UTC
Description:
Each sheet of a Calc spreadsheet must be exported as CSV individually. It would be great to be able to export all of them in a single action, something like the Print dialog where you set to export "all", "range" or "current sheet only".

Steps to Reproduce:
1. Create a spreadsheet with several sheets (eg 140)
2. Now you need to export the file as CSV
3. How to do it? 

Actual Results:
You have to export each sheet, one by one. This is, select the sheet, go to save as, select CSV, probably edit filter settings, save, go back to the file, select next sheet, and start again. Repeat 140 times.

Expected Results:
When you told Calc you want to save as CSV, the dialog would ask to select between "export current page only", "export range" or "export all sheets".
If it makes more sense this could be placed in "Export" instead of "save as" 


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 6.4.4.2
Build ID: 1:6.4.4-0ubuntu0.20.04.1
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded
Comment 1 Roman Kuznetsov 2020-08-14 20:15:56 UTC
Do you want save every sheet as different CSV file or all sheets into one CSV?
Comment 2 BogdanB 2020-08-16 13:58:26 UTC
I supose there will be a CSV file for each sheet.
Comment 3 BogdanB 2020-09-15 11:15:39 UTC Comment hidden (obsolete)
Comment 4 J22Gim 2020-10-13 15:36:48 UTC
The idea is to export each sheet as a CSV file. The name of the file could be the name of the sheet. It would be immensely useful for people in data science and programming to use Calc together with eg Python, R, etc. where you almost always work with CSV files.

Well, actually both Pyhon and R have functions to import from and export to Excel files, which kind of leaves Calc in disadvantage. So having this functionality would be great. Thanks!!
Comment 5 J22Gim 2020-11-10 15:47:46 UTC
I mean, each sheet would be exported as a separate CVS file.
Comment 6 Jean-Baptiste Faure 2021-05-11 20:20:05 UTC
(In reply to J22Gim from comment #5)
> I mean, each sheet would be exported as a separate CVS file.

You should check if it would be easy to do that with a macro.

Best regards. JBF
Comment 7 Eike Rathke 2021-07-12 11:54:01 UTC
Fwiw, there's a Python utility xlsx2csv that lumps together all sheets into one CSV file. Whether that's actually useful is debatable..

pip3 install xlsx2csv
xlsx2csv -a file.xlsx > file.csv

See also https://github.com/dilshod/xlsx2csv
Comment 8 Eike Rathke 2021-07-15 08:51:04 UTC
The command line --convert-to csv:... now accepts an 11th parameter -1
to export all sheets to CSV files, implemented with
https://git.libreoffice.org/core/+/b8903bc106dad036acb3d117e5c4fc955697fe02%5E%21/
on master, and
https://git.libreoffice.org/core/+/b41d21453fd3f109c77b03e296f62bc4d8b9ca9b%5E%21/
for 7-2

Sample usage:
soffice --convert-to csv:"Text - txt - csv (StarCalc)":44,34,UTF8,1,,0,false,true,false,false,false,-1 sample.ods

which exports
sample-Sheet1.csv
sample-Sheet2.csv
sample-Sheet3.csv

If the 11th parameter is not given, empty or 0 then the default first
sheet is exported as sample.csv as before.
Comment 9 Commit Notification 2021-07-15 11:02:25 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/fda91f8be16ba760e360940ebafd6244c648cb8c

Related: tdf#135762 Allow --convert-to csv to specify 1-based sheet number

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2021-07-15 12:55:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

https://git.libreoffice.org/core/commit/d756a599298abb23657469cfd94c4a201824c419

Related: tdf#135762 Allow --convert-to csv to specify 1-based sheet number

It will be available in 7.2.0.2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Commit Notification 2021-07-15 17:21:11 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/92d7ab0f9822bad7b003b317b6f08b7f84441ff0

Keep ScAsciiOptions in sync with ScImportOptions, tdf#135762 related

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 12 Eike Rathke 2021-07-16 08:34:26 UTC
Fwiw, actually it's the 12th parameter, token 11 0-based.
Comment 13 BogdanB 2021-07-16 18:45:50 UTC
I tried to Sacve as csv, but just the current sheet is saved as csv file. I tried selecting all sheets and after that Save as csv and nothing. I am missing something?...
Comment 14 Eike Rathke 2021-07-20 19:22:06 UTC
Yes, you are missing that the implementation is only for the command line conversion (and the filter options string that can be used in macros).
Comment 15 Commit Notification 2021-07-20 23:27:35 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/0cda081c9aa3b3dcb363f97bac60c845ce9a13e0

Related: tdf#135762 Suppress cout if not command line

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 BogdanB 2021-07-21 15:51:42 UTC
I was expecting that this change was in UI, because the reporter mentioned in the description 
--------------------------------------------
When you told Calc you want to save as CSV, the dialog would ask to select between "export current page only", "export range" or "export all sheets".
If it makes more sense this could be placed in "Export" instead of "save as"
--------------------------------------------
He was asking for the dialog to allow this export.

It's great to have this function even on terminal. But was greater to be in UI.
Comment 17 Eike Rathke 2021-07-22 09:29:00 UTC
And that is why this bug is not RESOLVED FIXED and all commits were done only as Related.

To have it available in UI it would also be necessary to define what happens if one or more files overwrite existing files, or one or more files could not be written, and the dialogs and actions to be presented/offered then.
Comment 18 Commit Notification 2021-07-25 13:54:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

https://git.libreoffice.org/core/commit/55f0121dd7ed2d7e84adc597bdd24ca40e0b0ce4

Related: tdf#135762 Suppress cout if not command line

It will be available in 7.2.0.2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 19 J22Gim 2021-09-21 21:26:47 UTC
A big THANK you people for moving forward in this direction. With time. hopefully it will be implemented in the UI as well.