CODEWITHSUNDEEP
sqlpowershellcsvformatting
t1red
t1red

Reputation: 175

PowerShell - Formatting SQL output and writing it to CSV

I am kind of stuck with my problem and after searching the Web for a few hours I still didn't find a solution to it.

I am trying to do the following: Reading Information in PowerShell from several inner joined SQL Tables and saving this information in a DataSet. This is what I am still able to do:

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

Then export it to CSV like this:

$DataSet.Tables[0] | Export-Csv -Delimiter "," C:\test.csv -Encoding "unicode"

Now here comes the Problem.

My CSV looks something like this:

Servername,"Administrator","Description"
SRV01,"Admin1","Description SRV01"
SRV01,"Admin2","Description SRV01"
SRV01,"Admin3","Description SRV01"
SRV02,"Admin1","Description SRV02"
SRV02,"Admin2","Description SRV02"

My goal is to get a CSV that looks like this:

Servername,Administrator,Description
SRV01,Admin1 Admin2 Admin3,Description SRV01
SRV02,Admin1 Admin2,Description SRV02

How do I manage to get that?

EDIT:

Ok got it to look like this:

Servername,Administrator,Description
SRV01,Admin1,Description SRV01
SRV01,Admin2,Description SRV01
SRV01,Admin3,Description SRV01
SRV02,Admin1,Description SRV02
SRV02,Admin2,Description SRV02

Has anyone got an idea how i can transform it into this syntax:

Servername,Administrator,Description
SRV01,Admin1 Admin2 Admin3,Description SRV01
SRV02,Admin1 Admin2,Description SRV02

Upvotes: 3

Views: 2891

Answers (1)

Chad Miller
Chad Miller

Reputation: 41847

Try this:

$csv = @"
Servername,Administrator,Description
SRV01,Admin1,Description SRV01
SRV01,Admin2,Description SRV01
SRV01,Admin3,Description SRV01
SRV02,Admin1,Description SRV02
SRV02,Admin2,Description SRV02
"@

$group = ConvertFrom-Csv $csv | Group-Object -Property Servername,Description

$group | select @{n='Name';e={ ($_.Name -split ",")[0] }}, @{n='Administrators';e={ ($_.Group | select -expandproperty Administrator) -join ","}},  @{n='Description';e={ ($_.Name -split ",")[1] }} |
 convertto-csv -NoTypeInformation | Foreach-Object  -begin { $start=$true }  -process { if ($start) { $start=$false } else { $_ }  } | foreach {$_ -replace '"'}

Upvotes: 2

Related Questions