Searching in Excel is taking too mush time to execute

PowerShell Help
1

I have a excel (keywordlistfile.xlsx) with there columns Keyword1, Keyword2, keyword3 2500 rows &
another excel (Datafile.xlsx) with Column short Description 17000+ rows

have to search Keword1 & keyword2 and keyword3 in Shortdecription and assign a value to category to it

Code is working with -Like but not with .contains & Taking more that 24+ hours

Sample code:

$StartTime = Get-Date
$CurrentDatetime = $(Get-Date).ToString(“yyyy_MM_dd_hh_mm_ss”)

#$ClientName = Read-host "Please enter Client name: "
$ClientName = “CBLF”
#Write-host $ClientName
$FolderPath = “C:\Gaurav-Dubey\Dataanalysis”
$DataFileName = “Data Aug to Nov 22.xlsx”
$DataAnalysisSheetName = “Sheet1”
$KeywordFileName = “Keyword list_sub category_category.xlsx”
$KewordSheetName= “Keywords”
$FinalDataName = “$ClientName-$CurrentDatetime.csv”
#Write-Host $FinalDataName

#Reading data from Data Analysis file
$ExcelDA = New-Object -ComObject Excel.Application
$WorkBookDA = $ExcelDA.Workbooks.Open(“$FolderPath$DataFileName”)
#Write-host $WorkBookDA.Name
$SheetNameDA = “$DataAnalysisSheetName”
$WorkSheetDA = $WorkBookDA.Sheets.Item($SheetNameDA)
#Write-host $WorkSheetDA.Name

$objRangeDA = $WorkSheetDA.UsedRange
$RowCountDA = $objRangeDA.Rows.Count
$ColumnCountDA = $objRangeDA.Columns.Count
Write-Host “RowCount from data Analysis file:” $RowCountDA
Write-Host “ColumnCount from data Analysis file:” $ColumnCountDA
#$column = 1
#$row = 2
#===========================—
#Reading data from Keyword list file
$ExcelKL = New-Object -ComObject Excel.Application
$WorkBookKL = $ExcelKL.Workbooks.Open(“$FolderPath$KeywordFileName”)
#Write-host $WorkBookKL.Name
$SheetNameKL = “$KewordSheetName”
$WorkSheetKL = $WorkBookKL.Sheets.Item($SheetNameKL)
#Write-host $WorkSheetKL.Name

