Manipulating Data on Transform or Load

[Jcharlie]

Hi All, 

I currently have a manual process that exists outside of ServiceNow. Data is extraced from a thrid party it is manipulated using excel macros into an acceptable format and then imported into ServiceNow using import sets.

I have been asked to investigate if the Data Manipulation is possible from within ServiceNow and so far I have not found a solution. Below I will share what the raw data looks like and how we need it to be imported into the table. 

Here is an example of the RAW data. This is to be imported in the departments table (cmn_departments) in this file each level of organisation (Org ID) represents a record. So for example

• Org at level 1 has no parent org and is a record in the departments table
• Org at level 2 has a parent that is the level 1 org 
• Org at level 3 has a parent that is the level 2 org

And so on...

Raw Data example

Here is how the data looks when it has been transformed outside of ServiceNow and is ready for import 

Sorted Data example

I have tried to acheive this with transform scripts but I havent found a way to split the records on the column. I have attempted manipulating the data on the import set table before running the transform but it doesn't 'feel right'.

Does anyone know of a way to achieve this in ServiceNow? For context these import have ~ 20,000 rows and are updated weekly.

Thanks 

[Aman Kumar S]

Hey,

Did you try to include the logic of splitting/processing column data using field map scripts?

That should be able to manipulate and map data anyway you want for a specific column

[Jcharlie]

Hi Aman, 

Yes I considered using the field mapping scripts but I was unable to map the fields as required. Since it processes one row at a time and on each row there can be up to 9 fields that would need to map to the ID field I was unable to achieve it this way. I need to be able to split the columns into new rows

[Alex240]

Hi @Jcharlie 

This is an interesting use case.. however I guess there is no solution if you already tried transform/mapping scripts.

I'll subscribe just to know if you manage to figure out how to accomplish it. Looks really interesting.

Cheers,

Alex

[Jorn van Beek · Accepted solution]

Main question is why you need to do data manipulation other than transform (note transform is a data manipulation).

Your requirement is to load the original file to the department table in ServiceNow. Correct?

Well that is possible. Here are 2 options.

Highly scripted. Should think of this option if max layers is variable

1. Create an import table for this file. that is including lvl1, lvl2, lvl3 etc
2. Create a fully scripted transfrom map handling all layers during transforming.

No-code. Solution to run without coding.

1. Create an import table for this file. that is including lvl1, lvl2, lvl3 etc
2. Create an transform map for each layer in the organitation. Ensuring no record created on empty coalesc. So transform map 1 would have coalesc on org1 id, uses name of company 1 and department head of 1. transform map 2 would have coalesc on org2 id, uses name of company 2, department head of 2 and parent is org1. etc.
3. ensure they are all run in a full tranform.