Export a CSV to Excel Using PowerShell

A CSV (Comma Separated Values) is a plain text file storing data in a tabular format. It contains a list of data separated by commas or other delimiters, like semicolons.

A CSV file has a .csv extension and can be viewed with a spreadsheet program, like Microsoft Excel. A file having the .xlsx extension is a Microsoft Excel Worksheet file.

This tutorial will teach you to export a CSV file to an Excel file using PowerShell.

Export CSV File to Excel File Using PowerShell

We have a username.csv file in which data is separated by semicolons. When it is opened in the Excel application, it displays data in a simple text format instead of a tabular format.

You can convert a CSV file to an Excel file using the following PowerShell script.

Script:

# Define file locations and delimiter
$csv = "C:\New\username.csv" # source file
$xlsx = "C:\New\output.xlsx" # destination file
$delimiter = ";" # delimiter used in the csv 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()

Output:

True

The data is exported in a specified location in a new output.xlsx file. If you open the output.xlsx file, you will see data in arranged cells in the tabular format.

Use the above script to convert a large delimited CSV file to Microsoft Excel Worksheet (xlsx). Hoping this article has helped you learn how to use PowerShell to export a CSV file to Excel.