Convert CSV to XLSX via PowerShell
Having extracted data from a substantial database, I was left with a massive delimited CSV file that required conversion to Microsoft Excel XLSX format.
I wanted to save the file as an XLSX and perform a text-to-columns function, thus combining two steps into one.
Thankfully this is possible using PowerShell.
The following code is substantially based on the work of Nixta but I have made several changes including the manual specification of the delimiting character. The original code was unable to determine the delimiting character within the CSV and so whilst I was successful in obtaining a XLSX file, it was still semi-colon separated.
The following script will open the specified CSV, save it as an XLSX file to a specified location and perform a text-to-columns based on the delimiter being a semicolon (;). Cells will be in the “General” format.
#Define locations and delimiter $csv = "c:/path/to/file/whatever.csv" #Location of the source file $xlsx = "c:/path/to/file/whatever.xlsx" #Desired location of output $delimiter = ";" #Specify the delimiter used in the file # 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 and reformat the data $TxtConnector = ("TEXT;" + $csv) $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1")) $query = $worksheet.QueryTables.item($Connector.name) $query.TextFileOtherDelimiter = $delimiter $query.TextFileParseType = 1 $query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count $query.AdjustColumnWidth = 1 # Execute & delete the import query $query.Refresh() $query.Delete() # Save & close the Workbook as XLSX. $Workbook.SaveAs($xlsx,51) $excel.Quit()