Text Delimited: Guide on creating templates.

Description

Text Delimited connector is one of the most flexible connectors for Merge1 and Alta Capture.

It gives customers the opportunity to construct a message in the way they want.

There are two files needed for the connector, one XML file and one CSV file. The CSV file contains the information to parse, and the XML file is used for constructing the message.

First, let’s start with the CSV file. In the first line,  it's identifying the values that columns will represent.

In this example, the values are “Subject, From, To, Time, Body”

(Important note, for editing/creating CSV files please use Notepad++ instead of Excel, as in Excel sometimes the values are represented wrong, for example, the time format which is visible in the screenshots. This article it’s just for presentation)

Starting from the second line, each line will represent one message. This also means that the information under each column will represent the values mentioned in the first line.

The values (Subject, From, To, Time, Body) can edit to something else and you can add other values based on your needs.

As for the XML file, there are three main sections which are “options”, “columns”, and “mapping”.

The first one is the options and here is the example based on the CSV file that we have.

  <options>

    <containsHeader>Yes</containsHeader>

    <maxCols>5</maxCols>

    <delimiter>","</delimiter>

    <text_qualifier>"</text_qualifier>

  </options>

<containsHeader>Yes</containsHeader> This line is for headers

<maxCols>5</maxCols> Here you are specifying the number of columns that you’re using. If in your example there are more then you can change it.

 <delimiter>","</delimiter>

<text_qualifier>"</text_qualifier> In these two lines we are specifying the delimiter and text qualifier which you can see in the above-shared screenshot.

 

The second part is the "Column" section, here is what it looks like.

<columns>

    <column>

      <order>1</order>

      <name>Subject</name>

      <datatype>String</datatype>

    </column>

    <column>

      <order>2</order>

      <name>From</name>

      <datatype>String</datatype>

    </column>

    <column>

      <order>3</order>

      <name>To</name>

      <datatype>String</datatype>

    </column>

    <column>

      <order>4</order>

      <name>Time</name>

      <datatype>DateTime</datatype>

      <datatype_options>

        <format>XX/DD/YYYY HH:MM:SS</format>

      </datatype_options>

    </column>

    <column>

      <order>5</order>

      <name>Body</name>

      <datatype>String</datatype>

    </column>

  </columns>

As you can see each column is specified in order and it must match the one in the CSV file. Every column is constructed in the same way (“order”, “name”, and “datatype” which always must be a string) except for the “time “column

      <order>4</order>

         <name>Time</name>

         <datatype>DateTime</datatype>

            <datatype_options>

            <format>XX/DD/YYYY HH:MM:SS</format>

         </datatype_options>

    </column>

The beginning is the same “order” and “name”, but in “datatype” instead of string, it comes to DateTime, and under that comes the options. The format of the time must match the one that is in the CSV file. Here it is XX/DD/YYYY HH:MM:SS as in CSV it is 06/13/2023 21:38:14

XX – month

DD – day

YYYY – year

HH – hour

MM – minute

SS – second

Also, pay attention to how they are separated from each other. In this example, the month, day, and year are separated with / sign and the hour, minute, and second are separated with : sign. Thus, in the XML file, the format must be the exact same that is in the CSV file. If you want to change it then you must do it from both CSV and XML files, for example.

DD-XX-YYYY HH:MM -  26-10-2021 21:38

The next section is the mappings.

  <mappings>

    <mapping can_be_empty="No">

      <property>Sender</property>

      <items>

        <item>From</item>

      </items>

    </mapping>

    <mapping can_be_empty="No">

      <property>Participants</property>

      <items>

        <item>To</item>

      </items>

    </mapping>

    <mapping can_be_empty="Yes">

      <property>Title</property>

      <items>

        <item>Subject</item>

      </items>

    </mapping>

    <mapping can_be_empty="Yes">

      <property>Content</property>

      <items>

        <string>"Body: "</string>

        <item>Body</item>

        <string>"

