How to use TAB as a column delimiter in SQLCMD

Question

SQLCMD supports the -s option to specify a column separator, but I could not figure out how to represent the tab character (CHAR (9)). I tried the following, but both of them do not work:

sqlcmd -S ServerName -E -Q"select * from mytable" -s"\t" -o results.txt sqlcmd -S ServerName -E -Q"select * from mytable" -s'\t' -o results.txt 

Any ideas how to do this in SQLCMD?

Answer 1

In the batch file, the tab between double quotes works.

 sqlcmd -S ServerName -E -Q"select * from mytable" -s" " -o results.txt 

to do the same in a PowerShell file, use escaped double quotes wrapped around an escaped tab

 sqlcmd -S ServerName -E -Q"select * from mytable" -s `"`t`" -o results.txt 

Answer 2

It is difficult to get unformatted SQLCMD results.

If you want to create a tab delimited output file, BCP might be better:

 bcp "select * from mytable" queryout results.txt -S server -T -c 

Answer 3

I tried many times to pass the actual TAB character to SQLCMD and I just can't get it to take it. My favorite job is to pass SQLCMD ASCII "Unit Separator", which is the hexadecimal 0x1F, and can be entered on the command line by typing Ctrl-_ (the underscore control character, which on the US keyboard becomes ctrl-shift -'- '("- "next to" 0 "in the top row of the keyboard).

The advantage of using "Unit Separator" is that it is very unlikely to be present in the text of any description and was developed specifically for this purpose (see https://en.wikipedia.org/wiki/Delimiter )

Having received the SQLCMD for this, I then pass its output using a Unix-style translation command, like:

 tr '\037' '\t' 

\ 037 is the octal for "Unit Separator", and \ t represents the tab character, "tr" translates BOTH of them for us, we do not need to rely on any quote tricks in our scripts or shells.

To get 'tr' in windows, you can install the CoreUtils package from GnuWin32 (see http://gnuwin32.sourceforge.net/packages/coreutils.htm ) or go hard and install a full Unix environment like Cygwin (http : //cygwin.com/).

Putting two together, we get:

 sqlcmd ... -h-1 -W -k -r1 -s^_ ... | tr '\037' '\t' 

and this will give you tabbed results.

Look at the other parameters that I used above, they are necessary to get pure output from SQLCMD (in order, without headers, trimming spaces, CRLF for spaces, errors in STDERR (not your output file!), And "^ _" is how Unit Separator appears on the command line). You also need to add "SET NOCOUNT ON"; to your query or sql script, otherwise you will get a line counter as a test message appearing on your output!

Answer 4

Found a good answer here: SQLCMD source file as tab delimited text file

• Open notebook
• Insert this: sqlcmd -S (local) -E -s"<TAB>" -Q "select * from sys.dm_exec_query_stats" -o MyOutput.txt -h-1 -W
• Highlight <TAB> , then press Tab
• Save the file as MyBatch.bat
• Run MyBatch.bat

Answer 5

A similar answer to the one above, but it is simpler than I consider significant.

• Open a text editor
• Press tab
• Select a piece of space (tab) created
• Copy and paste this to a place in your SQL command

Although this tab is represented as a wide fragment of spaces, it is one character.

Another answer had a few extra things about inserting the whole command using "<TAB>" . I think it drops people (it certainly threw me away).

Answer 6

To work in a command window, and not in a batch file, I found the only way to solve this problem:

sqlcmd -S server_name -E -d database_name -Q "select col1, char (9) , col2, char (9) , col3, char (9) , col4, char (9) , col5" -o] results from my list .txt -W -W 1024 -S "" -m 1

Answer 7

To achieve this, using sqlcmd, you need to use the Tab character like this: \ t An example of a query that exports one sql database table to a text file using a tab delimiter looks like this:

 sqlcmd -S ServerName -d databaseTableName -Q "SELECT * FROM TABLE_NAME" -o C:\backups\tab_delimiter_bakup.txt -s"\t" 

Answer 8

TL; DR: use the ALT+009 ascii tab code for the delimiter character

In this example, replace {ALTCHAR} with ALT+009 (hold the ALT key and enter the numbers 009)

 sqlcmd -E -d tempdb -W -s "{ALTCHAR}" -o junk.txt -Q "select 1 c1,2 c2,3 c3" 

Edit junk.txt . Tabs will be between columns.

For other command line options:

 sqlcmd -? 

Note. The shell converts the ALT character to ^ I, but if you try to execute the command by typing -s "^ I", you will not get the same results.