Asked by:
Match and update records in CSV

Question
-
Hello,
CSV2 (see below) needs to be updated for UUID and Asset ID by matching with product code from CSV1.
Unique UUID and asset ID must be updated for matched product code only depending on how many UUID and Asset ID available for each product code.
For example, CSV1 has two UUIDs available for product code HP1023 whereas CSV2 has four records in it. in that case, only two records should be updated and other should left blank.
CSV1:
Code,UUID,Asset HP1023,MNY07789-D112-E34C-B41D-4ACFB442B60P,23160 HP1023,ABC07789-D112-E34C-B41D-4ACFB442B65D,23145 HP2190,XYZ07789-D112-E34C-B41D-4ACFB442B65E,23146
CSV2:
Code,UUID,Asset Tag HP2190,, HP1023,, HP1023,, HP1023,, DL1721,, HP2190,, HP2190,, HP1023,,
Below is what I have so far...this just updates all matching records which isnt good...I need to update equal number of records against matching product codes...
function Update-SysDta { $csv1 = Import-Csv '.\001.csv' $csv2 = Import-Csv '.\002.csv' foreach ($record in $csv2) { $sysUID = ($csv1 | where {$_.code -eq $record.code}).UUID $record.UUID = $sysUID $SysAT = ($csv1 | where {$_.code -eq $record.code}).Asset $record.'Asset Tag' = $SysAT } return ,$csv2 } Update-SysDta
Monday, January 21, 2019 1:51 PM
All replies
-
https://powershell.org/forums/topic/update-csv-records-from-another-csv/
https://stackoverflow.com/questions/54264708/match-and-update-csv-records
https://stackoverflow.com/questions/54236818/update-csv-column-using-powershell
You might start to learn Powershell to be able to understand the help you get and to be able to "transfer" your knowledge to similar problems or tasks.
Live long and prosper!
(79,108,97,102|%{[char]$_})-join''
- Proposed as answer by LeeSeenLiMicrosoft contingent staff Tuesday, January 22, 2019 3:19 AM
Monday, January 21, 2019 1:55 PM -
https://powershell.org/forums/topic/update-csv-records-from-another-csv/
https://stackoverflow.com/questions/54264708/match-and-update-csv-records
https://stackoverflow.com/questions/54236818/update-csv-column-using-powershell
You might start to learn Powershell to be able to understand the help you get and to be able to "transfer" your knowledge to similar problems or tasks.
Live long and prosper!
(79,108,97,102|%{[char]$_})-join''
Thanks for you advise.
Yes, I'm beginner to Powershell and learning it day-by-day.
I agree, I have posted this query on multiple forums seeking help from 'expert' like you.
I would have been happy, if you have routed me to appropriate documents/reference to 'learn' it..
Monday, January 21, 2019 2:23 PM -
Here is a resource for learning PowerShell:
- Microsoft Virtual Academy - Getting Started with Microsoft PowerShell
- PowerShell Documentation
- PowerShell Style Guidelines
\_(ツ)_/
Monday, January 21, 2019 5:36 PM -
Here is a simple general purpose method for joining 2 CSVs on a single field.
# # Easily join multiple CSV tables - VERY FAST # Requires ACE drivers which can be installed form here: # https://www.microsoft.com/en-us/download/details.aspx?id=13255 Function Get-CsvReader { Param ( $csvPath=$pwd, [Parameter(Mandatory)] $CommandText ) $tmpl = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Text;HDR=Yes;FORMAT=Delimited"' $connStr = $tmpl -f $csvPath Write-Verbose $connStr $csvConnection = New-Object System.Data.OleDb.OleDbConnection($connStr) $csvConnection.Open() $cmd = $csvConnection.CreateCommand() $cmd.CommandText = $CommandText $dt = New-Object System.Data.DataTable $rdr = $cmd.ExecuteReader() $dt.Load($rdr) $rdr.Close() $dt } # Test code $csvPath = 'd:\scripts' $sql = @' SELECT F1.ID as ID, F1.Name as Name, F1.Status as Status1, F2.Status as Status2 FROM [test1.csv] as F1, [test2.csv] As F2 WHERE F1.ID = F2.ID '@ Get-CsvReader -csvPath $csvPath -CommandText $sql
# test data # TEST1,CSV "id","Name","Status" 1,"AdobeARMservice","OK" 2,"AJRouter","OK" 3,"ALG","ERROR" 4,"AppHostSvc","OK" # TEST2.CSV "id","Name","Status" 1,"AdobeARMservice","OK" 2,"AJRouter","OK" 3,"ALG","OK" 4,"AppHostSvc","OK"
\_(ツ)_/
- Edited by jrv Monday, January 21, 2019 5:41 PM
- Proposed as answer by LeeSeenLiMicrosoft contingent staff Tuesday, January 22, 2019 3:20 AM
Monday, January 21, 2019 5:40 PM -
Second method does not require ACE drivers.
#<# #Here is an an example of joining two tables in a DataViewRelation with disconnected data. # (of couse this is much easier with Linq} ##> #region Control Helper Functions function ConvertTo-DataTable { [OutputType([System.Data.DataTable])] param ( [ValidateNotNull()] $InputObject, [ValidateNotNull()] [System.Data.DataTable]$Table, [switch]$RetainColumns, [switch]$FilterWMIProperties) if ($null -eq $Table) { $Table = New-Object System.Data.DataTable } if ($InputObject -is [System.Data.DataTable]) { $Table = $InputObject } elseif ($InputObject -is [System.Data.DataSet] -and $InputObject.Tables.Count -gt 0) { $Table = $InputObject.Tables[0] } else { if (-not $RetainColumns -or $Table.Columns.Count -eq 0) { #Clear out the Table Contents $Table.Clear() if ($null -eq $InputObject) { return } #Empty Data $object = $null #find the first non null value foreach ($item in $InputObject) { if ($null -ne $item) { $object = $item break } } if ($null -eq $object) { return } #All null then empty #Get all the properties in order to create the columns foreach ($prop in $object.PSObject.Get_Properties()) { if (-not $FilterWMIProperties -or -not $prop.Name.StartsWith('__')) #filter out WMI properties { #Get the type from the Definition string $type = $null if ($null -ne $prop.Value) { try { $type = $prop.Value.GetType() } catch { Out-Null } } if ($null -ne $type) # -and [System.Type]::GetTypeCode($type) -ne 'Object') { [void]$table.Columns.Add($prop.Name, $type) } else #Type info not found { [void]$table.Columns.Add($prop.Name) } } } if ($object -is [System.Data.DataRow]) { foreach ($item in $InputObject) { $Table.Rows.Add($item) } return @( ,$Table) } } else { $Table.Rows.Clear() } foreach ($item in $InputObject) { $row = $table.NewRow() if ($item) { foreach ($prop in $item.PSObject.Get_Properties()) { if ($table.Columns.Contains($prop.Name)) { $row.Item($prop.Name) = $prop.Value } } } [void]$table.Rows.Add($row) } } return @( ,$Table) } #endregion # This method creates a LEFT OUTER JOIN T1(primarykey) += T2(primarykey) $csv1 = Import-Csv testjoin1.csv $dt1 = ConvertTo-DataTable $csv1 $dt1.PrimaryKey = $dt1.Columns[0] $csv2 = Import-Csv testjoin2.csv $dt2 = ConvertTo-DataTable $csv2 $dt2.PrimaryKey = $dt2.Columns[0] $dt3 = [System.Data.DataTable]::new() $dt3.Merge($dt1) $dt3.Merge($dt2) $dt3
# testjoin1.csv ID,Name,Status 1,joe,enabled 2,sam,enabled 3,chris,enabled 4,susan,disabled #> # testjoin2.csv ID,Phone 1,111-111-1111 2,222-222-2222 3,333-333-3333 # results ID Name Status Phone -- ---- ------ ----- 1 joe enabled 111-111-1111 2 sam enabled 222-222-2222 3 chris enabled 333-333-3333 4 susan disabled
\_(ツ)_/
- Edited by jrv Monday, January 21, 2019 5:43 PM
- Proposed as answer by LeeSeenLiMicrosoft contingent staff Tuesday, January 22, 2019 3:20 AM
Monday, January 21, 2019 5:43 PM