Want to remove white space returned from a SQL query via Powershell

Question

I want to retrieve all users from our HP ALM QC installation and have a Powershell script that does this. The problem is that it pads the output with spaces to, I assume, make it more readable.

This is the script

$ServerInstance = "qc-server "
$Database = "qcsiteadmin_db "
$ConnectionTimeout = 30
$Query = "select USER_NAME, FULL_NAME, EMAIL from [td].USERS"
$QueryTimeout = 120

$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

This script gives me the following output:

USER_NAME                               FULL_NAME                               EMAIL                                 
---------                               ---------                               -----                                 
user01                                  First Last                              user01@foo.com           
user02                                  First Last                              user02@foo.com             
user03                                  First Last                              user03@foo.com

Nicely tabulated data with lots of spaces, which makes import into Excel rather daunting...

I then tried to change my SQL-query into something that would be better suited, i.e., I tried to create a semicolon separated output since that lends itself to import rather splendidly.

$Query = "select USER_NAME + ';' + FULL_NAME + ';' + EMAIL from [td].USERS"

This did almost work, in that I got the following output:

Column1
-------

user01;First Last;user01@foo.com
user02;First Last;user02@foo.com
user03;First Last;user03@foo.com

What doesn't show is that all lines are padded at the end with spaces all the way up to position 119 - that is a lot of spaces...

So, my question at last: how can I get rid of all the spaces? Can I do it in the script above or do I need another script to use on the output of the first?

score 0 · Answer 1 · answered Jun 13 '13 at 13:54

0

Try exporting directly to a csv file:

$ds.Tables | Export-Csv file.csv

answered Jun 13 '13 at 13:54

Shay Levy

121,444
32
184
206

This gave me an output like this: "#TYPE System.Data.DataTable "CaseSensitive","IsInitialized","RemotingFormat","ChildRelations","Columns","Constraints","DataSet","DefaultView","DisplayExpression","ExtendedProperties","HasErrors","Locale","MinimumCapacity",... None of the table data showed, I'm sad to say. – koenig Jun 13 '13 at 14:08
According to your code you got a 3 column output with $ds.Tables. Does this gets you the same result? $ds.Tables | select USER_NAME,FULL_NAME,EMAIL – Shay Levy Jun 13 '13 at 14:59

score 0 · Answer 2 · answered Jun 13 '13 at 16:19

Tell the database to trim off the extra space (sounds like the fields are char(120) instead of varchar).

$Query = "select rtrim(USER_NAME) as USER_NAME, rtrim(FULL_NAME) as FULL_NAME, rtrim(EMAIL) as FULL_NAME from [td].USERS"

Then to export to CSV cleanly for Excel:

$ds.Tables | export-csv FILENAME -notypeinformation

Want to remove white space returned from a SQL query via Powershell

2 Answers2