locked
Match and update records in CSV RRS feed

  • 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''

    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 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"
    
    



    \_(ツ)_/


    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


    \_(ツ)_/


    Monday, January 21, 2019 5:43 PM