Removing the first line from a Flat file in Logic Apps

Welcome again to another Decoding Logic App Dilemmas: Solutions for Seamless Integration! This time we selected a real problem presented by a client during one of our Logic Apps training courses: How to remove the first line from a flat file (CSV)!

In this case, we have a CSV file where the first line contains the column headers that we want to see removed from the message in order to process only the data:

If you come from a BizTalk Server background, you may read in many blog articles that for that, you need to create a custom pipeline component in order to read the file and remove the first line – which is incorrect, by the way. You can create a custom pipeline component to do that, but you don’t need it – and in Logic Apps, we don’t have the concept of pipeline components. So the main question we may ask is: Can we process these types of flat-file messages inside Logic Apps? And if so, how?

The answer is: Yes. We can process these types of flat-file messages inside Logic Apps, and to accomplish this goal, we need a flat-file schema in the exact way we will also address this challenge inside BizTalk Server. For that, we have two approaches:

• The first approach is to isolate the Header information from the data information inside the message. That is the easier and simple approach and is very effective in many scenarios; this is the approach we address in this part 1.
• The second approach is to completely remove the first line, just using translation rules. This more advanced approach requires more knowledge of how the flat file wizard works and some advanced translation techniques that will be addressed in part 2.

How to isolate the Header information from the data information inside the message?

To accomplish this goal, first, we need to create a flat file schema for this CSV file. To do that, we need to:

• Open Visual Studio 2019 and create a new project by selecting Create a new project option.

• On the Create a new project window, search for the Integration Account template, select the Integration Account template, and click Next.

• On the Configure your new project window, give a Project name and a Solution name and set the Location. Then click Create.

• Once the Visual Studio project is created, right-click the project name and select the option Add > New Item…

• On the Add New Item window, on the left tree, select Logic Apps and then select the Flat File Schema, give it a name, and click Add.

• This will start the Logic Apps Flat File Schema Wizard. On the Welcome screen, click Next >.

• On the Flat File Schema Information screen, set the following configurations and then click Next >:
  • On the Instance file property, click the Browse button to locate the flat file from which the schema will be generated.
  • On the Record name property, give a name to define the root node name of your XML message—for example, People.
  • On the Target namespace property, set your desired namespace or leave the default.
  • On the Code page property, select UTF-8 (65001) from the drop-down selection list.

• Because this is a small message on the Select Document Data screen, leave the default selected data and click Next >.

• We will separate or define what separates a product lot on this first Select Record Format screen. And in this case, it is the new line. So, select the option By delimiter symbol and click Next >.
  • Of course, the first line is not a product lot but the headers of each property/element/column, but we will deal with that soon.

• On the Delimiter Record screen, the child delimiter property is already defined to be the new line ({CR}{LF}), so leave the default configuration and click Next >.

• On the Child Elements screen, on the first line, set the following configuration:
  • In the first line, we need to configure the following:
    • Element Name: Headers
    • Element Type:  Field element
    • Data Type: string
  • In the second line, we need to configure the following:
    • Element Name: Lot
    • Element Type:  Repeating record
  • In the second line, set the Element Type to Ignore since all lines represent a Lot, and this structure we will define on the second line. And then click Next >.
  • Note: we want to isolate the header line from the body of the message or data; in this case, we will treat the first line as a simple string.

• On the Schema View screen, you notice our Schema representation is being created. Click Next >.

• Now, back to the Select Document Data screen, you notice that only the second line is automatically selected, excluding the new line character, and this is what we want. We will be defining the structure of the Lot object. Click Next >.

• A comma separates this document, so in the Select Record Format screen, select the option By delimiter symbol and click Next >.

• On the Delimiter Record screen, change the child delimiter property to a comma (,), and click Next >.

• Now you notice that we have all fields separated in the Child Elements screen. Give the following configuration and click Next >.
  • P_ABB (string)
  • NUMBER (string)
  • SHIP_ID (string)
  • CREATE_DATE (string)
  • SHIP_NO (string)
  • SHIP_DATE (string)
  • SHIP_DEST (string)
  • PO_NO (string)
  • LOT_TYPE (string)
  • LOT_NO (string)
  • SHIP_QTY (string)
  • SHIP_PRD_NO (string)
  • OBS_LO – Record – Note that this part is a different structure separated by a semicolon, so we will create the rules for that in the next steps.

