Note: The other languages of the website are Google-translated. Back to English
English English

How to batch convert multiple Excel files to CSV files in Excel?

In Excel, we can convert the workbook to CSV file with the Save as function, but could you know how to batch convert multiple Excel files to separate CSV files? In this article, I introduce a VBA code to batch convert all Excel files in a folder to CSV files in Excel.

Batch convert Excel files of a folder to CSV files with VBA

Convert sheets of workbook into separate CSV files with Kutools for Excelgood idea3


Batch convert Excel files of a folder to CSV files with VBA

In Excel, there is no built-in function can solve this job quickly except VBA.

1. Enable Excel, and press Alt + F11 keys open Microsoft Visual Basic for Applications window.

2. Click Insert > Module to create a new Module.

3. Copy below code and paste them to the new Module window.

VBA: Batch convert Excel files to CSV

Sub WorkbooksSaveAsCsvToFolder()

'UpdatebyExtendoffice20181031

Dim xObjWB As Workbook

Dim xObjWS As Worksheet

Dim xStrEFPath As String

Dim xStrEFFile As String

Dim xObjFD As FileDialog

Dim xObjSFD As FileDialog

Dim xStrSPath As String

Dim xStrCSVFName As String

Dim xS  As String

    Application.ScreenUpdating = False

    Application.EnableEvents = False

    Application.Calculation = xlCalculationManual

    Application.DisplayAlerts = False

    On Error Resume Next

Set xObjFD = Application.FileDialog(msoFileDialogFolderPicker)

    xObjFD.AllowMultiSelect = False

    xObjFD.Title = "Kutools for Excel - Select a folder which contains Excel files"

    If xObjFD.Show <> -1 Then Exit Sub

    xStrEFPath = xObjFD.SelectedItems(1) & "\"

    Set xObjSFD = Application.FileDialog(msoFileDialogFolderPicker)

 
    xObjSFD.AllowMultiSelect = False

    xObjSFD.Title = "Kutools for Excel - Select a folder to locate CSV files"

    If xObjSFD.Show <> -1 Then Exit Sub

    xStrSPath = xObjSFD.SelectedItems(1) & "\"


    xStrEFFile = Dir(xStrEFPath & "*.xls*")


    Do While xStrEFFile <> ""

       xS = xStrEFPath & xStrEFFile

        Set xObjWB = Application.Workbooks.Open(xS)

        xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".") - 1) & ".csv"

        xObjWB.SaveAs Filename:=xStrCSVFName, FileFormat:=xlCSV

        xObjWB.Close savechanges:=False

        xStrEFFile = Dir

  Loop

    Application.Calculation = xlCalculationAutomatic

    Application.EnableEvents = True

    Application.ScreenUpdating = True

    Application.DisplayAlerts = True

End Sub

4. Press F5 key, select the folder contains the Excel files you want to convert to CSV files in first popping dialog.
doc batch to csv 1

5. Click OK, then in the second popping dialog, select the folder to place the CSV files.
doc batch to csv 2

6. Click OK, now the Excel files in the folder have been converted to CSV files and saved in another folder.
doc batch to csv 3


Convert sheets of workbook into separate CSV files with Kutools for Excel

As we known, we only can convert the whole workbook into one CSV file in Excel with its Save As function. But in some times, you want to convert the single sheet into CSV file, in this case, the Split Workbook utility of Kutools for Excel can help you.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Enable workbook you want to convert its sheets as separate CSV files, click Kutools Plus > Workbook > Split Workbook.
doc batch to csv 4

2. In the Split Workbook dialog, check the sheet name you want to split (all sheets are checked by default), check Save as type, choose CSV (Macintosh) (*.CSV) from the drop-down list.
doc batch to csv 5

3. Click Split to pop out a Browse For Folder dialog, choose or create a folder to place the CSV files.
doc batch to csv 6

4. Click OK, now the workbook has been split as separate CSV files.
doc batch to csv 7


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (33)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Ty it really works dear !!
This comment was minimized by the moderator on the site
Works great, thanks for the code!
My only remark would be that this code cuts of file names when there is a "." in the filename itself (e.g. file.123.csv turns into file.csv).
This comment was minimized by the moderator on the site
Have you found a way around this issue?
This comment was minimized by the moderator on the site
Carol,

On line 33 I've replaced this code:

xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".") - 1) & ".csv"

With this code:

xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".xlsx") - 1) & ".csv"

Note that if you're using some other excel extension (.xls, .xlsm, etc.) you should change it as such :)
This comment was minimized by the moderator on the site
Thank you so much! This has saved me so much time!!
This comment was minimized by the moderator on the site
Another small remark:

If the cells in the original Excel files are all formatted as "General", some accuracy is lost when the file is saved as a CSV
For example, if a cell value in Excel is 0.123456789123456, then the value in the CSV will be 0.123456789 (missing the remaining decimals), as long as the cell was formatted as 'General'. This can be solved by formatting all cells in the Excel file to anything else than 'General' (for example, 'Text'). In that case, the CSV *will* still have the full detail/accuracy. I.e. the values in the Excel files will be fully intact after saving as a CSV.

How could this macro be changes, so it sets the formatting of all cells in the Excel file to 'Text', before saving as a CSV?
I imagine that it must somehow make use of the following, but I can't figure out how to correctly include in within the macro:

Cells.Select
Selection.NumberFormat = "@"
This comment was minimized by the moderator on the site
Thank you for sharing. I'm trying to save out multiple xls files which contain a unique value, producing a prompt asking yes or no before saving. The prompt reads..

"Some features in your workbook might be lost if you save it as a CSV (Comma delimited). Do you want to keep using that format?"

Would someone know where to add the code to answer yes to this prompt?
This comment was minimized by the moderator on the site
I think it is worth adding better error handling for files with special characters, currently they are simply ignored.
This comment was minimized by the moderator on the site
top thanks :)
This comment was minimized by the moderator on the site
This is amazing. Thank-you!
This comment was minimized by the moderator on the site
Hello, is there a quick change to the code that would allow me to change from a CSV UTF-8 (Comma delimited) to just CSV (Comma delimited)? I tried the first method and was hopeful but it seems like it won't change them since they are already in some form of CSV. Maybe there is an easier process but I can't find anything. I have to convert maybe 150 files that were saved in this format and I don't want to open every file and Save As if I can avoid it. Any help is appreciated!
This comment was minimized by the moderator on the site
So, after reviewing the code a little closer, I saw where the initial file type had to be .xls. So replacing with .csv has solved the issue.
This comment was minimized by the moderator on the site
Hallo,
das Makro funktioniert ganz gut, aber bei mir kommt, wenn ich als filename:xlCSVUTF8 eine anders formatierte CSV raus als ich über speichern unter -> csv(utf8) erreiche!
This comment was minimized by the moderator on the site
xObjWB.SaveAs Filename:=xStrCSVFName, FileFormat:=xlCSVUTF8
This comment was minimized by the moderator on the site
also er schreibt die verschiedenen Spalten alle in eine Spalte, dass müsste der Fehler sein ?!
This comment was minimized by the moderator on the site
hi.. in some excel files that will be converted to csv there is a nominal that must be rounded, what is the solution? thank you
This comment was minimized by the moderator on the site
Hi, Sam, there is no solution to solve this problem except to format the data as text before converting file to csv.
This comment was minimized by the moderator on the site
When I ran this it only converted the first worksheet, and ignored additional sheets within the workbook. Is there a way to edit the code to include additional sheets?
This comment was minimized by the moderator on the site
Hi, you can try below code, it will save all sheets of workbooks in the folder to csv seperately. It cannot work the current workbook.
Sub WorkbooksSaveAsCsvToFolder()

'UpdatebyExtendoffice20220520

Dim xObjWB As Workbook

Dim xObjWS As Worksheet

Dim xStrEFPath As String

Dim xStrEFFile As String

Dim xObjFD As FileDialog

Dim xObjSFD As FileDialog

Dim xStrSPath As String

Dim xStrCSVFName As String

Dim xWSh As Worksheet

    Application.ScreenUpdating = False

    Application.EnableEvents = False

    Application.Calculation = xlCalculationManual

    On Error Resume Next