$objRangeKL = $WorkSheetKL.UsedRange
$RowCountKL = $objRangeKL.Rows.Count
$ColumnCountKL = $objRangeKL.Columns.Count
Write-Host “RowCount from Keyword List file:” $RowCountKL
Write-Host “ColumnCount from Keyword list file:” $ColumnCountKL
#=============================
$columnKL = 1
$rowKL = 2
$Header = @(“IncidentNumber” + “=” + “Category” + “=” + “SubCategory” + “=” + “Keyword1” + “=” + “Keyword2” + “=” + “Keyword3” )
$finalData = @()
$finalData = $Header
$ExcludeRows = @()
$ExludeIncidentNumbers = @()
for($i = 2; $i -le $RowCountKL; $i++)
#for($i = 2; $i -le 63; $i++)
#for($i = 2; $i -le 5; $i++)
{

    #Write-host "Row Number for Keywords file in for Keyword for loop = " $i
    $Category = $WorkSheetKL.Cells.Item($i,3).text
    $Category = $Category.Trim()
    #Write-host "Category = " $Category
    $SubCategory = $WorkSheetKL.Cells.Item($i,4).text
    $SubCategory= $SubCategory.Trim()
    #Write-host "Sub Category = " $SubCategory
    $Keyword1 = $WorkSheetKL.Cells.Item($i,5).text
    $Keyword1 = $Keyword1.Trim()
    #Write-host "KeyWord 1 = " $Keyword1
    $Keyword2 = $WorkSheetKL.Cells.Item($i,6).text
    $Keyword2 = $Keyword2.Trim()
    #Write-host "KeyWord 2 = " $Keyword2
    $Keyword3 = $WorkSheetKL.Cells.Item($i,7).text
    $Keyword3 = $Keyword3.Trim()
    #Write-host "KeyWord 3 = " $Keyword3
    $columnDA = 1
    $rowDA = 2

    for($j = 2; $j -le $RowCountDA; $j++)
    #for($j = 2; $j -le 5; $j++)
    {

    Write-host "Row Number in  Keywordlist file  = " $i
    Write-host "Row Number in  data Analysis file  = " $j
    $IncidentNumber = $WorkSheetDA.Cells.Item($j,1).text
    $IncidentNumber = $IncidentNumber.Trim()
    #Write-host "Incident Number = " $IncidentNumber
    $ShortDescription = $WorkSheetDA.Cells.Item($j,4).text
    #Write-host "Short Description = " $ShortDescription
    if( ($ExludeIncidentNumbers -Contains $IncidentNumber)) 
    {
        #Write-host " Incident is already in Exclude list "   $IncidentNumber    
        #$j= $j+1
        continue
    }
    Else{
            if( (-not ([string]::IsNullOrEmpty($Keyword1))) -and (-not ([string]::IsNullOrEmpty($Keyword2))) -and (-not ([string]::IsNullOrEmpty($Keyword3))))
            {
                 Write-host "KeyWords are not empty "
                         if (($ShortDescription -like "*$Keyword1*") -and ($ShortDescription  -like "*$Keyword2*") -and ($ShortDescription -like "*$Keyword3*"))
                         {
                            $finalData += $IncidentNumber + "="  + $Category + "="  + $SubCategory  + "="  + $Keyword1  + "="  + $Keyword2  + "="  + $Keyword3
                           # $finalData += $ShortDescription
                           # $finalData += $Category
                           # $finalData += $SubCategory
                           $ExcludeRows += $j
                           $ExludeIncidentNumbers += $IncidentNumber 
                         }
                         Else
                         {
                            #Write-host "Keywords 1 , 2 & 3 are not found"
                            #$j= $j+1
                            continue
                         }

            }
            Elseif( (-not ([string]::IsNullOrEmpty($Keyword1))) -and (-not ([string]::IsNullOrEmpty($Keyword2))) -and (([string]::IsNullOrEmpty($Keyword3))))
            {
                 #Write-host "KeyWord3 is empty "
                         if (($ShortDescription -like "*$Keyword1*") -and ($ShortDescription -like "*$Keyword2*"))
                         {
                            $finalData += $IncidentNumber + "="   + $Category + "="  + $SubCategory  + "="  + $Keyword1  + "="  + $Keyword2
                           # $finalData += $ShortDescription
                           # $finalData += $Category
                           # $finalData += $SubCategory
                           $ExcludeRows += $j
                           $ExludeIncidentNumbers += $IncidentNumber 
                         }
                         Else
                         {
                            #Write-host "Keywords 1 & 2 is not found"
                            #$finalData += $IncidentNumber + ";"  + $ShortDescription + ";"  + $Category + ";"  + "Other"
                            #$j= $j+1
                            continue
                         }
            }
            Elseif( (-not ([string]::IsNullOrEmpty($Keyword1))) -and (([string]::IsNullOrEmpty($Keyword2))) -and (([string]::IsNullOrEmpty($Keyword3))))
            {
                 #Write-host "KeyWord2 & 3 are empty "
                         if (($ShortDescription -like "*$Keyword1*"))
                         {
                            $finalData += $IncidentNumber + "="  + $Category + "="  + $SubCategory  + "="  + $Keyword1
                           # $finalData += $ShortDescription
                           # $finalData += $Category
                           # $finalData += $SubCategory
                           $ExcludeRows += $j
                           $ExludeIncidentNumbers += $IncidentNumber 
                         }
                         Else
                         {
                            #Write-host "Keywords 1 is not found"
                            #$finalData += $IncidentNumber + ";"  + $ShortDescription + ";"  + $Category + ";"  + "Other"
                            #$j= $j+1
                            continue
                         }
            }
            
           <# Else ( ( ([string]::IsNullOrEmpty($Keyword1))) -and (([string]::IsNullOrEmpty($Keyword2))) -and ( ([string]::IsNullOrEmpty($Keyword3))))
            {
                <# $finalData += $IncidentNumber + ";"  + $ShortDescription + ";"  + $Category + ";"  + "Other"
                   # $finalData += $ShortDescription
                   # $finalData += $Category
                   # $finalData += $SubCategory
                   $ExcludeRows += $j #>
                   <#break
            }#>
            
      }
  
  #Write-host "Excuding Row number :" $ExcludeRows
  #Write-Host "Exluded Incident numbers : " $ExludeIncidentNumbers 
  
  # $j= $j+1
    }

#$i= $i+1
}

Write-Host "before " $finalData

$ResultFile = “$FolderPath\First-$FinalDataName”
Write-host $ResultFile

$finalData | Set-Content $ResultFile # Working one

$WorkBookKL.close()
$ExcelKL.Quit()
$WorkBookDA.close()
$ExcelDA.Quit()
$Endtime = Get-date
$TotalTimeTaken= $Endtime - $StartTime

Write-host “Total Time taken is :” $TotalTimeTaken

2

Utkarsh,
Welcome to the forum. :wave:t3:

Wow … that’s a lot of code. And it’s all unformatted.

Please … before we proceed … go back, edit your question once again and fix the formatting of your code.

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org 1 <---- Click :point_up_2:t4: :wink:

Regardless of that … you did not ask any specific question about your code.

It is out of scope for a forum like this to review of refactor your code for you.

3

Hello,

First, I would like to say that your code is too long. When submitting a request, you need to narrow it to the least amount of lines to make it understandable by others.

But, I think your issue is dealing large number of items in arrays. This is NOT a good idea. -contains is a very slow command, that works well when dealing with hundreds of items. Not thousands.

Instead, you should store your data in Hashtables

$Hash = $MyArray | group  -AsHashTable

This is an easy way to convert an array into a hashtable.

Then, you simply need to see if your element is in the hashtable

$Hash.containskey("MyElement") # note : this returns a boolean

This will give you an instant results, where -contains will take a long time.