Match and update records in CSV

All replies

• 

  

  1

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  Here is a resource for learning PowerShell:

  1. Microsoft Virtual Academy - Getting Started with Microsoft PowerShell
  2. PowerShell Documentation
  3. PowerShell Style Guidelines

  ---

  \_(ツ)_/

  Monday, January 21, 2019 5:36 PM
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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