CODEWITHSUNDEEP
powershellcsv
boomcubist
boomcubist

Reputation: 881

Take a line from one csv file and split it into columns for another csv file using powershell

I have a csv file that contains lines that I'd like to extract and add as columns to a separate csv file.

This is an example of the contents of the first csv file.

enter image description here

I want to take everything between the third comma and semi-colon for each one and add them as columns to this csv file.

enter image description here

My desired output is this. Because there may be multiple servers in the first csv file I'd like to export a csv for each server but I can figure that bit out.

enter image description here

I've been playing around with the following but I can't figure it out.

Import-CSV $CsvTargetPath | ForEach-Object {
$newData = $_

Import-CSV $CsvSourcePath | ForEach-Object {
    $_.AdditionalDisks.split(";") | ForEach-Object {
        $newRecordProperties = [ordered]@{
            $DriveLetter = $_.split(",")[3]
            $Label = $_.split(",")[4]
            $Filesystem = $_.split(",")[5]   
        }
        $newRecord = new-object psobject -Property $newRecordProperties
        Write-Output $newRecord
    }

    $CsvTargetPath | Add-Member -MemberType NoteProperty -Name DriveLetter -Value $DriveLetter -PassThru
    $CsvTargetPath | Add-Member -MemberType NoteProperty -Name Label -Value $Label -PassThru
    $CsvTargetPath | Add-Member -MemberType NoteProperty -Name Filesystem -Value $Filesystem -PassThru
}      
} | Export-CSV $CsvTargetPath -NoTypeInformation

Here is the contents of the first csv in plain text.

VmName  AdditionalDisks
Server01    90,0,thick,b,sql backup,refs;110,1,thick,d,sql data,refs;60,2,thick,f,sql transaction log,refs;50,3,thin,l,sql audit,refs;30,0,thick,t,sql tempdb data,refs
Server02    90,0,thick,b,sql backup,refs;110,1,thick,d,sql data,refs;60,2,thick,f,sql transaction log,refs;50,3,thin,l,sql audit,refs;30,0,thick,t,sql tempdb data,refs

Upvotes: 0

Views: 57

Answers (2)

Theo
Theo

Reputation: 61148

Assuming the second csv file can also hold info for multiple servers (how else would you know the Uuid for each disk?), you could do like below

$additionalDiskInfo = Import-Csv -Path 'D:\Test\file1.csv'
$serverInfo         = Import-Csv -Path 'D:\Test\file2.csv'

$additionalDiskInfo | ForEach-Object {
    $server      = $_.VmName
    $targetItems = $serverInfo | Where-Object { $_.VmName -eq $server }
    $extraDisks  = $_.AdditionalDisks -split ';'
    # make sure you don't run into index errors
    $maxItems = [math]::Min($targetItems.Count, $extraDisks.Count)
    # loop through the arrays and output combined objects
    $result = for ($i = 0; $i -lt $maxItems; $i++) {
        $n1, $n2, $n3, $driveLetter, $label, $fileSystem = $extraDisks[$i] -split ','
        $targetItems[$i] | Select-Object *, 
                                         @{Name = 'DriveLetter'; Expression = {$driveLetter}},
                                         @{Name = 'Label'; Expression = {$label}},
                                         @{Name = 'FileSystem'; Expression = {$fileSystem}}
    } 
    # now write a new csv file for this server
    $result | Export-Csv -Path ('D:\Test\DiskInfo-{0}.csv' -f $server) -NoTypeInformation
}

The resulting csv files would look like this

"VMName","Harddisk","Uuid","DriveLetter","Label","FileSystem"
"Server01","Hard disk 2","600C293-7e48-3a63-fb02-cd44df98fe79","b","sql backup","refs"
"Server01","Hard disk 3","600C293-7e48-3a63-fb02-cd44df98f454","d","sql data","refs"
"Server01","Hard disk 4","600C293-7e48-3a63-fb02-cd44df98f10a","f","sql transaction log","refs"
"Server01","Hard disk 5","600C293-7e48-3a63-fb02-cd44df98f483","l","sql audit","refs"
"Server01","Hard disk 6","600C293-7e48-3a63-fb02-cd44df98fced","t","sql tempdb data","refs"

Upvotes: 1

wasif
wasif

Reputation: 15488

Try like this:

$csv=Import-Csv $CsvSourcePath
$csv|%{
[pscustomobject][ordered]@{
DriveLetter=($_.AdditionalDisks.Split(","))[3]
Label=($_.AdditionalDisks.Split(","))[4]
FileSystem=($_.AdditionalDisks.Split(","))[5]
}}|export-csv $CsvTargetPath -append

Upvotes: 0

Related Questions