Need to append some data of 1 csv to another

This topic contains 6 replies, has 3 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 1 year, 8 months ago.

  • Author
    Posts
  • #27702
    Profile photo of Harshall mankar
    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
    Profile photo of Simon Wåhlin
    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
    Profile photo of Harshall mankar
    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
    Profile photo of Rob Simmers
    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
    Profile photo of Harshall mankar
    Harshall mankar
    Participant

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

  • #28006
    Profile photo of Harshall mankar
    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
    Profile photo of Rob Simmers
    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.