4

I have the following csv file

Column
2,3
2,4

And everytime when I import the data via SSIS Packages I get an error because of the comma value. When I change 2,3 and 2,4 to 2.3 and 2.4 it is working fine. And the values will be stored correctly with 2,3 and 2,4 into table.

For import I use Codepage 1252 ANSI-Lateinisch I and the field in the db is decimal(18,2).

What is wrong with my import? Any ideas?

THanks

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Looking at your very small example though, it doesn't look right. you only declare 1 header (`Column`), but both rows have 2 values (for the first row `2` **and** `3`). a comma (`,`) is a delimiter. perhaps you mean to quote your values `"2,3"`? – Thom A Feb 08 '19 at 12:09
  • 1
    So, you're using comma as a decimal separator? The csv is wrong to begin with. – Salman A Feb 08 '19 at 12:13
  • 1
    @Flamms, you get errors because comma considered to be a default column separator and dot is decimal separator. Thats why CSV is stands for "**comma** separated value" – Alexander Volok Feb 08 '19 at 12:15

2 Answers2

1

In your csv connection manager, change the column delimiter to the {CrLf} character.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
1

You have mentioned the error cause in the question:

When I change 2,3 and 2,4 to 2.3 and 2.4 it is working fine. And the values will be stored correctly with 2,3 and 2,4 into table.

Which implies that , is causing the issue. Check that is not used as delimiter.

In the Flat File connection manager, go to Advanced Tab, select the column and change the Column delimiter property.

If it is the last column in the row, change the delimiter to {CRLF}.

If you have many columns in the file and the delimiter is comma , then you have to replace it with . or use add a text qualifier.

Workaround

If it still doesn't work, chande the column type to DT_STR then in the Data flow task add a derived column that replace , with , and cast the value as decimal. Example:

(DT_DECIMAl,2)REPLACE([COLUMN], ",", ".")
Hadi
  • 36,233
  • 13
  • 65
  • 124