How to Convert Multiple Excel Files to CSV (3 Suitable Ways)

The full form of CSV is Comma Separated Value. That means t values in CSV are separated only by commas. This is an essential data format in database management. We also use Excel in many data management projects. So it is quite normal to exchange data between these two formats. Although we can easily convert CSV format files to Excel using text to column feature, for converting multiple Excel to CSV there is no direct method that exists to date. You need to use VBA or online tools for this purpose. How you can convert multiple Excel files to CSV is discussed here with an adequate explanation.

How to Convert Multiple Excel Files to CSV: 3 Suitable Ways

For the demonstration purpose, we are going to use the following data set, which is fairly easy to understand. We have the Date of Purchase, Region, Product, and Quantity as the table header.

1. Embedding VBA to Convert Multiple Excel Files to CSV

Using a simple VBA Macro can drastically reduce the time to convert multiple Excel files to CSV format. This method will directly load Excel files and then convert them to CSV format files.

Steps

• First, go to the Developer tab, then click Visual Basic.

• Then click Insert > Module.

• In the Module window, enter the following code.

Sub convert_Excel_to_csv()

Dim Con_Mul_Excel As Workbook
Dim Con_Mul_Excel_ws As Worksheet
Dim Con_Mul_Excel_path As String
Dim Con_Mul_Excel_file As String
Dim Con_Mul_Excel_fd As FileDialog
Dim Con_Mul_Excel_sfd As FileDialog
Dim Con_Mul_Excel_spath As String
Dim Con_Mul_Excel_name As String

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

On Error Resume Next
Set Con_Mul_Excel_fd = Application.FileDialog(msoFileDialogFolderPicker)
Con_Mul_Excel_fd.AllowMultiSelect = False
Con_Mul_Excel_fd.Title = "Select the Folder for Your Excel Files"
If Con_Mul_Excel_fd.Show <> -1 Then Exit Sub
Con_Mul_Excel_path = Con_Mul_Excel_fd.SelectedItems(1) & "\"
Set Con_Mul_Excel_sfd = Application.FileDialog(msoFileDialogFolderPicker)
Con_Mul_Excel_sfd.AllowMultiSelect = False
Con_Mul_Excel_sfd.Title = "Select Destination Folder"

If Con_Mul_Excel_sfd.Show <> -1 Then Exit Sub
Con_Mul_Excel_spath = Con_Mul_Excel_sfd.SelectedItems(1) & "\"
Con_Mul_Excel_file = Dir(Con_Mul_Excel_path & "*.xls*")

Do While Con_Mul_Excel_file <> ""
Set Con_Mul_Excel = Workbooks.Open(Filename:=Con_Mul_Excel_path & Con_Mul_Excel_file)
Con_Mul_Excel_name = Con_Mul_Excel_spath & Left(Con_Mul_Excel_file, InStr(1, Con_Mul_Excel_file, ".") - 1) & ".csv"
Con_Mul_Excel.SaveAs Filename:=Con_Mul_Excel_name, FileFormat:=xlCSV
Con_Mul_Excel.Close savechanges:=False
Con_Mul_Excel_file = Dir
Loop

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

• Then close the window.
• After that, go to View tab > Macros(Double click).

• After clicking View Macros, select the macros that you created just now. The name here is convert_Excel-to CSV. Then click Run.

• After clicking Run, you will see that a new browser window will open, and from that window, you need to select the folder where your multiple Excel files are stored, select the folder and click OK.

• Then again another window will open asking the location of the CSV file after they converted from Excel files. Select the destination folder and click OK after this.

• Next, you will notice the circulating loading icon. After this check the destination folder for the converted CSV files.
• In the destination folder, you will notice that all three converted CSV files are there and their file extension is showing as CSV.

• Click on Dataset_1 for inspection.
• After clicking the file you will notice the file opened and showing as a CSV file rather than an Excel file.
• That affirms that the conversion is successful.

• Click on Dataset_2 for inspection.
• After clicking the file you will notice the file opened and showing as a CSV file rather than an Excel file.
• That affirms that the conversion is successful.

• Click on Dataset_3 for inspection.
• After clicking the file you will notice the file opened and showing as a CSV file rather than an Excel file.
• That affirms that the conversion is successful.

Read More: How to Save Excel File as CSV with Commas

2. Combine Multiple Excel Files and Convert Them to CSV

We are going to combine several Excel files altogether in a single workbook as worksheets. And then export those worksheets as individual CSV files.

Steps

• First, go to the Developer tab, then click Visual Basic.

• Then click Insert > Module.

• In the Module window, enter the following code.

Sub Combine_Excel_Files()

