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

Question

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).

Answer 1

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

Answer 2

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!

Answer 3

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!

Answer 4

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

Answer 5

Use Visual Basic to loop through worksheets and save .csv files.

1. Open up .xlsx file in Excel.

2. Press option+F11

3. Insert → Module

4. Copy this into the module code:

   Public Sub SaveWorksheetsAsCsv()
   
    Dim WS As Excel.Worksheet
    Dim SaveToDirectory As String
   
    SaveToDirectory = "./"
   
    For Each WS In ThisWorkbook.Worksheets
       WS.SaveAs SaveToDirectory & WS.Name & ".csv", xlCSV
    Next
   
   End Sub
   

5. Find your .csv files in ~/Library/Containers/com.microsoft.Excel/Data.

   Navigate there via Finder, or simply open up Terminal and run this:

   open ~/Library/Containers/com.microsoft.Excel/Data
   

6. Close .xlsx file.

7. Rinse and repeat for other .xlsx files.

Answer 6

For those interested, there's an updated version of the "XLS to CSV converter" tool mentioned in a previous answer here offered by @vembutech.

The updated tool is from the same author, Chris West and via his cwestblog.com site. Here's a blog post for the new version, now called "Excel to Text Converter". Note that the tools is now also open source as offered via github. The new version not only offers a binary installer for Mac (in addition to Windows as before), but it also supports exporting to json and tsv export, as well as csv like before.

Finally, for any who may prefer the older tool offered above, there was a blog post to go with the original tool as well, which offered more info on its use. The comments at the blog posts and issues at github may aid users interested in the tool.

(FWIW, I had offered some of this info initially as a comment on the previous answer, but then I thought it perhaps deserves to be on its own as it's a new tool. I wanted to revise the comment to add clarifications about what WAS new, as I learned more, but it turns out you can't edit comments after 5 mins, so that was just more motivation to just create a new answer.)