October 18, 2001 at 8:20 am
I would like to bcp any table to a text file so that every row become a string without space inside.
So I tried the following command with the table northwind territories but in the destination file, the two last field are separated by a large space on every line:
declare @STR varchar(255)
select @STR = 'bcp northwind..Territories out c:\terr.txt -t , -r \n -c -T'
exec master..xp_cmdshell @STR
The file terr.txt:
01581,Westboro ,1
01730,Bedford ,1
01833,Georgetow ,1
02116,Boston ,1
02139,Cambridge ,1
02184,Braintree ,1
02903,Providence ,1
03049,Hollis ,3
03801,Portsmouth ,3
06897,Wilton ,1
07960,Morristown ,1
08837,Edison ,1
10019,New York ,1
10038,New York ,1
11747,Mellvile ,1
14450,Fairport ,1
Note that when I execute the same command with another table, I get a file which exactly match my need:
declare @STR varchar(255)
select @STR = 'bcp pubs..publishers out c:\publ.txt -t , -r \n -c -T'
exec master..xp_cmdshell @STR
The file publ.txt:
0736,New Moon Books,Boston,MA,USA
0877,Binnet & Hardley,Washington,DC,USA
1389,Algodata Infosystems,Berkeley,CA,USA
1622,Five Lakes Publishing,Chicago,IL,USA
1756,Ramona Publishers,Dallas,TX,USA
9901,GGG&G,Mnchen,,Germany
9952,Scootney Books,New York,NY,USA
9999,Lucerne Publishing,Paris,,France
What is wrong with my first command ?
How to solve the problem ?
Thanks in advance.
October 18, 2001 at 1:06 pm
I am the author of that question.
I think I understand now why I got the large space between the two last fields in the file.
In the table, the second colums is of type nchar(50). So if you bcp the table, the corresponding fields are completed by spaces to have a length of 50.
But I was wrong when I said that the second command match exactly my need. Because, in the result file, I don't want a space in the lines at all, not only between the fields but even inside them.
Thank you all.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply