Flat file schema wizard

[Star0110]

Hi,

I'm new to Biztalk and would like to know how to create a flat file schema
from the instance below:
00|SR5N|TEST|PURCHASE RECEIPT|
19|1|7777|2|XX|3|BB|4|A1|5|LFL_FEED|6|20/11/08|7|PRODUCT|8|XXX|

the first lone is some kind of header with identifier 00
the second line is the details part with identifier 19
Each detail line has fields and each field has an identifier, e.g. |1| for
PO NUmber, |2| for PO line, |7| for product, etc.

Thank you in advance.

[Dan Rosanova]

This looks pretty strait forward. Get a sample of one of your
messages and then delete everything but the first two lines. Use the
Add Generated Items to create the flat file schema. Select the
example file and then in the wizard select both lines and use
delimited. CRLF will be your delimiter. Then it will ask you do
define records. Your first line will be a Record (string is default)
and second one Repeating Record. You’ll then be walked through
defining each record. Again they are delimited, but by | (pipe) this
time. Name them appropriately and be sure to identify the 00 as the
Tag Identifier. You’ll then be walked through the second line and
define it in a similar way: pipe delimited.

This ought to do it. Please let me know how it goes.

Kind Regards,
Dan Rosanova

[Star0110]

Hi Dan,

Thanks for prompt response.

However, I'm kind of stuck on the second line. I need to differentiate
between field identifiers (|1|, |2|, |3|, etc.) and actual contents (7777,
XX, BB, etc.). How can this be done? Should I consider both types as same?

I hope i don't sound confusing.

Thanks beforehand.

Steeve

Post by Dan Rosanova
This looks pretty strait forward. Get a sample of one of your
messages and then delete everything but the first two lines. Use the
Add Generated Items to create the flat file schema. Select the
example file and then in the wizard select both lines and use
delimited. CRLF will be your delimiter. Then it will ask you do
define records. Your first line will be a Record (string is default)
and second one Repeating Record. You’ll then be walked through
defining each record. Again they are delimited, but by | (pipe) this
time. Name them appropriately and be sure to identify the 00 as the
Tag Identifier. You’ll then be walked through the second line and
define it in a similar way: pipe delimited.
This ought to do it. Please let me know how it goes.
Kind Regards,
Dan Rosanova

[Dan Rosanova]

Hi,
Sorry I didn't notice that earlier, I try not to spend too much time
on the board at work (just during breaks). You can make them all
separate tags (like: 19|1|) if you want and if you don't have too many
that's OK (three isn't that many). Alternatively you can treat them
as the same and then convert them into another schema with a map using
the values of that element, which you would probably want to call like
RecordType or something). It's sort of squeezing a balloon, either it
is easier in the flat file schema definition or in the map.

Either way though I would suggest using a map to transform into your
own internal schema so that changes to the flat file schema wont
impact your internal system.

Kind Regards,
-Dan Rosanova

[Star0110]

Sorry but i'm not getting your point here. My output file should normally
have 19 fields (|1|, |2|, |3|,......,|19|). If possible pls help. thx

Post by Dan Rosanova
Hi,
Sorry I didn't notice that earlier, I try not to spend too much time
on the board at work (just during breaks). You can make them all
separate tags (like: 19|1|) if you want and if you don't have too many
that's OK (three isn't that many). Alternatively you can treat them
as the same and then convert them into another schema with a map using
the values of that element, which you would probably want to call like
RecordType or something). It's sort of squeezing a balloon, either it
is easier in the flat file schema definition or in the map.
Either way though I would suggest using a map to transform into your
own internal schema so that changes to the flat file schema wont
impact your internal system.
Kind Regards,
-Dan Rosanova

[Dan Rosanova]

OK, I get it (I think). I didn't exactly understand what you wanted.
Let me ask if this is it and offer a possible solution. Your saying
each field, within a line, has an identifier? This is the part I
didn’t get. Are the fields fixed width or variable width? I think
what you’ll want to do is stick with the repeating record part and
within the repeating record you’ll need to devise a way to define
another repeating record that is basically a collection of name value
pairs. In the XML part it would look more like
Header
Fields
Line
Item
Name
Value

When you do the creation of this flat file you’ll want to use the
value from within Name to fill in the |1| part and Value the part
after that. This is tricky I admit. There are still two ways to
solve it: the elegant way (which I have not yet figured out, but
described above) and the quick way.

The quick way, which I hinted at before would be to define each line
with the 19 name value pairs of [Name1 Value1], [Name2 Value2] etc.

Your schema would look like this:
Line
Item
Name1
Value1
Name2
Value2
You can also just call them all Name and Value and use Positional
offsetting (i.e. /Value[1] is the PO value Value[2] the PO line.
There is probably a better way with repeating records, but I’m not
there yet. Does the part I said earlier about the maps make sense?
If you go this route the Map you use to populate your flatfile (I take
it you’re creating the file not receiving it) will be more complex).

Kind Regards,
-Dan Rosanova