Skip to content

Instantly share code, notes, and snippets.

@tonyerskine
Last active February 22, 2024 13:14
Show Gist options
  • Save tonyerskine/77250575b166bec997f33a679a0dfbe4 to your computer and use it in GitHub Desktop.
Save tonyerskine/77250575b166bec997f33a679a0dfbe4 to your computer and use it in GitHub Desktop.
Windows Script to Convert Excel Files to CSV

Instructions

  1. Copy both files into the directory containing the files you want converted
  2. Run excel-to-csv.bat

Note: This script requires Excel to be installed.

REM source: http://stackoverflow.com/a/11252731/715608
FOR /f "delims=" %%i IN ('DIR *.xls* /b') DO to-csv.vbs "%%i" "%%i.csv"
'source: http://stackoverflow.com/a/10835568/715608
if WScript.Arguments.Count < 2 Then
WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
Wscript.Quit
End If
csv_format = 6
Set objFSO = CreateObject("Scripting.FileSystemObject")
src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)
oBook.SaveAs dest_file, csv_format
oBook.Close False
oExcel.Quit
@ZivYekutieli
Copy link

thanks man, that was helpful.
In the bat file, I think you might want to use:
FOR /f "delims=" %%i IN ('DIR .xls /b') DO to-csv.vbs "%%i" "%%~ni.csv"

instead of having filename.xlsx.csv you will get filename.csv (which i guess is what people might want)

@KiraDynnes
Copy link

I think you are right about what people want - but I think you missed a few '*' in your code :-)

FOR /f "delims=" %%i IN ('DIR *.xls* /b') DO to-csv.vbs "%%i" "%%~ni.csv"

in the bat files works for me :-).

@CzechMateBro
Copy link

This is amazing.

I wonder, how would I go about including subfolders?

I have a lot of XLSB files which are structured as hotel/year/month/*.xlsb

@CzechMateBro
Copy link

I found that:

FOR /f "delims=" %%i IN ('DIR .xls /b /s') DO ExcelToCsv.vbs "qrOUTPUT1" "%%i" "%%i.csv"

works also for subfolders, but then it saves .xlsb.csv as the file type... I'm missing some middle point between "%%i.csv" and "%%~ni.csv"

@ravesani-netspinlab
Copy link

Great job man! Saved me a lot of time :P

@webapalooza
Copy link

Works very nicely! But... how can I modify it to:

  1. Overwrite an existing .csv file that has the same name?
  2. Delete the original .xls* file after the conversion is complete?

Thanks!

@webapalooza
Copy link

OK, so I did some searching online, found some batch file commands, modified my process, and eventually answered my own question. Here is what I did:

  1. I do the file conversions in a different folder from where I'm storing the converted files. In other words, I have a "conversion" folder where I do the .xlsx to .csv conversion, and a "files" folder where I'm storing the previously converted .csv files. I then added a MOVE command to my batch file to move the converted .csv files from the "conversion" folder into the "file" folder, like so:

MOVE "C:\conversion\*.csv" "C:\files"

This automatically overwrites any existing .csv files in the "files" folder that have the same name (which is what I want).

  1. I added a DEL command to my batch file to delete the remaining .xlsx files (since they've already been converted to .csv, I no longer need them):

DEL "C:\conversion\*.xlsx"

So my batch file reads as follows:

FOR /f "delims=" %%i IN ('DIR *.xls* /b') DO to-csv.vbs "%%i" "%%~ni.csv"
MOVE "C:\conversion\*.csv" "C:\files"
DEL "C:\conversion\*.xlsx"

Works like a champ! Many thanks for sharing your wisdom! :-)

@urwtueat
Copy link

urwtueat commented Nov 7, 2018

I found that:

FOR /f "delims=" %%i IN ('DIR .xls /b /s') DO ExcelToCsv.vbs "qrOUTPUT1" "%%i" "%%i.csv"

works also for subfolders, but then it saves .xlsb.csv as the file type... I'm missing some middle point between "%%i.csv" and "%%~ni.csv"

Try:
FOR /f "delims=" %%i IN ('DIR .xls /b /s') DO ExcelToCsv.vbs "%%~dpni" "%%~dpni.csv"

@PauloLeixo
Copy link

This script runs fine but with a peculiar effect:

I have defined in Control Panel a pipe "|" as the separator and within Excel itself it saves the datasheet correctly with that separator. In the other hand, in the command line this script always uses the comma as the separator. Anyone knows why this is?

@suckerp
Copy link

suckerp commented Jul 5, 2019

Is there a way to use ; as a delimiter instead of ,? I tried adding , Local:=True in line 20 but I get an error that a statement is expected after the :
When I added , "Local:=True" no more errors but still the , is used instead of ; as delimiter.

Maybe one of you can help me out.

@majidpeidaei
Copy link

thanks for your project
i have a problem with turkish characters in convert time
example ş ğ in excel is normal but when convert to csv change to ???
please help
thanks

@leonet99
Copy link

leonet99 commented Jan 15, 2020

I think you are right about what people want - but I think you missed a few '*' in your code :-)

FOR /f "delims=" %%i IN ('DIR *.xls* /b') DO to-csv.vbs "%%i" "%%~ni.csv"

in the bat files works for me :-).

Doesn't work in my situation...I run Windows 10 Enterprise.
The cmd window shows proper batch run, but I keep getting "Execution of the Windows Script Host failed. (Access is denied.)" from the Windows Script Host.
I shared the .xls file with full access to Everyone, but that didn't change anything.
Can anybody point this newbie in the right direction?
Thx

@vallabhiaf
Copy link

Doesnt Work for me,the cmd opens for a fraction of seconds and closes.
I have xlsx file,
Sorry i am new to scripting

@vallabhiaf
Copy link

It Worked Thankyou

@vallabhiaf
Copy link

There is a issue,It is not converting all the sheets,

@jbnetwork
Copy link

this is fantastic. Saving me so much time. Thank you!

@eriegz
Copy link

eriegz commented Apr 30, 2021

Super-useful! Works like a charm 🥇

@rbxyz026
Copy link

rbxyz026 commented May 4, 2021

Hello, how can I start the export from Column number 3 (to exclude the header) and eliminate the blank cells?

@tonyerskine
Copy link
Author

@rbxyz026
I'm really not sure. I didn't author the original script. I just modified it. I cited the original StackOverflow question in the files in this gist. Maybe there will be more information there. Hope that helps.

@yassinebena
Copy link

Is there a way to use ; as a delimiter instead of ,? I tried adding , Local:=True in line 20 but I get an error that a statement is expected after the : When I added , "Local:=True" no more errors but still the , is used instead of ; as delimiter.

Maybe one of you can help me out.

@suckerp the := thing doesn't work for the .vbs files, so to specify the wanted attrebute (Local) you should secify all the other attributes in order as mentioned in this documentation -> https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas?source=recommendations

so what worked for me is changing the line 20 to this :

oBook.SaveAs dest_file, csv_format, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, TRUE

hope this helps for other people who visite this thred too as i suspect @suckerp won't benifit from a response 4 years later haha

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment