Convert Excel file to CSV on command line

Question

I'm hoping to convert an XLSX to CSV in a batch file on a Win server.

The server doesn't have Excel installed, so VB commands like CreateObject("Excel.Application") will not work.

It would be ideal if I could do this without installing any third party tools. I have seen some options that involve installing office data connections on the server and connecting to a copy of Excel, but I'm hoping to do this with as little change as possible to the server.

I'm wondering if there are other options? Thanks

Answer 1

Use ADO and "SELECT ... INTO ... IN External Database FROM ...":

  Dim goFS    : Set goFS  = CreateObject("Scripting.FileSystemObject")
  Dim sTDir   : sTDir     = resolvePath("..\data\ExcelCsv2")
  Dim sXFSpec : sXFSpec   = goFS.BuildPath(sTDir, "xls.xls")
  Dim sTFiNa  : sTFiNa    = "csv.csv"
  Dim sTFSpec : sTFSpec   = goFS.BuildPath(sTDir, sTFiNa)
  If goFS.FileExists(sTFSpec) Then goFS.DeleteFile sTFSpec
  Dim oDb     : Set oDb   = CreateObject("ADODB.Connection")
  oDb.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sXFSpec & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
  WScript.Echo sXFSpec
  WScript.Echo oDb.Execute("SELECT * FROM Demo").GetString(2, , vbTab, vbCrLf)
  oDb.Execute "SELECT * INTO [" & sTFiNa & "] IN '" & sTDir & "' 'Text;' FROM Demo"
  WScript.Echo sTFSpec
  WScript.Echo oDb.Execute("SELECT * FROM [" & sTFiNa & "] IN '" & sTDir & "' 'Text;'").GetString(2, , vbTab, vbCrLf)
  oDb.Close

output:

E:\trials\SoTrials\answers\8328305\data\ExcelCsv2\xls.xls
1       1,1     10.12.2011      text elm 1
2       2,2     11.12.2011      text elm 2
3       4,4     12.12.2011      text elm 3

E:\trials\SoTrials\answers\8328305\data\ExcelCsv2\csv.csv
1       1,1     10.12.2011      text elm 1
2       2,2     11.12.2011      text elm 2
3       4,4     12.12.2011      text elm 3

(cf. this answer)