用于保留 SQL 数据中的逗号的平面文件 [英] Flat File to retain commas from SQL data
问题描述
我正在使用平面文件连接管理器将 SQL 视图导入到 SSIS.我在 SQL 中的一列中有逗号.(123 Main St，波士顿，马萨诸塞州).当我将数据导入 SSIS 时，列中的逗号被视为分隔符，并且我的列被分成几列.我在网上做了很多研究，并遵循了一些对我不起作用的解决方法.
I’m importing a SQL view to SSIS using the Flat File Connection Manager. One of my columns in SQL has comma(s) in it. (123 Main St, Boston, MA) . When I import the data to SSIS, the commas within the column are being treated as delimiters, and my column is being broken into several columns. I have done a lot of research online, and have followed some workarounds which aren't working for me.
在 SQL Server 中，我在包含逗号的值周围添加了双引号.
In SQL Server, I added double quotes around the values that have comma(s) in it.
' "'+CAST(a.Address as varchar(100))+'" '
所以，123 Main St, Boston, MA 现在改为123 Main St, Boston, MA"
So, 123 Main St, Boston, MA now reads "123 Main St, Boston, MA"
然后在我的 SSIS 平面文件连接管理器中，
Then in my SSIS Flat File Connection Manager,
在常规标签中:
文本限定符设置为
Text Qualifier is set to "
标题行分隔符设置为 {CR}-{LF}
Header Row Delimiter is set to {CR}-{LF}
在列标签中:
行分隔符设置为 {LF}
Row delimiter is set to {LF}
列分隔符设置为逗号 {,}
Column delimiter is set to Comma {,}
在高级选项卡中，我的所有列都将文本限定设置为 True.
And in the advanced Tab, all of my columns have the Text Qualified set to True.
在所有这些之后，我的带有逗号的列仍然被分成多列.我错过了一步吗?如何让 SSIS 包将我的地址列视为一列而不是将其分成几列?
After all of this, my column with commas in it, is still being separated into multiple columns. Am I missing a step? How can I get the SSIS package to treat my address column as one column and not break it out to several columns?
只是为了添加更多细节.我从一个 SQL 视图中提取，该视图在任何包含逗号的字段周围都有双引号.然后我通过电子邮件发送该文件并在 MS Excel 中打开它.当我打开它时，文件内容如下:
Just to add more specifics. I am pulling from a SQL view that has double quotes around any field that has commas in it. I am then emailing that file and opening it in MS Excel. When I open it the file it read as follows:
123 Main St Boston MA" "(三个单元格)
123 Main St Boston MA" " (In three cells)
我需要把它读成
123 Main St, Boston, MA(在一个单元格中)
123 Main St, Boston, MA (in one cell)
推荐答案
我最终使用我的问题中列出的相同参数重新创建了包.我也换了这个
I ended up recreating the package, using the same parameters that are listed in my question. I also replaced this
' "'+CAST(a.Address as varchar(100))+'" '
在我的 SQL 视图中使用这个
with this in my SQL view
a.Address
它现在可以按需要运行了.不知道那里发生了什么.感谢大家的意见和建议.
And it now runs as desired. Not sure what was going on there. Thanks to everyone for their comments and suggestions.
这篇关于用于保留 SQL 数据中的逗号的平面文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
