asked on

Question About Data Returned from a SQL Query

I am returning rows from an invoke-sqlcmd cmdlet. From a previous question I had, I was provided the following for 'extracting' the data into a file:
$accounts | % {"{0, 26}" -f $_[0]} | Add-Content -Path $outTextFile
When I use that now, my output is right-justified. I want it left-justified and trailing spaces trimmed. I suppose some day I am going to have to sit down and learn everything there is about PowerShell :-), but until that day, I rely on Google first and EE second. This one I'm not able to find any suggestions on.
$accounts contains one column named [Data]

Also, can someone explain to me exactly what % {"{0, 26}" -f $_[0]} is doing? Like I said, I asked a question, got this answer and it worked for that problem. I should have asked at that time, but didn't.

D B

ASKER

Okay, found part of it. Changed 50 to -50 and now it is left-aligned.
How about trimming?

David Johnson, CD

is the string "ABCDE" or "ABCDE " ?
"{0, 26} = variable 0, minimum width 26 right justified

D B

ASKER

David,

Does it matter (not trying to be snotty, really want to know)?
If there is trailing space I want it trimmed.

David Johnson, CD

if you don't want spaces why are using a fixed minimum length for the output?

if you have $variable then $variable.trim() will remove leading/trailing spaces.
% {"{0, 26}" -f $_[0]}
% expands to foreach-object
"{0,26}" -f format variable $_[0] right justified minimum 26 characters
$_[0] first element of an object

My opinion is that making the code as compact as possible reduces readability and maintainability. which you you rather maintain

import-csv -file abc.csv |  % {"{0, 26}" -f $_[0]} {
# do something here
}

Open in new window

$data = import-csv -file abc.csv | select name,street,city,state,country
#$data.name = "{0, 26}" -f $data.name #untested and don't know why
$data.name.trim()
foreach ($name in $data.name) {
     # do something here
     }

Open in new window

ASKER CERTIFIED SOLUTION

Joshua Grantom

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

ASKER

The {0, 26) was a carryover from another project. The data is variable length. If using {0, 26} "forces" it to be written as fixed-length, then what to I need to do to write it out as variable length, and not worry about having to trim the data?

It appears that all I really need is:
$file | % {$_.TrimEnd()} | Add-Content $outTextFile

I will give that a try and see what happens.

D B

ASKER

The following worked. No need to use TrimEnd().

$file | % {"{0}" -f $_.Data} | Add-Content -Path $outTextFile

Not sure how I'll divvy up points. I'll work on it tomorrow.

Vitor Montalvão

dbbishop, do you still need help with this question?

D B

ASKER

Sorry for the delay. I had a vacation thrown in last week :-). I think Joshua's post got me on the right track, so I am accepting his solution, even though I ended up tweaking it some.
Thanks for the help.