Author Posts

August 14, 2018 at 3:34 pm

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

August 14, 2018 at 3:39 pm

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'?

August 14, 2018 at 4:28 pm

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.

 

August 14, 2018 at 4:43 pm

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?

August 14, 2018 at 4:46 pm

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

 

August 14, 2018 at 4:49 pm

Your 2 original files, what are the columns?

August 14, 2018 at 4:49 pm

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.

August 14, 2018 at 4:54 pm

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.

August 14, 2018 at 4:55 pm

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

 

 

 

August 14, 2018 at 4:57 pm

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.

August 14, 2018 at 4:58 pm

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

 

August 14, 2018 at 5:02 pm

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

August 14, 2018 at 5:36 pm

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


August 14, 2018 at 5:43 pm

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])
}

August 14, 2018 at 6:31 pm

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.

August 14, 2018 at 6:33 pm

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

August 14, 2018 at 6:50 pm

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.

 

August 14, 2018 at 7:15 pm

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

August 14, 2018 at 7:27 pm

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

August 14, 2018 at 8:05 pm

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.

August 14, 2018 at 9:34 pm

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? }

August 14, 2018 at 10:08 pm

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

August 14, 2018 at 10:30 pm

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

 

August 15, 2018 at 1:35 am

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!