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