Finding it impossible to Split a column based on a delimiter

Question

Finding it impossible to Split a column based on a delimiter

ashok.theagarajan

SSC-Addicted

Points: 421
More actions
August 18, 2011 at 7:34 am

#150456

Hi Everyone
I have an input column in an Excel Source, which is of type DT_NTEXT. Inside the data, there is a delimiter text which says "<Break Here>".
I have to split the column into two values. One before the delimiter and one after.
I am trying to write a script component, but am stuck with no idea.
Can some one please help as this helps a lot in automating an entire download - upload process.
Regards
Ashok T

Viewing 12 posts - 1 through 11 (of 11 total)

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

ashok.theagarajan (8/18/2011)
Hi Everyone
I have an input column in an Excel Source, which is of type DT_NTEXT. Inside the data, there is a delimiter text which says "<Break Here>".
I have to split the column into two values. One before the delimiter and one after.
I am trying to write a script component, but am stuck with no idea.
Can some one please help as this helps a lot in automating an entire download - upload process.
Regards
Ashok T

I would use a Derived Column instead of a script and use Findstring, SubString, and Right to find the <Break Here> to make 2 new columns.

ashok.theagarajan SSC-Addicted Points: 421 More actions · Answer 2

Hi.

Thanks for the reply. I tried but it keeps saying that DT_NTEXT cannot be used for those string manipulation functions.....

Any idea on how to get over it..

Thanks again

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 3

Here's an example using a Script component in the data flow task to do custom splits: [/url]. Can you see if that helps?

MWise

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

ashok.theagarajan (8/18/2011)
Hi.
Thanks for the reply. I tried but it keeps saying that DT_NTEXT cannot be used for those string manipulation functions.....
Any idea on how to get over it..
Thanks again

Cast it as another datatype DT_STR or DT_WSTR and then use the string functions

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

ashok.theagarajan (8/18/2011)
Hi Everyone
I have an input column in an Excel Source, which is of type DT_NTEXT. Inside the data, there is a delimiter text which says "<Break Here>".
I have to split the column into two values. One before the delimiter and one after.
I am trying to write a script component, but am stuck with no idea.
Can some one please help as this helps a lot in automating an entire download - upload process.
Regards
Ashok T

Regarding the synchronous transformation script component, check out this link:

http://www.sqlis.com/sqlis/post/The-Script-Component-as-a-Transformation.aspx

It's an excellent walkthrough of the process.

Now, regarding the NText stream... yeaaaah, you've got a concern here. You can pull the NText into a string variable, perform the necessary tasks, and redump, but that can be HUGE. Just be prepared for that and set your output columns appropriately... IE: NText. Or make sure you put checks and restrictors on the output from the transformation before you include them in the output columns.

- 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

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 6

I would just pork chop the provider of the spreadsheet and get them to do things right. 😉

--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)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

Jeff Moden (8/18/2011)
I would just pork chop the provider of the spreadsheet and get them to do things right. 😉

The problem here isn't the providers, it's Excel. Once you crack 255 characters in a column it shifts from nVARCHAR(255) to nTEXT. That's just Excel. Not really much you can do about it but avoid the software entirely.

- 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

ashok.theagarajan SSC-Addicted Points: 421 More actions · Answer 8

Sorry could not post before as was held up at work.

Got the issue resolved. Took the data into a temporary table and then did the conversion from text to string...

Luckily, the data is just a couple of 10000 rows only...

Thanks everyone

Cheers.....

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 9

Evil Kraig F (8/22/2011)
Jeff Moden (8/18/2011)
I would just pork chop the provider of the spreadsheet and get them to do things right. 😉
The problem here isn't the providers, it's Excel. Once you crack 255 characters in a column it shifts from nVARCHAR(255) to nTEXT. That's just Excel. Not really much you can do about it but avoid the software entirely.

Hmmm.. Is it Excel or is it SSIS?.

--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)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

Jeff Moden (8/24/2011)
Evil Kraig F (8/22/2011)
Jeff Moden (8/18/2011)
I would just pork chop the provider of the spreadsheet and get them to do things right. 😉
The problem here isn't the providers, it's Excel. Once you crack 255 characters in a column it shifts from nVARCHAR(255) to nTEXT. That's just Excel. Not really much you can do about it but avoid the software entirely.
Hmmm.. Is it Excel or is it SSIS?.

I suppose it would be best to say it's the drivers handling the connection, as I haven't used much else (besides VBA which doesn't row-sample, you have to define it) to connect to Excel in this method.

- 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

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 11

Evil Kraig F (8/25/2011)
Jeff Moden (8/24/2011)
Evil Kraig F (8/22/2011)
Jeff Moden (8/18/2011)
I would just pork chop the provider of the spreadsheet and get them to do things right. 😉
The problem here isn't the providers, it's Excel. Once you crack 255 characters in a column it shifts from nVARCHAR(255) to nTEXT. That's just Excel. Not really much you can do about it but avoid the software entirely.
Hmmm.. Is it Excel or is it SSIS?.
I suppose it would be best to say it's the drivers handling the connection, as I haven't used much else (besides VBA which doesn't row-sample, you have to define it) to connect to Excel in this method.

I agree with you. It's one of the many reasons that we ask all of our vendors and clients to use csv or pipe delimited files instead. Excel imports have caused me much hair pulling and nail biting in the past.

MWise