Parse Excel File to Update SCTASKs

[DylanB]

Hi all, we have a project starting up with a unique requirement. This is a bit outside my expertise so I wanted to ask the experts before spending lots of time on this. The situation is as follows:

We receive new hire information in ServiceNow via catalog tasks. This contains information like first name, last name, employee number, address, etc. During the new hire process, this information can change and needs to be updated on the new hire catalog tasks. Once a day, our HR team will send an email to ServiceNow with an Excel file attachment that contains all changed new hire information. The new hire info will be linked to new hire catalog tasks via a custom field that contains a unique identifier. 

The need is for a process to automatically parse each row in the Excel file and update the respective catalog task with that information. What would be the best way to parse that file?

[Kieran Anson · Accepted solution]

It may need tweaking as I believe this has been around a while, but the below article talks about how to process a file into an import set table. You can then script from there in terms of coalescing on fields and updating existing record values

https://www.servicenow.com/community/developer-blog/loading-data-from-an-email-attachment/ba-p/22797...

A more complicated approach, but doesn't require temp-loading the data, is to use the GlideExcelParser API to read the excel file, convert the data into an object and perform your action(s)

https://developer.servicenow.com/dev.do#!/reference/api/xanadu/server/sn_impex/GlideExcelParserScope... 

[DylanB]

Thank you for that information Kieran! I failed to mention that I'm attempting to update variables on SCTASKs, not necessarily records like user records. Either way, the first link looks very promising to process the Excel file! 

Because I am much more familiar with Flow Designer, I will probably make some changes to use that but I will report back with how I end up accomplishing this!

[DylanB]

Here is the summary of how I accomplished this need:

1. Create an inbound email action and script include by following the steps on this link: Loading data from an email attachment - ServiceNow Community

The inbound email action and script include create an import table that separates the Excel data out by rows and fields, which was sufficient for this need. 

Outside of this process, when a new hire SCTASK is created, the unique identifier for the new hire is stored on the new hire SCTASK. 

2. From there, I created a flow runs every 15 minutes and checks the import table for records that are 15 minutes old or less. If it finds any, it searches for the SCTASK with a matching unique identifier. It then posts a work notes comment on the SCTASK with the original and updated new hire details. 

The flow also finds and updates the actual variable values by looking up the Variable Ownership and associated Options records, but for a number of reasons, I'm not very happy with how this works so I might remove it. Here's where I got the info for that: Solved: Setting, or updating variables in flow designer - ServiceNow Community

I realize that this is not the ideal way to make these updates, but given the constraints imposed, I think it will be a reliable enough solution.