locked
how to compare formatted excel with powershell 2 RRS feed

  • Question

  • Hi there,

    I am trying to compare files in 2 different folders.

    Filenames are like:

    Folder 1:

    3633_DSXXXX_SRK322 Tax Calculation_Jul 2018_20180827_12-50-24

    Folder 2:

    3649_DSXXXX_SRK322 Tax Calculation_Aug 2018_20180913_13-49-59

    Only the highlighted part matches and that is what i need to compare i.e. if highlighted part of a file in folder 1 matches to file in folder 2 then compare the contents of the file.

    For this i have written below code but i am getting error "A parameter cannot be found that matches parameter name 'Encoding'."

    Can you please help?

    Please see the code below -

    Import-Module "C:\Users\Desktop\Initiative\PSExcel\Import-XLS.ps1"

    $firstDirPath = Read-Host "Insert Address of first directory"
    $secondDirPath = Read-Host "Insert Address of second directory"
    $saveDir = "C:\Users\Desktop\Initiative"
    $firstDir = Get-ChildItem ($firstDirPath)
    $secondDir = Get-ChildItem($secondDirPath)

    $Parentpath
    $FirstDirFiles = @()
    $secondDirFiles = @()

    $count = 0

    foreach ($file in $firstDir) {
        $FirstFiveChars = $file.Name.Substring(5, $file.name.LastIndexOf("_"))
        $Rundate = $FirstFiveChars.Substring(0, $FirstFiveChars.LastIndexOf("_"))
        $withoutTimestamp = $Rundate.Substring(0, $Rundate.LastIndexOf("_"))
        $withoutDate = $withoutTimestamp.Substring(0, $withoutTimestamp.LastIndexOf("_"))
        $FirstDirFiles = $withoutDate
        }

        foreach ($file in $secondDir) {
        $FirstFiveChars = $file.Name.Substring(5, $file.name.LastIndexOf("_"))
        $Rundate = $FirstFiveChars.Substring(0, $FirstFiveChars.LastIndexOf("_"))
        $withoutTimestamp = $Rundate.Substring(0, $Rundate.LastIndexOf("_"))
        $withoutDate = $withoutTimestamp.Substring(0, $withoutTimestamp.LastIndexOf("_"))
        $secondDirFiles = $withoutDate
         }
      
       $newDir = $secondDir
     
       ########################## ACTUAL COMPARE ##############################
       
       foreach($file in $newDir){
        for ($i = 0; $i -lt $firstDir.length; $i++){ # Loop through the entire first directory
          
            if ($file -match $FirstDirFiles[$i]){ # check whether second directory gile matches the '$i'th row of the first directory
               
                $newDir = $newDir | Where-Object { $_ -ne $secondDir[$i]} # if matches, remove item from the batch as I do not want to compare it on the next loop
                
                try{
                    $firstFilepath = "$firstDirPath\" + $FirstDir[$i]
                    $secondFilepath =  "$secondDirPath\" + $file
                    Write $firstFilepath
                    Write $secondFilepath
     
                    # Get the file extension
                    if (($firstDir[$i].Name.Substring($firstDir[$i].Name.LastIndexOf('.') + 1) -and ($secondDir[$i].Name.Substring($secondDir[$i].Name.LastIndexOf('.') + 1)) -eq "csv")){
                        #compareCSV $firstFilepath $secondFilepath
                    }elseif (($firstDir[$i].Name.Substring($firstDir[$i].Name.LastIndexOf('.') + 1) -eq "xls") -and ($secondDir[$i].Name.Substring($secondDir[$i].Name.LastIndexOf('.') + 1) -eq "xls")){
                         compareXLS $firstFilepath $secondFilepath $file $saveDir
                         $count ++
                    }
                } 

                catch [System.SystemException] 
                {
                    write-host $_ 
                    write-host ""
                }  
                
            } 
            
                $count++
         }
    }

    ShowLeftover $newDir $saveDir


     #####******************************COMPARE EXCEL FUNCTION************************************************######
     
     Function compareXLS($firstFilepath, $secondFilepath, $firstFile, $saveDir){
        
        $a = Import-XLS $firstFilepath 
        $b = Import-XLS $secondFilepath
        $same = 0

        for($i = 0; $i -lt $a.Length; $i++){
            if($a[$i] -match $b[$i]){
              
            }else{
                Write $a[$i]
                $diff +=  "PRE  = " + [System.String] $a[$i]  + "`n"
                $diff +=  "POST = " + [System.String] $b[$i]  + "`n`n"
            }
        }

        $diff | Out-File "C:\Users\Desktop\Initiative${Firstfile}.csv"
     


     Function showLeftOver ($newDir, $saveDir){
        Write "These are the leftover files:"
        $newDir

    }
       

    Tuesday, November 13, 2018 12:13 PM

All replies

  • Please edit your original post and format your code as code. Thanks.

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Tuesday, November 13, 2018 12:53 PM
  • To get the part of the name to compare:

    $name1= '3633_DSXXXX_SRK322 Tax Calculation_Jul 2018_20180827_12-50-24' -split '_'

    Now just compare the two bits that you need to compare in  n if statement.

    if($name[1] -eq $name2[1] -and $name1[2] -eq $name2[2]){ ...


    \_(ツ)_/


    Tuesday, November 13, 2018 1:58 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    LEE


    Just do it.

    Tuesday, November 27, 2018 2:36 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Tuesday, December 4, 2018 3:13 AM