hey @harshal001
This requirement can be achieved using a Record Producer as the front-end upload mechanism, combined with server-side Excel processing.
The important point is that the Excel file should not be treated only as an attachment. The attachment can be used as the input file, while the actual Excel data is read and mapped to fields in the target custom table.
Recommended Approach
The overall flow would be:
Record Producer
|
v
Upload Excel File
|
v
Read Excel File
|
v
GlideExcelParser
|
v
Validate Excel Headers/Data
|
v
Map Excel Columns
|
v
Insert/Update Custom Table Records
1. Record Producer
Create a Record Producer and add an Attachment variable for the Excel file.
For example:
Variable: excel_file
Type: Attachment
The user uploads the Excel file through the Record Producer.
2. Process the Excel File
Once the Record Producer is submitted, the server-side logic can retrieve the uploaded attachment and process the Excel file using GlideExcelParser.
For example, if the Excel contains:
Employee Name | Employee ID | Department | Location | Email |
Rahul Sharma | EMP001 | IT | Noida | rahul@test.com |
Priya Singh | EMP002 | HR | Lucknow | priya@test.com |
The script can read each row and retrieve the values from the Excel columns.
3. Map Excel Columns to Custom Table
Assuming the target table is:
u_employee_import
with fields:
u_employee_name
u_employee_id
u_department
u_location
u_email
the mapping would be:
Excel Column → Custom Table Field
------------------------------------------
Employee Name → u_employee_name
Employee ID → u_employee_id
Department → u_department
Location → u_location
Email → u_email
Each Excel row can then be processed using GlideRecord.
var gr = new GlideRecord('u_employee_import');
gr.initialize();
gr.u_employee_name = employeeName;
gr.u_employee_id = employeeId;
gr.u_department = department;
gr.u_location = location;
gr.u_email = email;
gr.insert();
If the Excel contains 100 rows, the script can create 100 records in the custom table.
4. Keep the Processing Logic in a Script Include
For a production implementation, I would recommend keeping the Excel processing logic in a Script Include rather than putting everything directly in the Record Producer script.
The architecture would be:
Record Producer
|
| Attachment Sys ID
v
Script Include
|
+-- Validate File
|
+-- Parse Excel
|
+-- Validate Headers
|
+-- Read Rows
|
+-- Map Fields
|
+-- Duplicate Check
|
+-- Insert/Update Records
|
+-- Return Processing Result
This provides better separation of responsibilities and makes the logic easier to maintain and troubleshoot.
5. Validation
I would also recommend validating the Excel file before inserting any records.
For example, verify:
File type
Required headers
Mandatory values
Data format
Duplicate records
Reference values
If required columns are missing, the import should stop and provide an appropriate error message instead of creating incomplete records.
6. Duplicate Handling
If Employee ID is unique, check whether the record already exists before inserting.
For example:
var existing = new GlideRecord('u_employee_import');
existing.addQuery('u_employee_id', employeeId);
existing.query();
if (!existing.next()) {
var gr = new GlideRecord('u_employee_import');
gr.initialize();
gr.u_employee_name = employeeName;
gr.u_employee_id = employeeId;
gr.u_department = department;
gr.u_location = location;
gr.u_email = email;
gr.insert();
}
The duplicate behavior can also be changed depending on the requirement, such as skip, update, or reject.
7. Error Handling
For a production solution, it would also be useful to provide an import summary, for example:
Total Rows : 100
Successful : 95
Failed : 5
And for failed rows:
Row 15 - Employee ID is missing
Row 32 - Invalid email
Row 47 - Invalid department
This makes the solution easier for the end user and administrator to troubleshoot.
One Important Clarification
The key requirement to confirm is whether:
One Excel row = One record in the custom table
If that is the requirement, then the above approach is suitable:
Excel Row 1 → Custom Table Record 1
Excel Row 2 → Custom Table Record 2
Excel Row 3 → Custom Table Record 3
If the entire Excel file represents only one record, then the implementation would be different.
Also, if this is a large or recurring data-import requirement, I would consider using Import Sets and Transform Maps. But if the Excel upload must specifically happen through a Record Producer, then using the Record Producer for file submission and server-side Excel parsing for the actual data mapping is a good approach.
***********************************************************************************************************************************
If this response helps, please mark it as Accept as Solution and Helpful.
Doing so helps others in the community and encourages me to keep contributing.
Regards
Vaishali Singh
Servicenow Developer
Linkedin - https://www.linkedin.com/in/vaishali-singh-2273361bb
