The Hidden Challenge of Delimited Files: When the Delimiter Is Also Part of the Data (Part 2) – The issue

In this second blog post, I will explain the traditional way developers create a flat-file schema and present the main issue that occurs in delimited files when business data contains the delimiter itself.

We are going to take these CSV files as samples:

• Without delimiters inside business data

Sandro,Pereira,Pedroso Street,4415-345,Pedroso,Portugal

• And business data contains the delimiter

Sandro,Pereira,"Pedroso Street, n12",4415-345,Pedroso,Portugal

Note that in both processes. BizTalk and Logic App use the same steps and approach. To simplify and to showcase BizTalk migration, I chose to create it in a BizTalk project.

How the flat file schema is normally created

To create the schema that will recognize the Persons text file, we need to go to the BizTalk solution created in Visual Studio, or a Logic App Consumption Integration Account solution in Visual Studio 2019, and perform the following steps:

• Right-click the project in Solution Explorer and select the option Add > New Item…

• On the Installed Templates menu in the Add New Item window, select the option Schema Files, and then select the option Flat File Schema Wizard. Then provide the name you want to give the schema in this example: Person.xsd.

Selecting this option launches the BizTalk Flat File Schema Wizard, which guides to through creating a Flat File Schema and defining its data structure (records, elements, attributes, …) based on the specified text file.

• On the Welcome to the BizTalk Flat File Schema Wizard window, click Next to continue.

• In the Flat File Schema Information window, we will have to:
  • Select an instance of the text file that will serve as the model of the structure that we want to transform.
  • Although it is not necessary, it is good practice to rename the Record name Root.
    • In this case, we will rename it to Persons.
  • And finally, assign a Target namespace to the schema and define the encoding of the input file
  • Click Next to continue.

Note: The wizard will load the text file so we can split it and map it to the desired structure. In this step, we need to define how the records or rows are differentiated. The structure of the example is: FirstName;LastName;Address;PostalCode;City;Country{CR}{LF}

• Since each Person record that we want to create is defined in a single line, in the Select Document Data, we can select only the data portion of the document that will set the record, i.e., the whole first line. Click Next to continue.

Note: Alternatively, we could select all the data in the file. With this approach, we need to treat the data differently, a few steps ahead in the Child Elements window.

• In the Select Record Format window, we will define whether we are dealing with a Flat File Delimited by symbols or positional. In our case, we will select the By delimiter symbol because each record is delimited by a return Carriage Return/Line Feed ({CR}{LF}). Click Next to continue.

• In the Delimited Record window, we will provide the record delimiter; in this case, since we want to define the structure of a person (i.e., each row is a person), our delimiter is {CR}{LF} (Carriage Return/Line Feed). The Child delimiter combo box includes, by default, the following options: {CR}, {LF}, {TAB}, {SPACE}, {0x1A}, {}, {.}, {;}, and {CR}{LF}. You can provide your own symbol.
  • Click Next to continue.

• In the Child Elements window, we define the element type we want to assign to the registry. As we are defining the Person structure and the file contains multiple elements, we have to select the Element Type as Repeating record. If we do not do this step, we will not be able to split the record into multiple elements/attributes.
  • Note: If you do not define the element as a Repeating record (or Record), you will not have the ability to break down your record into individual elements or attributes.
  • Click Next to continue.

Note: Earlier, I said that we could select all the data inside the flat file and that we would need to treat the information differently in this step. So if you see several similar lines being present in this window, because we want to have an Array of Persons, in this step we need to select the Element Type of the first element as Repeating record and the rest of the set as Ignore (You set this to Ignore because you have already specified Repeating Record for the Person element)

At this point, I have defined what the element Record looks like, but I have not broken down the various elements that make up the record itself. We have just mapped that each line of the text file corresponds to a Person record.

• In the Schema View window, click Next to continue processing the message. Click Next to continue.

In this phase, the wizard restarts the whole process described above, but if you noticed, the wizard no longer selects all information contained in the text file, but only what was selected to define the Person record.

• Now we will split the Person record information into different elements. To do that, select only the required information, leaving out the Carriage Return/Line Feed. Click Next to continue.

