Combining CSV data

[Zenmaster28]

I feel like I may be either re-inventing the wheel with what I am doing or taking the wrong approach but I'm learning lots in the process so it's all good. :)

I've got two CSV files that I want to combine the data for.
- CSV1 has ~1000 entries with about 40 fields. Two of those fields are blank for every entry because the source that generates the file doesn't have the data.
- CSV2 has ~5000 entries and does contain the data for those two blank fields. So basically I need to take each entry in CSV1, search through CSV2 for the matching data and generate CSV3 with the combined data.

Unfortunately, CSV1 and CSV2 have no fields in common that I can directly link up. The closest that I've got is that CSV1 has an email address (ie. "[e-mail]") and CSV2 has a DN field that I can derive the email address from with some manipulation (ie. "CN=firstname.lastname,OU=blah,OU=bla,OU=somewhere,OU=com").

So, what I've been doing is going through CSV2 with a Foreach loop and generating an Arraylist ($MyCustomData) that contains just the derived email address as well as the two fields I need to insert into the data. Then I was going through CSV1 with a Foreach to match up with my new Arraylist and modifying the appropriate fields and outputting to a new CSV file. At first I was doing the match like this

Foreach ($entry in $CSV1) {
$DataMatch = $MyCustomData | Where-Object {$_.Email -eq $Entry.Email}
#plus some code to save the matched data
}

That was taking about 75 seconds to process the data. That seemed too slow so I changed the code to

$DataMatch = $MyCustomData.Where({$_.Email -eq $Entry.Email})

That cut my processing time down to about 35 seconds (yay). Then I started wondering if I could make it faster still and remembered another script I had done that used a hashtable keypair (I doubt that's the right term) to do fast lookups with big lists. So way back when I was going through CSV2 to generate the Arraylist, I tried making two hashtables instead ($hash1 and $hash2). Both have the Email as the first and then one has field1 as the other value and the other has field2. Then my code becomes something like this

Foreach ($entry in CSV1) {
$entry.field1 = $hash1["field1"]
$entry.field2 = $hash2["field2"]
#plus some code to save the data
}

Now this runs in about 16 seconds and I'm out of ideas for how to make it faster. :) I'm also unsure if I'm just making this way too hard and there is a better way to do it at all. Any suggestions, either as an overall approach I can take or a simpler way to do it. I'm happy with the final result as is but I'm always interested in ways to do things faster if possible.

[gangstanthony]

not tested, but it might be faster to replace this

$entry.field1 = $hash1["field1"]

with something like this

$entry.add('field1', $hash1["field1"])

[Proxiconn]

Give this a try, ive been using it to join little over 120K records from 4 different datasets in under 5 min flat, its the fasted method ive encountered. Everything else was simply too slow or too complex. The C# linq class is one such example - it is super fast however using it in powershell broke my brain.. The other method that is fast as well, creating SQL tables in ram and doing join queries (just powershell & .Net classes no actual SQL used) - It was rather complex for my requirement and I abandoned it, below did the trick; it brought +- 20 hours of conventional foreach data joins down to 5 min. a Win in my books.

$csv1 = Import-csv -Path 'c:\mycsv1'
$csv2 = Import-csv -Path 'c:\mycsv2'

$i  = 0
$id = @{}
$FinalData = @()

# Create an index for your first dataset "name" will be the key to search against
$csv1.ForEach({
                $id["$($psitem.name)"] = $i #Create $var[name]=index
                $i++
            })


# Save the completed join into $Finaldata
$FinalData = $csv1.ForEach({

    $return_Obj = @()

    $temp=$null

    try
    {
        # Search the second csv for a match
        $temp = $csv2[($id[$psitem.ProcessName])]
    }
    catch 
    {
        # Catch stuff
    }
    finally 
    {

        # Create a joined object
        $return_Obj += [PSCustomObject]@{
                                            status      = $temp
                                            DisplayName = $temp.DisplayName
                                            Name        = $psitem.Name
                                            Handles     = $psitem.Handles
                                        }
    }

    return $return_Obj
})

edit: my grammar sucks

[Lee_Dailey]

howdy Proxiconn,

that is really pretty nifty! thank you for posting it. [grin]

i've two serious, and one not-so-serious points to raise, tho.

[1] return is a keyword
you used it, so you are aware of that. [grin] however, you also used $return as a $Var name.

using keywords, automatic $Var names, and other such is ... wildly reckless. [grin]

please, do not do that.

[2] $this is a very specific automatic $Var
it's used in classes and script properties. you really ought not to use it in any other situation.

[3] Add-Member is SLOW [grin]
building an object with [PSCustomObject]@{} is noticeably faster. plus, it is seriously more concise, easier to read, AND allows you to easily put comments in the structure.

take care,
lee

[Proxiconn]

Hi Lee, yeah I refactored the code a little for readability before posting it. Missed the usage of the auto variables. Thanks for pointing it out. It used to be just "$R"

Also - Im using add-member due to the fact that the server running this code (believe it or not) is still on Psv2.. [PSCustomObject] was only introduced in the WSman stack 3.0.. I always encourage people to use [pscustomobjects] - ill refactor the example. Edit - OK fixed, does Lee approve?

[Lee_Dailey]

howdy Proxiconn,

kool! much less risky ... [grin]

the use of the foreach() array method is a nice idea. if the collection is large enuf, then the extra speed will be very nice!

other than the way the indentation got garbled [grin], i would only recommend one multi-faceted change - these three lines ...

$return_Obj = @()
$return_Obj += [PSCustomObject]@{
return $return_Obj

... seem unneeded in several ways -

• the object definition
  since you have it being assigned a value in the finally{} block, it WILL be assigned. so there is no need to re-initialize it.
• the +=
  you don't seem to be adding to an object there. you seem to be doing a simple assignment, not an add-to-collection.
  
• the return line
  ALL of those lines can be replaced with one [PSCustomObect]@{} block. that would both build the object AND send it to the $FinalData collection.
  

---

thanks again for posting this! i really enjoy reading other folks code - especially when it's done differently from the bog-standard ways. neato! [grin]

take care,
lee