Would some kind soul please advice me or help me out with this flat file? I do not know how to achieve what I want.
As you can see below there are a few fields which can be used as tag identifiers.
We have 'S' for the start tag, 'I' for customer information, 'F' for invoice details, 'N' for the note section and lastly 'E' for the end section. As you can see there are repeating records as well and this mix of nesting and the use of positional records is giving me trouble. So, every F-section (customer) has its own set of repeating invoice with note.
I did try the tips on this wonderful post but in this issue no nesting exists unfortunately.
The strange thing is that I can manage to get what I want if I do not take into consideration the positional records. If I just take the entire data and put it into one field (i.e. entire F without positioning) I can get the structure right and that includes the repeating. But they are positional, I, F and N and all my struggle always yields in an error either stating that it is looking for a tag identifier letter or that it is looking for a carriage return and end of line. Any help would be greatly appreciated as I have been struggling with this for quite a while now.
Sample file:
S
I02710242Company name 01 Postboks 123 Sum 010223 14 15 50 54 9597598396200468 NO N
F141220178065428 00000102700-13012018000000080654288
NINK !!!!!!!!!!!
F141220178065429 00000197200-13012018000000080654296
NINK !!!!!!!!!!!
I02710242Company name 02 Postboks 234 Seum 010223 14 16 50 54 9597598396200468 NO N
F050120185794526 00003686250+04022018000000057945263
NINK !!!!!!!!!!!
F141220178065428 00000102700-13012018000000080654288
NINK !!!!!!!!!!!
F141220178065429 00000197200-13012018000000080654296
NINK !!!!!!!!!!!
E000000420000005000030679668+
So what I think I would like to achieve (unless any of you have a better setup) is a schema that looks like this:
S
---I
-F
-N
-F
-N
-I
-F
-N
-F
-N
E
I cooked up an example for you, this will validate with your sample message. I made this without the Wizard. Although the Wizard is a great starting point to get to know some things, complex nesting should just be tried out by hand.
A few things to notice:
record.positional.record you have that delimiter specified or you've specified a default one at the <Schema> level.I think your problem was partially about recognizing the pattern, as you see in my sample, the pattern is different; After the I row, there is a repeating pattern of F and N rows, which i encapsulated in a record named Repeatingchild.
You could still make your pattern work;
Repeatingchild record and placing all elements at the Repeating record. Parser Optimization to Complexity at the <Schema> level, if you don't do this, it will have trouble with the I record. What's still missing is your exact positional mapping for each field, but I've proven the positionality with the Irecord_part1 and Irecord_part2 i placed in. What i would do next is still use the wizard for each row individually to make all the row elements. So split out each row and run the Wizard on each row type.
To make it even more pretty, you could make individual schema's representing the header and the trailer. So i guess the S record would be the header, and the E record would be the trailer.
<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="StackOverflow.FlatFileSchema1" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="StackOverflow.FlatFileSchema1" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:appinfo>
<b:schemaInfo standard="Flat File" root_reference="Root" default_pad_char=" " pad_char_type="char" count_positions_by_byte="false" parser_optimization="speed" lookahead_depth="3" suppress_empty_nodes="false" generate_empty_nodes="true" allow_early_termination="true" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" />
<schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" child_delimiter_type="hex" child_order="infix" child_delimiter="0x0A" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element minOccurs="1" maxOccurs="1" name="SRecord">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="positional" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" tag_name="S" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Srecord" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" pos_length="200" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Repeating">
<xs:annotation>
<xs:appinfo>
<b:recordInfo sequence_number="2" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_delimiter_type="hex" child_delimiter="0x0A" child_order="infix" repeating_delimiter_type="hex" repeating_delimiter="0x0A" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element minOccurs="0" maxOccurs="unbounded" name="IRecord">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="positional" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" tag_name="I" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Irecord_part1" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" pos_length="133" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Irecord_part2" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo sequence_number="2" justification="left" pos_length="200" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Repeatingchild">
<xs:annotation>
<xs:appinfo>
<b:recordInfo sequence_number="2" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_delimiter_type="hex" child_delimiter="0x0A" child_order="infix" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element minOccurs="0" maxOccurs="unbounded" name="FRecord">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="positional" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" tag_name="F" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Frecord" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" pos_length="174" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="NRecord">
<xs:annotation>
<xs:appinfo>
<b:recordInfo sequence_number="2" structure="positional" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="N" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Nrecord" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" pos_length="229" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ERecord">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="positional" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="E" sequence_number="3" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Erecord" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" pos_length="29" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>