SQL Server BCP：如何在所有字段周围加引号？

发布于 2024-08-17 09:39:21 字数 208 浏览 5 评论 0原文

我有这个 BCP 命令：

'bcp DBName..vieter out c:\test003.txt -c -T /t"\",\"" -S SERVER'

我得到的输出 CSV 不会在字段名称周围加上引号，而是将其放在逗号周围！如何让 /t"\",\"" 在所有字段周围加上引号。

谢谢大家

原文

I have this BCP command:

'bcp DBName..vieter out c:\test003.txt -c -T /t"\",\"" -S SERVER'

The output CSV I get does not put quotes around the field names, instead it puts it around the commas! How can I get the /t"\",\"" to put quotes around all fields.

Thanks all

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

Bonjour°[大白 2024-08-24 09:39:21

除了字段终止符之外设置行终止符应该可以解决这个问题

'bcp DBName..vieter out c:\test003.txt -c -T -t"\",\"" -r"\"\n\"" -S SERVER'

这可能会起作用，但是错过了第一行的第一个字段的前导“，也许最后一行的最后一个字段 - 我不确定，只是猜测，这里没有服务器！

或者尝试使用 QUOTENAME 来换行文本字段（您也可以换行数字，但这通常不是必需的。）

'bcp "SELECT id, age, QUOTENAME(name,'"') FROM DBName..vieter" queryout c:\test003.txt -c -T -t"," -S SERVER'

Setting the row terminator in addition to the field terminator should do the trick

'bcp DBName..vieter out c:\test003.txt -c -T -t"\",\"" -r"\"\n\"" -S SERVER'

This will likely work, but miss off the leading " for the first field of the first line, and perhaps the last field of the last line - I'm not sure, just guessing really, no server here!

or try using QUOTENAME to wrap text fields (you could also wrap numbers, but that isn't normally required.)

'bcp "SELECT id, age, QUOTENAME(name,'"') FROM DBName..vieter" queryout c:\test003.txt -c -T -t"," -S SERVER'

回复收藏 0 原文

烂人 2024-08-24 09:39:21

您需要使用 CHAR(34) 进行引用。此页面有更多详细信息： http://www.sqlteam.com/forums/topic .asp?TOPIC_ID=153000

回复收藏 0 原文

弃爱 2024-08-24 09:39:21

或者，如果您适合基于 Powershell 的脚本，您可以尝试使用下面的代码，它会自动引用。

Invoke-sqlcmd -ConnectionString "Server=SERVERNAME, `
3180;Database=DATABASENAME;Trusted_Connection=True;"  `
-Query "SET NOCOUNT ON;SELECT * FROM TABLENAME" -MaxCharLength 700 | `
Export-Csv -NoTypeInformation -path C:\temp\FileName.csv -Encoding UTF8

Alternatively, if you are fine for Powershell based script, you can try with below code, which does automatic quoting.

Invoke-sqlcmd -ConnectionString "Server=SERVERNAME, `
3180;Database=DATABASENAME;Trusted_Connection=True;"  `
-Query "SET NOCOUNT ON;SELECT * FROM TABLENAME" -MaxCharLength 700 | `
Export-Csv -NoTypeInformation -path C:\temp\FileName.csv -Encoding UTF8

回复收藏 0 原文

一紙繁鸢 2024-08-24 09:39:21

bcp "SELECT char(34) + * +char(34) FROM atable queryout "C:\temp\out.csv" -T -N -c /t"\",\""

这将在每个字段之前和之后添加引号（包括第一个和最后一个字段）。

bcp "SELECT char(34) + * +char(34) FROM atable queryout "C:\temp\out.csv" -T -N -c /t"\",\""

This will put quotes before and after each field (including the first and the last).

回复收藏 0 原文

时光无声 2024-08-24 09:39:21

这是我使用的命令列表。

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from databaseName.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tableName'; select @colnames;" queryout "C:\HeadersOnly.csv" -r"\n\""  -c -T -Uusername -Ppassword -SserverName

bcp databaseName.schema.tableName out "C:\EmployeeDatawithoutheaders.csv" -T -t"\",\"" -r"\"\n\"" -c -Uusername -Ppassword -SserverName

copy /b C:\HeadersOnly.csv+C:\EmployeeDatawithoutheaders.csv C:\EmployeeData.csv

del C:\HeadersOnly.csv

del C:\EmployeeDatawithoutheaders.csv

Here are the list of commands i used .

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from databaseName.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tableName'; select @colnames;" queryout "C:\HeadersOnly.csv" -r"\n\""  -c -T -Uusername -Ppassword -SserverName

bcp databaseName.schema.tableName out "C:\EmployeeDatawithoutheaders.csv" -T -t"\",\"" -r"\"\n\"" -c -Uusername -Ppassword -SserverName

copy /b C:\HeadersOnly.csv+C:\EmployeeDatawithoutheaders.csv C:\EmployeeData.csv

del C:\HeadersOnly.csv

del C:\EmployeeDatawithoutheaders.csv

回复收藏 0 原文

蓝颜夕 2024-08-24 09:39:21

我想您的目标是通过使用唯一标识符来清楚地分隔字段值，以便导入过程不会出现问题。

我遇到了同样的问题，发现这个解决方法很有用：使用不寻常的字段终止符，例如 | 甚至字符串 /#/ 可以非常独特，不应该混淆你的字符串内容。您还可以使用十六进制值（有限，请参阅 https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017)

导出

bcp DB.dbo.Table out /tmp/output2.csv -c -t "/#/" -U sa -P secret -S localhost

导入

bcp TargetTable in /tmp/output2.csv -t "/#/" -k -U sa -P secret -S localhost -d DBNAME -c -b 50000

I guess your goal was to clearly seperate field values by using an unique identifier so that import procedure doesn't have an issue.

I had same issue and found this workaroud useful: Using an unusual field terminator, for example | or even a string /#/ can be very unique and shouldn't mess with your string content. You also can HEX-Values (limited, see https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017)

export

bcp DB.dbo.Table out /tmp/output2.csv -c -t "/#/" -U sa -P secret -S localhost

import

bcp TargetTable in /tmp/output2.csv -t "/#/" -k -U sa -P secret -S localhost -d DBNAME -c -b 50000

回复收藏 0 原文

も让我眼熟你 2024-08-24 09:39:21

删除前导引号的实际可行答案是：

A）使用 bcp 生成格式文件：

bcp db.schema.tabel format nul -c -x -f file.xml -t"\",\"" -r"\"\r\n" -T -k

B）编辑该文件以手动将字段 1 复制到上面的字段 0，作为第一个字段，设置 Max_Length=1 并删除分隔符并在 field1 中添加一个引号

<FIELD ID="0" xsi:type="CharTerm" TERMINATOR="\"" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

该技巧有效，因为您添加一个字段（文件的接口）来检测第一个分隔符，这会导致始终为空值，但不会添加一行（文件的接口）查询输出）。

The actual workable answer, that removes the leading quote, is to :

A) generate format file with bcp :

bcp db.schema.tabel format nul -c -x -f file.xml -t"\",\"" -r"\"\r\n" -T -k

B) edit that file to manually copy field 1 to field 0 above, as the first field, set Max_Length=1 and remove the separator and one quot the was in field1

<FIELD ID="0" xsi:type="CharTerm" TERMINATOR="\"" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

The trick works, as you are adding a field (interface to the file) to detect the first seprator, which results in an always null-value, but not add a row (interface for the query output).

回复收藏 0 原文

~没有更多了~