Author Posts

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

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

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.

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?

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

Your 2 original files, what are the columns?

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

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.

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


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. 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  Thanks I will work with this one and let you know, again thanks 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.

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

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

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

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!