Import data from excel (update vs insert)

[Jamy1]

A custom table was created to store data where choice list are better managed when lists are substantial. The table may be populated with data from an Excel template, loaded to import set table and transformed through new or existing Transform Maps.

• There are 5 columns with potential data in each field.
• There is no unique identifier nor consistent row where data would be unique

Right now, any data uploaded through the Transform map will always be an insert. How do I solve the problem where I get data that needs to get updated (not inserted / not new record/row)?

Another way to ask, how do we determine if the the data load is for an update or insert?

[Mike_R]

Is this new spreadsheet always the source of truth? For example: does it always contain the complete list, or does it just contain any changes?

[Jamy1]

Good question! The data will come from a Google spreadsheet. We'll export the entire list and any changes should change in ServiceNow. Any new or deleted rows should be accounted for in ServiceNow.

[Mike_R]

What I would do personally, since you don't have a unique identifier, is I would probably add a last found date and an active field to the table.

Then I would proceed with uploading ALL data, while also populating the last found date with the current date/time.

You can coalesce on all your fields, so that the records that do not have any changes will remain as is and your transform map can update the last found field.

Any changes will come in as a new record, and the last found date will also be populated.

Then after all that, you can have a script to look for anything with a last found date before today, and to mark it as inactive. This will take care of the old/deleted records.

Let me know if this makes sense. If not, I'll try to explain it better.

[BigZolo]

Use Conditional coalesce: Created a scripted coalesce field mapping, the script checks that the current row should be inserted (returns -1) or updated (returns the sys_id of the target record)
https://docs.servicenow.com/bundle/tokyo-platform-administration/page/administer/import-sets/concept...

[KileSnow]

I'm new here and I just wanna add that one approach to solving this problem is to add a unique identifier to your custom table, such as a primary key. This way, when you import data from Excel, you can check if a record with the same primary key already exists in the table. If it does, you can update the existing record with the new data from Excel. If not, you can insert a new record. Of course, adding a primary key to your custom table may require some changes to your data model and Transform Maps. I hope this information is helpful to you, even if the thread is a bit old! And if you're looking for a reliable software suite for managing your data, you might want to consider Office 2021 Professional Plus for Windows from https://turnkeypoint.com/product/office-2021-professional-plus-for-windows/. This suite includes all the standard apps like Word, Excel, and PowerPoint, along with powerful tools like Access and Publisher.