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

6 Answers

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

3

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

Direct link:

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!

2

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!

2

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

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

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

   open ~/Library/Containers/

6. Close .xlsx file.

7. Rinse and repeat for other .xlsx files.

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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