May 31, 2012 at 7:58 am
I hope someone can help me. This is driving me nuts.
I've got a big file that is tilda delimited. Within one of those tilda delimited sections is a bunch of columns that my vendor has shoved together in 1 giant string. (EDIT: I have 2 columns in my source. 1 is the RecordType, and 2 is AllOtherData because the different records have different lengths & datatypes. The below question deals with one specific recordtype).
So I'm doing a Derived Column Transformation to pull out the data in the proper columns. The last column before the third (and last) tilda could be anything between 1 to 10 digits. So I got the bright idea to use FINDSTRING() as the final character in a SUBSTRING() search.
Here's the code:
SUBSTRING(AllOtherData,382,(FINDSTRING(AllOtherData,"~",3) - 1))
--Sob. There's an error in here somewhere
I use the same expression, with a +1 instead as the start character, for the column after the tilda.
SUBSTRING(AllOtherData,(FINDSTRING(AllOtherData,"~",3) + 1),1000)
--This one works properly!
The top bit pulls not only my value, but the last column as well. Here's the last bit of the text file line in question:
100000~Warning: Zip Code received: 07126 is invalid.
I should be getting "100000" in one column and "Warning: Zip Code received: 07126 is invalid." in a second column. I get the second column correct, but in the first column, I get everything that I quoted. And I cannot figure out why my code is wrong. I counted the parens, I checked the commas. I know there's an error there, but I cannot see it.
Can anyone point it out to me? I've been looking at this stupid thing too long.
May 31, 2012 at 11:18 am
DOH!
Nevermind. I found the issue. I got my subtraction in the end length wrong.
Code is now:
(DT_STR,10,1252)SUBSTRING(AllOtherData,382,FINDSTRING(AllOtherData,"~",3) - 382)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply