Convert XLS to CSV on command line
Clash Royale CLAN TAG#URR8PPP
Convert XLS to CSV on command line
How could I convert an XLS file to a CSV file on the windows command line.
The machine has Microsoft Office 2000 installed. I'm open to installing OpenOffice if it's not possible using Microsoft Office.
13 Answers
13
Open Notepad, create a file called XlsToCsv.vbs and paste this in:
if WScript.Arguments.Count < 2 Then
WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"
Then from a command line, go to the folder you saved the .vbs file in and run:
XlsToCsv.vbs [sourcexlsFile].xls [destinationcsvfile].csv
This requires Excel to be installed on the machine you are on though.
Requires fully resolves path names on my XP instance.
– Andrew
Mar 23 '11 at 6:25
I have posted below a slightly modified version which handles file paths better. Thanks ScottF!
– plang
May 31 '12 at 14:26
The code converts only the active worksheet. To select another worksheet, add the following line after the
oExcel.Workbooks.Open
line with the desired index of the worksheet (starts at 1): oBook.Worksheets(1).Activate
– humbads
Oct 30 '13 at 18:19
oExcel.Workbooks.Open
oBook.Worksheets(1).Activate
It ought to be noted that this functional not just on xls or xlsx, but on any file that Excel itself can open.
– user1318135
Aug 23 '17 at 19:26
A slightly modified version of ScottF answer, which does not require absolute file paths:
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
I have renamed the script ExcelToCsv, since this script is not limited to xls at all. xlsx Works just fine, as we could expect.
Tested with Office 2010.
I am using this (with few adaptations) to convert from XML to XLS. However, I don't want to have the compatibility warning message box from Excel during this conversion. Do you know how can I disable this warning?
– jpnavarini
Aug 8 '12 at 14:18
Hi, I'm sorry, no idea! :)
– plang
Aug 8 '12 at 14:28
you saved my day. nice work
– Pushker Yadav
Aug 6 '15 at 18:44
I put this answer together with @user565869 's answer in a Gist with simple instructions. See: Script to convert Excel File to CSV
– 10GritSandpaper
Jan 6 '17 at 16:43
That's nice put together @10GritSandpaper
– amrrs
Nov 6 '17 at 8:13
A small expansion on ScottF's groovy VB script: this batch file will loop through the .xlsx files in a directory and dump them into *.csv files:
FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%i.csv"
Note: You may change extension .xlsx to .xls andname of script ExcelToCSV to XlsToCsv
@Rieaux: Regarding your comment-as-an-edit: if this gives the files a double extension, a second simple batch file can rename them. This is drifting into a new question, though; please give it a try and, if you're unable to make it work, post a new question here on SU.
– user565869
Sep 30 '13 at 20:20
this automation saved my life. :) Thank u
– Pushker Yadav
Aug 6 '15 at 18:45
I put this answer together with @plang 's answer in a Gist with simple instructions. See: Script to convert Excel File to CSV
– 10GritSandpaper
Jan 6 '17 at 16:44
@10GritSandpaper Using Excel 2007. Script in your link didn't work for me.
– Boris_yo
Dec 18 '17 at 8:34
How about with PowerShell?
Code should be looks like this, not tested though
$xlCSV = 6
$Excel = New-Object -Com Excel.Application
$Excel.visible = $False
$Excel.displayalerts=$False
$WorkBook = $Excel.Workbooks.Open("YOUDOC.XLS")
$Workbook.SaveAs("YOURDOC.csv",$xlCSV)
$Excel.quit()
Here is a post explaining how to use it
How Can I Use Windows PowerShell to Automate Microsoft Excel?
This looks like a good approach. Unfortunately, I couldn't get it going. I'm not familiar with PowerShell, so when I ran into an error I didn't know what to do. I couldn't find a PowerShell-specifc solution: support.microsoft.com/kb/320369
– Joel
Dec 7 '09 at 8:46
Here is some tips for powershell and excel, blogs.technet.com/heyscriptingguy/archive/2006/09/08/…
– YOU
Dec 7 '09 at 8:53
I did a test run of this and ran into problems as well. One thing I ran into was difficulty with the
$Excel.Workbooks.Open
method. It couldn't find the specified file. I worked around this by using Get-Item
on the file and piping it to a ForEach-Object
loop (something I'll end up doing in my final implementation anyway) for the two lines starting with $Workbook
.– Iszi
Feb 3 '15 at 15:12
$Excel.Workbooks.Open
Get-Item
ForEach-Object
$Workbook
That fixed that problem, but then I couldn't find the resulting "YOURDOC.csv" - it wasn't in the same folder as "YOUDOC.XLS". I went back to old & trusty CMD and did
CD /D C: && DIR YOURDOC.csv /s
. Turns out the file was saved into My Documents by default. So, you need to put more into the script if you want to save the file to the same folder you're working in (if other than My Documents).– Iszi
Feb 3 '15 at 15:13
CD /D C: && DIR YOURDOC.csv /s
I had a need to extract several cvs from different worksheets, so here is a modified version of plang code that allows you to specify the worksheet name.
if WScript.Arguments.Count < 3 Then
WScript.Echo "Please specify the sheet, the source, the destination files. Usage: ExcelToCsv <sheetName> <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(1))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(2))
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)
oBook.Sheets(WScript.Arguments.Item(0)).Select
oBook.SaveAs dest_file, csv_format
oBook.Close False
oExcel.Quit
Why not write your own?
I see from your profile you have at least some C#/.NET experience. I'd create a Windows console application and use a free Excel reader to read in your Excel file(s). I've used Excel Data Reader available from CodePlex without any problem (one nice thing: this reader doesn't require Excel to be installed). You can call your console application from the command line.
If you find yourself stuck post here and I'm sure you'll get help.
Actually, I have never written any C# ever. But I think I'll give it a crack with the Excel Data Reader.
– Joel
Dec 7 '09 at 8:49
A bit overkill don't you think. Smells of NIH.
– Mr. Boy
Sep 17 '10 at 9:56
I don't think the Excel Data Reader is NIH. First of all someone else wrote it. Secondly, it solved the problem better than full blown Excel.
– Justin Dearing
Mar 24 '11 at 21:12
Here is a version that will handle multiple files drag and dropped from windows.
Based on the above works by
Christian Lemer
plang
ScottF
Open Notepad, create a file called XlsToCsv.vbs and paste this in:
'* Usage: Drop .xl* files on me to export each sheet as CSV
'* Global Settings and Variables
Dim gSkip
Set args = Wscript.Arguments
For Each sFilename In args
iErr = ExportExcelFileToCSV(sFilename)
' 0 for normal success
' 404 for file not found
' 10 for file skipped (or user abort if script returns 10)
Next
WScript.Quit(0)
Function ExportExcelFileToCSV(sFilename)
'* Settings
Dim oExcel, oFSO, oExcelFile
Set oExcel = CreateObject("Excel.Application")
Set oFSO = CreateObject("Scripting.FileSystemObject")
iCSV_Format = 6
'* Set Up
sExtension = oFSO.GetExtensionName(sFilename)
if sExtension = "" then
ExportExcelFileToCSV = 404
Exit Function
end if
sTest = Mid(sExtension,1,2) '* first 2 letters of the extension, vb's missing a Like operator
if not (sTest = "xl") then
if (PromptForSkip(sFilename,oExcel)) then
ExportExcelFileToCSV = 10
Exit Function
end if
End If
sAbsoluteSource = oFSO.GetAbsolutePathName(sFilename)
sAbsoluteDestination = Replace(sAbsoluteSource,sExtension,"sheet.csv")
'* Do Work
Set oExcelFile = oExcel.Workbooks.Open(sAbsoluteSource)
For Each oSheet in oExcelFile.Sheets
sThisDestination = Replace(sAbsoluteDestination,"sheet",oSheet.Name)
oExcelFile.Sheets(oSheet.Name).Select
oExcelFile.SaveAs sThisDestination, iCSV_Format
Next
'* Take Down
oExcelFile.Close False
oExcel.Quit
ExportExcelFileToCSV = 0
Exit Function
End Function
Function PromptForSkip(sFilename,oExcel)
if not (VarType(gSkip) = vbEmpty) then
PromptForSkip = gSkip
Exit Function
end if
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
sPrompt = vbCRLF & _
"A filename was received that doesn't appear to be an Excel Document." & vbCRLF & _
"Do you want to skip this and all other unrecognized files? (Will only prompt this once)" & vbCRLF & _
"" & vbCRLF & _
"Yes - Will skip all further files that don't have a .xl* extension" & vbCRLF & _
"No - Will pass the file to excel regardless of extension" & vbCRLF & _
"Cancel - Abort any further conversions and exit this script" & vbCRLF & _
"" & vbCRLF & _
"The unrecognized file was:" & vbCRLF & _
sFilename & vbCRLF & _
"" & vbCRLF & _
"The path returned by the system was:" & vbCRLF & _
oFSO.GetAbsolutePathName(sFilename) & vbCRLF
sTitle = "Unrecognized File Type Encountered"
sResponse = MsgBox (sPrompt,vbYesNoCancel,sTitle)
Select Case sResponse
Case vbYes
gSkip = True
Case vbNo
gSkip = False
Case vbCancel
oExcel.Quit
WScript.Quit(10) '* 10 Is the error code I use to indicate there was a user abort (1 because wasn't successful, + 0 because the user chose to exit)
End Select
PromptForSkip = gSkip
Exit Function
End Function
Is there a way to do UTF-8 Encoding?
– StuBob
Sep 27 '17 at 17:54
How to skip writing header in the target csv file. I absolutely dont understand the above script , but I'm using it in my automation. Thank You.
– TharunRaja
Nov 22 '17 at 14:58
@TharunRaja The script opens the file in excel, then does a "save as" to CSV, just like if you'd done it by hand except it hides it in the background. Because the script itself isn't doing the conversion, and you're automating it, my suggestion would be to call a second script on the csv files this outputs, message me if you need help making one that strips out the first line of a file you pass it.
– Chris Rudd
Nov 26 '17 at 2:41
Building on what Jon of All Trades has provided, the following (~n) removed the pesky double extension issue:FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%~ni.csv"
FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%~ni.csv"
You can do it with Alacon - command-line utility for Alasql database. It works with Node.js, so you need to install Node.js and then Alasql package.
To convert Excel file to CVS (ot TSV) you can enter:
> node alacon "SELECT * INTO CSV('mydata.csv', headers:true) FROM XLS('mydata.xls', headers:true)"
By default Alasql converts data from "Sheet1", but you can change it with parameters:
headers:false, sheetid: 'Sheet2', range: 'A1:C100'
Alacon supports other type of conversions (CSV, TSV, TXT, XLSX, XLS) and SQL language constructions (see User Manual for examples).
Wow! Alasql is really powerful.
– Lukasz Wiktor
Aug 29 at 11:01
If you install AlaSQL globally (npm install alasql -g) then you can use simply > alasql "SELECT... INTO CSV(...) FROM XLS(...)"
– agershun
Aug 29 at 21:18
There's an Excel OLEDB data provider built into Windows; you can use this to 'query' the Excel sheet via ADO.NET and write the results to a CSV file. There's a small amount of coding required, but you shouldn't need to install anything on the machine.
I tried ScottF VB solution and got it to work. However I wanted to convert a multi-tab(workbook) excel file into a single .csv file.
This did not work, only one tab(the one that is highlighted when I open it via excel) got copied.
Is any one aware of a script that can convert a multi-tab excel file into a single .csv file?
Scott F's answer is the best I have found on the internet. I did add on to his code to meet my needs. I added:
On Error Resume Next <- To account for a missing xls files in my batch processing at the top.
oBook.Application.Columns("A:J").NumberFormat = "@" <- Before the SaveAs line to make sure my data is saved formatted as text to keep excel from deleting leading zero's and eliminating commas in number strings in my data i.e. (1,200 to 1200). The column range should be adjusted to meet your neeeds (A:J).
I also removed the Echo "done" to make it non interactive.
I then added the script into a cmd batch file for processing automated data on an hourly basis via a task.
I think you should consider posting final version of the code with comments.
– default locale
Nov 3 '12 at 6:45
All of these answers helped me construct the following script which will automatically convert XLS* files to CSV and vice versa, by dropping one or more files on the script (or via command line). Apologies for the janky formatting.
' https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line
' https://gist.github.com/tonyerskine/77250575b166bec997f33a679a0dfbe4
' https://stackoverflow.com/a/36804963/1037948
'* Global Settings and Variables
Set args = Wscript.Arguments
For Each sFilename In args
iErr = ConvertExcelFormat(sFilename)
' 0 for normal success
' 404 for file not found
' 10 for file skipped (or user abort if script returns 10)
Next
WScript.Quit(0)
Function ConvertExcelFormat(srcFile)
if IsEmpty(srcFile) OR srcFile = "" Then
WScript.Echo "Error! Please specify at least one source path. Usage: " & WScript.ScriptName & " SourcePath.xls*|csv"
ConvertExcelFormat = -1
Exit Function
'Wscript.Quit
End If
Set objFSO = CreateObject("Scripting.FileSystemObject")
srcExt = objFSO.GetExtensionName(srcFile)
' the 6 is the constant for 'CSV' format, 51 is for 'xlsx'
' https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlfileformat-enumeration-excel
' https://www.rondebruin.nl/mac/mac020.htm
Dim outputFormat, srcDest
If LCase(Mid(srcExt, 1, 2)) = "xl" Then
outputFormat = 6
srcDest = "csv"
Else
outputFormat = 51
srcDest = "xlsx"
End If
'srcFile = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
srcFile = objFSO.GetAbsolutePathName(srcFile)
destFile = Replace(srcFile, srcExt, srcDest)
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(srcFile)
' preserve formatting? https://stackoverflow.com/a/8658845/1037948
'oBook.Application.Columns("A:J").NumberFormat = "@"
oBook.SaveAs destFile, outputFormat
oBook.Close False
oExcel.Quit
WScript.Echo "Conversion complete of '" & srcFile & "' to '" & objFSO.GetFileName(destFile) & "'"
End Function
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
In case anyone was wondering, the parameter 6 in the oBook.SaveAs function is the constant for the CSV format.
– ScottF
Dec 7 '09 at 14:23