问题描述
我正在创建SSIS包，该包从csv文件读取数据并将其存储在SQL Server数据库中. csv文件中有一些数字字段.它们有时包含类似"1,008.54"
I am creating SSIS package which reads data from a csv file and stores in SQL Server database. There are a few numeric fields in the csv files.They sometimes contain value like "1,008.54"
如何从值中删除引号和逗号?
How do I remove the quotes and comma from the value?
我已经通过使用条件拆分转换成功地将带有此类数据的行分隔开了.(SUBSTRING([Column 9]，1,1)=="\")
I have successfully separated the rows with this kind of data by using Conditional Split Transformation.(SUBSTRING([Column 9],1,1) == "\"")
此后，我尝试使用派生列转换替换逗号和带空字符串的引号.但这不起作用.
After this, I tried using Derived Column Transformation to REPLACE comma and quotes with empty string. But it is not working.
请告知.
谢谢！
推荐答案
我在数据流中测试了样本值"1,008.54"，而我的来源是:
I tested your sample value "1,008.54" in a Data Flow where my source was:
SELECT '"1,008.54"' AS [Column]
然后我将以下表达式放在派生列转换"中(模仿您的尝试)
I then placed the following expression in a Derived Column Transformation (imitating what you did attempted)
REPLACE(REPLACE(Column,",",""),"\"","")
并成功实现了您的请求:使用派生列转换，REPLACE逗号和带空字符串的引号.
and successfully achieved your request: Using Derived Column Transformation, REPLACE comma and quotes with empty string.
以下是数据查看器在派生列转换"之后显示的结果:
Here's the result as presented by a data viewer after the Derived Column Transformation:
Column Derived Column 1
"1,008.54" 1008.54
这篇关于使用SSIS从csv文件中的数字字段中删除逗号和引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！