使用SSIS从csv文件中的数字字段中删除逗号和引号 [英] Removing commas and quotes from numeric fields in csv file using SSIS

问题描述

我正在创建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文件中的数字字段中删除逗号和引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！