How to Convert Excel (XLSX) File to CSV using PowerShell?

CSV (Comma-Separated Values) files offer a simple and efficient way to store and exchange tabular data, making them widely compatible with various applications and databases. Sometimes it becomes necessary to convert these Excel files to CSV (Comma-Separated Values) format. PowerShell, a powerful scripting language and automation tool, provides a simple and efficient way to accomplish this task. In this step-by-step guide, I will walk you through the process of converting Excel to CSV using PowerShell, enabling you to streamline your data manipulation workflows.

Understanding the difference between XLSX and CSV file formats

Before we dive into the conversion process, let’s take a moment to understand the difference between XLSX and CSV file formats. XLSX is the default file format used by Microsoft Excel for storing spreadsheet data. It is a binary file format that encapsulates various elements such as worksheets, formulas, formatting, and macros.

On the other hand, CSV is a plain text file format that represents tabular data as a series of values separated by commas. Unlike XLSX, CSV files do not contain any formatting or complex data structures. They offer a lightweight and universally accepted way to store data, making them ideal for data exchange and integration.

Why use PowerShell for converting Excel to CSV?

PowerShell is a powerful and extensible scripting language developed by Microsoft. It provides a comprehensive set of tools and modules for automating administrative tasks, including data manipulation. Here are a few reasons why using PowerShell for converting Excel to CSV is beneficial:

1. Flexibility: PowerShell allows you to customize the conversion process according to your specific needs. You can easily manipulate and transform data before exporting it to CSV format.
2. Automation: With PowerShell, you can automate the conversion process, saving time and effort. You can create scripts that convert multiple Excel files to CSV in one go, eliminating the need for manual intervention.
3. Compatibility: PowerShell is compatible with various operating systems, including Windows, macOS, and Linux. Regardless of the platform you’re working on, you can leverage PowerShell to convert Excel to CSV seamlessly.

How to Convert XLSX File Format to CSV using PowerShell?

Excel files are widely used for data storage and analysis, but the CSV format offers better compatibility and simplicity, making it a popular choice for data exchange and database import. PowerShell, with its extensive capabilities and libraries, enables us to automate the conversion process, saving time and effort.

Method 1: Converting Excel Files to CSV using the “ImportExcel” Module

In this approach, we will be using the ImportExcel module, which adds the Export-Excel and Import-Excel cmdlets to PowerShell, that make it easier to work with Excel files.

Step 1: Install the PowerShell modules

To convert Excel to CSV using PowerShell, we need to install and import the necessary modules that provide the required functionality. Here’s how you can install the modules:

1. Open PowerShell console as Administrator.
2. Type the following command to install the ImportExcel module: Install-Module ImportExcel

Once the module is installed, we can proceed with the conversion process. By installing the modules, we gain access to the necessary cmdlets and functions for handling Excel and CSV files in PowerShell.

Step 2: Converting the Excel file to CSV format

Now that we have the required modules installed, we can proceed to convert the Excel file into CSV in PowerShell. Here’s how you can do it:

Import-Excel -Path "C:\Scripts\Excel\Employees.xlsx" | Export-Csv -Path "C:\Scripts\Excel\Employees.csv" -NoTypeInformation

This script imports the XLSX file, converts the first available tab to CSV, and saves it to the given path. The -NoTypeInformation parameter tells the cmdlet not to include the data type in the first row of the CSV file. Similarly, you can batch convert all Excel files in a Folder and Subfolders as:

$Files = Get-ChildItem -Path "C:\Scripts\Excel" -Include *.xlsx -Recurse
ForEach ($File in $Files)
{
    Import-Excel -Path $File.FullName | Export-Csv ($file.FullName -replace '\.xlsx$', '.csv') -NoTypeInformation
}

This command gets all the XLSX files in the given folder and its subfolders, imports them, converts them to CSV, and saves them with the same name and location but with a .csv extension.

Save a Specific Excel Worksheet to CSV

If we need to convert a specific tab from an Excel file to CSV format, we can modify our script accordingly. The Import-Excel cmdlet supports specifying the tab name using the WorksheetName parameter.

Here’s an example script that converts a single tab from an Excel file to CSV:

1. Import the ImportExcel module using the Import-Module cmdlet. Specify the path to the Excel file you want to convert to CSV. For example, you would use the following command: $ExcelFilePath = "C:\Documents\data.xlsx"
2. Use the Import-Excel cmdlet to load the Excel file into PowerShell. The cmdlet takes the path to the Excel file as a parameter. Here’s an example command: $ExcelData = Import-Excel -Path $ExcelFilePath

