Post reply

SSIS Flat file source varying column and text delimiter issue :crying:

  • December 4, 2009 at 3:45 am

    #139762

    Hello All,

    how to handle flat files that have a varying number of columns along with text delimiter in data?

    below shows a sample data of file that uses a semi colon[;] to delimit the columns, and a cursor return[CR] / line feed[LF] to delimit the row and Double Quote as Text Delimiter.

    Col1;col2;col3;col4

    Emp1;1;Raghav;IT

    Emp2;2;"madhu;neethu";BPO

    Emp3;1;Shiva

    Emp4;4;"Somu;Ranga;Krishna;Keshava";ITICS

    Emp5;0

    Emp6;1;Reddy

    how i can transfer this data of a CSV file into a table using Data transfer?

    AS SSIS Data Transfer is having issues while parsing varying number of columns with text delimiter?

    help me guys..please......... 🙁 :exclamation:

  • December 4, 2009 at 5:32 am

    #1087595

    I would suggest you manually process this irregular file by implementing source script component.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • December 4, 2009 at 5:42 am

    #1087605

    yeah, i'm working on that workaround also........

    can you give me a script where i can split the input buffer string based on column delimiter [;] with giving preference to Text delimiter [''''] also....

    for ex:

    the below line

    Emp4;4;"Somu;Ranga;Krishna;Keshava";ITICS

    should split like

    Emp4

    4

    "Somu;Ranga;Krishna;Keshava"

    ITICS

    ?????

  • December 4, 2009 at 6:04 am

    #1087619

    Writing correct CSV parser is not trivial. I would suggest you find existing code and use it as a starting point. This project looks promising.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • December 4, 2009 at 6:51 am

    #1087663

    Thanks in Advance 🙂

    Any other CSV parser code specific to SSIS Script component ?

  • December 7, 2009 at 6:05 am

    #1088462

    Here is the link which helped me to easily solve the CSV reading file and spliiting issue..using a simple RegEx solution 🙂

    http://blog.offbeatmammal.com/post/2005/11/16/Using-a-RegEx-%28Regular-Expression%29-to-read-a-CSV-%28Comma-Seperated-Variable%29-file.aspx

  • December 3, 2010 at 12:37 am

    #1258229

    ssisdfs.codeplex.com is the best resolution!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply