推 cancelpc: 為啥用 /t,用 -q 才對吧 07/08 12:53
您好,我不太會用bcp,是google來的,請問要怎麼改用 -q 呢?
我改成
bcp "select * from dbo.user1" queryout "D:\user.csv" -q -S
192.168.0.1 -d UserDB -U sa -P password -w
變成
1 A001 邱小姐
2 A002 呂小姐
3 A003 陳先生
4 A004 蔡先生
5 A005 黃先生
還是不是我想要的,請問該怎麼改呢?
→ retsamsu: -t "," -q 07/08 13:32
我改成
bcp "select * from dbo.user1" queryout "D:\user.csv" -t "," -q -S
192.168.0.1 -d UserDB -U sa -P password -w
變成
1,A001,邱小姐
2,A002,呂小姐
3,A003,陳先生
4,A004,蔡先生
5,A005,黃先生
還是不是我想要的,請問還有什麼方式改呢?
推 cancelpc: sql 遇到欄位用 QUOTENAME(name,'"') 07/08 14:45
→ cancelpc: -t 只會加在欄位後面,當然前面會少了" 07/08 14:45
我改成
bcp "select QUOTENAME(id,'"'),QUOTENAME(acct,'"'),QUOTENAME(name,'"')
from dbo.user1" queryout "D:\user.csv" -q -S 192.168.0.1 -d UserDB
-U sa -P password -w
結果是
[1] [A001] [邱小姐]
[2] [A002] [呂小姐]
[3] [A003] [陳先生]
[4] [A004] [蔡先生]
[5] [A005] [黃先生]
參考二樓又改成
bcp "select QUOTENAME(id,'"'),QUOTENAME(acct,'"'),QUOTENAME(name,'"')
from dbo.user1" queryout "D:\user.csv" -t "," -q -S 192.168.0.1 -d UserDB
-U sa -P password -w
結果是
[1],[A001],[邱小姐]
[2],[A002],[呂小姐]
[3],[A003],[陳先生]
[4],[A004],[蔡先生]
[5],[A005],[黃先生]
感覺我哪裡沒對,所以一直做不出想要的
好,終於一邊測試一邊google,有達到要的部分了
bcp "select quotename(id,char(34)), quotename(acct,char(34)),
quotename(name,char(34)) from dbo.user1" queryout "D:\user.csv" -t "," -q -S
192.168.0.1 -d UserDB -U sa -P password -w
結果是
"1","A001","邱小姐"
"2","A002","呂小姐"
"3","A003","陳先生"
"4","A004","蔡先生"
"5","A005","黃先生"
非常感謝
※ 編輯: elic2577 (123.194.97.49 臺灣), 07/08/2020 17:29:04