CSV的SSIS派生列(从字符串到浮点) [英] SSIS Derived Column From CSV (Cast String to Float)

问题描述

我得到了一个如下所示的CSV.该列有一些空行，而CSV则将值作为字符串给出.但我想将其设为浮点数，例如"3.00000"(带小数点后5位).首先，我需要替换，"符号并替换空值我使用了这个派生的列表达式:(DT_R8)REPLACE(ISNULL(Col1)?"0.0000":Col1，，"，.")，但是它不起作用.你们知道更好的表达方式吗?该列如下所示:

I got a CSV like below. The column got some empty rows and the CSV gives the values as a string. But I want to make it a float like ‘3.00000’ with 5 decimals. First, I need to replace the ‘,’ symbol and replace the null values I used this derived column expression: (DT_R8)REPLACE(ISNULL(Col1) ? "0.0000" : Col1,",",".") but it is not working. Do you guys know a better expression? The column is like below:

+---------+
|  Col1   |
+---------+
| 3,00000 |
|         |
| 4,00000 |
| 6.56565 |
|         |
| 4.54545 |
+---------+

推荐答案

我相信您将需要在这里看两件事.首先，其中带有逗号的单元格很可能会被识别为字符串，因此它们将使用引号的文本限定符.如果您未在SSIS中明确设置，则SSIS将保留引号，然后您将无法将该字符串转换为数字.要确认，请在连接管理器上检查预览，如果您的电话号码带有双引号，如下所示，则需要将文本限定符设置为引号.

I believe that you will need to look at two things here. First, it is likely that the cells with a comma in them are recognized as strings and therefore they will use a text qualifier of quotes. If you don't explicitly set that up in your SSIS, then SSIS will keep the quotes and then you won't be able to convert that string to numeric. To confirm, on your connection manager, check your preview, if your numbers have a double quotes as below, then you need to set the text qualifier to a quote.

设置文本限定符之前的快照:

Snapshot before setting the text qualifier:

现在，如果是这样，您需要转到常规，并确保将文本限定符设置为，如下所示"

Now, if that's the case, you need to go to General, and make sure you text qualifier is set to " as below

完成后，您可以验证预览是否已修复

Once done, you can verify that your preview is now fixed

您遇到的问题是假设您正在处理NULL值.如果您的列是从平面文件导入的字符串类型，则不是这种情况.您正在处理一个空字符串.因此，您需要将表达式修改为:

The issue that you are having is assuming that you are dealing with NULL values. This isn't the case when your column is of type string imported from a flat file. You are dealing with an empty string here. So, you need to modify your expression to be:

(DT_R8)REPLACE(Col1 == "" ? "0.0000" : Col1,",",".")

最后，您是否认识到将，"替换为.".会导致"3,00000"被转换为3.0?请确保这是预期的行为.

Finally, do you recognize that replacing the "," with "." will result in "3,00000" being converted to 3.0 ? Please make sure that this is the expected behavior.

遵循上述内容将导致以下派生列

Following the above will result in the following derived column

这篇关于CSV的SSIS派生列(从字符串到浮点)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！