import/combine two csv files

Welcome Forums General PowerShell Q&A import/combine two csv files

This topic contains 23 replies, has 5 voices, and was last updated by

 
Participant
3 months ago.

  • Author
    Posts
  • #108454

    Participant
    Points: 0
    Rank: Member

    I'm in great need of assistance with a Powershell script to combine to csv files. I need to combine the second csv with all columns continuing with the first csv. Nothing special just combine the two csv's as is and in the exact order as the original files. However each csv is fairly large. I found one I was working with but some of the rows in the second csv are out of order. My Powershell experience is very limited. Thanks

  • #108455

    Participant
    Points: 59
    Rank: Member

    Can you post the first 3 or 4 rows of each file?

    Can you tell us the row count of each file?

    What do you mean 'rows out of order'?

  • #108457

    Participant
    Points: 0
    Rank: Member

    This information is from the combined csv (there are many more columns and rows from each csv however the rows will be identical in number as the scripts are pulling from the same serverlist.txt.  The reason two scripts are run for this information is there are multiple hard drives and each drive needs to be listed on the same row as the computer name.

    ComputerName LastUserUserLastLogin Manufacturer MemoryGB DriveC  FreeC
    LRT-AR005         xxxxx     8/8/2018 11:18 HP                    3.87             273.3 GB 172 GB
    AAPPSRV01         xxxxx 8/10/2018 16:05 VMware, Inc.  28                447.03 GB 347 GB
    FILESRV01          xxxxx 8/8/2018 9:03 VMware, Inc.      16
    WDS01                  xxxxx 8/13/2018 8:54 HP                       27.99           148.95 GB 91 GB
    NB-ITJT               xxxxx 8/12/2018 19:53 HP                     7.94              149.9 GB 44 GB
    TRT-LU004        Offline                                                                                124.9 GB 58 GB

    The srvr/pc pc information is from csv1, the hard drive info is from csv2

    The problem I'm having is when I combine the csv's the hard drive information is out order. The drive info for LRT-AR005 are flipped with WDS01. AAPPSRV01 is flipped with the offline pc TRT-LU004.

    I searched around for good ideas/example scripts and I found one that combined the csv's without needing to name headers and such but it causes some rows to flip.

     

    • #108460

      Participant
      Points: 68
      Rank: Member

      What are you looking for as desired output?

      ComputerName, LastUser, UserLastLogin, Manufacturer, MemoryGB, DriveC, FreeC, DriveD, FreeD, DriveE, FreeE , etc??

      Where does the data from the 2 csv files originate?  Other scripts?

  • #108463

    Participant
    Points: 0
    Rank: Member

    My apologies to include the rows, right now I'm at 157 rows this may vary from time to time.

     

  • #108466

    Participant
    Points: 68
    Rank: Member

    Your 2 original files, what are the columns?

  • #108469

    Participant
    Points: 0
    Rank: Member

    The scripts and csv are in the same location. Presently I was trying this script

    $lines1 = Get-Content "D:\scripts\audit.csv"
    $lines2 = Get-Content "D:\scripts\drive.csv"
    $destination = "D:\scripts\test.csv"

    $length = [Math]::Min($lines1.Length, $lines2.Length)
    for ($index = 0; $index -lt $length; $index++)
    {
    # Append rows
    Add-Content $destination ($lines1[$index] + "," + $lines2[$index])
    }

    It combines all columns and the number of rows but I cannot work around lines, in this script.

  • #108472

    Keymaster
    Points: 1,619
    Helping HandTeam Member
    Rank: Community Hero

    You should just be able to Import-CSV the second file and pipe it to Export-CSV using -Append, if the two have the same columns.

  • #108475

    Participant
    Points: 0
    Rank: Member

    csv1 columns

    computername,lastuser,UserLastLogin,Manufacturer,SystemType,SerialNumber,MemoryGB,ProcessorType,OS,OSVersion,IpAddress,MemroyGB,LastReboot

    CSV2 columns DRIVB,FREEB,DRIVEC,FREEC, and continues through DRIVEZ as we have up to DRIVEW presently

     

     

     

  • #108476

    Participant
    Points: 68
    Rank: Member

    So you aren't looking to append csv2 to csv1.  csv2 doesn't contain the computername?  Could it?  I'm not sure how you are building the drive.csv file.

  • #108478

    Participant
    Points: 0
    Rank: Member

    I'm trying to combine csv2 as is to the last column of csv1. When I was first tasked with this, I didn't think it would be this difficult however I'm experienced with Powershell

     

  • #108481

    Participant
    Points: 0
    Rank: Member

    This the script that was provided to me as it scans the drives

    $computerList = "D:\scripts\combined\computers.txt"
    $outputCSV = "D:\scripts\combined\RFV.csv"
    $scriptpath = $MyInvocation.MyCommand.Path
    pushd $dir

    [System.Collections.ArrayList]$sysCollection = New-Object System.Collections.ArrayList($null)

    foreach ($computer in (Get-Content $computerList))
    {
    "Collecting information from $computer"
    $totCores=0

    try
    {
    [array]$disks = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Filter DriveType=3 -Computername $computer
    [array]$disks = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Computername $computer

    $disks | foreach-object {$si."Drive$($_.Name -replace ':', ")"="$([string]([System.Math]::Round($_.Size/1gb,2))) GB"}
    $disks | foreach-object {$si."Free$($_.Name -replace ':', ")"="$([string]([System.Math]::Round($_.FreeSpace/1024/1024/1024, 0))) GB"}

    }
    catch [Exception]
    {
    "Error communicating with $computer, skipping to next"
    $si = @{
    computer          = [string]$computer
    ErrorMessage    = [string]$_.Exception.Message
    ErrorItem       = [string]$_.Exception.ItemName
    }
    Continue
    }
    finally
    {
    [void]$sysCollection.Add((New-Object PSObject -Property $si))
    }
    }

    $sysCollection `
    | select-object DriveB,FreeB,DriveC,FreeC,DriveD,FreeD,DriveE,FreeE,DriveF,FreeF,DriveG,FreeG,DriveH,FreeH,DriveI,FreeI,DriveJ,FreeJ,DriveK,FreeK,DriveL,FreeL,DriveM,FreeM,DriveN,FreeN,DriveO,FreeO,DriveP,FreeP,DriveQ,FreeQ,DriveR,FreeR,DriveS,FreeS,DriveT,FreeT,DriveU,FreeU,DriveV,FreeV,DriveW,FreeW,DriveX,FreeX,DriveY,FreeY,DriveZ,FreeZ `
    | sort -Property computer `
    | Export-CSV -path $outputCSV -NoTypeInformation

  • #108487

    Participant
    Points: 68
    Rank: Member

    I realize I probably don't have the whole story here – but there are some questionable things in the script you were given — here are some suggestions / comments:

    This should get you to the point where your drives file includes the computer name.  Once you have that, combining them the way you want becomes more reliable, rather than leaning too hard on the rows matching up from 2 separate files.  Lots of room for error there.

    $computerList = "C:\scripts\combined\computers.txt"
    $outputCSV = "C:\scripts\combined\RFV.csv"
    $scriptpath = $MyInvocation.MyCommand.Path
    pushd $dir
    
    [System.Collections.ArrayList]$sysCollection = New-Object System.Collections.ArrayList($null)
    
    foreach ($computer in (Get-Content $computerList))
    {
        "Collecting information from $computer"
        # not using this variable?
        $totCores = 0
    
        try
        {
            # not sure why you are doing this 2 times? - you are basically overwriting
            # whatever is in the first $disks DriveType=3 with all drives
            [array]$disks = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Filter DriveType=3 -Computername $computer
            [array]$disks = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Computername $computer
    
            # you were looping all of the drives 2 times - but not necessary
            foreach ($d in $disks)
            {
                # $si came out of nowhere here - not defined anywhere?
                $si."Drive$($d.Name -replace ':', '')" = "$([string]([System.Math]::Round($d.Size/1gb,2))) GB"
                $si."Free$($d.Name -replace ':', '')" = "$([string]([System.Math]::Round($d.FreeSpace/1024/1024/1024, 0))) GB"
            }
    
    
        }
        catch [Exception]
        {
            "Error communicating with $computer, skipping to next"
            $si = @{
                computer     = [string]$computer
                ErrorMessage = [string]$_.Exception.Message
                ErrorItem    = [string]$_.Exception.ItemName
            }
            Continue
        }
        finally
        {
            [void]$sysCollection.Add((New-Object PSObject -Property $si))
        }
    }
    
    
    
    
    
    # suggestion
    $sysCollection |
        select-object Computer, DriveB, FreeB, DriveC, FreeC, DriveD, FreeD, DriveE, FreeE, DriveF, FreeF, DriveG, FreeG, DriveH, FreeH, DriveI, FreeI, DriveJ, FreeJ, DriveK, FreeK, DriveL, FreeL, DriveM, FreeM, DriveN, FreeN, DriveO, FreeO, DriveP, FreeP, DriveQ, FreeQ, DriveR, FreeR, DriveS, FreeS, DriveT, FreeT, DriveU, FreeU, DriveV, FreeV, DriveW, FreeW, DriveX, FreeX, DriveY, FreeY, DriveZ, FreeZ |  # computer isn't a selected property in original so how are you sorting on it (line below)
        Sort-Object -Property computer |
        Export-CSV -path $outputCSV -NoTypeInformation
    
    
    
  • #108488

    Participant
    Points: 0
    Rank: Member

    My primary question is it possible to make this script run without manipulating the lines or rows?

    $lines1 = Get-Content "D:\scripts\audit.csv"
    $lines2 = Get-Content "D:\scripts\drive.csv"
    $destination = "D:\scripts\test.csv"

    $length = [Math]::Min($lines1.Length, $lines2.Length)
    for ($index = 0; $index -lt $length; $index++)
    {
    # Append rows
    Add-Content $destination ($lines1[$index] + "," + $lines2[$index])
    }

    • #108508

      Participant
      Points: 68
      Rank: Member

      So I posted a reply to the script that was given to you — there are some things that look a little bit off there. Most importantly, this:

      $sysCollection `
      | select-object DriveB,FreeB,DriveC,FreeC,DriveD,FreeD,DriveE,FreeE,DriveF,FreeF,DriveG,FreeG,DriveH,FreeH,DriveI,FreeI,DriveJ,FreeJ,DriveK,FreeK,DriveL,FreeL,DriveM,FreeM,DriveN,FreeN,DriveO,FreeO,DriveP,FreeP,DriveQ,FreeQ,DriveR,FreeR,DriveS,FreeS,DriveT,FreeT,DriveU,FreeU,DriveV,FreeV,DriveW,FreeW,DriveX,FreeX,DriveY,FreeY,DriveZ,FreeZ `
      | sort -Property computer `
      | Export-CSV -path $outputCSV -NoTypeInformation
      

      The script selects all of the drive* and free* properties, but not the computer property. Then, the script sorts on computer – however, it doesn't exist in the list of exported properties. I think if you add select-object computer, driveB,FreeB...... your output file will get you computername.

      Once you have that – getting to the combined file you are looking for will be much easier.

  • #108509

    Participant
    Points: 68
    Rank: Member

    Some comments / suggestions on the code you were given:

    $computerList = "C:\scripts\combined\computers.txt"
    $outputCSV = "C:\scripts\combined\RFV.csv"
    $scriptpath = $MyInvocation.MyCommand.Path
    pushd $dir
    
    [System.Collections.ArrayList]$sysCollection = New-Object System.Collections.ArrayList($null)
    
    foreach ($computer in (Get-Content $computerList))
    {
        "Collecting information from $computer"
        # not using this variable?
        $totCores = 0
    
        try
        {
            # not sure why you are doing this 2 times? - you are basically overwriting
            # whatever is in the first $disks DriveType=3 with all drives
            [array]$disks = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Filter DriveType=3 -Computername $computer
            [array]$disks = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Computername $computer
    
            # you were looping all of the drives 2 times - but not necessary
            foreach ($d in $disks)
            {
                # $si came out of nowhere here - not defined anywhere?
                $si."Drive$($d.Name -replace ':', '')" = "$([string]([System.Math]::Round($d.Size/1gb,2))) GB"
                $si."Free$($d.Name -replace ':', '')" = "$([string]([System.Math]::Round($d.FreeSpace/1024/1024/1024, 0))) GB"
            }
    
    
        }
        catch [Exception]
        {
            "Error communicating with $computer, skipping to next"
            $si = @{
                computer     = [string]$computer
                ErrorMessage = [string]$_.Exception.Message
                ErrorItem    = [string]$_.Exception.ItemName
            }
            Continue
        }
        finally
        {
            [void]$sysCollection.Add((New-Object PSObject -Property $si))
        }
    }
    
    
    
    
    
    # suggestion
    $sysCollection |
        select-object Computer, DriveB, FreeB, DriveC, FreeC, DriveD, FreeD, DriveE, FreeE, DriveF, FreeF, DriveG, FreeG, DriveH, FreeH, DriveI, FreeI, DriveJ, FreeJ, DriveK, FreeK, DriveL, FreeL, DriveM, FreeM, DriveN, FreeN, DriveO, FreeO, DriveP, FreeP, DriveQ, FreeQ, DriveR, FreeR, DriveS, FreeS, DriveT, FreeT, DriveU, FreeU, DriveV, FreeV, DriveW, FreeW, DriveX, FreeX, DriveY, FreeY, DriveZ, FreeZ |  # computer isn't a selected property in original so how are you sorting on it (line below)
        Sort-Object -Property computer |
        Export-CSV -path $outputCSV -NoTypeInformation
    
  • #108515

    Participant
    Points: 0
    Rank: Member

    This is great and thanks so much for correcting this for I was not aware. Is it possible to assist me with fixing the script I'm using to combine these two csv's. It gives me all of the data needed from csv2 but I can't stop it from manipulating the lines/rows. Is there a way I can use this script without manipulating the rows?

    $lines1 = Get-Content "D:\scripts\audit.csv"
    $lines2 = Get-Content "D:\scripts\drive.csv"
    $destination = "D:\scripts\test.csv"

    $length = [Math]::Min($lines1.Length, $lines2.Length)
    for ($index = 0; $index -lt $length; $index++)
    {
    # Append rows
    Add-Content $destination ($lines1[$index] + "," + $lines2[$index])
    }

    It combines all columns and the number of rows but I cannot work around lines, in this script.

     

  • #108518

    Participant
    Points: 68
    Rank: Member

    I think this should get you close:

    $audit = Import-Csv -Path c:\path\to\audit.csv
    $drives = Import-Csv -Path c:\path\to\drives.csv
    
    $combined = foreach($a in $audit)
    {
        $ThisComputersDrives = $drives | Where-Object{$_.computer -eq $a.computername}
        $prop = @{
    
            ComputerName = $a.computername 
            LastUser = $a.LastUser
            UserLastLogin = $a.UserLastLogin
            Manufacturer = $a.Manufacturer
            SystemType = $a.SystemType
            SerialNumber = $a.SerialNumber
            MemoryGB = $a.MemoryGB
            Processor = $a.Processor
            DriveB = $ThisComputersDrives.DriveB
            FreeB = $ThisComputersDrives.FreeB
            DriveC = $ThisComputersDrives.DriveC
            FreeC = $ThisComputersDrives.FreeC
            DriveD = $ThisComputersDrives.DriveD
            FreeD = $ThisComputersDrives.FreeD
            DriveE = $ThisComputersDrives.DriveE
            FreeE = $ThisComputersDrives.FreeE
            
        }
    
        $obj = New-Object -TypeName psobject -Property $prop 
        Write-Output $obj 
    
    }
    
    $combined | Export-Csv -Path c:\path\to\comined.csv -NoTypeInformation
    
    
  • #108527

    Participant
    Points: 0
    Rank: Member

    Thanks I will work with this one and let you know, again thanks

  • #108532

    Participant
    Points: 158
    Helping Hand
    Rank: Participant

    There are a couple things you need to combine objects.

    1. Most important. Both objects need to have a unique value that you can join on. The computername or serial number for instance
    2. The same properties for each row

    Look at this example:

    $obj1 = @()
    $obj1 += [pscustomobject]@{ComputerName='Computer1';OS='Windows Server 2016'}
    $obj1 += [pscustomobject]@{ComputerName='Computer2';OS='Windows Server 2012 R2'}
    
    $obj2 = @()
    $obj2 += [pscustomobject]@{Server='Computer1';Drive_C='200GB';Drive_C_Free='138GB'}
    $obj2 += [pscustomobject]@{Server='Computer2';Drive_C='500GB';Drive_C_Free='288GB';Drive_D='800GB';Drive_D_Free='201GB'}
    
    #Create properties for Drive A-Z
    $props = @()
    $props += 'Server'
    for($i=65;$i -le 90;$i++) {
        $props += ('DRIVE_{0}' -f ([char]$i).ToString())
        $props += ('DRIVE_{0}_FREE' -f ([char]$i).ToString())
    }
    
    
    foreach ($obj in $obj1) {
        #Find the identifier in the other object
        $otherObj = $obj2 | 
                    Select -Property $props | 
                    Where{$_.Server -eq $obj.ComputerName}
    
        if ($otherObj) {
            #Since there are alot of properties in obj2, let's use that first
            $otherObj | 
                Select *,
                       @{Name='OperatingSystem';Expression={$obj.OS}}
        }
    }
    

    If you run this code, just look at $obj2:

    PS C:\Users\Rob> $obj2
    
    Server    Drive_C Drive_C_Free
    ------    ------- ------------
    Computer1 200GB   138GB       
    Computer2 500GB   288GB   
    

    Notice that drive D is missing even though you can see we created D properties for Computer2. In order to see those properties, they need to exist in BOTH objects. You can do this using Select-Object to create properties for all objects. This gives you result like this:

    erver          : Computer1
    DRIVE_A         : 
    DRIVE_A_FREE    : 
    DRIVE_B         : 
    DRIVE_B_FREE    : 
    Drive_C         : 200GB
    Drive_C_Free    : 138GB
    DRIVE_D         : 
    DRIVE_D_FREE    : 
    DRIVE_E         : 
    DRIVE_E_FREE    : 
    DRIVE_F         : 
    DRIVE_F_FREE    : 
    DRIVE_G         : 
    DRIVE_G_FREE    : 
    DRIVE_H         : 
    DRIVE_H_FREE    : 
    DRIVE_I         : 
    DRIVE_I_FREE    : 
    DRIVE_J         : 
    DRIVE_J_FREE    : 
    DRIVE_K         : 
    DRIVE_K_FREE    : 
    DRIVE_L         : 
    DRIVE_L_FREE    : 
    DRIVE_M         : 
    DRIVE_M_FREE    : 
    DRIVE_N         : 
    DRIVE_N_FREE    : 
    DRIVE_O         : 
    DRIVE_O_FREE    : 
    DRIVE_P         : 
    DRIVE_P_FREE    : 
    DRIVE_Q         : 
    DRIVE_Q_FREE    : 
    DRIVE_R         : 
    DRIVE_R_FREE    : 
    DRIVE_S         : 
    DRIVE_S_FREE    : 
    DRIVE_T         : 
    DRIVE_T_FREE    : 
    DRIVE_U         : 
    DRIVE_U_FREE    : 
    DRIVE_V         : 
    DRIVE_V_FREE    : 
    DRIVE_W         : 
    DRIVE_W_FREE    : 
    DRIVE_X         : 
    DRIVE_X_FREE    : 
    DRIVE_Y         : 
    DRIVE_Y_FREE    : 
    DRIVE_Z         : 
    DRIVE_Z_FREE    : 
    OperatingSystem : Windows Server 2016
    
    Server          : Computer2
    DRIVE_A         : 
    DRIVE_A_FREE    : 
    DRIVE_B         : 
    DRIVE_B_FREE    : 
    Drive_C         : 500GB
    Drive_C_Free    : 288GB
    Drive_D         : 800GB
    Drive_D_Free    : 201GB
    DRIVE_E         : 
    DRIVE_E_FREE    : 
    DRIVE_F         : 
    DRIVE_F_FREE    : 
    DRIVE_G         : 
    DRIVE_G_FREE    : 
    DRIVE_H         : 
    DRIVE_H_FREE    : 
    DRIVE_I         : 
    DRIVE_I_FREE    : 
    DRIVE_J         : 
    DRIVE_J_FREE    : 
    DRIVE_K         : 
    DRIVE_K_FREE    : 
    DRIVE_L         : 
    DRIVE_L_FREE    : 
    DRIVE_M         : 
    DRIVE_M_FREE    : 
    DRIVE_N         : 
    DRIVE_N_FREE    : 
    DRIVE_O         : 
    DRIVE_O_FREE    : 
    DRIVE_P         : 
    DRIVE_P_FREE    : 
    DRIVE_Q         : 
    DRIVE_Q_FREE    : 
    DRIVE_R         : 
    DRIVE_R_FREE    : 
    DRIVE_S         : 
    DRIVE_S_FREE    : 
    DRIVE_T         : 
    DRIVE_T_FREE    : 
    DRIVE_U         : 
    DRIVE_U_FREE    : 
    DRIVE_V         : 
    DRIVE_V_FREE    : 
    DRIVE_W         : 
    DRIVE_W_FREE    : 
    DRIVE_X         : 
    DRIVE_X_FREE    : 
    DRIVE_Y         : 
    DRIVE_Y_FREE    : 
    DRIVE_Z         : 
    DRIVE_Z_FREE    : 
    OperatingSystem : Windows Server 2012 R2
    

    There are many ways to do things. You could also have a Drives property with all of the drives a system. You could also use a Join-Object function (there are several iterations) to Join on the computername. The key is you need to have a property in both objects to do the join.

  • #108547

    Participant
    Points: 0
    Rank: Member

    Sorry I was pulled away on another instance, thank you both Rob and Stephen. I worked with both scripts and they perform well however the format is not what I need unfortunately. However I have this script that combines/joins the two csv files line by line but it has a function flips 2 lines  after a given number of lines are counted. My question: Is it possible to modify this script so that it doesn't manipulate any lines.  A script that would just combine these to csv files line for line.

    $lines1 = Get-Content "D:\scripts\audit.csv"
    $lines2 = Get-Content "D:\scripts\drive.csv"
    $destination = "D:\scripts\test.csv"

    $length = [Math]::Min($lines1.Length, $lines2.Length) <–flagging? for ($index = 0; $index -lt $length; $index++) { # Append rows Add-Content $destination ($lines1[$index] + "," + $lines2[$index]) <–flagging? }

  • #108551

    Participant
    Points: 68
    Rank: Member

    James,

    Would it be possible to post examples of the 2 input files – with 3 or 4 dummy data entries as well as a desired output file with that data?

    I think I am not fully understanding your requirements with the "function flip" etc

  • #108556

    Participant
    Points: 0
    Rank: Member

    Is it possible to attach a sample of the files for the columns on each are very long. The output on this page doesn't allow it to look normal

     

  • #108562

    Participant
    Points: 0
    Rank: Member

    After look at the examples you provided me my issue was with my script that was scanning the drives. I added the values ComputerName as suggested by Rob and ran the scripts and all data and lines are matching. Thanks for your time and advice to get me on the right track!

You must be logged in to reply to this topic.