# Import the ImportExcel module
Import-Module -Name ImportExcel

# Define the path to the Excel file
$ExcelFilePath = "C:\Documents\data.xlsx"

# Specify the tab name to convert
$worksheetName = "InactiveUsers" #or "Sheet1

# Import the specific tab from the Excel file
$data = Import-Excel -Path $ExcelFilePath -WorksheetName $worksheetName

# Export the data to a CSV file
$CsvFilePath = "C:\Documents\CsvData.csv"
$data | Export-Csv -Path $CsvFilePath -NoTypeInformation

This conversion allows us to work with the data in a more versatile and standardized format. In this script, we first We then define the path to the Excel file and specify the tab name we want to convert using the $worksheetName variable. Using the Import-Excel cmdlet with the -WorksheetName parameter, we import only the specified tab’s data. Finally, we export the data to a CSV file using the Export-Csv cmdlet.

By executing these commands, the contents of the Excel file will be loaded into a PowerShell object, allowing us to manipulate the data before converting it to CSV format.

Converting Multiple Excel Files in a Folder to CSV: All Tabs

In some cases, We may have multiple Excel files and our Excel files may contain multiple tabs, each representing a different dataset or category. To convert all the tabs of an Excel file to CSV format, we can use the Import-Excel and Export-Csv cmdlets in PowerShell.

Here’s an example script that batch converts all tabs of all Excel files to separate CSV files:

# Get all the Excel files in the specified directory
$ExcelFiles = Get-ChildItem -Path "C:\Scripts\Excel" -Filter "*.xlsx"

# Loop through each Excel file
ForEach ($File in $ExcelFiles) {
    # Import the Excel file
    $Tabs = Get-ExcelSheetInfo $File.FullName | Select -ExpandProperty Name

    # Export each tab as a separate CSV file
    foreach ($Tab in $Tabs) {
        $csvFileName = Join-Path -Path "C:\Scripts\Excel" -ChildPath ($tab + ".csv")
        $Data = Import-Excel -Path $File.FullName -WorksheetName $Tab
        $data | Export-Csv -Path $CSVFileName -NoTypeInformation
        Write-host -f Green "Exported to CSV:"$csvFileName
    }
}

Let’s break down the script:

• We use the Get-ChildItem cmdlet to retrieve all the Excel files in the specified directory.
• We then loop through each Excel file and import its data using the Import-Excel cmdlet.
• Within the loop, we iterate over each tab in the Excel file using the Get-ExcelSheetInfo property.
• For each tab, we construct the CSV file name and export the data to a separate CSV file using the Export-Csv cmdlet.

This script converts each tab of the Excel file into a separate CSV file, allowing for easy analysis and manipulation of the data.

Filtering Specific Columns and Rows from Excel File and Export

In some cases, our Excel files may contain user-specific data or attributes that we want to exclude during the conversion process. To achieve this, we can modify our conversion script to filter out specific columns or rows that contain user data or attributes.

Here’s a modified version of the previous script that excludes specific columns and rows:

# Import the ImportExcel module
Import-Module -Name ImportExcel

# Define the path to the Excel file
$ExcelFile = "C:\Scripts\Excel\Employees.xlsx"

# Specify the tab name to convert
$worksheetName = "Sheet1"

# Import the specific tab from the Excel file
$Data = Import-Excel -Path $ExcelFile -WorksheetName $WorksheetName

# Exclude specific columns containing user data or attributes
$data = $data | Select-Object -Property * -ExcludeProperty "Mail", "UserEmail"

# Exclude specific rows containing user data or attributes
$data = $data | Where-Object { $_."Designation" -ne "Web Developer" }

# Export the modified data to a CSV file
$CSVFile = "C:\Scripts\Excel\FilteredEmployees.csv"
$Data | Export-Csv -Path $csvFile -NoTypeInformation

In this script, we first import the ImportExcel module and define the path to the Excel file and the tab name to convert. We then import the specific tab from the Excel file using the Import-Excel cmdlet.

To exclude specific columns containing user data or attributes, we use the Select-Object cmdlet with the -ExcludeProperty parameter, specifying the column names to exclude. Similarly, to exclude specific rows, we use the Where-Object cmdlet with the appropriate condition.

Finally, we export the modified data to a CSV file using the Export-Csv cmdlet. This approach allows you to interact with Excel programmatically without having Excel installed on your system.

Method 2: Convert Excel to CSV using Excel.Application COM Object

Another method to convert Excel files to CSV format is by using the Excel.Application COM Object. Here’s how you can accomplish the conversion:

