How to convert Excel file with multiple sheets to a set of CSV files?

I have an Excel file with 20 sheets on it. Is there a way to quickly convert the file into 20 CSV files - one for each sheet?

I can do it manually of course, but I'll have to be processing these files a lot, so it would be great to have some kind of command line utility (or even Excel command line switches).

The method by vembutech is a definitely a more polished solution. Here is a lightweight VBA macro that would export all the sheets from one workbook.

Goes in the active workbooks folder all names are workbookname_sheetname.csv

Sub exportcsv()
Dim ws As Worksheet
Dim path As String

path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
For Each ws In Worksheets
    ws.Activate
    ActiveWorkbook.SaveAs Filename:=path & "_" & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Next
End Sub

EDIT:

For the comment. The original macro simply uses the save as function (which is sort of like closing the original). If you want to do this while leaving the original open you need to copy to a new workbook, save, close the new workbook.

Sub exportcsv()
Dim ws As Worksheet
Dim path As String

path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
For Each ws In Worksheets
    ws.Copy
    ActiveWorkbook.SaveAs Filename:=path & "_" & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close False
Next
End Sub

Instead of batch script you can use the XLS to CSV converter which would give you better user interface

Direct link: http://cwestblog.com/wp-content/uploads/2013/04/XLS-to-CSV-Converter-1.1.0.zip

Unzip the .HTA file.

Double-click on the .HTA file

Locate the folder where you have your xls file

select the csv naming schema and click start conversion which will convert all xls files with in the folder to csv files

If your folder has 3 xls files with 3 sheets then it would create 9 csv files for the each sheets and you can identify the csv file with csv naming schema

Hope this helps!

Here is a python script getsheets.py (mirror), you should install pandas and xlrd before you use it.

Run this:

pip3 install pandas xlrd  # or `pip install pandas xlrd`

How does it works?

$ python3 getsheets.py -h
Usage: getsheets.py [OPTIONS] INPUTFILE

Convert a Excel file with multiple sheets to several file with one sheet.

Examples:

    getsheets filename

    getsheets filename -f csv

Options:
-f, --format [xlsx|csv]  Default xlsx.
-h, --help               Show this message and exit.

Convert to several xlsx:

$ python3 getsheets.py goods_temp.xlsx
Sheet.xlsx Done!
Sheet1.xlsx Done!

All Done!

Convert to several csv:

$ python3 getsheets.py goods_temp.xlsx -f csv
Sheet.csv Done!
Sheet1.csv Done!

All Done!

A macro from ExtendOffice to save the tabs in your Documents folder:

Sub ExportSheetsToCSV()
    Dim xWs As Worksheet
    Dim xcsvFile As String
    For Each xWs In Application.ActiveWorkbook.Worksheets
        xWs.Copy
        xcsvFile = CurDir & "\" & xWs.Name & ".csv"
        Application.ActiveWorkbook.SaveAs Filename: = xcsvFile, _
            FileFormat: = xlCSV, CreateBackup: = False
        Application.ActiveWorkbook.Saved = True
        Application.ActiveWorkbook.Close
    Next
End Sub