Excel upload / validation via catalog item variable before submitting the catalog request

[Sachin Gavhane]

hi @Ankur Bawiskar 
I’m working on a Service Catalog item with the following variables:

• Attachment variable – used to upload an Excel file

• List Collector variable – used to store users

The uploaded Excel file contains only two columns:

• User Name

• User ID (primary key; corresponds to sys_user.upn)

Requirement

When a user uploads the Excel file via the attachment variable:

1. The system should read and validate all rows in the Excel file against the sys_user table using the User ID.

2. If every entry in the Excel file is valid:

   • Populate the List Collector variable with the corresponding users.

3. If any single row contains an invalid or non-existent User ID:

   • Prevent the catalog item from being submitted.

   • Display a clear list of invalid entries on the catalog form so the user can correct the Excel file and re-upload it.

Key Constraint

• Validation must be all-or-nothing — partial population of the List Collector is not allowed.
  
  Requesting your help for this solution.
  
  Thanks !

[Ankur Bawiskar · Accepted solution]

@Sachin Gavhane 

it worked fine for me, see below alert and record in sys_attachment

[Ankur Bawiskar]

@Sachin Gavhane 

can you try this?

var attachmentSysId = g_form.getValue('attachmentVariableName');

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Sachin Gavhane]

Yes @Ankur Bawiskar  it is working fine for me- Able to see sysid of the attachment. Not sure why i was not getting alert earlier.

[Sachin Gavhane]

hi @Ankur Bawiskar 

I need your help in the next part of this requirement. 
1. excel sheet will consist of two columns ----- a) Name b) User id
Once excel sheet is uploaded --> Onchange client script will trigger --> it will check records of excel with the sys _user table --> if all entries are correct then it will populate those excel records in list collector variable--> if even single entry is incorrect it will abort catalog form submission and we have to notify end user with the incorrect entries in the excel. {how to notify user with the incorrect records in excel sheet- need to understand the approach}

let me know if you want this as a separate question.
Your help in this solution is highly appreciated !

[Ankur Bawiskar]

@Sachin Gavhane 

we can continue discussion in same thread.

As per community feature you can mark multiple responses as correct.

Where are you stuck?

Pass the sysId, in script include query that and grab the excel file and use GlideExcel parser to read line by line row

GlideExcelParser - Scoped, Global 

see if this link helps and enhance your script, I shared solution on how to validate file when file is attached to attachment variable

Validate Excel Data and populate in MRVS 

Validations on MRVS - validate Employee ID 

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Sachin Gavhane]

Sure @Ankur Bawiskar 
I will check the validation part and get back to you.

Can you help me with the design part for -- how to inform / notify end user with the specific incorrect entries in the excel sheet?
1. should we populate those incorrect / invalid entires in some other variable?
2. or should we email them
3. we can show some error message with all incorrect users
4. limit of having records in excel sheet [as per best practice in servicenow]

your help in getting best optimal design for showcasing incorrect users is appreciated