Thursday, December 4, 2014

How to remove Column Header and White spaces in output file

I come across a requirement that I need to create a job on a SQLServer database and generate output separated by a pipe ("|" ) symbol for each column. Also remove all the white spaces between the columns.

As the requirement is to load the data into an ERP system. I found an easy solution using "sqlcmd"

use -W option to remove white spaces

use -h-1 option to remove column heading or header.

Here is the syntax:-

c:\> sqlcmd -W -h-1 -S <Server_name>,<port> -U <user>-P <password> test -i "c:\query.sql" -o "c:\output.txt"

       query.sql - where you will be writing the query
        for ex:- select  @@version
        I would recommend to use "set nocount on" in the query.sql to suppress the record count.

       output.txt  - where you will see the clean output

Hope this helps.

Satishbabu Gunukula

No comments:

Post a Comment