3

I have about 16,000 .xlsx files in a directory. Each file follows the same structure. The first sheet is empty, and called "Sheet 1". The second is where the interesting data is, and it's called "Partners". I need to convert these to text/csv.

I've tried various solutions I found online, including ssconvert, xlsx2csv, both of which produce runtime errors. I've also tried these with a dummy file I created with Libre Office Calc, just to check it's not that my files are corrupt (and those files open fine).

How do I do this?

4

2 Answers 2

3

LibreOffice has a feature built in to convert to csv:

localc --headless --convert-to csv input.xlsx

This will only export the first sheet however. To get the second sheet you need the help of a macro. Open LibreOffice Calc and go to Tools -> Macros -> Edit Macros.

This should open an editor, replace anything in the editor with this code:

REM  *****  BASIC  *****

Sub Main
    sheets = ThisComponent.Sheets
    sheets.removeByName("Sheet1")
    ThisComponent.storeSelf(Array())
    ThisComponent.close(true)
End Sub

And then save the macro (ctrl+s). This should save it to Standard.Module1.Main (says it on the bottom left).

Then you can close LibreOffice again and run the following command:

localc --headless input.xlsx macro:///Standard.Module1.Main

This will remove the first sheet (well, any sheet named Sheet1 - change in the code if needed).

So your bash script in the end will probably look something like this:

mkdir csvs
for i in *.xlsx ; do
    localc --headless $i macro:///Standard.Module1.Main
    localc --convert-to csv $i
done
mv *.csv csvs/
2

You can use csvkit (https://csvkit.readthedocs.io/en/latest/) and run

in2csv -I --sheet "sheetName" ./input.xlsx >./output.csv

It's up you to write a bash for loop.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .