|
Home > Archive > BizTalk Server > January 2006 > question: schema for a complex flat file
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
question: schema for a complex flat file
|
|
| Karen M 2006-01-16, 5:54 pm |
| Hello.
I'm using BizTalk server 2006 try to parse a flat file which contains
different type of records and each type have different formats(Records
started by "T" belongs to records started by "M" and both have different
types), can anybody help on how to creat a schema for it?
<here is what the flat file looks like>
--------------
Header
M,E17570850BP
,01,E,175,70850,BP,GBP,-0000000003890.54,+0000000000000.00,+0000000000000.00,+0000000000000.00,+0000000000000.00,
M,E17570850BP
,02,+0000000000000.00,-0000000003890.54,-0000000003890.54,+0000000000000.00,-0000000003890.54,+0000000000000.00,
M,E17570850BP
,03,+0000000000000.00,+0000000000000.00,-0000000003890.54,+0000000000000.00,+0000000000000.00,+0000000000000.00,
T,P,5K22 CECO0,01,E,175,70850,04,HG , ,+0000000.00000000,F,200512,
,20051122,+0000195.90000000,1,+00000000001.0000,S, ,20051122,
T,P,5K22 CECO0,02,+0000195.90000000,195.9000000,10VAL,F1,6, ,
,USD,US,0.0000,F, , ,00000250.0000000, , ,USD, ,
.......
T,P,5K22 CECO0,20,DEC 05 CMX HG COPPER ,
, ,.DHG0000005L, ,
T,P,5K22 B77I0,01,E,175,70850,04,HG , ,+0000000.00000000,F,200512,
,20051122,+0000196.20000000,1,+00000000001.0000,S, ,20051122,
T,P,5K22 B77I0,02,+0000196.20000000,196.2000000,10VAL,F1,6, ,
,USD,US,0.0000,F, , ,00000250.0000000, , ,USD, ,
.......
T,P,5K22 B77I0,20,DEC 05 CMX HG COPPER ,
, ,.DHG0000005L, ,
T,T,5L23 BCMU0,01,E,175,70850,02,48 , ,+0000000.00000000,F,200606,
,20051223,+0000086.45000000,1,+00000000001.0000, , ,20051223,
T,T,5L23 BCMU0,02,+0000086.45000000, 86.45 ,10VAL,F1, , ,
,USD,US,0.0000,F,Y,N,00000400.0000000, , ,USD, ,
.......
T,T,5L23 BCMU0,20,JUN 06 CME CATTLE ,
, ,.B480000006F, ,
M,E71767240F1
,01,E,717,67240,F1,USD,+0000002251576.27,+0000000517337.70,+0000000000000.00,+0000000000000.00,+0000000000000.00,
M,E71767240F1
,02,+0000000000000.00,+0000002768913.97,+0000002768913.97,+0000000631175.97,+0000003153725.03,-0000000031137.94,
M,E71767240F1
,03,+0000002137738.00,+0000001586224.00,+0000000631175.97,+0000000000000.00,+0000000000000.00,+0000000000000.00,
T,P,5L13 J280,01,E,717,67240,14,CD , ,+0000000.00000000,F,200603,
,20051213,+0001205.00000000,1,+00000000015.0000, , ,20051213,
T,P,5L13 J280,02,+0001205.00000000, 1205.00 ,C0L0A,F2, , ,
,USD,US,0.0000,F, , ,00000005.0000000, ,D,USD, ,
.......
T,P,5L13 J280,20,MAR 06 LDN COFFEE RO ,
, ,.NCD0000006C, ,
T,T,5L23 HF00,01,E,717,67240,14,CD , ,+0000000.00000000,F,200603,
,20051223,+0001169.00000000,1,+00000000002.0000, , ,20051223,
T,T,5L23 HF00,02,+0001169.00000000, 1169.00 ,C0L0A,F2, , ,
,USD,US,0.0000,F,Y,N,00000005.0000000, ,D,USD, ,
.......
T,T,5L23 HF00,20,MAR 06 LDN COFFEE RO ,
, ,.NCD0000006C, ,
T,L,5K18 JF10,01,E,717,67240,19,GL , ,+0000000.00000000,F,200601,
,20051118,+0000532.50000000,1,+00000000001.0000, , ,20051118,
T,L,5K18 JF10,02,+0000532.50000000, 532.50 ,C0L0A,F2, , ,
,USD,US,0.0000,F,N,Y,00000100.0000000, , ,USD, ,
T,L,5K18 JF10,03, , , ,AEJT, ,
, , ,4, , , , , , , , , ,
.......
T,L,5K18 JF10,20,JAN 06 IPE GAS OIL ,
, ,.SGL0000006A, ,
T,T,5L23 JBU0,01,E,717,67240,19,GL , ,+0000000.00000000,F,200601,
,20051223,+0000511.00000000,2,+00000000001.0000, , ,20051223,
T,T,5L23 JBU0,02,+0000511.00000000, 511.00 ,C0L0A,F2, , ,
,USD,US,0.0000,F,Y,N,00000100.0000000, , ,USD, ,
.......
T,T,5L23 JBU0,20,JAN 06 IPE GAS OIL ,
, ,.SGL0000006A, ,
Footer
Thanks~~
| |
| Jay Kinker 2006-01-19, 2:56 am |
| Hi Karen,
You can create a positional schema.
Set the value of property (for <schema> ) "Schema Editor Extensions" to "Flat
File Extension".
For child nodes you can set property "Structure" to "Delimited"
For delimeter between two Lines set property "Child Delimiter Type" to
"Hexadecimal" and "Child Delimiter" to "0x0D 0x0A"
Below links should be helpful for you as it contains the required steps in
detail:
http://www.winterdom.com/dev/bts/BTSFFSchemaTut1.html
http://www.winterdom.com/dev/bts/BTSFFSchemaTut2.html
Regards,
Jay Kinker__
------------------------------------------------------------------------------
"Karen M" wrote:
> Hello.
>
> I'm using BizTalk server 2006 try to parse a flat file which contains
> different type of records and each type have different formats(Records
> started by "T" belongs to records started by "M" and both have different
> types), can anybody help on how to creat a schema for it?
>
> <here is what the flat file looks like>
> --------------
> Header
> M,E17570850BP
> ,01,E,175,70850,BP,GBP,-0000000003890.54,+0000000000000.00,+0000000000000.00,+0000000000000.00,+0000000000000.00,
> M,E17570850BP
> ,02,+0000000000000.00,-0000000003890.54,-0000000003890.54,+0000000000000.00,-0000000003890.54,+0000000000000.00,
> M,E17570850BP
> ,03,+0000000000000.00,+0000000000000.00,-0000000003890.54,+0000000000000.00,+0000000000000.00,+0000000000000.00,
> T,P,5K22 CECO0,01,E,175,70850,04,HG , ,+0000000.00000000,F,200512,
> ,20051122,+0000195.90000000,1,+00000000001.0000,S, ,20051122,
> T,P,5K22 CECO0,02,+0000195.90000000,195.9000000,10VAL,F1,6, ,
> ,USD,US,0.0000,F, , ,00000250.0000000, , ,USD, ,
> ......
> T,P,5K22 CECO0,20,DEC 05 CMX HG COPPER ,
> , ,.DHG0000005L, ,
> T,P,5K22 B77I0,01,E,175,70850,04,HG , ,+0000000.00000000,F,200512,
> ,20051122,+0000196.20000000,1,+00000000001.0000,S, ,20051122,
> T,P,5K22 B77I0,02,+0000196.20000000,196.2000000,10VAL,F1,6, ,
> ,USD,US,0.0000,F, , ,00000250.0000000, , ,USD, ,
> ......
> T,P,5K22 B77I0,20,DEC 05 CMX HG COPPER ,
> , ,.DHG0000005L, ,
> T,T,5L23 BCMU0,01,E,175,70850,02,48 , ,+0000000.00000000,F,200606,
> ,20051223,+0000086.45000000,1,+00000000001.0000, , ,20051223,
> T,T,5L23 BCMU0,02,+0000086.45000000, 86.45 ,10VAL,F1, , ,
> ,USD,US,0.0000,F,Y,N,00000400.0000000, , ,USD, ,
> ......
> T,T,5L23 BCMU0,20,JUN 06 CME CATTLE ,
> , ,.B480000006F, ,
> M,E71767240F1
> ,01,E,717,67240,F1,USD,+0000002251576.27,+0000000517337.70,+0000000000000.00,+0000000000000.00,+0000000000000.00,
> M,E71767240F1
> ,02,+0000000000000.00,+0000002768913.97,+0000002768913.97,+0000000631175.97,+0000003153725.03,-0000000031137.94,
> M,E71767240F1
> ,03,+0000002137738.00,+0000001586224.00,+0000000631175.97,+0000000000000.00,+0000000000000.00,+0000000000000.00,
> T,P,5L13 J280,01,E,717,67240,14,CD , ,+0000000.00000000,F,200603,
> ,20051213,+0001205.00000000,1,+00000000015.0000, , ,20051213,
> T,P,5L13 J280,02,+0001205.00000000, 1205.00 ,C0L0A,F2, , ,
> ,USD,US,0.0000,F, , ,00000005.0000000, ,D,USD, ,
> ......
> T,P,5L13 J280,20,MAR 06 LDN COFFEE RO ,
> , ,.NCD0000006C, ,
> T,T,5L23 HF00,01,E,717,67240,14,CD , ,+0000000.00000000,F,200603,
> ,20051223,+0001169.00000000,1,+00000000002.0000, , ,20051223,
> T,T,5L23 HF00,02,+0001169.00000000, 1169.00 ,C0L0A,F2, , ,
> ,USD,US,0.0000,F,Y,N,00000005.0000000, ,D,USD, ,
> ......
> T,T,5L23 HF00,20,MAR 06 LDN COFFEE RO ,
> , ,.NCD0000006C, ,
> T,L,5K18 JF10,01,E,717,67240,19,GL , ,+0000000.00000000,F,200601,
> ,20051118,+0000532.50000000,1,+00000000001.0000, , ,20051118,
> T,L,5K18 JF10,02,+0000532.50000000, 532.50 ,C0L0A,F2, , ,
> ,USD,US,0.0000,F,N,Y,00000100.0000000, , ,USD, ,
> T,L,5K18 JF10,03, , , ,AEJT, ,
> , , ,4, , , , , , , , , ,
> ......
> T,L,5K18 JF10,20,JAN 06 IPE GAS OIL ,
> , ,.SGL0000006A, ,
> T,T,5L23 JBU0,01,E,717,67240,19,GL , ,+0000000.00000000,F,200601,
> ,20051223,+0000511.00000000,2,+00000000001.0000, , ,20051223,
> T,T,5L23 JBU0,02,+0000511.00000000, 511.00 ,C0L0A,F2, , ,
> ,USD,US,0.0000,F,Y,N,00000100.0000000, , ,USD, ,
> ......
> T,T,5L23 JBU0,20,JAN 06 IPE GAS OIL ,
> , ,.SGL0000006A, ,
> Footer
>
> Thanks~~
| |
| Karen M 2006-01-19, 6:07 pm |
| Thank you Jay!
My file is more complicated, there should be two groups(not including header
and footer), each group has different types and each type has different
formats, and the identifiers are not only at the beginning of the record but
also in the middle somewhere so I have to use Positional structure~
Best regards,
Karen
"Jay Kinker" wrote:
> Hi Karen,
>
> You can create a positional schema.
>
> Set the value of property (for <schema> ) "Schema Editor Extensions" to "Flat
> File Extension".
> For child nodes you can set property "Structure" to "Delimited"
>
> For delimeter between two Lines set property "Child Delimiter Type" to
> "Hexadecimal" and "Child Delimiter" to "0x0D 0x0A"
>
> Below links should be helpful for you as it contains the required steps in
> detail:
>
> http://www.winterdom.com/dev/bts/BTSFFSchemaTut1.html
> http://www.winterdom.com/dev/bts/BTSFFSchemaTut2.html
>
>
> Regards,
>
> Jay Kinker__
>
|
|
|
|
|