From Command window output to a text file

Hi,

I want to output from the database, throught the command prompt window and into a text file. I have found a command called bcp command, i have used it and it works just fine . However, i have a problem. The problem is that by default it outputs into the file using tab delimited form. Each column is seperated by a tab. Is there any way , either from the query analyzer onwards or from the bcp command to output the data without the tabs inbetween?

Thank you
George

Check BCP- Specifying Field and Row Terminators in BOL.

Madhu

|||HI,

i did my search before posting , however, the only part i could find similar to my problem was :

terminating character Indicated by

Tab

\t

This is the default field terminator.

Newline character

\n

This is the default row terminator.

Carriage return/line feed

\r

Backslash¹

\\

Null terminator (nonvisible terminator)²

\0

Any printable character (control characters are not printable, except null, tab, newline, and carriage return)

(*, A, t, l, and so on)

String of up to 10 printable characters, including some or all of the terminators listed earlier

(**\t**, end, !!!!!!!!!!, \t—\n, and so on)

There is a lot about terminating characters,however, NOTHING on NO charactersat all. I want to tab inbetween, no backslash,no space, no nothing. How can i do that?

Thank you
George|||

i guess i am not clear with the requirement. is it that u want to have a o/p file having Column1+column2+colum3 etc ect without any delimitter?

Madhu

|||hi,

exactly. This is the output with tab delimited format.

vvvXXXXXXXXXXXXXXXXXXXXXX 20071112 h 0000000000005555,70 066,50000
abcXXXXXXXXXXXXXXXXXXXXXX 19000101 y 0454545454523456,45 077,30000
xyzcccXXXXXXXXXXXXXXXXXXX 19000101 x 0000000000003456,00 077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX 20030302 6 0000000000232323,45 005,00000

I want the output to have to tabs , as shown below:

vvvXXXXXXXXXXXXXXXXXXXXXX20071112h0000000000005555,70066,50000
abcXXXXXXXXXXXXXXXXXXXXXX19000101y0454545454523456,45077,30000
xyzcccXXXXXXXXXXXXXXXXXXX19000101x0000000000003456,00077,99865
fXXXXXXXXXXXXXXXXXXXXXXXX2003030260000000000232323,45005,00000

Columns values should not be seperated by tabs. Any thoughts?

Thank you
George|||

use QueryOut option with BCP

Eg.

C:\>bcp "Select col1+col2 from Tablename" queryout 2.txt -T -c

Madhu

|||Hi,

I am already using queryout in my command. the command is below:

C:\>bcp "select ltrim(rtrim(char25))+replicate ('X',25-len(char25)),CONVERT(varc
har(8),dateg,112) as [yyyymmdd],flag1,replace( replicate ('0',19-len(amount)) +
ltrim(rtrim(amount)),'.',','),replace(replicate ('0',9-len(dperc)) + ltrim(rtrim
(dperc)),'.',',') from Bank_Info.dbo.ddd" queryout c:\xxxx\replicate_replace.txt -c -U sax -S KARAFOKAS -C 1252 -P passwsax

I am also using -c which assumes tab delimited as default.|||

c:\sqlcmd -q"select ltrim(rtrim(char25))+replicate ('X',25-len(char25)),CONVERT(varc
har(8),dateg,112) as [yyyymmdd],flag1,replace( replicate ('0',19-len(amount)) +
ltrim(rtrim(amount)),'.',','),replace(replicate ('0',9-len(dperc)) + ltrim(rtrim
(dperc)),'.',',') from Bank_Info.dbo.ddd" -o"c:\xxxx\replicate_replace.txt" -h-1

Check this

if its 2000 replace sqlcmd with OSQL

Madhu

|||

i can get the result as desired in both BCP and SQLCMD.

Madhu

|||Hi,

I dont understand. I want to use bcp command . Can you write the bcp command to use ?

Thank you
George|||

Have you tried with SQLCMD /OSQL . I can get the result as you mentioned using both BCP and SQLCMD. If still you are not able to then the workaround is add a new column to this table (or for that matter create a new table) and update the column with this Value and select that column using QUERYOUT.

Madhu

|||Hi,

I have tried with SQLCMD but it doesnt work. You said you got the same desired result using also bcp. well, what did u do to avoid the field delimeters? Can you copy paste the correct command to use without removing any of my properties/parameters that i have used?

Thank you
George|||Hi,

After a lot of hours i have managed to do what i wanted to . The only way NOT to have field delimiters is to create a format file and use that one in order to run the command. The format file looks like the one below (name: bcp.fmt)

8.0
5
1 SQLCHAR 0 25 "" 1 char25 Greek_BIN
2 SQLCHAR 0 8 "" 2 yyyymmdd Greek_BIN
3 SQLCHAR 0 1 "" 3 flag1 Greek_BIN
4 SQLCHAR 0 19 "" 4 amount Greek_BIN
5 SQLCHAR 0 9 "\r\n" 5 dperc Greek_BIN

As you can see , the field delimiters are empty. The command i used is :

C:\>bcp "select ltrim(rtrim(char25))+replicate ('X',25-len(char25)),CONVERT(varc
har(8),dateg,112) as [yyyymmdd],flag1,replace( replicate ('0',19-len(amount)) +
ltrim(rtrim(amount)),'.',','),replace(replicate ('0',9-len(dperc)) + ltrim(rtrim
(dperc)),'.',',') from Bank_Info.dbo.ddd" queryout c:\xxxx\replicate_replace.tx
t -fc:\xxxx\bcp.fmt -U sax -S KARAFOKAS -C 1252 -P passsax

instead of

C:\>bcp "select ltrim(rtrim(char25))+replicate ('X',25-len(char25)),CONVERT(varc
har(8),dateg,112) as [yyyymmdd],flag1,replace( replicate ('0',19-len(amount)) +
ltrim(rtrim(amount)),'.',','),replace(replicate ('0',9-len(dperc)) + ltrim(rtrim
(dperc)),'.',',') from Bank_Info.dbo.ddd" queryout c:\xxxx\replicate_replace.tx
t -c -U sax -S KARAFOKAS -C 1252 -P passsax

Hope others dont lose 2-3 hours of their time.

Thank you Madhu. I am marking this question sovled and helpful

George