Read Excel File Using PSExcel In PowerShell

Recently we got one requirement from a client where we need to read data from Excel (shared in a common location) and update it in a SharePoint list.

There is one external job which will update the Excel file in a shared location every day. We need to sync this Excel data with list in SharePoint. The external job didn't have access to the SharePoint environment because of access restriction.

As part of this requirement initially we used “New-Object –ComObject” excel Com objects to perform activities related to Excel files, like read/search data in excel file.

$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open ($ExcelFile)
$WorkBook.sheets | Select-Object -Property Name
$WorkSheet = $WorkBook.Sheets.Item(1)
$totalNoOfRecords = ($WorkSheet.UsedRange.Rows).count

When we wanted to run the code in a test environment we observed that server did not have Excel COM objects installed. We searched for some suggestions/articles where we can do the actions related to Excel without installing COM objects.

Below is the approach we followed to solve this issue.

Usually we use "Microsoft.Office.Interop.Excel" to read data from Excel files. But in most of the production environments Excel COM objects will not be installed because of the performance issue. So we can use “PSExcel” power shell scripts to perform actions related to Excel.

Download the required “PSExcel” module (attached as Zip file) and save it in your system by extracting the Zip folder.

$currentDir = "Give here the path of the current folder where scripts are stored".

Then import the PSExcel module by using Import-Module command

Import-Module$currentDir"\PSExcel"

Below are the two activities which we have done as part of this requirement

Read data from the excel file

Give the path of the file where Excel file is stored
Then create an instance of the Excel file
Get each worksheet data from the Excel
Then loop through each item in the worksheet and get values of the required column.

Search value in the Excel file

As part of this requirement we have to search word in shared Excel file.

Give the path of the file where Excel file is stored
Then create an instance of the Excel file
Get each worksheet data from the Excel
Pass the word to be searched to the function as parameter
In this we are searching for “Sachin” in the column “FirstName”

If the value matches then return true, if not false.

Read excel file data

$currentDir = "Give here the path of the current folder where scripts are stored"
Import - Module $currentDir "\PSExcel"
$ExcelFile = $currentDir + "\filename.xlsx"
$objExcel = New - Excel - Path $ExcelFile
$WorkBook = $objExcel | Get - Workbook
# Loop through all items in the excel
ForEach($Worksheet in @($Workbook.Worksheets)) {
$totalNoOfRecords = $Worksheet.Dimension.Rows
$totalNoOfItems = $totalNoOfRecords - 1
# Declare the starting positions first row and column names
$rowNo, $colFirstName = 1, 1
$rowNo, $colLastName = 1, 2
if ($totalNoOfRecords - gt 1) {
#Loop to get values from excel file
for ($i = 1; $i - le $totalNoOfRecords - 1; $i++) {
$firstName = $WorkSheet.Cells.Item($rowNo + $i, $colFirstName).text
$lastName = $WorkSheet.Cells.Item($rowNo + $i, $colLastName).text
}
}
}

Search text in excel file

Function Search - Excel($Source, $SearchText) {
$objExcel = New - Excel - Path $Source
$WorkBook = $objExcel | Get - Workbook
Foreach($Sheet in $WorkBook.Worksheets) {
$Dimension = $Sheet.Dimension
$RowStart = 2
$ColumnStart = 1
# Column where we need to search
for text in our
case searching
for FirstName
$RowEnd = $Dimension.End.Row
$ColumnEnd = $Dimension.End.Column
for ($Row = $RowStart; $Row - le $RowEnd; $Row++) {
$Value = $Sheet.Cells.Item($Row, $ColumnStart).Value
if ($Value) {
if ($Value.trim() - eq $searchText.trim()) {
Write - Host $searchText "found in excel file"
return $true
}
}#If value is not null
}#End of
for each row
$objExcel.Dispose()
}
}
Search - Excel - Source $ExcelFile - SearchText "Sachin"#
Text to be searched

Thanks for reading.

2 Comments

Join the conversation! Your thoughts help the community grow.

Love Thakker5y ago
This is not working. Few issues I have fixed from my side is that every command is written with space i.e Import - Module should be Import-Module. Same issue with operator i.e - gt should be -gt. the owner of the repository has mentioned in git that this is dependent on EPPlus which is now commercial API for production purpose.
Vinay Ayinapurapu5y ago
Nice one!!!