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 |
| Backslash1 | \\ |
| Null terminator (nonvisible terminator)2 | \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
