using SQLCMD to export/extract data withot trailing spaces

Question

I am trying to extract data from some of my tables using sqlcmd utility as SSMS does not generate appropriate csv-s for me.

My issue: all csv extract includes trailing spaces according to table definitions (I mean field length) regardless of using char, varchar, nvarchar, etc...

When using sqlcmd utility eg with following options:

sqlcmd -S . -d /*databasename*/ -E -s, -W -Q "SELECT * FROM /*tablename*/" > C:\Test.csv

it is still contains trailing spaces, eg:

,"273 ",

any hints or tips to do this in SSMS and/or correcting my parameters for SQLCMD?

• user1822: Sounds as if that column is defined as char(n) which will contain fixed length, blank padded values.

Answer 1

Despite how the SQLCMD usage screen (SQLCMD -?) describes the -W parameter:

[-W remove trailing spaces]

the actual purpose of this parameter is not to remove existing trailing spaces but rather to skip padding a value with spaces. If a string value already has trailing spaces of its own, they cannot be removed if you simply specify the -W parameter.

As far as I can see, there is no way to resolve this issue with parameters. You will have to either manipulate the output with text processing utilities or generate the values without the trailing spaces in the first place. For the latter you can use the RTRIM() function, but that, of course, means you will have to replace the SELECT * with an actual column list, where some columns would be computed columns:

SELECT
 ...
 RTRIM(SomeStringColumn) AS SomeStringColumn,
 ...
FROM
 ...

Answer 2

Did you try these sqlcmd parameters?

sqlcmd
 -o output_file 
 -W (remove trailing spaces)

• Andriy M: The OP's command line has the -W parameter, so apparently yes, the OP did try that option. From my own experience I can confirm that it doesn't work for char columns, as @a_horse_with_no_name suggested in a comment under the question.
• Stephen Morris - Mo64: so he´ll have to convert(varchar(10), thecolumn) in that case