PowerShell - Formatting SQL output and writing it to CSV

Question

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

Both are equivalent as far as CSV is concerned. And how would you treat embedded quotes or commas? — Joey
– Joey, Commented Jul 24, 2012 at 9:20

Chad Miller · Accepted Answer · 2012-07-24 12:40:25Z

2

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 '"'}

answered Jul 24, 2012 at 12:40

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PowerShell - Formatting SQL output and writing it to CSV

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related