No need to fuss! Here’s How to Convert CSV to XLSX Without Opening Excel – Just Use CMD!

Getting Started with the ImportExcel Module

Before you can take advantage of the power of this module, you need to install it first. The installation process is very simple:

Install-Module -Name ImportExcel -Force -Scope CurrentUser

Some important things about this installation process:

  1. Parameter -Force ensures the installation runs without additional confirmation
  2. -Scope CurrentUser makes the module installed only for your current account
  3. The process requires an internet connection to download the package from the PowerShell Gallery

If you encounter an error about the execution policy, run it first:

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

CSV to Excel Conversion Magic Script

Once the module is successfully installed, you can immediately run the CSV to Excel file conversion script with the following syntax:

Import-Csv “C:\path\to\data.csv” |  Export-Excel “C:\output\data.xlsx” -WorksheetName “Sheet1”

Let’s dissect this script part by section:

  • Import-Csv: Read a CSV file and convert it into a PowerShell object
  • | (Pipeline): Sends data from one command to the next
  • Export-Excel: Receive data and write it to an Excel file
  • -WorksheetName: Specifies the name of the worksheet to be created

Advanced Variations for Complex Needs

The real strength of PowerShell lies in its flexibility. Here are some advanced modifications that you can implement:

1. Convert Multiple CSVs at Once

Get-ChildItem “C:\data\*.csv” |  ForEach-Object {
  $excelPath = $_.FullName -replace '\.csv$','.xlsx'
     Import-Csv $_ |  Export-Excel $excelPath
}

2. Add Multiple Worksheets

$excel = New-Excel
Import-Csv “data1.csv” | Export-Excel -ExcelPackage $excel -WorksheetName “Sales”
Import-Csv “data2.csv” |  Export-Excel -ExcelPackage $excel -WorksheetName “Inventory”
Close-ExcelPackage $excel -SaveAs “report.xlsx”

3. With Automatic Formatting

Import-Csv “data.csv” |  Export-Excel “output.xlsx” -AutoSize -BoldTopRow -FreezeTopRow

Other Interesting Articles

Troubleshooting

While the process of converting a CSV file to Excel (XLSX) using PowerShell tends to be simple, in practice, you may encounter some technical issues, especially if you’re using the command line for the first time or working with large files. The following are some of the problems that often arise and how to deal with them effectively:

Unknown Modules

If you get an error message like The term ‘Export-Excel’ is not recognized, it signifies that PowerShell doesn’t recognize commands from the ImportExcel module. This issue usually occurs because the module is not installed correctly or PowerShell is running without administrator permission.

Solution:

  • Make sure that you are running PowerShell as an Administrator. Right-click the PowerShell icon and select Run as administrator before running the Install-Module command.
  • Check if the module is installed by running:
Get-Module -ListAvailable ImportExcel
  • If the module is not already present, repeat the installation process by:
Install-Module -Name ImportExcel -Force

Error Encoding in CSV Files

Some CSV files, especially those from other systems or exports from third-party applications, may have encoding issues. If you notice strange characters or unreadable data after exporting to Excel, it is most likely an encoding issue.

Solution:

  • Use the UTF8 Encoding parameter when importing CSV files. This will ensure that special characters such as accented letters or symbols are not damaged.
Import-Csv -Path “C:\path\data.csv” -Encoding UTF8 |  Export-Excel “C:\output\data.xlsx”

Table of contents [hide]

Latest Articles

Related Articles