locked
Memory usage of powershell RRS feed

  • General discussion

  • Some background: We have a powershell program to be used for comparing 2 excelsheets given by my client. The script essentially has to compare two excel sheet along with some condition and give the output of rows with error in 3rd sheet.

    My issue: The code is supposed to be used handling rows with 100,000 to 200,000 rows.

    But if i give 10K rows of data the program hangs and memory usage reach 5-6 GB.

    My question: Is how do i reduce the memory usage and improve the efficiency.

    #DMS 11-Oct-2017 - 11 PM Continue from here 
    C:\Users\user\Desktop\DMS\codes\Screenshot.ps1
    #3 array creation and comparison working
    ############################################################################################
    # Initialise Excel
    Add-Type -AssemblyName Microsoft.Office.Interop.Excel
    $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $true
    $sousheet = "MAKT"
    <############################################################################################
    $xlShiftToRight = -4163
    $xlpsttype = -4163
    $default = [Type]::Missing
    $concval = ""
    $concvaltemp = ""
    ############################################################################################>
    $sourcefile = "C:\Users\user\Desktop\DMS\sou_2K.xlsx"
    $soucetemp = Split-Path $sourcefile
    $soucedump = $soucetemp + "\temp.xlsx"
    $loadingWorkBook = $Excel.Workbooks.Open($sourcefile)
    $loadingsheets = $loadingWorkBook.worksheets.item($sousheet)
    $testsheets = $loadingWorkBook.worksheets
    $inti = 0
    ############################################################################################
    #Selecting the sheet MAKT
    ##########################
    foreach($ls in $testsheets)
    {
        $inti++
        if($sousheet -eq $ls.Name)
        {
            $inti
            break;}}
    
    $testsheets=$ls=$i=$inti=$Null
    ############################################################################################
    #Counting the number of Rows and Columns
    ########################################                     
    $norequestcols = $loadingsheets.UsedRange.Columns.Count
    "Columns count in Source is :" + $norequestcols
    $dynamicol = $norequestcols + 1
    "Dynamic Columns in Source count is :" + $dynamicol
    $norequestrows = $loadingsheets.UsedRange.Rows.Count
    "Rows count in Source is :" + $norequestrows
    <############################################################################################
    #Creation of Source Array - Source array not used
    #########################
    Write-Host "";
    Write-Host "Source Array";
    
    [array]$sourcearr = @()
    [string]$celldata  = @()
    
    for ($i=2; $i -lt $norequestrows+1; $i++)
    {
        for ($j=1; $j -lt $dynamicol; $j++)
        {
            $celldata = $celldata + $loadingsheets.Cells.item($i,$j).Value2
            $celldata = $celldata + ":"
        }
        #$celldata
        $sourcearr+= @($celldata.TrimEnd(":"))
        $celldata = ""
        #$sourcearr[$i]
    }
    $sourcearr#.Length
    $sourcearr.Length
    Write-Host "";
    
    ############################################################################################>
    # $loadingsheets - Variable pointing to MAKT sheet in Source
    # Adding a column concating all the columns in source sheet
    ############################################################
    $objRange = $loadingsheets.Range('A1').EntireColumn
    $objRange.Insert($xlShiftToRight)   
    
    for ($i=2; $i -lt $dynamicol+1; $i++)
    {
    
        $coladdr = $loadingsheets.Cells.item(1,$i).address($false,$false)
        $coladdr
        $concvaltemp += "$coladdr,"
    }
    
    $len = $concvaltemp.length
    $len
    $concval = $concvaltemp.TrimEnd(",")
    
    $formula1 = "=CONCATENATE($concval)"
        
    $loadingsheets.range("A1:A$norequestrows").NumberFormat ="General"
    $loadingsheets.Cells.Item(1,1).Formula= "$formula1"
    $loadingsheets.range("A1:A$norequestrows").formula = $loadingsheets.range("A1").formula
    ############################################################################################
    $formula1=$len=$i=$coladdr=$Null
    ############################################################################################
    <#$loadingsheets.Range("A1:A$norequestrows").Select()
    #$loadingsheets.Range("A1:A$norequestrows").Copy()
    $rangerpnextcolumnrange = $loadingsheets.Range("$rangerpsubs$t").EntireColumn	
    $tocolumn=$rangerpnextcolumnrange
    $tocolumn.PasteSpecial($xlpsttype)
    $loadingsheets.Range("$rangerpsubs$t").EntireColumn.Select()
    $loadingsheets.Range("$rangerpsubs$t").EntireColumn.Copy()
    $loadingWorkBook2 = $Excel.Workbooks.Open("C:\Users\user\Desktop\DMS\Result.xlsx")
    $loadingsheets2 = $loadingWorkBook2.worksheets.item(1)
    $con1 = $loadingsheets2.Range("B1").EntireColumn
    $con1.PasteSpecial($xlpsttype)
    $loadingsheets.Range("A1").EntireColumn.Delete()#>
    #################################################################################################
    # $loadingsheets - Variable pointing to MAKT sheet in Source
    # Adding a column to find the result of Vloopup in target sheet
    ################################################################
    $objRange = $loadingsheets.Range('A1').EntireColumn
    $objRange.Insert($xlShiftToRight) 
    #################################################################################################
    $i=$Null
    #################################################################################################
    $tarfile = "C:\Users\user\Desktop\DMS\tar_2K.xlsx"
    $tarsheet = "MAKT"
    $concvaltartemp = ""
    $concvaltar = ""
    $xlpsttype = -4163
    $loadingWorkBook1 = $Excel.Workbooks.Open($tarfile)
    $tarfilename = Split-Path $tarfile -Leaf 
    $tarfilename
    $loadingsheets1 = $loadingWorkBook1.worksheets.item($tarsheet)
    $testsheets1 = $loadingWorkBook1.worksheets
    $inti1 = 0
    #################################################################################################
    foreach($ls1 in $testsheets1)
    {
        $inti1++
        if($tarsheet -eq $ls1.Name)
        {
            $inti1
            break;}}
    #################################################################################################
    # $loadingsheets1 - Variable pointing to MAKT sheet in Target
    # Taking count of the columns in target sheet
    ################################################################
    $norequestcols1 = $loadingsheets1.UsedRange.Columns.Count
    "Columns count in Target is :" + $norequestcols1
    $dynamicoltar = $norequestcols + 1
    "Dynamic Columns count in Target is :" + $dynamicoltar
    $norequestrows1 = $loadingsheets1.UsedRange.Rows.Count
    "Rows count in Target is :" + $norequestrows1
    Write-Host "";
    Write-Host "Target Array";
    
    [array]$targetearr = @()
    [string]$celldata1  = @()
    
    for ($k=2; $k -lt $norequestrows+1; $k++)
    {
        for ($l=1; $l -lt $dynamicol; $l++)
        {
            $celldata1 = $celldata1 + $loadingsheets1.Cells.item($k,$l).Value2
            $celldata1 = $celldata1 + ":"
        }
        #$celldata
        $targetearr+= @($celldata1.TrimEnd(":"))
        $celldata1 = ""
        #$sourcearr[$i]
    }
    $targetearr#.Length
    "Rows in target array is :" + $targetearr.Length
    Write-Host "";
    #################################################################################################
    # $loadingsheets1 - Variable pointing to MAKT sheet in Target
    # Adding a column concating all the columns in target sheet
    ################################################################
    $objRange1 = $loadingsheets1.Range('A1').EntireColumn
    $objRange1.Insert($xlShiftToRight)  
    
    for ($i=2; $i -lt $dynamicoltar+1; $i++)
    {
    
        $coladdr = $loadingsheets1.Cells.item(1,$i).address($false,$false)
        $coladdr
        $concvaltar += "$coladdr,"
    }
    $len01 = $concvaltemp.length
    $concvaltar = $concvaltemp.TrimEnd(",")
    
    $formula1 = "=CONCATENATE($concvaltar)"
    $loadingsheets1.range("A1:A$norequestrows1").NumberFormat ="General"
    $loadingsheets1.Cells.Item(1,1).Formula= "$formula1"
    $loadingsheets1.range("A1:A$norequestrows1").formula = $loadingsheets1.range("A1").formula
    $loadingsheets1.Range("A1:A$norequestrows1").Select()
    $loadingsheets1.Range("A1:A$norequestrows1").Copy()
    #################################################################################################
    $coladdr=$i=$formula1=$Null
    #################################################################################################
    <#
    $rangerpnextcolumnrange1 = $loadingsheets1.Range("$rangerpsubs1$t1").EntireColumn	
    $tocolumn1=$rangerpnextcolumnrange1
    $tocolumn1.PasteSpecial($xlpsttype)
    $loadingsheets1.Range("$rangerpsubs1$t1").EntireColumn.Select()
    $loadingsheets1.Range("$rangerpsubs1$t1").EntireColumn.Copy()
    $con21 = $loadingsheets2.Range("C1").EntireColumn
    $con21.PasteSpecial($xlpsttype)
    $loadingsheets1.Range("A1").EntireColumn.Delete()
    #>
    ##########################################################################################################
    $fal = "False"
    $tru = "True"
    
    $loadingsheets.range("A1:A$norequestrows").NumberFormat ="General"
    $formula12 = "=IF(ISNA(VLOOKUP(B1,[$tarfilename]$tarsheet!A`$1:A`$$norequestrows,1,FALSE)),$fal,$tru)"
    $loadingsheets.Cells.Item(1,1).Formula= "$formula12"
    $loadingsheets.range("A1:A$norequestrows").formula = $loadingsheets.range("A1").formula
    $loadingsheets.Range("A1:XFD1").EntireColumn.Copy()
    
    $soucedump = $soucetemp + "\temp.xlsx" #- address of Temp file
    $loadingWorkBooktemp = $Excel.Workbooks.Open($soucedump)
    $loadingsheettemp = $loadingWorkBooktemp.worksheets.item("Sheet1")
    
    $con21 = $loadingsheettemp.Range("A1:XFD1").EntireColumn
    $con21.PasteSpecial($xlpsttype)
    $norequestrowstemp=$loadingsheettemp.UsedRange.Rows.Count
    "Rows count in temp sheet is :" + $norequestrowstemp
    
    #$loadingWorkBook.Worksheets.item($sousheet).Select()
    #$loadingWorkBook.Worksheets.item($sousheet).Copy($loadingWorkBooktemp.Worksheets.item("Sheet1"))
    $filtercolno = 1
    $filterval = "False"
    $xlFilterValues = 7
    #$Excel.Selection.AutoFilter($filtercolno ,$filterval ,$xlFilterValues)
    $con23=$loadingsheettemp.Range("A1:A$norequestrowstemp").AutoFilter($filtercolno ,$filterval ,$xlFilterValues)
    #$Excel.Selection.EntireRow.Delete()
    
    #<#
    $loadingsheettemp.Range("A1:XFD1").EntireColumn.Copy()
    $result = "C:\Users\user\Desktop\DMS\error.xlsx"
    
    $loadingWorkBookres = $Excel.Workbooks.Open($result)
    $loadingsheetres = $loadingWorkBookres.worksheets.item("Sheet1")
    
    $con31 = $loadingsheetres.Range("A1:XFD1").EntireColumn
    $con31.PasteSpecial($xlpsttype)
    
    $loadingsheetres.Range("A1").EntireColumn.Delete()
    $loadingsheetres.Range("A1").EntireColumn.Delete()
    $loadingsheetres.Range("A1").Select()
    
    $norequestcols_err = $loadingsheetres.UsedRange.Columns.Count
    "Columns count in Error sheet is :" + $norequestcols_err
    $norequestrows_err = $loadingsheetres.UsedRange.Rows.Count
    "Rows count in Error sheet is :" + $norequestrows_err
    #>
    
    Write-Host "";
    Write-Host "Error Sheet Array";
    
    [array]$errorarr = @()
    #[array]$errorarr1 = @()
    [string]$celldata2  = @()
    
    for ($i=2; $i -lt $norequestrows_err+1; $i++)
    {
        for ($j=1; $j -lt $norequestcols_err+1; $j++)
        {
            $celldata2 = $celldata2 + $loadingsheetres.Cells.item($i,$j).Value2
            $celldata2 = $celldata2 + ":"
        }
        #$celldata
        $errorarr+= @($celldata2.TrimEnd(":"))
        $celldata2 = ""
        #$sourcearr[$i]
    }
    $errorarr#.Length
    "Rows in Error array is :" + $errorarr.Length
    
    Write-Host "";
    Write-Host "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
    #============== Writing for blank ==============
    #Have to get user input for blank or  no change
    # Blank :- if the change request if for blank, then logic should be
    # Blank:- If data is blank in source then the data should be blank in target too, else it is error.
    # No Change:- If data is blank in source, then we do not reconcile / check that cell.
    
    # Now coding for no change
    # There can be more than 2 columns which combine to form an unique entry. 
    # Since this a generic code we have to take user input on how many columns form a unique entry.
    
    #============================= Taking user input for Column Number =============================
    $col_comp = $(
          Add-Type -AssemblyName Microsoft.VisualBasic
          [Microsoft.VisualBasic.Interaction]::InputBox('Enter No. of Columns that form unique Key','', '')
         )
    "No of Columns to form unique entry is :" + $col_comp
    #============================= Taking user input for Column Number =============================
    #Closing excel without saving
    #$sourcefile.Close($False)
    #$tarfile.Close($False)
    #$result.Close($False)
    #$loadingWorkBooktemp.Close($False)
    
    <#$loadingWorkBook.Close($False) 
    $loadingWorkBook1.Close($False)
    $loadingWorkBookres.Close($False)
    $loadingWorkBooktemp.Close($False)
    
    $Excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
    Stop-Process -Name EXCEL
    #>
    #============================= Closing excels =============================
    $newerrexcel = New-Object -comobject Excel.Application
    $newerrexcel.visible = $True
    $newerrworkbook = $newerrexcel.Workbooks.Add()
    $newerrworksht = $newerrworkbook.Worksheets.Item(1)
    $newerrcell=$newerrworksht.Range("A2")
    
    $i=$j=$k=$l=$m=$n=0
    [array]$chk1 = @()
    [array]$chk2 = @()
    [string]$chk3 = @()
    [string]$chk4 = @()
    [int]$match1=0
    [string]$celldata2  = @()
    
    Write-Host "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
    for ($i=0;$i -lt $norequestrows_err; $i++)
    {
        C:\Users\user\Desktop\DMS\codes\move_mousepoint.ps1
        if ($i % 100 -eq 0) {C:\Users\user\Desktop\DMS\codes\Screenshot.ps1} #Taking screenshot to check memory usage
        $chk3=$null
        Write-Host "=============================================================="
        $chk1=$null
        $chk1 = $errorarr[$i] -split":"
        if($chk1) 
        {  
            for ($l=0;$l -lt $col_comp; $l++) {$chk3=$chk3+$chk1[$l]} #combains the given number of columns in a string. THis will help in initial comparision
        for ($j=0; $j -lt $norequestrows1; $j++)
        {
            $chk4=$null
            Write-Host "Now Checking Err Sheet Row Number: $i having data : $chk1"
            $chk3
            $chk2=$null
            $chk2 = $targetearr[$j] -split":"
            if($chk2) 
            { 
                for ($m=0;$m -lt $col_comp; $m++) {$chk4=$chk4+$chk2[$m]} #combains the given number of columns in a string. THis will help in initial comparision
                
                Write-Host "Now Checking tgt Sheet Row Number $j having data : $chk2"
                $chk4
                #}}}}
                $match1=0
            if ($chk3 -match $chk4)
            {
            # If the initial comparision is successful the all the columns are compared to get the output.
    
            for ($k=0;$k -lt $norequestcols_err; $k++)
            {
                #if ($chk1[0] -match $chk2[0] -and $chk1[1] -match $chk2[1]) 
                #{
                    if ($chk1[$k]) 
                    {
                        if($chk1[$k] -eq $chk2[$k]) 
                        {$chk1[$k];$match1=$match1+1;}
                    }
                    else 
                    {
                        $match1=$match1+1;
                    }
                #} else {Write-Host "No Matching, Hence breaking"; Write-Host "=============================================================="; break}
            }
            if ($match1 -eq $norequestcols_err) {<#$errorarr[$i]=""#>} else {for ($n=0; $n -lt $norequestcols1; $n++)     {$newerrworksht.Cells.item($i+1,$n+1).Value2=$chk1[$n] }}
            if ($match1 -gt 0) {Write-Host "No. of Matches $match1"; Write-Host "No. of Cols $norequestcols_err"; Break}
            } else {Write-Host "No Matching, Hence breaking"; Write-Host "==============================================================";} #else {Write-Host "String is EMPTY or NULL"}
        }else {Write-Host "String is EMPTY or NULL"}
        }#else {Write-Host "String is EMPTY or NULL"}
    }else {Write-Host "String is EMPTY or NULL"}
    }
    Write-Host "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
    Write-Host
    Write-Host
    C:\Users\user\Desktop\DMS\codes\Screenshot.ps1
    $errorarr
    $chk1=$chk2=$null
    
    $i=$j=$k=$l=$m=$n=$null
    
    C:\Users\user\Desktop\DMS\codes\Screenshot.ps1
    
        


    Santhoshrao11


    • Edited by Santhoshrao11 Friday, November 17, 2017 6:38 PM
    • Changed type Bill_Stewart Thursday, January 25, 2018 10:39 PM
    • Moved by Bill_Stewart Thursday, January 25, 2018 10:39 PM This is not "fix/debug/rewrite my script for me" forum
    Friday, November 17, 2017 6:35 PM

All replies

  • Do not copy the cells into memory.  You are forcing all data to be copied to memory.  This will use a lot of memory.  Do not assign spreadsheet objects to variables.  This forces them to be locked into memory.

    Mostly your issues are do to an incorrect design for the tsk you are attempting.

    We can use ADO.Net and SQL to compare two or more tables.  This is very fast and uses very little memory.

    You can also directly compare cells by reference which would use less memory and be faster although ADO is the best and fastest method.

    Directly match two cells:

    $doc1.Sheets[1].UsedRange.Cells($x,$y).Value -eq $doc2.Sheets[1].UsedRange.Cells($x,$y).Value


    \_(ツ)_/


    • Edited by jrv Friday, November 17, 2017 6:46 PM
    Friday, November 17, 2017 6:43 PM
  • Please read the following:

    This forum is for scripting questions rather than script requests

    Troubleshooting and/or redesigning custom long scripts is outside this forum's scope.


    -- Bill Stewart [Bill_Stewart]

    Friday, November 17, 2017 6:44 PM
  • Sound good let me try that.

    Santhoshrao11

    Monday, November 20, 2017 3:51 PM