Set xObjFD = Application.FileDialog(msoFileDialogFolderPicker)

    xObjFD.AllowMultiSelect = False

    xObjFD.Title = "Kutools for Excel - Select a folder which contains Excel files"

    If xObjFD.Show <> -1 Then Exit Sub

    xStrEFPath = xObjFD.SelectedItems(1) & "\"

    Set xObjSFD = Application.FileDialog(msoFileDialogFolderPicker)

    xObjSFD.AllowMultiSelect = False

    xObjSFD.Title = "Kutools for Excel - Select a folder to locate CSV Files "

    If xObjSFD.Show <> -1 Then Exit Sub

    xStrSPath = xObjSFD.SelectedItems(1) & "\"

    xStrEFFile = Dir(xStrEFPath & "*.xls*")

    Do While xStrEFFile <> ""

        Set xObjWB = Workbooks.Open(Filename:=xStrEFPath & xStrEFFile)

        For Each xWSh In xObjWB.Worksheets

            xWSh.Activate

            xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".") - 1) & "_" & xWSh.Name & ".csv"

            xObjWB.SaveAs Filename:=xStrCSVFName, FileFormat:=xlCSV

        Next

        xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".") - 1) & ".csv"

        xObjWB.SaveAs Filename:=xStrCSVFName, FileFormat:=xlCSV

        xObjWB.Close savechanges:=False

        xStrEFFile = Dir

  Loop

    Application.Calculation = xlCalculationAutomatic

    Application.EnableEvents = True

    Application.ScreenUpdating = True

End Sub
This comment was minimized by the moderator on the site
Haven't been able to get this to work for my xls files. They're 97-03 worksheets and I'm currently running the most active microsoft 365 version. When I run the code the folder comes up as blank
This comment was minimized by the moderator on the site
Hi, I am sorry that your problem does not reappear in my version, I have no idea to help you.
This comment was minimized by the moderator on the site
Same issue with me. Running latest office 365 but nothing happens after setting the two folders dialog boxes.
This comment was minimized by the moderator on the site
Hi, Doc SJ, thanks for your reminder, I have check the VBA code, it has some issues in Office 365, now I have updated the VBA, please try it again.
This comment was minimized by the moderator on the site
Bom Dia!!

muito top esse codigo, porem estou com o problema abaixo

Tem como salvar em formato "CSV (separado por vírgulas) (*.csv)", pois ao executar esse código meus arquivos ficam com "," no formato que eu preciso eles não ficam com essa vírgula
This comment was minimized by the moderator on the site
2019버전입니다.
게시글 그대로 실행해봤지만 아무것도 일어나지 않았습니다 ..
This comment was minimized by the moderator on the site
Hi, YOY, I have tested the VBA in Professional Plus Excel 2019, it works smoothly and correctly. Should you give me more details about your files, such as detailed verison, the contents of files you want to convert?
This comment was minimized by the moderator on the site
Hi, How can i change the delimiter ?
In the script above, the delimiter is comma (,).
Thank you :D
This comment was minimized by the moderator on the site
Hi,
I'm facing some problems when using the script
Ho can I change the delimiter in the script because the delimiter in csv output is in comma (,)
I need the delimiter not in comma because my datasets consist a lot of commas.


Thank you
This comment was minimized by the moderator on the site
Ajouter Local:=True à cette ligne:
xObjWB.SaveAs Filename:=xStrCSVFName, FileFormat:=xlCSV, Local:=True

Permet de prendre le séparateur de votre système, moi c'est point-virgule ;
This comment was minimized by the moderator on the site
Hallo,

vor einem Jahr habe ich den VBA-Code ohne Probleme ausgeführt. Heute wollte ich es nochmal versuchen, leider gibt es einen Laufzeitfehler 1004 mit dem Hinweis
"Die Methode 'Calculation' ist für Objekt '_Application' fehlgeschlagen."

Debuggen verweist auf folgende Zeile:
Application.Calculation = xlCalculationManual

Kann jemand weiterhelfen?

LG, Max
This comment was minimized by the moderator on the site
Hallo,

ich habe den VBA-Code vor einem Jahr ohne Probleme angewendet. Jetzt funktioniert es nicht mehr.
Es gibt einen Laufzeitfehler 1004 mit dem Hinweis: "Die Methode 'calculation' für das Objekt '_Application' ist fehlgeschlagen."

Debuggen verweist auf die Zeile "Application.Calculation = xlCalculationManual".

Ich würde mich sehr freuen, wenn jemand weiterhelfen könnte.

LG, Max
This comment was minimized by the moderator on the site
Hi, Gast, pleae check if the code you copied is correct firstly, there is no "_" in the code. If you have checked all code is correct, you can remove the code line
Application.Calculation = xlCalculationManual then try again. Please notice that there are two lines Application.Calculation = xlCalculationManual in the code.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations