Convert CSV to XLSX via PowerShell
A nifty PowerShell script that will take a delimited CSV file and convert it to a XLSX file.
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.
There are several pieces of code online that purport to do this but don’t work well (Example 1, Example 2, Example 3). Online tools exist too (Example 1, Example 2).
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()