Geeks With Blogs
SOA & Integration Services - BizTalk, WCF, WF, AppFabric etc Why is it drug addicts and computer afficionados are both called users?

I have done a heap of work with Flat Files (mostly csv) in Biztalk 2004 I collected a lot of articles from other Blogs on this which were fab I though I would centralise this info for you, again thanks goes to the fellow bloggers for posting this stuff.

BizTalk 2004 Flat File Schema Tutorial 1

BizTalk 2004 Flat File Schema Tutorial 2

BizTalk Flat File Parsing Annotations

The flat file strikes back: BizTalk 2004 parsing positional records

BizTalk Flat File Parsing Annotations new!

Not sure where I got this from but it's a great way of removing the first line from a file if it contains the column headers without having to write a new biztalk dissassembler pipeline component, so thanks to whoever worked this one out.

“To setup a CSV / TSV file, do the following
Setup a schedma as normal. It should be -> products -> product -> all your fields
In , select schema editor extensions, and add "Flat File Extension"
In Products, set the child order to postfix, the child delimiter type to "Hex" and the delimiter to "0x0D 0x0A" (CR/LF). Adjust as needed.
In Product, set the Child order to Infix, the type to Hex (or char) and the delimiter to 0x09 (tab), or , (comma). Adjust as needed.
To skip the first line of a file, during the mapping (eg, the file has a header).
Setup the mapping as normal
Add an Iteration functiod. Connect it up to the "product" level record (eg, product, not products).
Add a not equal (<>) functiod. Connect it to the Iteration functoid, and the target record. Set the second value on that to 1.
If you have trouble deploying a generated xml deployment file (eg, from BTSDeploy wizard) which contains a sql connector, you may find that the user that BizTalkServer is using, does not have access to the target database. Give it access :).
Send Ports are the best place to put maps. I never managed to get them to work in receive ports - the documents tended to disappear.
When in doubt, reboot. For example, if everything works on the development machine, but for some odd reason deploy on the test server and then doesn't work - reboot the test server. Chances are, its managed to have something hang around in the GAC or something.... restarting BTS might work too.”

This next one is a good overview of property setting on a flat file schema it is taken from this blog post which is not accessable at the moment: http://weblogs.ilg.com/brumfieldb/archive/2004/08/09/440.aspx

There doesn't seem to be a lot of good information on disassembling various types of flat-files in BizTalk.   There are a number of flat-file properties added for a schema set with the Flat-File Extensions, but it's not always clear on how to use all these options to accomplish what you need.  Hopefully this post will help serve as an example of how to use some of the flat-file features in BizTalk to parse more than a straightforward comma delimited file.

For one of my projects, I needed to disassemble a file with a format like this:

HEADER    USER_1234
PURCHASE    PO_001
LINEITEM    Item32    33
LINEITEM    Item63    45
PURCHASE    PO_002
LINEITEM    Item454    12

The file contains a header record for each file, one or more purchase orders each with one or more line items.  Each line contains a tag that indicates its usage (e.g. LINEITEM).  The elements for each line are separated by a tab (ASCII hex code of 0x09).

We define a target schema to represent the xml-ized version of this file.

The schema root and each element group must be appropriately configured to correctly parse the flat file.  The configuration for each root and group node are defined as follows:

Schema Root

Property Setting
Default Child Delimiter 0x0D 0x0A
Default Child Delimiter Type Hexadecimal
Default Child Order Postfix

The settings in the schema root define the default usage in sub-groups throughout the schema.  Here we've defined it to be CR LF and the Default Child Order defines that the data will precede the delimiters.  These settings can be overridden at each group level if necessary.

Orders

Property Setting
Child Delimiter Type None
Child Order Postfix

Header

Property Setting
Child Delimiter 0x09
Child Delimiter Type Hexadecimal
Tag Identifier HEADER
Child Order Prefix
Min Occurs 1
Max Occurs 1

The header group allows us to specify a tag identifier as well as note that the elements on this line are tab (0x09) delimited.

Order

Property Setting
Child Delimiter Type None
Child Order Postfix
Min Occurs 1
Max Occurs unbounded

OrderHeader

Property Setting
Child Delimiter 0x09
Child Delimiter Type Hexadecimal
Tag Identifier PURCHASE
Child Order Prefix

LineItems

Property Setting
Child Delimiter Type Default Child Delimiter
Child Order Postfix

