[SQL ] SQL使用bcp匯出csv的問題

[elic2577 (pupx1000你他媽雜碎)]

   資料庫名稱：MS SQL

   資料庫版本：2014

內容/問題描述：

create table user1
(id int, acct varchar(50), name varchar(50))
insert into user1
values (1, 'A001', '邱小姐')
     , (2, 'A002', '呂小姐')
     , (3, 'A003', '陳先生')
     , (4, 'A004', '蔡先生')
     , (5, 'A005', '黃先生')


想匯出每一行的每個字串都被""雙引號包住的csv

想要的結果是：
"1","A001","邱小姐"
"2","A002","呂小姐"
"3","A003","陳先生"
"4","A004","蔡先生"
"5","A005","黃先生"

使用bcp的方式
bcp "select * from dbo.user1" queryout "D:\user.csv" -t"\",\"" -r"\"\n\"" -S
192.168.0.1 -d UserDB -U sa -P password -w
但出來的結果是
1","A001","邱小姐"
"2","A002","呂小姐"
"3","A003","陳先生"
"4","A004","蔡先生"
"5","A005","黃先生"
"

差一點點，第一列的"不見了，好像跑去最後一列
請問我該怎麼調整最好呢？
非常感謝


--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 123.194.97.49 (臺灣)
※ 文章網址: https://www.ptt.cc/bbs/Database/M.1594178509.A.019.html
推 cancelpc: 為啥用 /t，用 -q 才對吧
您好，我不太會用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
我改成
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,'"')
→ cancelpc: -t 只會加在欄位後面，當然前面會少了"
我改成
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