How to Read Excel Files into an Array in PowerShell?

Recently, I got one requirement to read an Excel file into an array in PowerShell. In this PowerShell tutorial, I will explain to you how to read excel files into an array in PowerShell.

To read Excel files into an array in PowerShell, you can use the ImportExcel module which simplifies the process of importing data from Excel spreadsheets. After installing the module with Install-Module -Name ImportExcel, you can use the Import-Excel cmdlet to read the data from an Excel file directly into a PowerShell array. For instance, $dataArray = Import-Excel -Path ‘path\to\your\file.xlsx’ will import the data into the $dataArray variable.

Read Excel Files into an Array in PowerShell Using the ImportExcel Module

One of the easiest ways to work with Excel files in PowerShell is by using the ImportExcel module. This free, open-source module simplifies the process of reading and writing Excel data without requiring Microsoft Office or Excel to be installed on your system.

Installing the ImportExcel Module

To use the ImportExcel module, you need first to install it in the system. You can do this by running the following command in PowerShell:

Install-Module -Name ImportExcel -Scope CurrentUser

Reading Excel Data into an Array

Once the module is installed, you can easily read data from an Excel spreadsheet into an array. Here’s a simple example:

# Import the module
Import-Module ImportExcel

# Define the path to your Excel file
$excelPath = 'C:\Bijay\PowerShell.xlsx'

# Import the data from the first worksheet into an array
$dataArray = Import-Excel -Path $excelPath

# Output the array to the console
$dataArray

In this example, Import-Excel reads the data from the first worksheet by default. If you want to specify a different worksheet, you can use the -WorksheetName parameter.

Filtering Columns

If you’re only interested in certain columns, you can select them using the -Range parameter:

# Import specific columns into an array
$dataArray = Import-Excel -Path $excelPath -Range "B:C"

This command would import only the data from columns B and C.

Read Excel Files into an Array in PowerShell Using COM Objects

Another method to read Excel files is by using COM objects in PowerShell. This approach requires Excel to be installed on your system.

Reading Excel Data into an Array

Here’s how you can read data from an Excel file using COM objects in PowerShell:

# Create a new Excel application object
$excel = New-Object -ComObject Excel.Application

# Hide the Excel application window (optional)
$excel.Visible = $false

# Open the Excel workbook
$workbook = $excel.Workbooks.Open('C:\path\to\your\file.xlsx')

# Select the first worksheet
$worksheet = $workbook.Worksheets.Item(1)

# Define the range of cells to read from
$range = $worksheet.UsedRange

# Read the values into an array
$dataArray = $range.Value2

# Close the workbook and quit Excel
$workbook.Close($false)
$excel.Quit()

# Release COM objects
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

# Output the array to the console
$dataArray

In this script, $range.Value2 retrieves the values from the used range of the worksheet and stores them in $dataArray.

Read Excel Files into an Array in PowerShell Import-Csv Cmdlet

If your Excel file can be saved or converted to a CSV format, you can use PowerShell’s native Import-Csv cmdlet to read the data into an array.

Reading CSV Data into an Array

Here’s an example of how to read a CSV file into an array using Import-Csv:

# Define the path to your CSV file
$csvPath = 'C:\Bijay\PowerShell.xlsx'

# Import the CSV data into an array
$dataArray = Import-Csv -Path $csvPath

# Output the array to the console
$dataArray

The Import-Csv cmdlet treats each row in the CSV file as an object, with the column headers as the property names.

Conclusion

Reading Excel files into an array in PowerShell can be achieved through various methods like the ImportExcel module that doesn’t require Excel to be installed. COM objects provide a more direct interface with Excel but require Excel to be installed on your system. Lastly, converting your Excel files to CSV format allows you to use PowerShell’s native Import-Csv cmdlet for simple and effective data reading.

In this PowerShell tutorial, I have explained how to Read Excel Files into an Array in PowerShell.

You may also like: