Remove column space from sqlcmd output file

Question

I am trying to use sqlcmd to output the results from a stored procedure to a text file. However, it is putting a space between the resulting columns (a default delimiter) and I need to remove this. I am using the below code and have tried specifying nothing as the column separator see code below (-s "") but this does not work. It gives me a missing argument error.

sqlcmd -S Server name -U Username-P Password -d Db name-Q 
          "exec Stored procedure" -h -1 -s "" -o c:\newtest.txt

How can I specify nothing as the delimiter?

Why do you want no delimiter? With no delimiter how can you parse the output? — Andrew
– Andrew, Commented Apr 30, 2015 at 15:09
It needs to be a fixed width (ragged right) output so it can be imported into another piece of software — Liz W-J
– Liz W-J, Commented Apr 30, 2015 at 15:27

Christopher · Accepted Answer · 2015-04-30 15:44:22Z

1

It can be done with bcp, using -t 0x90

-t is the field terminator in bcp. you can use it on stored procs as well as tables so it should do everything you need.

I just verified this works:

bcp dbname.dbo.tablename out "c:\test.txt" -S serveraddress -U username -P password -t 0x90 -c

Using a stored proc would be something like:

bcp "EXEC YourDatabaseNameGoesHere.dbo.sp_myproc ''0123,0234,0345''"

edited Apr 30, 2015 at 15:44

answered Apr 30, 2015 at 15:37

Christopher

1347 bronze badges

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

3 Comments

Liz W-J Over a year ago

That works great thanks! It's outputting the row numbers though. Any idea how do I suppress them?

Christopher Over a year ago

If it's the id/identity column in your table you don't want you can replace the table string in the example with a select statement (in quotes) selecting only the fields you want. Or perhaps your stored proc is returning it in which case you would have to alter the proc or use a select statement instead. Sorry if I'm misunderstanding the question. If solution works pls up it :)

Liz W-J Over a year ago

Ah yes! Sorted it thanks! Unfortunately it has removed the spaces but it's also removed the formatting set in the stored procedure. Each column needs to be a set width but the fixed width seems to have been altered using this process. Is there a way I can remove the space between columns but not the spaces in the columns themselves? Here is my code: bcp "EXEC LandChargesLive.dbo.InternalCreateMonthlyExportFile " queryout "c:\test.txt" -S db2008.dev.local -U Development -P D3v3l0pm3nt -t 0x90 -c

Collectives™ on Stack Overflow

Remove column space from sqlcmd output file

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related