如何在派生列转换中替换双引号?
我有平面文件。我正在使用 ssis 将数据从平面文件加载到源表。其中一列具有以下值:
<Somecol1 =""1"" col1values= ""223,567,890,653"">
我想要以下列输出:
<Somecol1 ="1" col1values= "223,567,890,653">
我尝试在派生列中替换。
REPLACE( COLA, "\"","\")
但这不起作用。
I have flat file. I am loading data from flat file to source table using ssis.And one of the column has following values:
<Somecol1 =""1"" col1values= ""223,567,890,653"">
I want following column output:
<Somecol1 ="1" col1values= "223,567,890,653">
I have tried to replace in derived column.
REPLACE( COLA, "\"","\")
but this doesnt work.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我认为除了替换字符串中的附加斜杠之外,您几乎已经得到了正确的表达式。以下是可能适合您的表达方式。
表达式#1:删除给定字符串中的所有双引号。
表达式 #2: 将所有双出现的双引号替换为单出现的双引号。
以下是演示表达式 #1 的示例:
数据流任务
内的派生列转换
,该转换将替换名为 Header 的第一列中的所有双引号。下面是演示表达式#2 的示例:
数据流任务
内的派生列转换
,该转换将替换所有双出现的双引号在名为 标题 的第一列中出现单双引号。希望有帮助。
屏幕截图 #1:
屏幕截图 #2:
屏幕截图#3:
屏幕截图 #4:
屏幕截图 #5 :
I think you have almost got the expression correct except for the additional slash in the replacement string. Here are the expressions that might work for you.
Expression #1: Removes all double quotes within a given string.
Expression #2: Replaces all double occurrences of double quotes with single occurrence of double quotes.
Here is an example that demonstrates expression #1:
Derived Column transformation
inside theData Flow task
that will replace all double quotes within the first column named as Header.Here is an example that demonstrates expression #2:
Derived Column transformation
inside theData Flow task
that will replace all double occurrences of double quotes with single occurrence of double quotes within the first column named as Header.Hope that helps.
Screenshot #1:
Screenshot #2:
Screenshot #3:
Screenshot #4:
Screenshot #5:
如果您的其他列没有同样的问题,这可能不起作用,但如果这是您要导入的唯一文本列或者它们都是这样,您可以将文本标识符更改为两个双引号而不是一个。然后,SSIS 会正确地将其从平面文件中取出,您无需稍后尝试清理它。
This may not work if your other columns don't have this same issue, but if this is the only text column you are importing or they are all like this, you could change your text identifier to be two double quotes instead of one. Then SSIS would bring it out of the flat file correctly and you wouldn't have to deal with trying to clean it up later.
请查找以下示例
Please find below samples