Convert XLS to CSV on command line

Question

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.

Solution 1

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.

Solution 2

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.

Solution 3

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

Solution 4

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?

Solution 5

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

View more solutions

Comments

[Ganymede]

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

[Ganymede]

Actually, I have never written any C# ever. But I think I'll give it a crack with the Excel Data Reader.

[YOU]

Here is some tips for powershell and excel, blogs.technet.com/heyscriptingguy/archive/2006/09/08/…

[ScottF]

In case anyone was wondering, the parameter 6 in the oBook.SaveAs function is the constant for the CSV format.

[Mr. Boy]

A bit overkill don't you think. Smells of NIH.

[Justin Dearing]

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.

[plang]

Works just fine, and not only for xls files, but also xlsx. As said by Andrew, file paths must either be absolute, or in the user "data" directory (I'm not sure what is the exact name in english). I still haven't figured out how to solve that, I'm not doing much vbscript! :)

[plang]

I have posted below a slightly modified version which handles file paths better. Thanks ScottF!

[jpnavarini]

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]

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?

[default locale]

I think you should consider posting final version of the code with comments.

[Admin]

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

[humbads]

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

[Stefano Lombardi]

Works perfectly. Is there a way to keep the intial cells formatting in the final csv file? (in my case the content of some cells of th Excel file is crossed and I'd like to have it crossed also in the csv)

[Iszi]

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]

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

[Mark Kram]

You are a life saver!

[Pushker Yadav]

this automation saved my life. :) Thank u

[Firee]

Is it possible to convert all files in the folder from XLSX to XLSB?

[rjv]

Is there any way to save this file as Unicode char set?

[fatih_dur]

If anyone keen to associate this vbs in a Python process, refer to stackoverflow.com/questions/19112944/…

[SGT Grumpy Pants]

I put this answer together with @user565869 's answer in a Gist with simple instructions. See: Script to convert Excel File to CSV

[SGT Grumpy Pants]

I put this answer together with @plang 's answer in a Gist with simple instructions. See: Script to convert Excel File to CSV

[user1318135]

It ought to be noted that this functional not just on xls or xlsx, but on any file that Excel itself can open.

[StuBob]

Is there a way to do UTF-8 Encoding?

[amrrs]

That's nice put together @10GritSandpaper

[TharunRaja]

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.

[Chris Rudd]

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

[Boris_yo]

@10GritSandpaper Using Excel 2007. Script in your link didn't work for me.

[Lukasz Wiktor]

Wow! Alasql is really powerful.

[agershun]

If you install AlaSQL globally (npm install alasql -g) then you can use simply > alasql "SELECT... INTO CSV(...) FROM XLS(...)"

[Chris Rudd]

Myself I needed a more powerful version in powershell that can correctly process dates, can handle headers that aren't on the first line, and a few other things (it actually parses the individual cells rather than trusting excel). If people express a need for it I'll post it but since this is essentially answered and we'd be changing languages I don't want to post duplicates.

[suckerp]

Is there a way to use ; as a delimiter instead of ,? I tried changing oBook.SaveAs dest_file, csv_format, Local:=True 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.

[Shaohua Li]

You can change "%%i.csv" to "%%~ni.csv" to remove the ".xls" file extension in the csv files.

[Geoff Griswald]

Underrated answer. Why would anyone install a third-party .DLL file when Windows has the functionality built in?

[Hasan A Yousef]

i got an error: Unable to get the Open property of the Workbooks, I'm at O365

[eriegz]

@10GritSandpaper Life-saver! 💪 Thanks a bunch

[user734028]

a solution to convert all sheets to separate csv?