Readind Excel Worksheet

PowerShell Help
1

Hi
I have the following code that reads Excel file. It has 2 work sheets. One called Input the other called Targeted. Please see following.

Excel2
Excel21338×547 238 KB

Excel1
Excel11340×523 82.8 KB

I want to match in the input worksheet thos columns “B” and “C” to To worksheet Targeted Columns “C” and “E”.

Once there is a match then I would Like to replace Value of Column “o” in the Input worksheet to Value of column “H” in the worksheet Targeted.

I have following code that worls and reads correct columns and values from INPUT worksheet.

$File = "c:\Excel\converted2_txt_csv.xlsx"

# Setup Excel, open $File and set the the first worksheet

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $False

$Workbook = $Excel.workbooks.open($file)

$Worksheets = $Workbooks.worksheet

$Worksheet1 = $Workbook.Worksheets.Item(1)

$Worksheet2 = $Workbook.Worksheets.Item(2)

 

#Echo $Worksheet

#Echo $Worksheet2

 

$intRowMax =  ($worksheet.UsedRange.Rows).count

$intColMax =  ($worksheet.UsedRange.Columns).count

 

#select total rows

$rowMax = ($Worksheet1.UsedRange.Rows).Count

 

#create new object with Name, Address, Email properties.

$myData = New-Object -TypeName psobject

$myData | Add-Member -MemberType NoteProperty -Name AccountNumber -Value $null

$myData | Add-Member -MemberType NoteProperty -Name AccountName -Value $null

#$myData | Add-Member -MemberType NoteProperty -Name Email -Value $null

 

 

 

 

 

#   #Write-Host "Processing: " $intRowMax " rows" "and columns " $intColMax

 

                for ($i = 1; $i -le $rowMax; $i++)

                             {

                                $objTemp = $myData | Select-Object *

                               Echo $objTemp

  

                                #read data from each cell

                                 $objTemp.AccountNumber = $Worksheet1.Cells.Item($i,2).Text

                                 $objTemp.AccountName = $Worksheet1.Cells.Item($i,3).Text

#                                 Write-Host 'AccountNumber' $objTemp.AccountNumber 'AccountName-' $objTemp.AccountName

#                                 Echo $objTemp.AccountNumber

#                                 Echo $objTemp.AccountName

 

                                

#                                 If ($Worksheet2.Item(2).Range("A:Z").Find($objTemp.AccountNumber))                   

                                     

                                      

                                      Echo $objTemp.AccountNumber

                                     

    

                              }
2

What’s the question?

And BTW: have you considered using the great module from Doug Finke ImportExcel? It might make it a lot easier to work with Excel files.

3

How do i do the following:

Once there is a match then I would Like to replace Value of Column “o” in the Input worksheet to Value of column “H” in the worksheet Targeted.

Thanks

4

Your explanation is quite confusing. :thinking:

If I got it right you will need a nested loop iterating over the rows of the targeted worksheet in the outer loop and the input worksheet in the inner loop comapring the two pairs of the desired cell contents and changing the value in the column “O” from the inner loop (targeted worksheet) to the value of the column “H” from the outer loop (input worksheet) when the comparison results in a match.

It would have been actually helpful to post some sample CSV data instead of the images of the data.

Regardless of that: Your code is really badly formatted and has a lot of unnecessary and anoying whitespace. :face_with_diagonal_mouth:

5

[quote=“Olaf, post:2, topic:18522”]

[
THanks Olaf