locked
Search large array created via import-csv RRS feed

  • Question

  • Hello,

    I've got a problem with the search I am performing in my Script.

    There are two  large arrays imported from CSV-files, they hold DNS data like

    name,type,data
    hostname,CNAME,alias123.contoso.com

    I'm now searching to see if my $dataONE exists in $inputTWO, and get the values of the array.

    -match does not work as it is ambigious and finds lines with additional letters behind my $dataONE, like "alias123ETC" aswell.

    So what i did is 

    		if ($inputTWO.name -eq $dataONE)

    However, that only gives me the the inputTWO.name back, but i need to work with the inputTWO.type and data of that line aswell.

    I then tried 

    (($inputTWO -match $dataONE) | where {$_.name -eq $dataONE}) 

    But that takes 27-28 seconds to run, that for my 4000 Records in inputONE, will take me over 30 hours to run :S Let alone the rest of the script.

    Is there a better way to search for the line where $inputTWO.name matched $dataONE, without ambigious results, and enabling me to directly work with $inputTWO.type etc.?

    Btw, my $inputTWO is approv 140k records big.

    Thanks in advance :)

    Wednesday, May 8, 2013 11:38 AM

Answers

  • I think, instead of the following:

    if ($inputTWO.name -eq $dataONE)	

    you have to build a second loop through $inputTWO within your first looping through $inputONE.

    Kind regards,

    wizend

    • Marked as answer by Teemoe Monday, May 13, 2013 6:16 AM
    Wednesday, May 8, 2013 1:02 PM

All replies

  • could you post your whole script i am not sure what you are doing with those lines
    Wednesday, May 8, 2013 12:29 PM
  • Yes, here is the important part.

    $inputONE = import-csv "C:\Tools\Scripts\PowerShell\INPUT1.csv"

    $inputTWO = import-csv "C:\Tools\Scripts\PowerShell\INPUT2.csv"

    foreach ($line in $inputONE){

    #removes the domain name from InputONE's data value, to exactly match with -eq below

    $dataONE = $line.Data -replace ".domain.com."

    #checks if $dataONE exists in $inputTWO's ".name" set

    if ($inputTWO.name -eq $dataONE) {

    #This part is checking with -match to get the whole line of $inputTWO, so i can get $inputTWO.type aswell

    $matchingCORRECT = (($inputTWO -match $dataONE) | where {$_.name -eq $dataONE})

    $inTWOType = $matchingCORRECT.type

    $inTWOdata = $matchingCORRECT.data

    }



    • Edited by Teemoe Wednesday, May 8, 2013 12:43 PM double-line
    Wednesday, May 8, 2013 12:43 PM
  • I think, instead of the following:

    if ($inputTWO.name -eq $dataONE)	

    you have to build a second loop through $inputTWO within your first looping through $inputONE.

    Kind regards,

    wizend

    • Marked as answer by Teemoe Monday, May 13, 2013 6:16 AM
    Wednesday, May 8, 2013 1:02 PM
  • If both files are formatted the same, and you're just checking for lines that match, you don't need to treat them as CSVs.  Consider:

    $file1 = Get-Content C:\Input1.csv
    $file2 = Get-Content C:\Input2.csv
    $matchedLines, $unmatchedLines = @(), @()
    
    foreach ( $line in $file2 ) {
      if ( $file1 -contains $line ) {
        $matchedLines += $line
        }
      else { $unmatchedLines += $line }
      }
    
    $matchedLines | Set-Content C:\MatchedRecords.txt
    $unmatchedLines | Set-Content C:\UnmatchedRecords.txt

    • Proposed as answer by MrPanacea Wednesday, May 8, 2013 1:48 PM
    Wednesday, May 8, 2013 1:48 PM
  • you could use compare cmdlet

    # compare the objects
    $result = compare-object -referenceobject $(import-csv c:\test1.csv) -differenceobject $(import-csv c:\test2.csv) -ExcludeDifferent -IncludeEqual
    # export the results to a new csv file
    $result | foreach {$_.inputobject} | export-csv c:\res.csv -NoTypeInformation

    • Edited by ImMax Wednesday, May 8, 2013 2:09 PM
    Wednesday, May 8, 2013 2:05 PM
  • Thanks!

    I just created a second foreach loop inside my first one and queried the record to get the types.

    foreach ($lineTWO in $inputTWO)
    				{
    				if ($dataONE -eq $linetwo.name) 
    					{
    					$inTwoName = $linetwo.name
    					$inTwoType = $linetwo.type
    					$inTwoData = $linetwo.data 
    					$inTWO = "RecordISinTWO"
    					}
    				}			

    Script runs ~20 times faster now :)

    Monday, May 13, 2013 6:20 AM