May 6, 2009 at 11:19 pm
Hi
I have a SSIS package which imports a flat file to DB. Row delimiter is {CR}{LF} and Column Delimiter is Comma.
We have a amount column in the flat file whose value is 54,400 dollars.
If the column has a comma in it then the values are present as "54,400".
Due to that "," present in 54,400 , it splits it into two column 54 and 400 while importing. Is there a work around for this.(modify the original import file is not an option.)
Thanks!
Ex: "54,400.00",Ethel,National City
expected result : "54,400.00" Ethel National City
present result : "54 400.00" Ethel NationalCity
May 7, 2009 at 12:30 am
That's a real pain!
I think that you might have to import every row as a single text string and then use code (ie a Script component) to split the items out as you want them. Otherwise you will get errors because the apparent number of fields in each row is varying.
Or maybe you could run some sort of wildcard find/replace on the file before it is imported, along the lines of
Replace(Text, "n,n", "nn")
where 'n' is any number 0, 1, ..., 9.
That, of course, depends on the data in your text file and will not work if you have two coincident numeric fields - as it would erroneously replace the delimiting comma between them.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 9, 2009 at 1:17 am
Hi
Thanks for the reply...
The whole probelm vanished in a jiffy when I entered " in text qualifier
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply