Need to append some data of 1 csv to another

This topic contains 6 replies, has 3 voices, and was last updated by  Rob Simmers 2 years, 4 months ago.

  • Author
    Posts
  • #27702

    Harshall mankar
    Participant

    I want to read 2 csv file in 1 I have some data which I want to append to another file using powershell how can I do it

  • #27706

    Simon Wåhlin
    Participant

    It depends on the data in the two files.
    If they both have the same columns you can give more than one path to Import-CSV like this:
    [pre]Import-Csv -Path '.\FirstFile.csv', 'SecondFile.csv' | Export-Csv -Path .\Export.csv -NoTypeInformation[/pre]

    If you want to join files with different columns in it gets a bit trickier. I searched the web for "powershell join csv files" and this was the first result: http://www.powershelladmin.com/wiki/An_Advanced_Cmdlet_to_Merge_Csv_Files_in_PowerShell

  • #27709

    Harshall mankar
    Participant

    @simon that was helpful but I want to merge two file which do not have any thing in common I have 1 file which have machine details n other 1 is some report of the same machine
    So I want these machine details to be appended on 2nd file
    but only in top row no repetitions

    Please consider that scenario

  • #27715

    Rob Simmers
    Participant

    It sounds like you are trying to create a report. Take a look at the free eBook under Resources > Free eBook > Creating HTML Reports in PowerShell . You might also want to look at the "Ditch Excel" eBook as well.

  • #27754

    Harshall mankar
    Participant

    I saw it ROB But my requirement is only csv file can you help on that
    please if you can

  • #28006

    Harshall mankar
    Participant

    $subid=(Get-AzureSubscription -Current).SubscriptionId
    $subscriptionname= (Get-AzureSubscription -Current).SubscriptionName
    Select-AzureSubscription $subscriptionname

    $storagename ="pchealth"
    $clustername= "pchealth"

    $key1=(Get-AzureStorageKey $storagename).Primary
    $context=New-AzureStorageContext -StorageAccountName $storagename -StorageAccountKey $key1

    $containername = "pchealth"

    Get-AzureStorageBlob -Context $context -Container $containername

    $FolderWithComputerName = (Get-Item -Path D:\* | Where-Object{$_.PSIsContainer}|sort LastWriteTime -Descending | select -First 1).Name

    $FolderName = (Get-ChildItem -Path "D:\$FolderWithComputerName" |Where-object {$_.PSIsContainer} |sort LastWriteTime -Descending| select -First 1).Name

    $FileList1 = Get-ChildItem D:\$FolderWithComputerName\$FolderName | sort LastWriteTime | select -Last 11

    $MakeFileName = (Get-Childitem -Path "D:\$FolderWithComputerName" |sort LastWriteTime -Descending|Select -Last 1| Where-Object {$_.Name -match "Make"}).Name

    For($i=0;$i -lt $FileList1.count;$i++)
    {
    $filetoupdate=$FileList1[$i].name
    $csv = Import-Csv -Path D:\$FolderWithComputerName\$MakeFileName
    $columns = $csv | Get-Member -MemberType NoteProperty

    foreach( $c in $columns )
    {
    foreach( $row in $csv )
    {
    $newcsv = Import-Csv D:\$FolderWithComputerName\$FolderName\$filetoupdate
    $newCsv| Add-Member -MemberType NoteProperty -Name $c.Name -Value $row.$( $c.Name ) -force
    $newCsv | Export-Csv -Path D:\$FolderWithComputerName\$FolderName\$filetoupdate
    }
    }
    }

    This is the solution

  • #28009

    Rob Simmers
    Participant

    Looking through what you are trying to accomplish, I still see a lot of potential issues like if another file has multiple rows and another does not, but for proof of concept, here is how I tested this:

    #CSV's in "source" directory had the following format in Dir 1

    "ComputerName","User"
    "Computer1", "Jim"
    

    #Dir2 had two files with two different data sets

    #foo1
    "Deparment","EmpID"
    "Marketing", "1234"
    
    #foo2
    "MoreData","SomeMoreData"
    "blah", "foo"
    

    I processed these files with the following code:

    #Get CSV's in source directory
    $dir1 = Get-ChildItem -Path C:\Temp\Dir1\* -Include *.csv -File
    #Get CSV's that will append to source files
    $dir2 = Get-ChildItem -Path C:\Temp\Dir2\* -Include *.csv -File
    
    #for each file in the source...
    foreach ($file in $dir1) {
        #Import the CSV
        $dir1CSV = Import-CSV -Path $file.FullName
        #for each file in directory that will append to source
        foreach ($csv in $dir2) {
            #Import the file that will be appended
            $dir2Csv = Import-CSV -Path $csv.FullName
            #for each property in that file
            foreach($prop in $dir2Csv.PSObject.Properties) { 
                #Append the properties to the source data
                $dir1CSV | Add-Member -MemberType NoteProperty -Name $prop.Name -Value $prop.Value -Force
            }
        }
         $dir1CSV | Format-Table -AutoSize
         #$dir1CSV | Export-CSV C:\TEmp\NewFIleFormat.csv -NoTypeInformation
    }
    

    which resulted in:

    ComputerName User Deparment EmpID MoreData SomeMoreData
    ------------ ---- --------- ----- -------- ------------
    Computer1    Jim  Marketing 1234  blah     foo         
    
    
    
    ComputerName User  Deparment EmpID MoreData SomeMoreData
    ------------ ----  --------- ----- -------- ------------
    Computer5    Sally Marketing 1234  blah     foo         
    
    

You must be logged in to reply to this topic.