LineItems is a logical grouping of LineItems.  Since LineItems in the flat file are separated by a CR LF, the Delimiter type is set to the default child delimiter and that the delimiter will appear after the child item.

LineItem

Property Setting
Child Delimiter 0x09
Child Delimiter Type Hexadecimal
Tag Identifier LINEITEM
Child Order Prefix
Min Occurs 1
Max Occurs unbounded

Notes

  • Most of the group nodes do not have any real values in the flat-file, but instead are logical groupings of real elements.  For instance, LineItems has no real representation in the flat file, but is the logical grouping of LineItem elements from the flat file.  These groups must be setup to for the postfix child order.
  • Tag Identifiers are used by BTS to recognize a line, but are not imported as data.
  • With this specification, there needs to be a CR LF following the last line.

R. Addis

Posted on Friday, April 22, 2005 3:14 AM BizTalk | Back to top


Comments on this post: Managing Flat Files in BizTalk 2004

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
The positional tutorial link is broken
Left by BA on Jun 02, 2005 1:33 PM

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
Seems to work?
Left by Rob on Jun 03, 2005 5:06 AM

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
Hey,

I am wondering if you ever tried to convert XML to flat file.

I tried to do so, but when I configure the XSD properties to input as XML and output as Native
i can't test the XSD with XML input file.

Can you please explain how it can be tested?
Left by LirLir on Nov 09, 2005 8:42 AM

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
Hi your blog has been very helpful to me. I am a Biztalk Newbie, just wanted to ask how to make schema for this kind of flatfile.

"190330" "SMP (CKAG)" "91489" "09/11/2004" "09/11/2004" "SUPER SM, INC." "SU" "RTV No. 49011" "9701.09"
"190330" "SMP (CKAG)" "91489" "09/11/2004" "09/11/2004" "SUPER SM, INC." "SU" "RTV No. 49011" "970.1"
"190330" "SMP (CKAG)" "1040900707" "09/10/2004" "09/10/2004" "MAIN STREAM BUSINESS, INC. - 1" "BA" "SI No. 666937" "3239.2"
"190330" "SMP (CKAG)" "1040900707" "09/10/2004" "09/10/2004" "MAJOR SHOPPING MANAGEMENT CORP - 1" "OR" "SI No. 471923" "30500"
"190330" "SMP (CKAG)" "1040900707" "09/10/2004" "09/10/2004" "MANDURRIAO STAR, INC-BICUTAN" "BI" "SI No. 665932" "9150"
======================

just copy it in the notepad to see correctly, all these comes from only one file.

thanks for the help
Left by Jinky_ph on Dec 03, 2005 10:28 AM

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
It is possible.
Just use delimiter of type
hexadecimal and value 0x22 0x20 0x22
Left by NISHIL on Apr 21, 2006 10:10 AM

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
Thanks for this. It really helps a lot! Can anyone help with another problem! I have a flat file, that contains a column on which I need to make decision flows:
EMP001 Mike C 45.55
EMP002 Carol D 53.45

The "C" and "D" is a code, and depending on this code, I have to make certain process flows.. However, I can't promote the column if "maxoccurs" has been set ?!
Any help will be appreciated!!
Thanks
Deon
Left by Deon on Oct 06, 2006 11:18 AM

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
Hey Deon,

Are you trying envelope splitting to split up your original schema into individual messages for each record? From a first glance this is what you need to do to be able to promote the column and achieve content based routing.

Cheers,
Rob
Left by Rob A on Oct 08, 2006 6:17 PM

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
Helo,

I need to convert an XML file into an excel file, using bizTalk. Do you have any idea?

thank you

oana
Left by oana on Sep 04, 2007 3:04 AM

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
Hello,

I need to convert an XML file into an flatl file, using bizTalk. Do you have any idea?

thank you

oana
Left by rajesh on Apr 28, 2008 4:28 PM

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
Look at using the Flat File Assembler Pipeline component in a send pipeline this is really simple stuff.
Left by Rob on Apr 28, 2008 8:05 PM

# re: Managing Flat Files in BizTalk 2004
Requesting Gravatar...
How to convert and XML to flat file using a delimiting character ?
Left by Kiran on Jul 23, 2008 3:19 AM

Your comment:
 (will show your gravatar)


Copyright © Rob Addis | Powered by: GeeksWithBlogs.net