How to run in a loop for column A , find a value and print the corresponding data from column B-CodePudding

I am working on xlsx file and i need to read values from column A and display the values in column B For an example column A has 100 rows and some of them have a string. At column B (Also 100 rows) i have also values. I want to run in a loop a search for all the cells in column A, Store them and print the corresponding values in column B

I want to search for # and display 1,2,7 from B I need an object that holds the values from A and object for B (For further actions)

The code below search in all the columns and display the values. What i need is to read only from a specific column. and i need an object that holds the values from A and B

$data holds the data of column A. I want to in a loop and search for data and then display the same data in the same row in column B?

$ExcelFile = "C:\Temp\SharedFolder\Test.xlsx" 
$excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Excel.DisplayAlerts = $False # Disable comfirmation prompts
$workbook = $excel.Workbooks.Open($ExcelFile)

$data = $workbook.Worksheets['Sheet1'].UsedRange.Rows.Columns[1].Value2

CodePudding user response：

Doing this in Excel can be done, but takes a bit more work.

If this is your Excel file:

$ExcelFile   = "D:\Test\Test.xlsx" 
$searchValue = '@'

$excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $False # Disable comfirmation prompts
$workbook  = $excel.Workbooks.Open($ExcelFile)
$worksheet = $workbook.Worksheets.Item(1)

# get the number of rows in the sheet
$rowMax = $worksheet.UsedRange.Rows.Count

# loop through the rows to test if the value in column 1 equals whatever is in $searchValue
# and capture the results in variable $result
$result = for ($row = 1; $row -le $rowMax; $row  ) {
    $val = $worksheet.Cells.Item($row, 1).Value2
    if ($val -eq $searchValue) {
        # output an object with both values from columns A and B
        [PsCustomObject]@{A = $val; B = $worksheet.Cells.Item($row, 2).Value2}
    }
}

# when done, quit Excel and remove the used COM objects from memory (important)
$excel.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Now you can process the objects in $result. For demo just output:

$result

A B
- -
@ 1
@ 2
@ 7

Of course, it would be far easier if you save your Excel file as CSV..

$searchValue = '@'
$result = Import-Csv -Path 'D:\Test\Test.csv' -UseCulture | Where-Object { $_.A -eq $searchValue }

$result

When exporting an Excel file to Csv, Excel won't always use the comma as delimiter character. That depends on your machine's local settings. This is the reason I added switch -UseCulture to the Import-Csv cmdlet which will make sure it uses the same delimiter character your locally installed Excel uses for its output.