In this post, we will see how to batch convert Excel to CSV files. There are 3 different methods which we will use to convert Excel to CSV format in bulk.
The first method explained here, only works with Excel files from 97-2003 format or .XLS format. This is a very simple method and the program which I used is “QuadXL“. This is a portable software, so doesn’t require installation. In the case of 2nd method (VBA Macro method), all versions of Excel files are supported (XLS and/or XLSX). You have to copy and paste a VBA code to a VBA Editor window in Excel and then you can run the code by running the Macro any time. In the 3rd method, I use the XLS to CSV Converter application as HTA HTML application to do the batch conversion of Excel to CSV file(s).
Now, I will guide you through the methods one after another.
Method 1: Batch Convert Excel to CSV with QuadXL
QuadXL is a free software which lets you convert XLS to CSV easily. It supports Excel files from 97-2003 or in .XLS format. The program is just 356.9 KB in size. Just download and extract the ZIP file to any folder and run the provided “QuadXL.exe” from it.
You can use its “Export to CSV or Text File” feature to convert the XLS workbook contents as a CSV or TXT file format. It supports multiple pages and multiple files at once.
Step 1: Just click the “Export to CSV or Text File” tab and browse for a folder containing XLS files. Now select the file by clicking on it and you will see the output file name automatically in the Output File box. You can also provide the different name if required.
Step 2: You can choose any of the following from the radio buttons provided at this window: One Sheet, All Sheets into Separate Files, or All Sheets into One File.
Step 3: Now choose the File Type as CSV from the File Type radio button and finally click Convert File(s) button to start the process.
Your file(s) will be saved in the same folder where the original file is.
Also, you can do the batch conversion of XLS to CSV by clicking the Batch Mode button to convert all the files in the chosen directory in one go.
This program only supports Excel files in XLS format from 97-2003 version, so you have to save the latest XLSX file to old XLS format before using this software.
Method 2: Bulk Convert Excel to CSV using VBA Macro
This VBA Macro method is very simple as it lets you convert multi-page Excel file into Single Sheet per file in CSV format.
Let’s say you have an Excel file containing 3 worksheets and you want to convert each sheet separately in CSV format with a different name. Then you have to write the below code in a VBA Editor of your MS Excel program window. After adding the code, you can save the code in Excel’s default template (Personal.XLSM) if you want to use this code in future.
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
Now open the Excel file you want to convert to multiple CSV files and run the Macro named exportcsv by pressing the keyboard shortcut if assigned or choosing it from the Macro list by pressing the “Alt+F8” key of your keyboard.
The CSV files will be auto renamed based upon the original file name, for e.g. FileName_SheetNumber.CSV in the same folder where the original Excel file exists. You can easily understand the output by seeing the above screenshot.
Further, you can try different VBA Macros for the same purpose from different websites from below links.
Method 3: Batch Convert Excel to CSV with Running .HTA File
This method is also simple and easy to understand. Just download the HTA file in ZIP format from the link at the bottom of this tutorial and run it after extracting. Now provide the path to the folder containing Excel files in XLS or XLSX format.
Use the required CSV Naming Schema and click Start XLS conversion button at the bottom. You can also choose the delimiters for output CSV files as Comma, Semicolon, or Tab.
HTA HTML application is just 18.9 KB which does your process of converting Excel to CSV in seconds.
Conclusion:
As you see, the above tutorials easily explain the methods of converting Excel to CSV in different ways. In my opinion, all the 3 methods stated are good enough for every type of user. QuadXL offers various other various useful options like preview XLS file, Search within Folder for a keyword, while HTA HTML method is also good. You can create a Macro if you know how to use VBA Macro in Excel.
Also Read:
- Word Processor Software for Windows with SpellCheck, Auto-Save
- Word Processor Software with Char Map and Spell Checker
