Post reply

Comma as delimiter in Flat file import

  • May 6, 2009 at 11:19 pm

    #131225

    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

    #990147

    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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 9, 2009 at 1:17 am

    #991567

    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