none
Compare a Column from CSV with a Column in EXL and add the result to my CSV File

    Question

  • Hello,

    I want to compare my CSV File with EXL file.

    The EXL file has many columns such as USERID and WAVE. My CSV file has just USERID Column.

    it should compare my CSV Column USerID with USERID Column in Exl and checks in which wave the user A in CSV has in EXL file and add a wave column in CSV File called "WAVE” and write the wave Number related to USER A in CSV file.

    If no name from CSV file is found in EXL file, write in the Column Wave “NOT Found".

    I am not sure what I wrote "Read and Get Values from Excel" helps or not but I have no clue how/what to do further.


    $TXTFile = "C:\A.txt" 
    $CSVFile = "C:\B.csv" 
    $EXLFIle = "C:\C.xlsx"
    $SheetName = "Tabelle1"

    #find all CNs in TXT file and list them in CSV File
    Select-String -Path $TXTFile  -Pattern 'CN=(.*?),' -AllMatches |
      Select-Object -Expand Matches |
      ForEach-Object { $_.Groups[1].Value } | 
      select @{L="UserID"; E={$_}} |
      Export-CSV $CSVFile -noTypeInformation
      
    # Read and Get Values from Excel 
    #Create an instance of Excel.Application and Open Excel file
    $ObjExcel = New-Object -ComObject Excel.Application
    $Workbook = $ObjExcel.Workbooks.Open($EXLFIle)
    $Sheet = $workbook.Worksheets.Item($SheetName)
    $ObjExcel.Visible = $false

    #Count max Rows
    $RowMax = ($sheet.UsedRange.rows).count

    #Declare the starting positions
    $rowUserID,$colUserID = 1,2
    $rowWave,$colWave = 1,9

    for ($i=1; $i -le $RowMax-1; $i++)
    {
    $UserID = $Sheet.Cells.Item($rowUserID+$i,$colUserID).text
    $Wave = $Sheet.Cells.Item($rowWave+$i,$colWave).text

    Write-Host ("USERID: "+$UserID)
    Write-Host ("Wave: "+$Wave)
    }

    $objExcel.quit() 




    • Edited by frhling Friday, February 09, 2018 12:39 PM
    Friday, February 09, 2018 12:38 PM

All replies

  • $UserID = $Sheet.Cells.Item($I,2).Value
    $Wave = $Sheet.Cells.Item($I,9).Value

    \_(ツ)_/

    Friday, February 09, 2018 12:53 PM
    Moderator
  • If your excel sheet is a real table, I mean all columns got a header, you can save the sheet as csv file and read it with Import-CSV. Then, you can use it as an array of objects.
    Friday, February 09, 2018 1:24 PM
  • ok. I did so and converted it:

    $excelwb = New-Object -ComObject excel.application
    $workbook = $excelwb.Workbooks.Open($SourceEXLFIle)
    $workbook.SaveAs($TargetCSVFile,6)
    $workbook.Close($false)
    $ExcelWB.quit()
    $csv = Import-Csv $TargetCSVFile -Delimiter ";"

    what next? I do not know how to continue....

    Friday, February 09, 2018 1:44 PM
  • How to navigate an Excel file and find a value in a column.

    $xlFile = 'd:\TEST\book1.xlsx'
    $sheetName = 'sheet1'
    $xl = New-Object -ComObject Excel.Application
    $wb = $xl.Workbooks.Open($xlFile)
    
    for($i = 2;$i -le $wb.Worksheets[$sheetName].UsedRange.Rows.Count;$i++){
        Write-Host $wb.Worksheets[$sheetName].UsedRange.Cells($i,1)
    }
    
    foreach ($row in $wb.Worksheets[$sheetName].UsedRange.Rows) {
        Write-Host $row.Cells[1].Value2
    }
    
    # find row number of value in column 1
    $rownum = $wb.Worksheets[$sheetName].UsedRange.Columns[1].Find('2').Row
    # get value in column 9 using row.
    $wb.Worksheets[$sheetName].UsedRange.Cells($rownum,9).Value2

    "Find" is the easiest way to compare the two file types.  Converting to a CSV is not necessary;

    foreach($line in (Import-Csv a.csv)) {
        # find userid in Excel and assign to CSV
        $rownum = $wb.Worksheets[$sheetName].UsedRange.Columns[1].Find($line.UserID).Row
        $line.Wave = $wb.Worksheets[$sheetName].UsedRange.Cells($rownum, 9).Value2
    }


    \_(ツ)_/


    Friday, February 09, 2018 1:54 PM
    Moderator
  • Thanks.

    When I run the script it has some errors such as:

    • You cannot call a method on a null-valued expression.
    • It is not possible to apply an index to a NULL array

    What could be the problem?

    I also have some questions:

    • foreach ($row in $wb.Worksheets[$sheetName].UsedRange.Rows)    what is $row here? We have not defined it before.
    • I do not understand what the first “for” loop and “for each” loop do.
    • Should I save the value in “get value in column 9 using row” in $line?


    Friday, February 09, 2018 7:27 PM
  • From your question

    foreach ($row in $wb.Worksheets[$sheetName].UsedRange.Rows)    what is $row here? We have not defined it before.

    We can understand you need some basic PowerShell knowledge.

    https://mva.microsoft.com/liveevents/powershell-jumpstart


    Regards kvprasoon

    Saturday, February 10, 2018 7:30 PM
  • From your question

    foreach ($row in $wb.Worksheets[$sheetName].UsedRange.Rows)    what is $row here? We have not defined it before.

    We can understand you need some basic PowerShell knowledge.

    https://mva.microsoft.com/liveevents/powershell-jumpstart


    Regards kvprasoon

    This is standard and common PowerShell use of Excel.  You can also find many C# examples of this..  Just run it to see how it works,

    "$row" is an Excel row in a Range. Look at the Excel object model to see how these objects are constructed.

    PowerShell is poorly understood by even those who think they understand PowerShell.  I recommend experimenting with the code to gain further understanding.

    Examples:

    $wb.Worksheets[$sheetName].UsedRange.Rows.Count
    $wb.Worksheets[$sheetName].UsedRange.Columns.Count
    $wb.Worksheets[$sheetName].UsedRange.Cells(1,1).Value2

    Creating extra variables with Excel is a bad idea.  This makes it difficult to close Excel because all of these objects have to be released before Excel will close.

    The use of "Find" to get values in a Row, Column or Range is the Excel method for matching data.  You can also use "FindNext" abd "FindPrevious" to collect multiple values in a range or we can use "Filter" to restrict a Range to specific values to work with.  In this way Excel behave like a datatable.

    Other usful techniquese are to extract a Range as a two dimensional array.


    \_(ツ)_/

    Saturday, February 10, 2018 8:36 PM
    Moderator