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.

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.

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.

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

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?

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

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

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

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.

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.

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 UTF-8 works for Microsoft Office 2016 and higher!

Try this code:

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 = 62

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

Create a TXT file on your desktop named "xls2csv.vbs" and paste the code:

Dim vExcel
Dim vCSV
Set vExcel = CreateObject("Excel.Application")
Set vCSV = vExcel.Workbooks.Open(Wscript.Arguments.Item(0))
vCSV.SaveAs WScript.Arguments.Item(0) & ".csv", 6
vCSV.Close False
vExcel.Quit

Drag a XLS file to it (like "test.xls"). It will create a converted CSV file named "test.xls.csv". Then, rename it to "test.csv". Done.

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.

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