Supress extra Carriage returns
Moderators: chulett, rschirm, roy
Supress extra Carriage returns
Hi All,
I am extracting data from SQL server into a sequential file and then loading that into an Oracle table. My problem is, for some of the rows, the data contains an extra carriage return. Can anyone guide me on how to supress the extra carriage return and also how to supress an extra pair of CR-LF from the data. I tried to use EREPLACE and replaced the character with space and then used TRIM function. But, that doesn't solve my purpose. Any thoughts??
I am extracting data from SQL server into a sequential file and then loading that into an Oracle table. My problem is, for some of the rows, the data contains an extra carriage return. Can anyone guide me on how to supress the extra carriage return and also how to supress an extra pair of CR-LF from the data. I tried to use EREPLACE and replaced the character with space and then used TRIM function. But, that doesn't solve my purpose. Any thoughts??
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
I was able to resolve the Issue with the following function
I didn't include char(10) earlier and hence the problem.
Code: Select all
Trim(Ereplace(input.Link, Char(13): Char(10) ,"",-1,0))
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
I am back to where I was
I need to supress or trim only the extra carriage returns.But, what seems to happen is all the terminators within the data are being converted into a space and hence I have data which is supposed to be in three lines smushed into a single line. It would be great if anyone could throw some light on how to trim only the extra CR-LF at the end of the sentence or data and retain the one's which are in between the data.
I need to supress or trim only the extra carriage returns.But, what seems to happen is all the terminators within the data are being converted into a space and hence I have data which is supposed to be in three lines smushed into a single line. It would be great if anyone could throw some light on how to trim only the extra CR-LF at the end of the sentence or data and retain the one's which are in between the data.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
You could always look at the last two characters and remove them if they are <CR><LF>. You could write a DS function that repeatedly strips off the last two characters of a string until the last two characters are not <CR><LF>. Something like (off the top of my head, please test):
Code: Select all
Text=Arg1
Done = @FALSE
Loop UNTIL Done Do
If RIGHT(Text,2)=CHAR(13):CHAR(10) Then Text=TRIM(Text[1,LEN(Text)-2]) Else Done = @TRUE
Repeat
Ans = Text
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
I'm not sure what your data volume is, but if there aren't many records, you could read the data as one big column. In our Sequential File Stage under Stage tab. There is a Line Termination selection. Select "None" and read all lines into 1 column.
In your transformer you can use the convert function as follows
Convert(char(13):char(10):char(13):char(10),char(13):char(10),input column)
In your transformer you can use the convert function as follows
Convert(char(13):char(10):char(13):char(10),char(13):char(10),input column)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
Ereplace(InLink.TheString,svCRLF,svLF,1,Count(InLink.TheString,svCRLF))
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Ray,
I get the following error while running the Job after I used the derivation
The lines around line number 362 in the RT_BP185 folder go like this
The field I am using the derivation is an Address field and of datatype Varchar. I don't get it why the Job is aborting. Any insight would be helpful.
I get the following error while running the Job after I used the derivation
Code: Select all
DataStage Job 185 Phantom 3412
Program "JOB.47473586.DT.1398128902.TRANS1": Line 362, Improper data type.
Attempting to Cleanup after ABORT raised in stage
Code: Select all
V0S1.Stagevar%%22 = Char(13) : Char(10)
V0S1.Stagevar%%23 = Char(10)
IF STAGECOM.TRACE.STATS THEN CALL $PERF.END(-2)
STAGECOM.PINNO = 2
IF STAGECOM.TRACE.STATS THEN CALL $PERF.BEGIN(-3)
IF V0S1.Stagevar%%21 = 0 THEN
* Column derivation code for pin out_address
Pin%%V0S1P2.Column%%5 = (Ereplace(Pin%%V0S1P1.Column%%5, V0S1.Stagevar%%22, V0S1.Stagevar%%23, 1, Count(Pin%%V0S1P1.Column%%5, V0S1.Stagevar%%22)))
Pin%%V0S1P2.Column%%9 = (Trim(Pin%%V0S1P1.Column%%9))
Pin%%V0S1P2.Column%%11 = (Trim(Ereplace(Pin%%V0S1P1.Column%%11, Char(13) : Char(10), "", -1, 0)))
Pin%%V0S1P2.Column%%17 = (Field(Pin%%V0S1P1.Column%%17, ".", 1, 1))
Pin%%V0S1P2.Column%%19 = (Field(Pin%%V0S1P1.Column%%19, ".", 1, 1))
Pin%%V0S1P2.Column%%23 = (Field(OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : ".000", ".", 1, 1))
Pin%%V0S1P2.Column%%24 = ('ADS')
Pin%%V0S1P2.REJECTED = @FALSE
IF STAGECOM.TRACE.STATS THEN CALL $PERF.END(-3)
The field I am using the derivation is an Address field and of datatype Varchar. I don't get it why the Job is aborting. Any insight would be helpful.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Kris,
I can't see which line in the code you posted might be causing the invalid datatype error. The line number that the compiler thinks is at fault is often not correct, this is due to a limitation in the compiler (this problem has been around since over 20 years!).
If you've done this much work already and seeing the generated BASIC code didn't frighten you off I'll suggest one step further.
go into TCL in your project and the your Job's JobNo if you don't know if already. You can do this with a LIST DS_JOBS {JobName} JOBNO and then issue the command VLIST RT_BP{JobNo} JOB.47473586.DT.1398128902.TRANS1
This will invoke listing your job with the opcodes as well as the line numbers that the compiler is using. Page through until you get to your error line 362 and see if you can spot the potential problem there, or post that line.
I can't see which line in the code you posted might be causing the invalid datatype error. The line number that the compiler thinks is at fault is often not correct, this is due to a limitation in the compiler (this problem has been around since over 20 years!).
If you've done this much work already and seeing the generated BASIC code didn't frighten you off I'll suggest one step further.
go into TCL in your project and the your Job's JobNo if you don't know if already. You can do this with a LIST DS_JOBS {JobName} JOBNO and then issue the command VLIST RT_BP{JobNo} JOB.47473586.DT.1398128902.TRANS1
This will invoke listing your job with the opcodes as well as the line numbers that the compiler is using. Page through until you get to your error line 362 and see if you can spot the potential problem there, or post that line.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
ArndW
Looks like the Count function in the Derivation
is causing the problem.
Here are the lines when I run the statement from TCL in my Project
I am not sure what is causing the problem here. Any insights would be helpful.
Thanks
Looks like the Count function in the Derivation
Code: Select all
Ereplace(InLink.TheString,svCRLF,svLF,1,Count(InLink.TheString,svCRLF))
Here are the lines when I run the statement from TCL in my Project
Code: Select all
00362: Pin%%V0S2P2.Column%5 = ( Ereplace(Pin%%V0S2P1.Column%%5, V0S2.Stagevar%%1, V0S2.Stagevar%2, 1, Count(Pin%%V0S2P1.Column%%5, V0S2.Stagevar%%1)))
00362 00234 : 0E6 matrix STAGECOM.ARR 7 1 =>$R7
00362 0023E : 0E6 matrix STAGECOM.ARR 1 1 =>$R8
00362 00248 : 0E6 matrix STAGECOM.ARR 2 1 =>$R9
00362 00252 : 0E6 matrix STAGECOM.ARR 7 1 =>$R10
00362 0025C : 0E6 matrix STAGECOM.ARR 1 1 =>$R11
00362 00266 : 040 count $R10 $R11 =>$R12
00362 0026E : 24A ereplace $R7 $R8 $R9 $R12 =>$R13
00362 0027C : 0FC move_matrix $R13 27 1 =>STAGECOM.ARR
Thanks
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is no way you can say that the Count() function is causing the problem.
Element 1 of STAGECOM.ARR contains InLink.TheString (by now a variable named Pin%%V0S1P1.Column%%5); this is moved into register #11 (named $R11) for the Count() function. The value of the stage variable svCRLF (by now a variable named V0S1.Stagevar%%22) is mapped into the second element of STAGECOM.ARR and is moved into register #12 (named $R12) for the Count() function. $R11 and $R12 are used as the arguments to the Count() function, the result of which is loaded into register #13 (named $R13) which is subsequently used in the Ereplace() function.
To answer the original question, if the additional CRLF is at the end of the data then you can simply return a shorter string:
This is unrelated to "invalid data type"; this usually arises from invalid use of file or subroutine variables. You would need to post the entire VLIST report for us to comment further.
BTW the expression Field(OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : ".000", ".", 1, 1) is doing too much work. There is no "." in the first argument of the Field() function. Oconv(Date(),"D-YMD[4,2,2]") : " " : Oconv(Time(), "MTS:") would suffice. If you require the date and time to be unchanging, prefer @DATE to Date() and @TIME to Time() and, for maximum efficiency, calculate it once only, in initializing a stage variable, perhaps called svNowAsTimestamp.
Element 1 of STAGECOM.ARR contains InLink.TheString (by now a variable named Pin%%V0S1P1.Column%%5); this is moved into register #11 (named $R11) for the Count() function. The value of the stage variable svCRLF (by now a variable named V0S1.Stagevar%%22) is mapped into the second element of STAGECOM.ARR and is moved into register #12 (named $R12) for the Count() function. $R11 and $R12 are used as the arguments to the Count() function, the result of which is loaded into register #13 (named $R13) which is subsequently used in the Ereplace() function.
To answer the original question, if the additional CRLF is at the end of the data then you can simply return a shorter string:
Code: Select all
Left(InLink.TheString, Len(InLink.TheString-2))
BTW the expression Field(OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:") : ".000", ".", 1, 1) is doing too much work. There is no "." in the first argument of the Field() function. Oconv(Date(),"D-YMD[4,2,2]") : " " : Oconv(Time(), "MTS:") would suffice. If you require the date and time to be unchanging, prefer @DATE to Date() and @TIME to Time() and, for maximum efficiency, calculate it once only, in initializing a stage variable, perhaps called svNowAsTimestamp.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
If anyone cares, the original problem was MULTIPLE CR/LF at the END of the string, so you need to remove as many as you can from the end, preserving the rest of the formatting. My original answer still looks to be the only one that solves that issue.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Yes Ken. I am also of the same opinion. That looks like the solution as of now.
Even Ray's solution looked good ( in fact perfect) to me until I ran the job. but I am not sure why it isn't working. I don't have access to Datastage now. I shall post the whole VLIST and see if it tells us anything ASAP.
And Ray, doesn't fullfill my requirment as not all the rows contain extra CRLF's. It's only a few of them.
Even Ray's solution looked good ( in fact perfect) to me until I ran the job. but I am not sure why it isn't working. I don't have access to Datastage now. I shall post the whole VLIST and see if it tells us anything ASAP.
And Ray,
Code: Select all
Left(InLink.TheString, Len(InLink.TheString-2))
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson