使用转义引号将SQL数据库批量导出为CSV

I'm looking for a way to batch Export a SQL Server table to a csv file.

我正在寻找一种批量导出SQL Server表到csv文件的方法。

There are some solutions using sqlcmd or bcp, but so far I found none which properly escapes quotes, commas or line-breaks.

有一些使用sqlcmd或bcp的解决方案,但到目前为止,我找不到任何正确转义引号,逗号或换行符的解决方案。

For example this command creates a nice csv but does ignore quotes and commas which renders the csv file unusable:

例如,此命令创建一个很好的csv,但忽略了引号和逗号,这使得csv文件无法使用:

bcp MyDatabase..MyTable out c:\test.csv -c -T -t, -r\n -S MYPC

From my sample data of four rows each containing some other special character this would create a file like this:

从我的四行示例数据中,每行包含一些其他特殊字符,这将创建一个这样的文件:

1,contains " quote
2,contains , comma
3,contains ; semi
4,contains ' single quote

Due to the quotes and the comma this is not importable by other programs. Of course I could change the separator to tab or the pipe symbol, but this does not fix the real problem: Whatever the separator is, if it exists in the data it will render the export file unusable.

由于引号和逗号,其他程序无法导入。当然,我可以将分隔符更改为制表符或管道符号,但这不能解决实际问题:无论分隔符是什么,如果它存在于数据中,它将使导出文件无法使用。

So how do I bulk export data in a batch to a working csv file using standard SQL tools like BCP, sqlcmd or similar?

那么如何使用标准SQL工具(如BCP,sqlcmd或类似工具)将批量数据批量导出到工作csv文件?

4 个解决方案

#1

Using quotename should properly escape quotes (but it's limited to max 128 chars, no line-breaks):

使用引号应该正确地转义引号(但它限制为最多128个字符,没有换行符):

BCP " select quotename(quotedCol,CHAR(34)),quotename(secondCol,CHAR(34))from 
testdb.dbo.table_1" queryout temp.csv -c -T -S. -t","

given values this is "between quotes" and def it produces:
"this is ""between quotes""","def" which is I believe properly quoted/escaped according to csv quidelines.

给定值这是“引用之间”和def它产生:“这是”“引号”“”,“def”之间我相信根据csv quidelines正确引用/转义。

Source: http://social.technet.microsoft.com/wiki/contents/articles/4666.sql-server-bcp-utility-experts-guide.aspx#Use_Text_Qualifier_on_BCP_Output

#2

Either make it fixed width or manually add quote delimiters. Both of these can be achieved with a view

使其固定宽度或手动添加引号分隔符。这两个都可以通过视图实现

For example your view would be

例如,你的观点是

SELECT C1, '"' + REPLACE(C2,'"','\"') + '"' As C2 FROM YourTable

Then you select from this view in your BCP and C2 will be quote delimited, and quotes in the data will be escaped with \ (mostly)

然后你在BCP中从这个视图中选择,C2将被引用分隔,并且数据中的引号将被转义为\(大多数)

To make it fixed width is just another string expression that concatenates the fields with appropriate padding.

使其固定宽度只是另一个字符串表达式,它将字段与适当的填充连接起来。

You can use a query in BCP but I'm not sure how you escape the quotes (!) No matter what you do those quotes are a pain.

您可以在BCP中使用查询,但我不确定您如何逃避引号(!)无论您做什么,这些引用都是一种痛苦。

#3

I found a solution which properly encodes csv files in another * answer by Iain Elder:

我在Iain Elder的另一个*回答中找到了一个正确编码csv文件的解决方案:

He uses PowerShell to Export proper csv:

他使用PowerShell导出适当的csv:

Import-Module -Name SQLPS
$cd = Get-Location
Invoke-Sqlcmd -Query "SELECT * FROM DimDate;" `
              -Database AdventureWorksDW2012 `
              -Server localhost |
Export-Csv -NoTypeInformation `
           -Path "$cd\DimDate.csv" `
           -Encoding UTF8

His solution properly encodes delimiters, line breaks, quotes and works with long content, too.

他的解决方案也适当地编码分隔符,换行符,引号和长内容的作品。

I still find it strange that no other export seems to properly support csv. It's not that complicated.

我仍然觉得奇怪,没有其他出口似乎正确支持csv。这并不复杂。

#4

-1

Does it have to be csv? I usually prefer txt files to avoid this kind of problem.

它必须是csv吗?我通常更喜欢txt文件来避免这种问题。

     bcp MyDatabase..MyTable out c:\test.csv -c -T , -r\n -S MYPC

If you have the possibility to use other delimiters try

如果你有可能使用其他分隔符试试

     bcp MyDatabase..MyTable out c:\test.csv -c -t| -T, -r\n -S MYPC

Other ways to achieve well formed csv are decribed here: https://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

其他实现格式良好的csv的方法在此处描述:https://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

#1