"</string>

      </items>

    </mapping>

    <mapping can_be_empty="No">

      <property>ActivityDateTime</property>

      <items>

        <item>Time</item>

      </items>

    </mapping>

    <mapping can_be_empty="Yes">

      <property>X-KVS-MessageType</property>

      <items>

        <string>"Whatever you need"</string>

      </items>

    </mapping>

  </mappings>

 

In this section, the properties such as “Sender”, “Participants”, “Title”, “Content”, and “ActivityDateTime” are mandatory and cannot be changed. Under each property comes the “item” node, and here we are specifying the column that will work under that property, for example.

Under the “Sender” property we choose the “From” column from the CSV

Under the “Participants” property we choose the “To” column from the CSV

Under the “Title” property we choose the “Subject” column from the CSV

Under the “Content” property we choose the “Body” column from the CSV

Under the “ActivityDateTime” property we choose the “Time”

Also, we have a property for headers which is “X-KVS-MessageType”. We didn’t specify anything here, but you can change it to whatever you need.

 

These three section options, column, and mapping are mandatory, and you can also have threading enabled. Here is how it looks like

<threading disabled="No">

    <case_sensitive>No</case_sensitive>

    <date_sort_direction>Ascending</date_sort_direction>

    <items>

        <item> From </item>

     </items>

</threading>

To make threading work, the appropriate column must be picked, as the connector respects the value that we are identifying. Here in this example, the appropriate column is “From”. To be more specific, if the values under the From column will be the same, the connector will construct the message with thread, using the values under the specified column

In order to make threading work, you need to choose the right column in <item> </item> field. For this example, it will be the “From” column. To put it simply, if the values in the "From" column are the same, the connector will create a message thread using those values. So, by selecting the right column and having matching values in that column, the connector can organize messages in a threaded way.

 

So, the final look of the XML file must be like this.

<?xml version="1.0" encoding="utf-8"?>

<configuration>

  <version>TD_3.0</version>

  <options>

    <containsHeader>Yes</containsHeader>

    <maxCols>5</maxCols>

    <delimiter>","</delimiter>

    <text_qualifier>"</text_qualifier>

  </options>

  <columns>

    <column>

      <order>1</order>

      <name>Subject</name>

      <datatype>String</datatype>

    </column>

    <column>

      <order>2</order>

      <name>From</name>

      <datatype>String</datatype>

    </column>

    <column>

      <order>3</order>

      <name>To</name>

      <datatype>String</datatype>

    </column>

    <column>

      <order>4</order>

      <name>Time</name>

      <datatype>DateTime</datatype>

      <datatype_options>

        <format>XX/DD/YYYY HH:MM:SS</format>

      </datatype_options>

    </column>

    <column>

      <order>5</order>

      <name>Body</name>

      <datatype>String</datatype>

    </column>

  </columns>

  <mappings>

    <mapping can_be_empty="No">

      <property>Sender</property>

      <items>

        <item>From</item>

      </items>

    </mapping>

    <mapping can_be_empty="No">

      <property>Participants</property>

      <items>

        <item>To</item>

      </items>

    </mapping>

    <mapping can_be_empty="Yes">

      <property>Title</property>

      <items>

        <item>Subject</item>

      </items>

    </mapping>

    <mapping can_be_empty="Yes">

      <property>Content</property>

      <items>

        <string>"Body: "</string>

        <item>Body</item>

        <string>"

"</string>

      </items>

    </mapping>

    <mapping can_be_empty="No">

      <property>ActivityDateTime</property>

      <items>

        <item>Time</item>

      </items>

    </mapping>

    <mapping can_be_empty="Yes">

      <property>X-KVS-MessageType</property>

      <items>

        <string>"Whatever you need"</string>

      </items>

    </mapping>

  </mappings>

 <threading disabled="No">

    <case_sensitive>No</case_sensitive>

    <date_sort_direction>Ascending</date_sort_direction>

    <items>

        <item> From </item>

     </items>

  </threading>

</configuration>