Tersus: Re: Load Excel Table with variable number of columns and column names

Re: Load Excel Table with variable number of columns and column namesDate: Apr 19, 2016Hi Ofer,Thanks for your reponse :)
Would not be able to meddle with the Excel file as it would be uploaded by users.Found a way out for this and sharing it here.
Concept is basically converting xls file to csv file.
Do note method below is for Windows and requires MS Excel to be installed on server.

For solution on Linux platforms, there's a variety of solutions from Googling Linux
convert xls to csv.

The VB script code below can be saved into a .vbs file (ExcelToCSV.vbs)
A batch file (ConvertDoc.bat ) can then be created to call the VBS file with the source file location and destination file location as the first and second argument respectively. Example: ConvertDoc.bat C:\temp\test1.xls C:\temp\test1.csvThen using Miscellaneous/Run External Command to run the batch file, we would obtain a csv file which we can easily manipulate.

Cheers
Cliff

[ConvertDoc.bat]@echo off
cscript [Path To VBS file]\ExcelToCSV.vbs %1 %2[ExcelToCSV.vbs]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.QuitSee Also:

To use the full functionality of this web site, JavaScript needs to be turned on.

For best results, use the Firefox browser..

Loading ...