• On the Schema View screen, you notice our Schema representation is being created. Click Next >.
• Now, back to the Select Document Data screen, you notice that only the last segment of the line is automatically selected, excluding the rest of the data. We will be defining the structure of the Observation object. Click Next >.

• A semicolon separates this structure, so in the Select Record Format screen, select the option By delimiter symbol and click Next >.
• On the Delimiter Record screen, change the child delimiter property to a semicolon (;), and click Next >.

• Now you notice that we have all fields separated in the Child Elements screen. Give the following configuration and click Next >.
  • OBS1 (string)
  • OBS2 (string)
  • OBS3 (string)

• On the Schema View screen, you notice our Schema representation is finished. Click Finish.

• The Schema will open inside Visual Studio.

Now that we have our schema created, we need to upload it to our Integration Account – if you don’t have it, you need to create an Integration Account inside the Azure Portal. To do that:

• In the Azure Portal, access your Integration Account and select the Schemas option under Settings.

• On the Schemas page, click in + Add and on the Add Schema panel on the Schema property, browse for the schema we just created and click Ok.

• This will add the schema to your Integration Account, which can be used inside your Logic Apps.

Now we need to create a Logic App to process this CSV. To do that, we need to:

• Create a Logic App and use the HTTP Request-Response template – of course, this can be adapted to your requirements.

• Once the Logic App design is loaded, click Save and return to your Logic App page. From there, click Workflow settings under Settings.

• On the Workflow settings page, under the Integration account section, select the integration account in which we added our previous flat file schema on the Select an Integration account property; Next, click Save.

• Now back to our Logic App designer. After the When a HTTP request is received trigger, select Add an Action.

• On the Choose an operation panel, search for Flat File and then choose the Flat File > Flat File Decoding action.

• On the Flat File Decoding action, set the following configurations:
  • Content: Body from the When a HTTP request is received trigger.
  • Schema Name: Persons – the flat file we created earlier.

• Now, select the Response action and set the following configurations:
  • Status Code: 200
  • Headers: Content-Type: text/xml
  • Body: Body from the Flat File Decoding action.

• And finally, save the Logic App.

Now if we open Postman and send the following request, the expected response will be the same payload translated to XML, as you see in the picture below:

Of course, here in this exercise, we are using the HTTP request to emulate reading that CSV file, but the goal was to be able to isolate the header from the data.

We already explained the easier approach: isolating the Header information from the data information inside the message. In this second part, we will address the second approach: completely remove the first line using translation rules.

How to completely remove the first line, just using translation rules?

This process will be similar to the first approach, changing only some translation rules inside the Logic Apps Flat File Schema Wizard. To accomplish this goal, first, we need to create a flat file schema for this CSV file. To do that, we need to:

• Open Visual Studio 2019 and create a new project by selecting Create a new project option.

• On the Create a new project window, search for the Integration Account template, select the Integration Account template, and click Next.

• On the Configure your new project window, give a Project name and a Solution name and set the Location. Then click Create.

• Once the Visual Studio project is created, right-click the project name and select the option Add > New Item…

• On the Add New Item window, on the left tree, select Logic Apps and then select the Flat File Schema, give it a name, and click Add.

• This will start the Logic Apps Flat File Schema Wizard. On the Welcome screen, click Next >.

• On the Flat File Schema Information screen, set the following configurations and then click Next >:
  • On the Instance file property, click the Browse button to locate the flat file from which the schema will be generated.
  • On the Record name property, give a name to define the root node name of your XML message—for example, People.
  • On the Target namespace property, set your desired namespace or leave the default.
  • On the Code page property, select UTF-8 (65001) from the drop-down selection list.

• Because this is a small message on the Select Document Data screen, leave the default selected data and click Next >.

• We will define what separates a product lot on this first Select Record Format screen, and in this case, it is the new line. So, select the option By delimiter symbol and click Next >.
  • Of course, the first line is not a product lot but the headers of each property/element/column; we will deal with that soon.

• On the Delimiter Record screen, the child delimiter property is already defined to be the new line ({CR}{LF}). Still, here, in contrast with the first approach, we are going to set the following configuration and then click Next >:
  • Check the Record has a tag identifier property
  • On the Tag property, copy the value of the entire first line except for the new line characters:
    • P_ABB,NUMBER,SHIP_ID,CREATE_DATE,SHIP_NO,SHIP_DATE,SHIP_DEST,PO_NO,LOT_TYPE,LOT_NO,SHIP_QTY,SHIP_PRD_NO,OBS_LOT

