Using Comma Separated Values to Define Start Position

In Astera ReportMiner, you can manually specify the start position of a selected data field in the Field Properties panel. There are three options available in the drop-down menu to specify the Start Position, as shown below.

00-Field-Properties-Size-and-Position

The start position can be fixed or can follow a particular string in the current or previous line. To learn more about the Start Position options, click here.

In this document, we will see how we can define multiple strings using comma-separated values to specify the start position of a data field. This feature is useful when the document does not follow a fixed pattern or has an inconsistent format. The purpose of this option will be clear with the use-case given below.

Sample Use-Case

In this case, we have an unstructured document which contains the orders information and the details of items against each order. As we can see below, the order number is followed by different strings, “ORDER NUMBER:”, “ORDER ID:” and “ORDER:”.

02-use-case

Let’s see how we can capture the order number for all the orders by specifying comma-separated strings to define the start position.

Using Comma Separated Values for Start Position

1. Here we have loaded the unstructured document in a report model. To learn how to load a document in a report model, click here.

03-load

2. Create the data region to capture the field containing the order number by defining an appropriate pattern. Define the pattern as ‘ORDER’ and rename the data region to Orders in the Region Properties panel.

04-pattern

3. To create the data field, select the data, right-click on it and select the Add Data Field option from the context menu. Rename the field to Order_ID.

05-add-data-field

4. As we can see in the blue highlighted area, the data in the field is misaligned as it does not reoccur at the same position.

06-misaligned

5. To account for this variation in our document, go to Field Properties > Size and Position > Start Position and select Follows String in Current Line option from the drop-down menu. This option will start reading the field data following a particular string.

07-follow-string

6. Since our data field follows multiple different strings, specify the comma-separated values in the text box. You may adjust the length of the field to capture the data correctly.

08-CSV

Comma Separated Values with Text Qualifiers

Here, we have a comma as part of the string which precedes the data for a particular field. To cater to this case, we can specify a list of comma-separated strings with double quote text qualifiers.

09-case-2

Here, you can see that a comma character is extracted along with the data because the comma after ‘SHIPdate’ is read as a separator and not as a part of the string.

10-text

To resolve this, enclose the comma-separated strings with text qualifiers (as shown below) so that the comma is read as part of the string.

11-final

This is how you can extract data in a particular field if it follows multiple strings using comma separated values in Astera ReportMiner.