CODEWITHSUNDEEP
powershellcsv
Cameron
Cameron

Reputation: 171

Edit one .CSV using Information from Another

I have two .csv files, one with a listing of employee ID's and a department identification number, and another with a listing of all equipment registered to them. The two files share the employee ID field, and I would like to take the department number from the first file and add it to each piece of the corresponding employee's equipment in the second file (or possibly output a third file with the joined information if that is the most expedient method). So far I have pulled the information I need from the first file and am storing it in a hash table, which I believe I should be able to use to compare to the other file, but I'm not sure exactly how to go about that. The other questions I have found on the site that may be related seem to be exclusively about checking for duplicates/changes between the two files. Any help would be much appreciated. Here is the code I have for creating the hashtable:

Import-Csv "filepath\filename.csv"|ForEach-Object -Begin{
    $ids = @{}
} -Process {
    $ids.Add($_.UserID,$_.'Cost Center')}

Edit:

Here is a sample of data:

First CSV:

UserID | Legal Name | Department
---------------------------------
XXX123| Namey Mcnamera | 1234

XXX321| Chet Manley | 4321

XXX000| Ron Burgundy | 9999

Second CSV:

Barcode | User ID | Department
--------------------------------

000000000000 | xxx123 | 0000
111111111111 | xxx123 | 0000
222222222222 | xxx123 | 0000
333333333333 | xxx321 | 0000
444444444444 | xxx321 | 0000
555555555555 | xxx000 | 0000

The second csv also has several more columns of data, but these three are the only ones I care about.

Edit 2:

Using this code from @wOxxOm (edited to add -force parameters as was receiving an error when attempting to write to department column due to an entry already existing):

$csv1 = Import-Csv "filename.csv"
$csv2 = Import-CSV "filename.csv"

$indexKey = 'UserID'
$index1 = @{}; foreach($row in $csv1){$index1[$row.$indexKey] = $row.'department'}

$copyfield = 'department'
foreach($row in $csv2){
    if ($matched = $index1[$row.'User ID']){
        Add-Member @{$copyField = $matched.$copyfield} -InputObject $row -Force
     }
}

export-csv 'filepath.csv' -NoTypeInformation -Encoding UTF8 -InputObject $csv2 -Force

outputs the following information:

Count   Length  LongLength  Rank    SyncRoot    IsReadOnly  IsFixedSize IsSynchronized
48  48  48  1   System.Object[] FALSE   TRUE    FALSE

EDIT 3:

Got everything worked out with help from @Ross Lyons. Working code is as follows:

#First Spreadsheet
$users = Import-Csv "filepath.csv" 

#Asset Listing
$assets = Import-Csv "filepath.csv" 

[System.Array]$data = ""

#iterating through each row in first spreadsheet
foreach ($user in $users) {
      #iterating through each row in the second spreadsheet
      foreach ($asset in $assets) {
        #compare user ID's in each spreadsheet
        if ($user.UserID -eq $asset.'User ID'){
            #if it matches up, copy the department data, user ID and barcode from appropriate spreadsheets
            $data += $user.UserID + "," + $user."Department" + "," + $asset."Barcode" + ","
            }
        }

}
$data | Format-Table | Out-File "exportedData.csv" -encoding ascii -Force

Upvotes: 0

Views: 490

Answers (2)

Ross Lyons
Ross Lyons

Reputation: 171

Ok first, be gentle please, I'm still learning myself! Let me know if the following works or if anything is glaringly obviously wrong...

#this is your first spreadhseet with usernames & department numbers
$users = Import-Csv "spreadsheet1.csv"

#this is your second spreadsheet with equipment info & user ID's, but no department numbers
$assets = Import-Csv "spreadsheet2.csv"

#set a variable for your export data to null, so we can use it later
$export = ""

#iterating through each row in first spreadsheet
foreach ($user in $users) {
      #iterating through each row in the second spreadsheet
      foreach ($asset in $assets) {
        #compare user ID's in each spreadsheet
        if ($user.UserID -like $asset.'User ID')
            #if it matches up, copy the department data, user ID and barcode from appropriate spreadsheets
            $data = "$user.UserID" + "," + "$user.Department" + "," + "$asset.barcode" + "," + "~"

            #splits the data based on the "~" that we stuck in at the end of the string
            $export = $data -split "~" | Out-File "exportedData.csv" -Encoding ascii
            }

}

Let me know what you think. Yes, I know this is probably not the best or most efficient way of doing it, but I think it will get the job done.

If this doesn't work, let me know and I'll have another crack at it.

Upvotes: 1

woxxom
woxxom

Reputation: 73616

The hashtable key should be the common field, its value should be the entire row which you can simply access later as $hashtable[$key]:

$csv1 = Import-Csv 'r:\1.csv'
$csv2 = Import-Csv 'r:\2.csv'

# build the index    
$indexKey = 'employee ID'
$index1 = @{}; foreach ($row in $csv1) { $index1[$row.$indexKey] = $row }

# use the index
$copyField = 'department number'
foreach ($row in $csv2) {
    if ($matched = $index1[$row.$indexKey]) {
        Add-Member @{$copyField = $matched.$copyField} -InputObject $row
    }
}

Export-Csv 'r:\merged.csv' -NoTypeInformation -Encoding UTF8 -InputObject $csv2

The code doesn't use pipelines for overall speedup.

Upvotes: 1

Related Questions