Transforming Comma-Delimited Text to One-To-Many Records in a Creatio Postgresql Database

A client recently had an Excel file that was imported into their Creatio system. The Excel file had a text column of tags that were comma-delimited text values. This values in the Excel file looked something like this:

Name Email Other Stuff Tags
Bill Person bill@email.com Bill’s stuff woocommerce-customer, mailchimp-subscribed, 24monthunengaged
Mary Person bill@email.com Mary’s stuff woocommerce-customer, repeat-customer

To import this Excel file, a text field named UsrTags was created and the comma-delimited text from the Tags column was imported there. That got the data into Creatio, but it’s not very usable in that format. Ideally, the comma-delimited text would be created as actual Tags and then added to the contacts created by the import.

Most recent Creatio systems have a Postgresql database. Postgres has some functions that make transforming comma-delimited text to multiple 1:many rows an easy task.

First, Postgres has an array data type and also a function called string_to_array which takes a comma-delimited string and creates it as an array.

Then, you can use the Postgres unnest function to turn the array into multiple rows, one for each array item. You can include other fields with it that will repeat for each new row from the array.

Example

select unnest(string_to_array('text1,text2,text3', ',')) as "MyField"

Becomes:

MyField
text1
text2
text3

And combined with other fields like this:

select unnest(string_to_array('text1,text2,text3', ',')) as "MyField1", 'Something' as "MyField2"

Becomes:

MyField1 MyField2
text1 Something
text2 Something
text3 Something

View more about Postgresql’s array functions
Array Functions and Operators

Back to the point. For this customer, to transform this comma-delimited text in the UsrTags field of the Contact, we’ll do two just statements in Postgres. One that creates the tags (for any tags that don’t yet exist), and a second query to add the tags to the contacts based on the comma-delimited text each contact record has.

First, the query below inserts into the Tag table the distinct text values stored in the UsrTags field across all contacts. Note, the join to Tag plus t.”Id” is null at the end ensures the tag doesn’t already exist.

insert into "Tag" ("Name", "TagAccessId", "EntitySchemaName") 
select
    tagtext."Tag",
    '5126592f-5e93-42b4-b3a1-10752e6d413e',
    'Contact'
from 
    (
        select 
            distinct 
            unnest(string_to_array("UsrTags", ',')) as "Tag"
        from 
            "Contact" 
        where 
            "UsrTags" <> ''
    ) tagtext 
    
    left join "Tag" t on t."Name" = tagtext."Tag"
where 
    t."Id" is null

The tags themselves are created at this point. Next we need to add the tags to the contacts (adding a record to TagInRecord for each tag for the contact).

insert into "TagInRecord" ("RecordSchemaName", "TagId", "RecordId", "TagRecordId") 
select
    'Contact',
    t."Id",
    tagtext."ContactId",
    t."Id"
from 
    (
        select 
            distinct 
            unnest(string_to_array("UsrTags", ',')) as "Tag",
            "Id" as "ContactId"
        from 
            "Contact" 
        where 
            "UsrTags" <> ''
    ) tagtext 
    
    left join "Tag" t on t."Name" = tagtext."Tag"
where 
    t."Id" is not null

Note, you could also add a join to TagInRecord to make sure the contact doesn’t already have the tag, if needed. The end result is just as expected and a far better experience.

A quick task thanks to Postgresql’s built in functions. As a side benefit, the data is far easier to work with as actual tag records as well

As a side note, you can take the 1:many tags back to comma-delimited text pretty easy too:

select "RecordId" as "ContactId", array_to_string(array_agg(t."Name"), ',') as "TagsText” 
from "TagInRecord" tr inner join "Tag" t on tr."TagId" = t."Id” 
group by "RecordId"

Or you can do it like this, without the need for using a group by:

select 
    "Id" as "ContactId”, 
    array_to_string(array(
        select t."Name” from "TagInRecord" tr inner join "Tag" t on tr.”TagId" = t."Id” where tr."RecordId" = c.”Id"
    ), ',') as TagsText 
from "Contact" c

Just in case you ever need to have a comma-delimited list of tags for a contact again.

Want content like this delivered to your inbox? Sign up for our newsletter!
ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

Submit a Comment

Your email address will not be published. Required fields are marked *