none
Im a powershell noob. please help :) RRS feed

  • 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,987654321

    22222,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