Post reply

Split Comma Seperate Column Problem

  • March 15, 2011 at 7:02 pm

    #236762

    Here is my source data

    Description

    D-1,D2

    D-2,D-U,T1

    U-5,T2,K-5,T3

    NULL

    Here is the expressions i am using in "Derived Column"

    Col1 = SUBSTRING(Description,1,FINDSTRING(Description,",",1) - 1)

    Col2 = SUBSTRING(Description,FINDSTRING(Description,",",1) + 1,FINDSTRING(Description,",",2) - FINDSTRING(Description,",",1) - 1)

    Col3 = SUBSTRING(Description,FINDSTRING(Description,",",2) + 1,LEN(Description))

    Col4 = Need help for expression

    Note:- I am receiving error "Disposition" Error. Please help me out where i am wrong. Thanks in advance

  • March 15, 2011 at 8:02 pm

    #1299190

    jscot91 (3/15/2011)

    Here is my source data

    Description

    D-1,D2

    D-2,D-U,T1

    U-5,T2,K-5,T3

    NULL

    Here is the expressions i am using in "Derived Column"

    Col1 = SUBSTRING(Description,1,FINDSTRING(Description,",",1) - 1)

    Col2 = SUBSTRING(Description,FINDSTRING(Description,",",1) + 1,FINDSTRING(Description,",",2) - FINDSTRING(Description,",",1) - 1)

    Col3 = SUBSTRING(Description,FINDSTRING(Description,",",2) + 1,LEN(Description))

    Col4 = Need help for expression

    Note:- I am receiving error "Disposition" Error. Please help me out where i am wrong. Thanks in advance

    No reflection on you but a lot of folks make some pretty good mistakes in their functions without even realizing it. Please post the code for the FINDSTRING function you're using.

    Also, please verify that you're using SQL Server 2005.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 15, 2011 at 8:17 pm

    #1299192

    Thanks for prompt reply, I am using SQL Server 2008. I am sorry i don't understand which code? I am using SSIS and this expression in derived column transformation. Thanks.

  • March 15, 2011 at 11:33 pm

    #1299216

    Hi,

    Data of this kind could be broke down using split function available in VB.Net.Hence instead of using derived column transformation with substring function , same result would be obtained by using script component by chopping input string using delimiter (comma in your case).

    Thanks and Regards,

    Gurulakshmanaprabhu Gurusamy

  • March 16, 2011 at 6:06 am

    #1299298

    Gurulakshmanaprabhu Gurusamy (3/15/2011)

    Hi,

    Data of this kind could be broke down using split function available in VB.Net.Hence instead of using derived column transformation with substring function , same result would be obtained by using script component by chopping input string using delimiter (comma in your case).

    Thanks and Regards,

    Gurulakshmanaprabhu Gurusamy

    Yes it could. Why anyone would need to step outside of SQL for a simple problem like this is another story. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 16, 2011 at 6:09 am

    #1299300

    jscot91,

    We need to know something to wrap it up with code, though. What is the code for FINDSTRING?

    We're just trying to help here.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 16, 2011 at 6:14 am

    #1299301

    Description

    D-1,D2

    D-2,D-U,T1

    U-5,T2,K-5,T3

    NULL

    Is that real sample data? Or have you tidied it up to remove trailing commas, perhaps?

    If it is real, you are going to get errors with your derived columns in cases where there are fewer than four fields.

    One option may be to pre-process the file and add in the necessary number of trailing commas, then let SSIS import it as a standard CSV file. Then SSIS gets to do the parsing for you.

    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.

  • March 16, 2011 at 6:17 am

    #1299303

    Jeff

    FINDSTRING is a built-in SSIS function, see here.

    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.

  • March 16, 2011 at 7:29 pm

    #1299756

    Phil Parkin (3/16/2011)

    Jeff

    FINDSTRING is a built-in SSIS function, see here.

    Ah! Thanks, Phil... That's what I get when I post on a forum for something I don't use. :blush: It appears to be similar to CHARINDEX in T-SQL with the added sophistication of being able to indentify which occurance you want to find. Thanks again.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 16, 2011 at 7:32 pm

    #1299759

    Ok... I'm pretty much ignorant of SSIS. My question at this point is why couldn't one of the common split functions written in T-SQL work here for the OP?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 16, 2011 at 7:51 pm

    #1299765

    Jeff Moden (3/16/2011)

    Ok... I'm pretty much ignorant of SSIS. My question at this point is why couldn't one of the common split functions written in T-SQL work here for the OP?

    Because you can't use a function from SQL in a derived column, and to try to do so would interrupt the stream, forcing a block at a point, slowing the process down.

    OP: Can you post the full error you're getting in the debug output? Or from the errors list if it's not compiling at all?

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 16, 2011 at 8:13 pm

    #1299768

    Nevermind, I see the problem. You don't have evaluations in your strings, as Phil mentioned earlier. You need to test findstring to see if it found the character

    IE: FINDSTRING( col, ",", 3) != 0 (Yes, SSIS still uses this archaic form of not equals)

    then use the if/then/else structure to deal with it:

    fxn == 0 ? dt_null : substring()

    Replace FXN with your finding function, the substring with the extractor function, and (dt_null) with the nullable datatype version of whatever string format you want in the string (you can find them in the datatypes node in the upper right).

    You may, if you don't have trailing commas, need to test the tail of the string in a nested if function (which is described by the ? : structure) to see what form of the substring function you'll need to use.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 17, 2011 at 6:30 am

    #1299898

    Craig Farrell (3/16/2011)

    Jeff Moden (3/16/2011)

    Ok... I'm pretty much ignorant of SSIS. My question at this point is why couldn't one of the common split functions written in T-SQL work here for the OP?

    Because you can't use a function from SQL in a derived column, and to try to do so would interrupt the stream, forcing a block at a point, slowing the process down.

    Really?! Don't ya just love the interoperability between SQL Server products? 😛 Thanks, Craig.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 17, 2011 at 6:37 am

    #1299900

    Yes, SSIS still uses this archaic form of not equals

    Not that archaic ... so does C#.NET 🙂

    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.

  • March 17, 2011 at 6:49 am

    #1299905

    Jeff Moden (3/16/2011)

    jscot91,

    We need to know something to wrap it up with code, though. What is the code for FINDSTRING?

    We're just trying to help here.

    Jeff, stop interfering in SSIS threads. 😛 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 22 total)

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