Windows Script to Convert Excel Files to CSV

README.MD

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.

excel-to-csv.bat

REM source: http://stackoverflow.com/a/11252731/715608
FOR /f "delims=" %%i IN ('DIR *.xls* /b') DO to-csv.vbs "%%i" "%%i.csv"

to-csv.vbs

'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

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)

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

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

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"

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

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!

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

2. 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! :-)

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"

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?

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.

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

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

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

It Worked Thankyou

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

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

Super-useful! Works like a charm 🥇

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

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

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