asked on

Strip 'trailing' spaces

I am running a SQL query using SQLCMD that returns a large number of columns, some of them consisting of several thousand characters. The only way to get the contents of all columns without truncation is to use -y 0 and I am using a delimiter -s "~" as using -W will not return any more than 256 characters. The issue is, each column is padded with spaces up to the length of the column. Thus, if a column is defined as VARCHAR(100) and contains the value 'A', the output will be A..., where .... is 49 spaces.
That being said, if the table schema is:

LastName VARCHAR(25),
FirstName VARCHAR(25),
Gender VARCHAR(15),
DoB VARCHAR(20)

Open in new window

my output file looks something like:

Jones                    ~Mary                     ~F              ~1987-12-22          [eol]

Open in new window

I need to strip all space after each column value, up to the delimiter (~) and also the last column (where there is no delimiter but end of line so my output looks like:

Jones~Mary~F~1987-12-22[eol]

Open in new window

Assuming my output is in a file identified by the variable $outFile, how can I accomplish this in PowerShell?

Ben Personick (Previously QCubed)

Here you are:

( (Get-content $outFile) -replace " *~",'~' ) -replace " *$",'' | Set-content $outFile

Open in new window

edit taking care of the end of line spaces and changed delimiter back

D B

ASKER

It works, but in examining the data, I've got a number of numeric fields with a similar issue, except there are leading blanks
(e.g. ~ 45.32~)
Can those be taken care of in the same cmdlet so the output is ~45.32~?

aikimark

How about getting the SQL results in XML format?

Since these are Varchar fields, run an update query to trim the spaces inside the database table.

D B

ASKER

aikimark: XML won't work for the client we are providing data to. The spaces are not a result of the data stored in the table, but a result of the defined table width. There are dozens of columns in numerous tables and attempting to trim on the database side would be a monumental task. I've done something similar in the past by concatenating column values and adding the delimiter in the TSQL, but that was for a small number of columns. For example:
SELECT RTRIM(LastName) + '~' + RTRIM(FirstName) + '~' + RTRIM(Gender) + '~' + CONVERT(CHAR(10), DoB, 120) AS [Data]
and output the data as a single column, but that is not feasible here.

aikimark

How are you getting fixed-length data from your varchar fields?

I was suggesting you use XML as an intermediary format to see if the data contains trailing spaces. Powershell can handle XML data and convert it as you need.

D B

ASKER

If the column is defined as VARCHAR(100), SQLCMD will return the data in a 100 character field (F____, where ____ represents 100 spaces).
Not 100% certain I understand where you are going with the XML. I need a file that contains a header row (I'm adding that outside the query) and the fields with a tilde (~) separator. I've got everything in place now from Ben's code with the exception of stripping leading spaces from numeric fields.

aikimark

Aren't you making the call from within your Powershell script? If not, why?

ASKER CERTIFIED SOLUTION

Ben Personick (Previously QCubed)

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

Works like a charm. Thanks a lot.

Ben Personick (Previously QCubed)

Hey dbbishop,

Glad to help! :)

If you have any questions don;t be afraid to PM me.

Ben