• Again, our structure is delimited by a symbol, in this case a comma (,). For that reason, we will need to select the option By delimiter symbol and then click Next to continue.

• As we can see, all elements are separated by the comma (,), which is our delimiter; then, in the Delimited Record window, we must change the Child delimiter option to “,” (comma).

• In the Child Elements window, we will define the different elements/attributes in the Person record structure. This operation is similar to any XSD, where we define the names and data types. Adjust the values according to the table below:

Note: You should rename the parent node, each record, element, or attribute in the BizTalk Flat File Schema Wizard, mainly in this step, by modifying the Element Name field to your desired name. If you forgot to do that or make a mistake defining one of them, you can always fix it in the Schema Editor. The same applies to the Data Type.

• Click Next to continue.
• Finally, the wizard shows the equivalent XML structure your text file document will have. Once you select the option Finish, the schema will be available for you to use in your BizTalk solution.

After we finalize the creation of the Flat File Schema, which will contain the transformation rules of the text file, we can easily test our CSV translation without having to get out of our development tool (Visual Studio) and without having to deploy our solution.

How to test the flat file schema

After the creation of the flat file schema, we can easily test our CSV translation without having to get out of our development tool (Visual Studio) and without having to deploy our solution into BizTalk or to Azure.

For that, we just need to:

• If you check the Person.xsd Properties window, you will see that the CSV file we have used to generate our flat file schema is automatically defined as our Input Instance Filename.
  • Notice that the file does not contain delimiters inside the business data.

• In the Solution Explorer, right-click on the Person.xsd file and select the Validate Instance option. This will take the CSV file defined in the previous property and try to parse it to XML.

• In the Output window, you will see errors if a problem occurs, or an output link with the result of the CSV parsing.
  • Press the Ctrl key + left-click on the mouse.

• This will open the output of that CSV parsing in a dedicated window, and as you can see below, it will correctly parse the file.

<Persons xmlns="http://POC.CSV.WithComma.Person">
  <Person xmlns="">
    <FirstName>Sandro</FirstName>
    <LastName>Pereira</LastName>
    <Address>Pedroso Street</Address>
    <PostalCode>4415-345</PostalCode>
    <City>Pedroso</City>
    <Country>Portugal</Country>
  </Person>
</Persons>

Now, if we take the second sample file containing the delimiter inside the business data:

Sandro,Pereira,"Pedroso Street, n12",4415-345,Pedroso,Portugal

The output will still be a valid output – in other words, a valid XML message. But with an incorrect data structure. If you check:

• Address element contains the value “Pedroso Street
  • The double quote is not part of the data; it is just a character that defines the beginning and end of the string
• PostalCode element contains the value 12″
  • The double quote is not part of the data; it is just a character that defines the beginning and end of the string
  • n12 is part of the Address element
• City element contains the value 4415-345
  • The correct value should be Pedroso
• Country element contains the value Pedroso,Portugal
  • Since this is the last element, it will contain all data until it finds the {CR}{LF} (Carriage Return/Line Feed).
  • The correct value should be Portugal

<Persons xmlns="http://POC.CSV.WithComma.Person">
  <Person xmlns="">
    <FirstName>Sandro</FirstName>
    <LastName>Pereira</LastName>
    <Address>"Pedroso Street</Address>
    <PostalCode> n12"</PostalCode>
    <City>4415-345</City>
    <Country>Pedroso,Portugal</Country>
  </Person>
</Persons>

Basically, it destroys all the transformations present in our workflows since the parsed data is incorrect, despite the message being valid.

Next Steps

In the next parts of this blog post series, we will explore different ways to address this challenge in BizTalk Server and Azure Logic Apps. We will review the advantages and drawbacks of each approach. We will also examine native configuration options available on both platforms. The goal is straightforward. We want file parsing to work correctly. At the same time, we want to preserve data integrity, maintain consistency across systems, and avoid unnecessary technical debt.

I hope you find this helpful! If you liked the content or found it useful and want to help me write more, you can consider buying (or helping to buy) my son a Star Wars Lego set. 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira