Code: Select all
#
# Script: Save CSV as XLSX
#
# Last change: 28-10-2014, Sander - Wihabo
#
# Sources: http://stackoverflow.com/questions/17688468/how-to-export-a-csv-to-excel-using-powershell
#
param(
[string]$fileinput = "", # Switch geeft %1 mee als input variabele: %1: the absolute path of the input file/job folder
[string]$fileoutput = "" # Switch geeft %2 mee als output variabele: %2: the absolute path of the output file/job folder (which does not yet exist) including filename and extension
)
### Set input and output path
#$fileinput = "c:\users\test\desktop\00001.csv"
#$fileoutput = "c:\users\test\desktop\00001.xlsx"
### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data ยป From Text" in Excel
$TxtConnector = ("TEXT;" + $fileinput)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = ","
### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1
### Execute & delete the import query
$query.Refresh()
$query.Delete()
### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($fileoutput,51)
$excel.Quit()
