Asked by:
Im a powershell noob. please help :)

Question
-
I want to merge these 2 documents by combining a column that has data that matches in their respective columns. They might not completely match and for those that dont, i want to keep that data blank and keep everything.
Example1,csv
StudentID,StudentFirstName,StudentStateID 11111,John
22222,Smith
33333,Mary,
44444,Steve
Example2.csv
StudentID,StudentStateID
44444,765432198
11111,987654321
33333,
22222,876543219
Merged.CSV
StudentID,StudentFirstName,StudentStateID
11111,John,98765432122222,Smith,876543219
33333,Mary,
44444,Steve,765432198
i know this doesnt work but if you can understand the logic of this really bad attempt, here is what i tried and obviously failed:
$csv1 = import-csv $env:USERPROFILE\Desktop\example2.csv
import-csv $env:USERPROFILE\Desktop\example1.csv|
ForEach-Object {if ($_.'STUDENT ID' -match $csv1.'STUDENT ID') {$_.'STATE STUDENT ID' = $csv1.'STATE STUDENT ID'}$_} |
Export-Csv -path $env:USERPROFILE\Desktop\result.csv -NoTypeInformation
- Edited by tregrgfd Thursday, January 17, 2019 8:01 PM
- Moved by Bill_Stewart Friday, March 15, 2019 6:08 PM This is not "scripts on demand"
Thursday, January 17, 2019 7:59 PM
All replies
-
If you search you will find many different methods for "joining" two CSV files of dissimilar structure.
My favorite is this one:
# # Easily join multiope 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" #>
TO save the new data:
$dt | Export-Csv ….
\_(ツ)_/
- Edited by jrv Thursday, January 17, 2019 8:06 PM
Thursday, January 17, 2019 8:05 PM -
Here is a second method that 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} ##>
# This method creates a LEFT OUTER JOIN T1(primarykey) += T2(primarykey)
#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 $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
$ds3 | Export-Csv ….
Test CSV files:
<# 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 Thursday, January 17, 2019 8:44 PM
Thursday, January 17, 2019 8:10 PM -
This is way above me. I said i was a noob and crazy scripts like that i cant comprehend. Maybe this is above my paygrade.Thursday, January 17, 2019 8:37 PM
-
This is way above me. I said i was a noob and crazy scripts like that i cant comprehend. Maybe this is above my paygrade.
Maybe its time to learn PowerShell. The code is very explicit and quite easy to use.
\_(ツ)_/
Thursday, January 17, 2019 8:47 PM