How to convert .csv file to .xlsx format in VBScript?

Question

excel

Straight to the point. There is a text file (table) in .csv format. ";" are used as column separators. (semicolon), for example:

Column 1.1;Column 1.2;...;Column 1.N
Column 2.1;Column 2.2;...;Column 2.N

How can I convert this file to .xlsx format using VBScript?

Answer 1

powershell

#Write-Host "Hello" -BackgroundColor Black -ForegroundColor Yellow
$objExcel = New-Object -ComObject Excel.Application
#$o = $objExcel
#$o | Get-Member
#$o.Workbooks | Get-Member
#$objExcel.Workbooks | Select-Object -Debug -Property "OpenText"

$objExcel.Workbooks.OpenText( "z:\dev\ps\1.csv", # file to open
         [Microsoft.Office.Interop.Excel.XlPlatform]::xlWindows, 
         1, # start from row 1
         [Microsoft.Office.Interop.Excel.XlTextParsingType]::xlDelimited,
         [Microsoft.Office.Interop.Excel.XlTextQualifier]::xlTextQualifierDoubleQuote,
         $false, # Consecutive Delimiter
         $false, # tab
         $false, # semicolon
         $false, # comma
         $false, # space
         $true,  # use other
         ';')

$book = $objExcel.Workbooks[1]
$book.SaveAs("z:\dev\ps\1.xlsx", [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault)

$book.Close($false)

$objExcel.Quit()