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