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.
  1. $objExcel = New-Object -ComObject Excel.Application
  2. $WorkBook = $objExcel.Workbooks.Open ($ExcelFile)
  3. $WorkBook.sheets | Select-Object -Property Name
  4. $WorkSheet = $WorkBook.Sheets.Item(1)
  5. $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
  1. $currentDir = "Give here the path of the current folder where scripts are stored"
  2. Import - Module $currentDir "\PSExcel"
  3. $ExcelFile = $currentDir + "\filename.xlsx"
  4. $objExcel = New - Excel - Path $ExcelFile
  5. $WorkBook = $objExcel | Get - Workbook
  6. # Loop through all items in the excel
  7. ForEach($Worksheet in @($Workbook.Worksheets)) {
  8. $totalNoOfRecords = $Worksheet.Dimension.Rows
  9. $totalNoOfItems = $totalNoOfRecords - 1
  10. # Declare the starting positions first row and column names
  11. $rowNo, $colFirstName = 1, 1
  12. $rowNo, $colLastName = 1, 2
  13. if ($totalNoOfRecords - gt 1) {
  14. #Loop to get values from excel file
  15. for ($i = 1; $i - le $totalNoOfRecords - 1; $i++) {
  16. $firstName = $WorkSheet.Cells.Item($rowNo + $i, $colFirstName).text
  17. $lastName = $WorkSheet.Cells.Item($rowNo + $i, $colLastName).text
  18. }
  19. }
  20. }
Search text in excel file
  1. Function Search - Excel($Source, $SearchText) {
  2. $objExcel = New - Excel - Path $Source
  3. $WorkBook = $objExcel | Get - Workbook
  4. Foreach($Sheet in $WorkBook.Worksheets) {
  5. $Dimension = $Sheet.Dimension
  6. $RowStart = 2
  7. $ColumnStart = 1
  8. # Column where we need to search
  9. for text in our
  10. case searching
  11. for FirstName
  12. $RowEnd = $Dimension.End.Row
  13. $ColumnEnd = $Dimension.End.Column
  14. for ($Row = $RowStart; $Row - le $RowEnd; $Row++) {
  15. $Value = $Sheet.Cells.Item($Row, $ColumnStart).Value
  16. if ($Value) {
  17. if ($Value.trim() - eq $searchText.trim()) {
  18. Write - Host $searchText "found in excel file"
  19. return $true
  20. }
  21. }#If value is not null
  22. }#End of
  23. for each row
  24. $objExcel.Dispose()
  25. }
  26. }
  27. Search - Excel - Source $ExcelFile - SearchText "Sachin"#
  28. Text to be searched
Thanks for reading.