Hi,
I am having a sequential file which is Tab delimited.
The records in the file is as follows:
Rec# AcNo Value1 Value2 Value3 Value4
1 100 111 112 113 114
2 200 211 212 213 214
3 300 311 312
313 314
4 400 411 412 413 414
5 500 511 512 513 514
6 600 611 612
613 614
For the 3rd record, the value of value3 column is moved into next line instead of being in the same line and that column and the remaining columns are Null for this record. Because of this problem,
the fourth row is treated as 4th column while reading which is having the AcNo as Null. But the actual 4th record AcNo is 400.
I want the data of the 4th row should be treated as 3rd record while reading.
Please help me how to handle this stalemate situation.
Thx in Advance.
With Lv,
Selva
Reading a record from Tab delimited sequential file
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 47
- Joined: Fri Sep 23, 2005 6:01 pm
-
- Charter Member
- Posts: 130
- Joined: Mon Sep 06, 2004 3:05 am
- Location: Dubai,UAE
Hi bharathappriyan,
Your Input:
Field1,Field2,Field3,Field4,Field5,Field6
1 100 111 112 113 114
2 200 211 212 213 214
3 300 311 312
313 314
4 400 411 412 413 414
5 500 511 512 513 514
6 600 611 612
613 614
Your file is messed up and you will have to correct it by getting the 4th row up to the 3rd row.
If this is your requirement then you can try the below method to try to correct your input file
<Step 1>
Declare Stage Variables:
StageVar1 = IF IsNull(DSLink2.Field3) THEN StageVar2:',':DSLink2.Field1:',': DSLink2.Field2 ELSE ''
StageVar2 = If IsNull(DSLink2.Field5) AND Not(IsNull(DSLink2.Field4)) THEN DSLink2.Field1:',': DSLink2.Field2:',': DSLink2.Field3:',': DSLink2.Field4 ELSE ''
<Step 2>
Define Transformer Constaint as Len(StageVar2)=0
<Step 3>
Your Input file has one column
OutputField=if Len(StageVar1)>1 then StageVar1 else DSLink2.Field1:',':DSLink2.Field2:',':DSLink2.Field3:',':DSLink2.Field4:',':DSLink2.Field5:',':DSLink2.Field6
Now browse to the location where this output file is located and open it and you will see the output as
1,100,111,112,113,114
2,200,211,212,213,214
3,300,311,312,313,314
4,400,411,412,413,414
5,500,511,512,513,514
6,600,611,612,613,614
You can use a Sequential File stage to read the data correctly
If you do not want ',' as the delimiter then you can replace the delimiter in the above code with your required delimiter.
Hope this helps
Your Input:
Field1,Field2,Field3,Field4,Field5,Field6
1 100 111 112 113 114
2 200 211 212 213 214
3 300 311 312
313 314
4 400 411 412 413 414
5 500 511 512 513 514
6 600 611 612
613 614
Your file is messed up and you will have to correct it by getting the 4th row up to the 3rd row.
If this is your requirement then you can try the below method to try to correct your input file
<Step 1>
Declare Stage Variables:
StageVar1 = IF IsNull(DSLink2.Field3) THEN StageVar2:',':DSLink2.Field1:',': DSLink2.Field2 ELSE ''
StageVar2 = If IsNull(DSLink2.Field5) AND Not(IsNull(DSLink2.Field4)) THEN DSLink2.Field1:',': DSLink2.Field2:',': DSLink2.Field3:',': DSLink2.Field4 ELSE ''
<Step 2>
Define Transformer Constaint as Len(StageVar2)=0
<Step 3>
Your Input file has one column
OutputField=if Len(StageVar1)>1 then StageVar1 else DSLink2.Field1:',':DSLink2.Field2:',':DSLink2.Field3:',':DSLink2.Field4:',':DSLink2.Field5:',':DSLink2.Field6
Now browse to the location where this output file is located and open it and you will see the output as
1,100,111,112,113,114
2,200,211,212,213,214
3,300,311,312,313,314
4,400,411,412,413,414
5,500,511,512,513,514
6,600,611,612,613,614
You can use a Sequential File stage to read the data correctly
If you do not want ',' as the delimiter then you can replace the delimiter in the above code with your required delimiter.
Hope this helps
Warm Regards,
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: