[Solved] How to mass export certain sheet to CSV?

[Mikeyy]

Hello,

This is setup:

Folder with xx number of xls/ods files (usualy 180+).
Every file is same structure, exactly same sheet names (SHEET1, SHEET2, etc...), only values in sheets are different.

Is there a way to automaticly export SHEET2 from all xls/ods files into CSV (values sparated with ?
1 CSV or multiple CSV, it doesn't matter.

[rudolfo]

My installation of OpenOffice Apache OpenOffice 3.4.1 installed a Javascript macro into the folder <open-office-install-path>/Basis/share/Scripts/javascript/ExportSheetsToHTML. Right, it looks more complicated then it should be. But it gives you the right concept: "Hide all other sheets and exoprt the one that remains visible". The Javascript code exports to HTML, but that's easy to adjust. You just need to change the line

Code: Select all   Expand viewCollapse view

storeProps[0] = new PropertyValue();
storeProps[0].Name = "FilterName";
storeProps[0].Value = "HTML (StarCalc)";


to

Code: Select all   Expand viewCollapse view

storeProps[0] = new PropertyValue();
storeProps[0].Name = "FilterName";
storeProps[0].Value = "Text - txt - csv (StarCalc)";
storeProps[1] = new PropertyValue();
storeProps[1].Name = "FilterOptions";
storeProps[1].Value = "59,34,ANSI,1";


The last line is a bit cryptic as it uses ASCII code values for the separator and delimiter. The 59 in the beginning specifies the field separator to be a semicolon (;) -- alternatively 9 for TAB or 44 for comma -- and the 34 (ASCII code of the doublequote) says that all fields are delimited by doublequotes.

[Mikeyy]

Great! Managed to edit it to work for me.
Changed this:

Code: Select all   Expand viewCollapse view

//set only one sheet visible, and store to HTML doc
for(var i=0;i<xSheetsIndexAccess.getCount();i++)

Into this:

Code: Select all   Expand viewCollapse view

//set only one sheet visible, and store to HTML doc
for(var i=3;i<xSheetsIndexAccess.getCount();i++)

Now it only checks from sheet 4. Since I just want to export sheet 4, which is last, that solves my problem with exporting just 1 sheet.

It has bug/feature that if document is protected, it always exports ALL SHEETS in one file.

And now I have more questions.

1. How do I apply it to whole folder of files?
2. Is there a workaround protected document so it exports just 1 sheet? I can always remove protection from whole document, but if there is a workaround I would prefer that.
3. Is there a way to not export empty rows to CSV (but which contain formula)?
4. Is there a way to export that sheet from all files in folder, but into 1 CSV file? EDIT: This can be solved quite easily with this: http://www.solveyourtech.com/merge-csv-files/ If you have some other way, please mention it.

2nd, 3rd and 4th is optional, and maybe nitpicking, but it improves whole process and makes it easier for end user.

[Mikeyy]

2. Decided to just remove document protection.
3. Solved outside of office suite, with windows commands: https://forum.openoffice.org/en/forum/v ... =9&t=75430
4. Solved with: http://www.solveyourtech.com/merge-csv-files/ , it's also part of final script in 3. question.

Now I only need to be able to run this javascript macro on whole folder of files, everything after that is set.
Found this: https://forum.openoffice.org/en/forum/v ... 20&t=73807
But I just don't know how to apply it to my case.

[rudolfo]

The steps that you need to do are:

1. List all the files in a certain directory. Depending wether all files are located flat in a single directory or if you also have subfolders the way to do it is different. (see Using Shell in Base to Execute Linux Commands)
2. Loop through the list of found spreadsheet files and open each of them with calc (see the BASIC programming Wiki for ideas how to do this)
3. Run the macro that you already have currently to save each file to CSV
4. Close the opened file
5. ... continue with the next file ...

1) is difficult to achieve in Javascript. Javascript is typically used in the Browser Sandbox and does hardly ever in touch with opening and closing files. The above cited thread discusses an implementation in Basic and one in Python for listing all files of a directory. In any case this would still mean that you will need to re-write your current Javascrip macro in the other language (either Basic or Python).

Opening a document is very similar for all programming languages, because it depends heavily on the UNO objects and methods. If you have a working example in one language it is normally easy to implement it in another language. Particulary the section Replacing the Content of the Document Window on the Wiki page is interesting, because it offers a way to automatically close a document when the next one is opened. This way you will not get into performance problems because you have too many opened files.

As you have the Javascript code, that is already working, you have enough keywords in it, that you can use as search phrases for a search on this forum. Chances are good that you find something similar for Basic or for python.

[Mikeyy]

That would be hard since I have 0 programming experience.
I was digging around internet last and found out that you can do same with command line.

Code: Select all   Expand viewCollapse view

soffice.exe --headless --convert-to csv:"Text - txt - csv (StarCalc)":"59,ANSI,1" filename.ods

There are 2 problems with this.
1. If I replace "filename.ods" with "*.ods" on win 8.1, it fails without error. Not sure why.
2. It only exports first default sheet. Do you know, is it possible to export specific sheet with this command?

[Mikeyy]

Since I'm not programmer and making macros is out of my league, I solved this easier way.

1. I made a batch ".bat" file for windows.

Code: Select all   Expand viewCollapse view

for /f "delims=|" %%f in ('dir /b .\ODSDIRECTORY') do "C:\Program Files (x86)\LibreOffice 4\program\soffice.exe" --headless --convert-to csv:"Text - txt - csv (StarCalc)":"59,ANSI,1" --outdir .\CSV ".\ODSDIRECTORY\%%f"
exit

What it does is next:
- searches all files in directory
- for every file in directory it executes command after "do"
- "delims=|" takes into account files with spaces in name, but files with brackets will not pass, and probably also files with other strange characters
- "C:\Program Files (x86)\LibreOffice 4\program\soffice.exe" is path name to LO or AOO, if it's not global command
--convert-to csv:"Text - txt - csv (StarCalc)":"59,ANSI,1" for this part thanks to rudolfo, without him it wouldn't be possible
--outdir .\CSV this is only if you want to have output directory different then directory where your command is executed
- ".\ODSDIRECTORY\%%f" this points to files which will be converted, if you remove "" filenames with spaces in them will fail

2. I merge all CSV into one CSV:

Code: Select all   Expand viewCollapse view

copy .\CSV\*.csv merged.csv

After that, you can edit it however you like it.
Since I didn't find a way to export 2nd, 3rd, 4th... sheet, you must have that sheet as 1st in your document.

Hope it helps someone. Thank you for help.