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?


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
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
mv *.csv csvs/

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.