Note: Tags functionality was not designed for this scenario, but this little strategy removes that line. Tags were designed to identify different structures inside a flat file, but the trick is that Tags are removed once we define them; they serve only to identify the structure.

• On the Child Elements screen, you will see that the first line is gone, and now we only see the data we want to process. Here, apply the following configuration:
  • In the first line, we need to configure the following:
    • Element Name: Lot
    • Element Type:  Repeating record
  • In the second line, set the Element Type to Ignore since all lines represent a Lot and this structure we will define on the first line. Now, click Next >.

• On the Schema View screen, you notice our Schema representation is being created. Click Next >.

• Now, back to the Select Document Data screen, you notice that only the second line is automatically selected, excluding the new line character, and this is what we want. We will be defining the structure of the Lot object. Click Next >.

• A comma separates this document, so in the Select Record Format screen, select the option By delimiter symbol and click Next >.

• On the Delimiter Record screen, change the child delimiter property to a comma (,), and click Next >.

• Now you notice that we have all fields separated in the Child Elements screen. Give the following configuration and click Next >.
  • P_ABB (string)
  • NUMBER (string)
  • SHIP_ID (string)
  • CREATE_DATE (string)
  • SHIP_NO (string)
  • SHIP_DATE (string)
  • SHIP_DEST (string)
  • PO_NO (string)
  • LOT_TYPE (string)
  • LOT_NO (string)
  • SHIP_QTY (string)
  • SHIP_PRD_NO (string)
  • OBS_LO – Record – Note that this part is a different structure separated by a semicolon, so we will create the rules for that in the next steps.

• On the Schema View screen, you notice our Schema representation is being created. Click Next >.
• Now, back to the Select Document Data screen, you notice that only the last segment of the line is automatically selected, excluding the rest of the data. We will be defining the structure of the Observation object. Click Next >.

• A semicolon separates this structure, so in the Select Record Format screen, select the option By delimiter symbol and click Next >.
• On the Delimiter Record screen, change the child delimiter property to a semicolon (;), and click Next >.

• Now you notice that we have all fields separated in the Child Elements screen. Give the following configuration and click Next >.
  • OBS1 (string)
  • OBS2 (string)
  • OBS3 (string)

• On the Schema View screen, you notice our Schema representation is finished. Click Finish.

• The Schema will open inside Visual Studio.

Now that we have our schema created, we need to upload it to our Integration Account – if you don’t have it, you need to create an Integration Account inside the Azure Portal. To do that:

• In the Azure Portal, access your Integration Account and select the Schemas option under Settings.

• On the Schemas page, click in + Add and on the Add Schema panel on the Schema property, browse for the schema we just created and click Ok.

• This will add the schema to your Integration Account, which can be used inside your Logic Apps.

Now we need to create a Logic App to process this CSV. To do that, we need to:

• Create a Logic App and use the HTTP Request-Response template – of course, this can be adapted to your requirements.

• Once the Logic App design is loaded, click Save and return to your Logic App page. From there, click Workflow settings under Settings.

• On the Workflow settings page, under the Integration account section, select the integration account in which we add our previous flat file schema on the Select an Integration account property. Now click Save.

• Now back to our Logic App designer. After the When a HTTP request is received trigger, select Add an Action.

• On the Choose an operation panel, search for Flat File and then choose the Flat File > Flat File Decoding action.

• On the Flat File Decoding action, set the following configurations:
  • Content: Body from the When a HTTP request is received trigger.
  • Schema Name: Persons – the flat file we created earlier.

• Now, select the Response action and set the following configurations:
  • Status Code: 200
  • Headers: Content-Type: text/xml
  • Body: Body from the Flat File Decoding action.

• Finally, save the Logic App.

Now if we open Postman and send the following request, the expected response will be the same payload translated to XML, as you see in the picture below:

The header line is completely removed from the document without the need for custom code.

Of course, here in this exercise, we are using the HTTP request to emulate reading in that CSV file, but the goal was to show how to isolate the header from the data.

The goal of this new series is to find common problems that people are facing with Logic Apps, either on StackOverflow, Logic App forums, Azure Logic Apps Microsoft Q&A, or any other source – feel free to provide ideas/problems you would like to be addressed – and provide a solution or solutions to that problem. At least, I will add my point of view to address that issues. Of course, there may be other solutions; feel free to comment if that’s the case.