How to Convert a XLSX File to CSV?

Here are the steps to convert Excel Files to CSV using Excel.Application COM object method:

• Step 1: Start by creating a new instance of the Excel.Application COM Object
• Step 2: Use the Open method to open the Excel file you want to convert.
• Step 3: Once the Excel file is open, you can save it as a CSV file using the SaveAs method.
• Step 4: Close and Quit Excel: After saving the file, close the workbook and quit Excel.

# Step 1: Create an Excel.Application Object:
$Excel = New-Object -ComObject Excel.Application

# Step 2: Open method to open the Excel file you want to convert
$Workbook = $Excel.Workbooks.Open("C:\Scripts\Excel\Employees.xlsx")

# Step 3: save it as a CSV file using the SaveAs method.
$workbook.SaveAs("C:\Scripts\Excel\Employees.csv", 6)

# Step 4: Close the workbook and Quit Excel
$workbook.Close()
$Excel.Quit()

This method provides flexibility in converting Excel files to CSV format and requiring Excel to be installed on your system.

Export Each Workbook as a CSV using COM Object

Please note, Microsoft Excel must be installed on your machine for the below approach to work!

Function Convert-ExcelToCsv ($FilePath) {
    Try {
        $Excel = New-Object -ComObject Excel.Application
        $wb = $Excel.Workbooks.Open($FilePath)
    
        $File = Get-Item -Path $FilePath
        $FileInfo = $File | Select-Object Directory, BaseName
        $FileName = [System.IO.Path]::Combine($FileInfo.Directory, $FileInfo.BaseName)

        $Excel.DisplayAlerts = $false
        foreach ($ws in $wb.Worksheets) {
            $CSVFileName = $FileName + " - " + $ws.Name +".csv"        
            $ws.SaveAs($CSVFileName, 6)
            Write-host -f Green "Exported CSV File:"$CSVFileName
        }
    }
    Catch {
        Write-Host -f Yellow "Error on line $($_.InvocationInfo.ScriptLineNumber): $($_.Exception.Message)"
    }
    Finally {
        $wb.close()
        $Excel.Quit()
    }
}

#Call the function to convert Excel to CSV
Convert-ExcelToCsv -FilePath "C:\Temp\Inactive-Users.xlsx"

Troubleshooting common issues and errors

While converting Excel to CSV using PowerShell, you may encounter some common issues and errors. Here are a few troubleshooting tips to help you overcome them:

1. Missing modules: Ensure that you have imported the necessary module (ImportExcel) before attempting the conversion.
2. File path issues: Double-check the file paths you provide to PowerShell. Make sure they are valid and accessible.
3. Encoding problems: If you encounter encoding issues with the converted CSV file, try specifying a different encoding when saving the file using the Out-File cmdlet.
4. Data manipulation errors: If you encounter errors while manipulating the loaded Excel data, review your PowerShell commands and ensure they are correct.

By following these troubleshooting tips, you can overcome common issues and errors that may arise during the conversion process.

Tips and tricks for converting Excel to CSV using PowerShell

Here are a few additional tips and tricks to enhance your experience of converting Excel to CSV using PowerShell:

1. Batch conversion: If you have multiple Excel files that need to be converted to CSV, you can create a script that loops through the files and performs the conversion automatically. This allows you to convert multiple files in one go.
2. Data cleaning: Before converting Excel to CSV, you can use PowerShell to clean and normalize the data. You can remove unnecessary columns and rows, and perform other data cleaning operations to ensure the integrity of the exported CSV file.
3. Error handling: When writing scripts for converting Excel to CSV, it’s important to include error handling mechanisms. PowerShell provides various error handling techniques, such as try-catch blocks, to handle errors gracefully and ensure the reliability of your scripts.

By leveraging these tips and tricks, you can optimize your Excel to CSV conversion process and tailor it to your unique needs.

Wrapping up

Converting Excel files to CSV format is a common requirement in data processing and analysis tasks. Whether you choose to use the ImportExcel module or Excel.Application COM Object, PowerShell provides a robust and efficient solution to automate this process, allowing us to convert Excel files to CSV with ease. In this guide, we explored different methods to convert Excel files to CSV using PowerShell. We learned how to convert all tabs of an Excel file to separate CSV files, convert a single tab to CSV, and handle user data and attributes during the conversion process.

Whether you’re dealing with a single Excel file or a batch of files, PowerShell’s extensive capabilities allow you to automate the conversion process, saving you time and effort. By following the step-by-step guide outlined in this article, you can convert Excel files to CSV format with ease.