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

User · Answer

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.

User · Answer

For UTF-8 works for Microsoft Office 2016 and higher   Try this code   if WScript Arguments Count  lt  2 Then     WScript Echo  Please specify the source and the destination files  Usage  ExcelToCsv  lt xls xlsx source file gt   lt csv destination file gt       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

User · Answer

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  lt - To account for a missing xls files in my batch processing at the top  oBook Application Columns  A J   NumberFormat        lt - 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Open Notepad  create a file called XlsToCsv vbs and paste this in   if WScript Arguments Count  lt  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

User · Answer

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     amp  WScript ScriptName  amp    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     amp  srcFile  amp     to     amp  objFSO GetFileName destFile   amp       End Function

User · Answer

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  lt  3 Then     WScript Echo  Please specify the sheet  the source  the destination files  Usage  ExcelToCsv  lt sheetName gt   lt xls xlsx source file gt   lt csv destination file gt       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

User · Answer

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

User · Answer

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  amp             A filename was received that doesn t appear to be an Excel Document    amp  vbCRLF  amp             Do you want to skip this and all other unrecognized files    Will only prompt this once    amp  vbCRLF  amp                amp  vbCRLF  amp             Yes    - Will skip all further files that don t have a  xl  extension   amp  vbCRLF  amp             No     - Will pass the file to excel regardless of extension   amp  vbCRLF  amp             Cancel - Abort any further conversions and exit this script   amp  vbCRLF  amp                amp  vbCRLF  amp             The unrecognized file was    amp  vbCRLF  amp            sFilename  amp  vbCRLF  amp                amp  vbCRLF  amp             The path returned by the system was    amp  vbCRLF  amp            oFSO GetAbsolutePathName sFilename   amp  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

[windows] Convert XLS to CSV on command line

Examples related to windows

Examples related to excel

Examples related to csv