作者elic2577 (pupx1000你他妈杂碎)
看板Database
标题[SQL ] SQL使用bcp汇出csv的问题
时间Wed Jul 8 11:21:47 2020
资料库名称：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://webptt.com/cn.aspx?n=bbs/Database/M.1594178509.A.019.html
1F：推 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 黄先生
还是不是我想要的，请问该怎麽改呢？
2F：→ 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,黄先生
还是不是我想要的，请问还有什麽方式改呢？
3F：推 cancelpc: sql 遇到栏位用 QUOTENAME(name,'"') 07/08 14:45
4F：→ 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