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) 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.
- Most important. Both objects need to have a unique value that you can join on. The computername or serial number for instance
- 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.