Auto Import Excel File from Service Catalog Attachment into Custom Table Using Data Source and Trans

[Tejonath]

Hello Everyone,

I have a requirement in ServiceNow and I need guidance to implement it correctly.

Requirement:
---------------------------------
A user submits a Service Catalog item and attaches an Excel file. After the user clicks "Order Now", the attachment should be processed automatically.

Expected Process:
---------------------------------
1. Detect the uploaded Excel file attachment
2. Create or use an existing Data Source
3. Load the file into an Import Set Table
4. Run a Transform Map to map the fields
5. Insert the records into a Custom Table

Expected Outcome:
---------------------------------
The Excel records should be imported into the target table automatically, without any manual steps such as running Load Data or executing the Transform Map.

If anyone has implemented a similar use case or has best practices, guidance, or example scripts, please share.

Thanks in advance.

[k_lutz]

Hello Tejonath,

I had found an old posting I had used as my basis and used that as my guidance for the following. Here is a high-level of what I created with some of the code that works for our situation.

Assumptions:

Data source created

Related transform map created and validated

Scheduled Data import created related to the data source. Includes an "Execute post-import script" to remove the file from the data source so a new one can be placed on it. Also trigger an event letting customer know it has been imported. Pull info from the import set so they are aware of overall success with inserts, updates, etc. Does not need to be active - probably better not being active as it should only be executed for this flow.

Excel template created and linked to the cat item. Suggest people try it in our sub-prod before using in prod (loading asset records)

Workflow:

<set variables>

workflow.scratchpad.datasource = 'c25e7493975ee110beddb4221153af19';

workflow.scratchpad.schedimport = '5ecab5db97922510beddb4221153af83';

<Timer 3 seconds>

<run script>

var gr = new GlideRecord("sys_attachment");

gr.addQuery("table_name", "ZZ_YYsc_req_item");

gr.addQuery("table_sys_id", current.sys_id);

gr.query();

if (gr.next()) {

gr.table_name = 'sc_req_item';

gr.update();

GlideSysAttachment.copy('sc_req_item',current.sys_id,'sys_data_source', workflow.scratchpad.datasource);

}

<timer 10 seconds>

<run script>

var exportGr = new GlideRecord("scheduled_import_set");

exportGr.addQuery("sys_id", workflow.scratchpad.schedimport); // sys_id of the record which you will be creating for scheduled import

exportGr.query();

if (exportGr.next()) {

gs.executeNow(exportGr);

}

Again, this is meant to import a spreadsheet of assets into the system without no manual intervention.