How to Convert CSV to XLSX using PowerShell

Overview of CSV and XLSX File Formats

CSV (Comma Separated Values) files contain plain text data that is delimited by commas. They can be opened by many programs including Excel and are useful for storing tabular data.

XLSX is the file extension for Microsoft Excel Open XML Spreadsheet files. Unlike CSV, XLSX files can contain features like formulas, formatting, charts, etc.

The main reasons you may want to convert a CSV file to XLSX format are:

• Apply formatting, colors, fonts in Excel
• Use Excel features like formulas, pivots, charts etc.
• Better organize data into separate worksheets
• Easier sharing of data with others

PowerShell Cmdlet for Converting CSV to XLSX

PowerShell has a built-in cmdlet called Import-Csv that can be used to convert a CSV file to XLSX format.

The syntax is simple:

Import-Csv -Path CSVFileName.csv | Export-Excel -Path XLSXFileName.xlsx

This takes the CSV file “CSVFileName.csv”, imports it, and then pipes the output to the Export-Excel cmdlet to generate “XLSXFileName.xlsx”.

Let’s look at a complete example.

Step-by-Step Example

Follow these steps to convert a CSV file to XLSX using PowerShell:

1. Create a Sample CSV File

First, we’ll create a simple CSV file to use for this demo. This will contain some sample data headers in the first row, and a few data rows below it.

Launch PowerShell and run the following commands:

"Name,Age,BirthDate" | Out-File "SampleData.csv" 
"John,45,11/12/1978" | Out-File "SampleData.csv" -Append
"Jane,42,5/23/1982" | Out-File "SampleData.csv" -Append
"Bob,53,8/30/1970" | Out-File "SampleData.csv" -Append

This will create a CSV file called “SampleData.csv” in the current folder with some sample data.

2. Install the ImportExcel Module

The Export-Excel cmdlet used for the XLSX conversion is contained in a PowerShell module called ImportExcel.

To install it, run:

Install-Module -Name ImportExcel

This will install the ImportExcel module from the PowerShell gallery.

3. Convert CSV to XLSX

Now we can run the conversion command:

Import-Csv -Path .\SampleData.csv | Export-Excel -Path ConvertedData.xlsx

This will import the CSV and pipe it to Export-Excel to create an XLSX file called “ConvertedData.xlsx”

4. Verify the Output XLSX File

Open the “ConvertedData.xlsx” file in Excel.

You should see:

• The headers are in the first row
• The CSV data is populated in the rows below
• Each CSV column is converted to an Excel column

And there you have it! The CSV file has now been successfully converted to a native XLSX Excel file using PowerShell.

Key Points to Remember

Here are some key points to remember about this technique:

• Relies on the ImportExcel module so install that first
• Use Import-Csv to import the source CSV data
• Pipe the output to Export-Excel to generate the XLSX
• XLSX file will match CSV format but enables Excel features
• Adjust Export-Excel parameters to customize worksheets, formatting etc.

Advanced Usage and Tips

There are additional parameters in Export-Excel that can be used to further customize the output XLSX file:

Worksheet Names:

Use -WorksheetName to specify a different worksheet name than the default:

Export-Excel -WorksheetName "DataSheet"

Column Headings:

To skip using the first CSV row as column headings, use -NoHeader:

Export-Excel -NoHeader

Format Data:

Use -AutoSize to autosize columns based on data:

Export-Excel -AutoSize

Use -AutoFilter to add autofilter dropdowns to headers:

Export-Excel -AutoFilter

Multi-Sheet Excel Files:

To output to multiple worksheets in one XLSX file, use arrays for input CSVs and specify worksheet names:

$csv1 = Import-Csv Csv1.csv
$csv2 = Import-Csv Csv2.csv

$csv1,$csv2 | Export-Excel -WorkSheetname @("Sheet1","Sheet2") -Path MultiSheet.xlsx

This provides the flexibility to create multi-tab Excel reports from several CSV files.

Summary

Converting CSV data into XLSX format is straightforward in PowerShell using the Import-Csv and Export-Excel cmdlets. This unlocks many additional features of Excel for improved analysis and reporting.

The key steps are:

1. Install ImportExcel module
2. Use Import-Csv to import CSV data
3. Pipe to Export-Excel to generate XLSX
4. Customize formatting and options as needed

Manipulating data from CSV to Excel programmatically with PowerShell can automate reporting workflows and allows enhancing plain CSV data with Excel’s formulas, charts, pivots and more.