LibreOffice Calc: Export All Sheets to CSV

April 18, 2018 | Filed under: Tech Tips

I needed a way to export all the sheets out of a LibreOffice Calc spreadsheet into individual CSV files that could be imported into the game I was building. It had to be quick so this could be used as a fast iterative process. LibreOffice macro scripting accomplishes this.

I found that J-P Stacey had created a macro doing much the same thing I needed. The only downside was that it required you to reopen the document each time you exported. With some digging into the macro API, I found the solution to that and modified the macro so that it creates the CSV files and keeps the original file open.

In LibreOffice 5, macros aren't exactly well documented and the UI is rather confusing, so I will give full instructions to get this macro working.

In LibreOffice, go to Tools > Macros > Organize Macros > LibreOffice Basic…
Select where you want to save the macro, I used the default of Standard > Module1. Click the New button.
Now copy and paste the macro below into the macro window, replacing the empty function that was created.

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

Sub ExportToCsv
    document = ThisComponent

    ' Use the global string tools library to generate a path to save each CSV
    GlobalScope.BasicLibraries.loadLibrary("Tools")
    FileDirectory = Tools.Strings.DirectoryNameoutofPath(document.getURL(), "/")

    ' Work out number of sheets for looping over them later.
    Sheets = document.Sheets
    NumSheets = Sheets.Count - 1

    ' Set up a propval object to store the filter properties
    Dim Propval(1) as New com.sun.star.beans.PropertyValue
    Propval(0).Name = "FilterName"
    Propval(0).Value = "Text - txt - csv (StarCalc)"
    Propval(1).Name = "FilterOptions"
    Propval(1).Value ="59,34,0,1,1"   'ASCII  59 = ;  34 = "

    For I = 0 to NumSheets
        ' For each sheet, assemble a filename and save using the filter
        document.getCurrentController.setActiveSheet(Sheets(I))
        Filename = FileDirectory + "/" + Sheets(I).Name + ".csv"
        FileURL = convertToURL(Filename)
        document.StoreToURL(FileURL, Propval())
    Next I

End Sub

Macro

Close the macro window. Right click on a toolbar where you want to add a button to run the export macro and choose Customize Toolbar…
Click on Add Command. From the Category section, choose LibreOffice Macros > My Macros > Standard > Module1 (or wherever you saved the macro earlier). Select ExportToCsv from the Command section and click Add. Click Close to close the window.
ExportToCsv has now been added to your toolbar. You can place it anywhere you want in the toolbar with the up and down arrows.
Running this macro will place a CSV file for each sheet alongside the original document. It overwrites any file that was previously there, so be aware of any naming conflicts that could arise before running this macro.

Differences from the original macro

The biggest difference between this and the original J-P Stacey macro is the subtle change from document.StoreAsURL() (equivalent to the Save As… command) to document.StoreToURL() (equivalent to the Save a Copy… command). That one change means you don't have to reopen the document every time you export, which made this a perfect macro for fast iteration.

Differences from the original macro

Question or Comment?