Dim fnList, fnCurFile As Variant
Dim cFiles, cSheets As Integer
Dim wkCurSheet As Worksheet

Dim wbCurBook, wbSrcBook As Workbook

fnList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
If (vbBoolean <> VarType(fnList)) Then
If (UBound(fnList) > 0) Then
cFiles = 0
cSheets = 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wbCurBook = ActiveWorkbook
For Each fnCurFile In fnList
cFiles = cFiles + 1
Set wbSrcBook = Workbooks.Open(Filename:=fnCurFile)
For Each wkCurSheet In wbSrcBook.Sheets
cSheets = cSheets + 1
wkCurSheet.Copy after:=wbCurBook.Sheets(wbCurBook.Sheets.Count)
Next
wbSrcBook.Close SaveChanges:=False
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Worked on " & cFiles & " files" & vbCrLf & "Joined " & cSheets & " worksheets", Title:="Merging Multiple Excel Files"
End If
Else
MsgBox "No File is Selected", Title:="Merging Multiple Excel files"
End If

End Sub

• Then close the window.
• After that, go to View tab > Macros(Double click).

• After clicking View Macros, select the macros that you created just now. The name here is Combine_Excel_Files. Then click Run.

• After that, a new file browser window will open, and from that window, you need to select the files that you want to merge into a single Excel file.
• Select the files and click Open.

• After clicking Open, you will notice that there are loading screens one by one for each of the sheets. After a few moments, the below window will spawn with the following message.

• Then, you will see three different Excel workbooks are now loaded into the active Excel Workbook as individual worksheets.

• Next, go to the Developer tab, then click Visual Basic.

• Then click Insert > Module.

• In the Module window, enter the following code.

Sub Excel_TO_CSV()

Dim wrsh As Worksheet
Dim excsv As String

For Each wrsh In Application.ActiveWorkbook.Worksheets
wrsh.Copy
excsv = CurDir & "\" & wrsh.Name & ".csv"
Application.ActiveWorkbook.SaveAs Filename:=excsv, _
FileFormat:=xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next

End Sub

• Then close the window.
• After that, go to View tab > Macros(Double Click).

• After clicking View Macros, select the macros that you created just now. The name here is Excel_TO_CSV. Then click Run.

• After clicking run, there will be moments of the loading phase in the worksheet.
• Then you need the file documents folder on your computer. In this directory, you are going to see the converted CSV files.
• You will notice that the file extensions of these files are showing in CSV format.

• Open Dataset_1, Then you will notice that the file is In CSV format and is converted from the Excel worksheet.

Read More: Set Comma Instead of Semicolon as Delimiter in Excel CSV

3. Utilizing Online Conversion Tool

If you have online access, this is probably the easiest way of converting an Excel file to CSV format. You just have to upload the necessary files and click convert. After completion of the conversion, you just have to download the files to your computer.

Steps

• Go to XLSX (EXCEL) to CSV (Online & Free) — Convertio. This is a very good online converter to convert multiple Excel sheets To CSV format.
• Select the input format and the output format of the files. Here the input format will be XLSX, then select the output format CSV.
• Next, On the website click on the Choose Files. icon.

• A file browse menu will open, from that menu select the files that need to be converted and click Open. In this case, we selected Dataset_1, Datset_2, Dataset_3.

• All three of them will now be uploaded to the website, and their destination format will show CSV Click Convert after this.

• After clicking Convert, the conversion process will start.
• Then click Download on the right side of each file to download the converted files.

• After completion of the download, open the download folder and notice that all of the Excel files are now converted to CSV format.

• Open Dataset_1 for inspection.
• Notice the file is now open as a CSV file. That means the conversion is successful.

Read More: Save Excel as CSV with Double Quotes

Download Practice Workbook

Conclusion

To sum it up, the question “how to convert multiple Excel files into CSV ” is answered here in 3 different ways. We used 2 different VBA approaches and one online converter method. Among all of the methods used here, using the online conversion method is the most easier to do and time-saving. The VBA process is also less time-consuming but requires prior VBA-related knowledge. The online method doesn’t have such a requirement.

For this problem, a macro-enabled workbook is attached where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.

Related Articles

• How to Create CSV File from Excel
• How to Convert Excel File to Text File with Comma Delimited
• Convert Excel to Comma Delimited CSV File
• How to Make a CSV File in Excel for Contacts
• How to Convert Excel Files to CSV Automatically
• How to Convert Excel to CSV without Opening
• [Fixed!] Excel Not Saving CSV with Commas

<< Go Back to Export Excel to CSV | Export Data from Excel | Learn Excel