How to Export a CSV to Excel Using PowerShell

1. Export CSV File to Excel File Using Excel COM Object PowerShell
2. Export CSV File to Excel File Using Excel COM Object PowerShell
3. Export CSV File to Excel File Using PSExcel PowerShell
4. Conclusion

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 article delves into the specifics of exporting CSV files to Excel using PowerShell, covering three distinct methods: the Excel COM Object, the ImportExcel module, and the PSExcel module. Each method caters to different scenarios and requirements, offering unique advantages.

Export CSV File to Excel File Using Excel COM Object PowerShell

The Excel COM Object method is particularly useful when dealing with data transformations and reporting in Windows environments. This approach is ideal for those who need to automate the process of converting CSV data to a more user-friendly Excel format, which is widely used in business and data analysis.

Script:

# Define the CSV file path and the Excel file path
$csvPath = "C:\path\file.csv"
$excelPath = "C:\path\file.xlsx"

# Start Excel application
$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add()
$worksheet = $workbook.worksheets.Item(1)

# Import CSV data into the worksheet
$row = 1
Import-Csv $csvPath | ForEach-Object {
    $col = 1
    foreach ($value in $_.PSObject.Properties.Value) {
        $worksheet.Cells.Item($row, $col) = $value
        $col++
    }
    $row++
}

# Save and close Excel workbook
$workbook.SaveAs($excelPath)
$excel.Quit()

In this script, we start by defining the paths for our source CSV file and the destination Excel file. We then initiate an Excel application instance and add a new workbook with a single worksheet.

The Import-Csv cmdlet reads the CSV file, and we iterate through each record using ForEach-Object. Inside the loop, we assign each value to the corresponding cell in the Excel worksheet.

After populating the worksheet with all data from the CSV file, we save the workbook to the specified path and close the Excel application.

Output:

Export CSV File to Excel File Using Excel COM Object PowerShell

The Excel COM Object method is particularly useful when dealing with data transformations and reporting in Windows environments. This approach is ideal for those who need to automate the process of converting CSV data to a more user-friendly Excel format, which is widely used in business and data analysis.

First, you need to install the ImportExcel module if you haven’t already.

Install-Module -Name ImportExcel -Force -Scope CurrentUser

Script:

# Install the ImportExcel module if not already installed
# Install-Module -Name ImportExcel -Scope CurrentUser

# Define the CSV and Excel file paths
$csvPath = "C:\path\file.csv"
$excelPath = "C:\path\file.xlsx"

# Import data from CSV and export to Excel
Import-Csv -Path $csvPath | Export-Excel -Path $excelPath

# Print completion message
Write-Host "CSV data has been exported to Excel at: $excelPath"

In this script, we first check if the ImportExcel module is installed. This module is not included in PowerShell by default and may need to be installed from the PowerShell Gallery using Install-Module.

Next, we define the file paths for our source CSV and the destination Excel file. The Import-Csv cmdlet reads the CSV file, and the pipeline (|) passes the imported data directly to the Export-Excel cmdlet, which creates an Excel file at the specified location.

This script is designed to be simple and efficient, making it accessible even to those new to PowerShell scripting. It effectively demonstrates the ease of converting data from CSV to Excel format using PowerShell.

Output:

Export CSV File to Excel File Using PSExcel PowerShell

The PSExcel module leverages the capabilities of PowerShell to interact with Excel files without needing Excel installed on the system. This makes it particularly useful in environments where installing Excel is not feasible, such as on servers or automated systems.

PSExcel is widely used for tasks like report generation, data analysis, and converting data into a more presentable and accessible format like Excel.

First, you need to install the PSExcel module if you haven’t already.

Install-Module -Name PSExcel -Force -Scope CurrentUser

Script:

#Define the CSV and Excel file paths
$csvPath = "C:\path\file.csv"
$excelPath = "C:\path\file.xlsx"

# Import data from CSV
$csvData = Import-Csv -Path $csvPath

# Export data to Excel
$csvData | Export-Excel -Path $excelPath

# Print completion message
Write-Host "CSV data has been exported to Excel at: $excelPath"

In this script, we begin by ensuring that the PSExcel module is installed. It’s not included by default in PowerShell and can be installed from the PowerShell Gallery.

We then define the paths for our source CSV file and the destination Excel file. The Import-Csv cmdlet is used to read the CSV file into a variable.

This imported data is then piped into the Export-Excel cmdlet, which creates an Excel file at the specified location.

Output:

Conclusion

This comprehensive guide on exporting CSV files to Excel using PowerShell has demonstrated the flexibility and power of PowerShell in handling data conversion tasks. Each method explored - the Excel COM Object, ImportExcel module, and PSExcel module - offers unique benefits, catering to different environments and requirements.

The Excel COM Object is a robust choice for direct interaction with Excel in Windows environments, ideal for intricate data manipulation tasks. The ImportExcel and PSExcel modules provide an alternative approach, extending PowerShell’s functionality to handle Excel files effortlessly, even in the absence of Excel software.

These methods not only underscore the versatility of PowerShell but also offer practical solutions for automating and simplifying data transformation tasks. Whether you’re a novice to PowerShell scripting or an experienced professional, these methods provide valuable tools to enhance your data processing and reporting capabilities in a range of scenarios.