0

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
Improve this question
2
  • Are you using the Flat File Wizard?
    – DTRT
    Commented Sep 4, 2018 at 12:06
  • Hi. Yes. I have tried both the wizard and manual creation of the schema. I am not able to get it to work, unfortunately. I would prefer a combination of the schema wizard and manual handling so that I can get a thorough understanding of the process and what and how to do it.
    – Emperor 2052
    Commented Sep 4, 2018 at 12:32

2 Answers 2

Reset to default
0

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:

  • Your child order is infix for every repeating record.
  • You don't have a child order for non-repeating ones, instead, these are specified as positional.
  • The delimiter is a linefeed if it's repeating. This means on every repeating 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;

  • Edit my sample below by removing the Repeatingchild record and placing all elements at the Repeating record.
  • Set 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>
Improve this answer
2
  • Hi r3verse. I can not thank you enough for this clear and specific suggestion. There is much to be learned from this flat file. According to your suggestion, I had trouble to set up the pattern right and that is true. One thing I still do not understand is why the delimiter is a line feed only. I use Notepad++ and see all of the symbols including CLRF. There is one for every new row. If you hadn't told me I would never have realized this. Why is repeating rows line feed only? Care to elaborate? Once again thanks!
    – Emperor 2052
    Commented Sep 5, 2018 at 6:08
  • CRLF also means there is a linefeed (LF) in there. You can still delimit by CRLF, just use the right hexadecimals for that. Using only LF makes sure you cover both CRLF and LF types.
    – r3verse
    Commented Sep 5, 2018 at 20:21
0

The trick is, in one step of the Wizard, you select an entire repeating group (I...N) and set that of one Repeating element.

The Wizard will then drill down into the I...N and have you define sub-records, which can also be repeating Groups or Records with Fields.

There's nothing wrong with running the Wizard multiple times to get the structure right. We've all done it...many times.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.