How to Load Data from an Excel File into a ServiceNow Table

Importing data into ServiceNow tables efficiently is crucial for maintaining data accuracy and saving time. Rather than manually entering large data sets, using ServiceNow’s import tools ensures consistency and leverages automation. A smooth import process helps organizations migrate legacy data, update records in bulk, and populate new applications with initial data without disrupting ongoing operations. Common use cases include:

• Data Migration: Moving legacy system records (like users, assets, or tickets) into ServiceNow.

• Bulk Updates: Updating many ServiceNow records at once (for example, modifying all location records via an Excel update).

• Initial Data Seeding: Loading foundational data (such as CMDB assets, project data, or contacts) from spreadsheets when setting up new ServiceNow applications.

  
  

By understanding ServiceNow’s import process, especially for Excel files, administrators can avoid errors and ensure data lands in the right place. In this guide, we’ll walk through the standard Import Set method, explore the Easy Import feature for quick uploads, and share troubleshooting tips and best practices for a successful data import.

Importing Data with Import Sets (Standard Method)

The Import Set feature in ServiceNow is the standard method to import Excel or CSV data into a ServiceNow table. It uses a staging table and a transform process to move data to the target table, giving you control over mapping and data quality.

Below is a step-by-step guide using Import Sets to upload an Excel file into a ServiceNow table:

1. Navigate to Load Data: In your ServiceNow instance (with admin role), use the Application Navigator to go to System Import Sets > Load Data. This opens the Load Data form where you will configure the import.

2. Select or Create a Staging Table: In the Load Data form, choose an Import Set Table.

You have two options:

• Create table: Let ServiceNow create a new staging table for this import. You’ll provide a Label (e.g., “Employee Data Import”) and the system will generate a table name (e.g., u_employee_data_import).

• Existing table: Select a pre-created staging table if you have one (useful for recurring imports). Ensure the staging table has the required fields to store your data.

If the target table doesn’t exist yet, create it and its fields beforehand so they can be mapped later.

3. Upload the Excel File: Under Source of the import, choose File and click Choose File to upload your Excel spreadsheet (ServiceNow supports Excel .xls/.xlsx or CSV files). If your Excel has multiple sheets or a header row not on the first line, specify the Sheet number and Header row accordingly. Once configured, click Submit to load the data. ServiceNow will parse the file and load records into the import set (staging) table. (This step only uploads into a staging table and does not yet populate the final target table.)

4. Review the Loaded Data (Optional): After submission, you’ll see a confirmation that the data was loaded. It’s good practice to navigate to the import set table (e.g., navigate to System Import Sets > Import Set Tables, find your import table, and open it) to verify the records were loaded correctly. Check that all columns from your Excel appear and data looks correct (no shifted columns or format issues).

5. Create a Transform Map: Now that data is in the staging table, you need to transform it into the target table. On the confirmation screen (Next Steps) after loading data, click Create Transform Map. (You can also manually go to System Import Sets > Administration > Transform Maps and click New.) On the Transform Map form:

   • Enter a Name for this map (e.g., “Employee Data Transform”).

   • For Source table, the import staging table (from step 2) should be pre-filled (or select it if not).

   • For Target table, choose the ServiceNow table where the data should go (e.g., if importing users, select the User [sys_user] table, or a custom table you created).

   • Ensure Active is checked (so the transform can run), and decide if you want to Run business rules and Enforce mandatory fields during import (usually yes for business rules, so that any data integrity rules apply, and enforce mandatory to avoid missing required data).

   • Click Save (or Submit) to save the transform map. This will reveal additional related lists for Field Maps.

6. Map Fields (Field Mapping): Next, map the columns from the Excel (staging table fields) to the fields in your target table. ServiceNow offers two handy options for field mapping:

   • Auto Map Matching Fields: Click the Auto Map Matching Fields button. The platform will automatically map fields that have the same name in both the import set and target table. For example, if your Excel had a column "Email" and the target table also has a field named "Email", auto-mapping will pair them. This feature speeds up mapping when your Excel column headers match the target field names.

   • Mapping Assist: For fields that didn’t auto-map or need manual mapping, use Mapping Assist. This opens a dual-pane interface showing source fields on one side and target fields on the other. You can drag and drop to create mappings or pick from drop-downs. For example, drag the source “Emp Name” field onto the target “Employee Name” field if they didn’t auto-map. Mapping Assist is very helpful for aligning fields accurately, especially if names differ.

   Ensure every important column from the Excel file is mapped to the correct ServiceNow field. If there are fields in the Excel that are not needed in ServiceNow, you can skip mapping those. Conversely, any mandatory fields in the target table that aren’t in your Excel need a default value or the import will skip those records (unless you turned off mandatory enforcement).

7. Set Coalesce (Unique Key for Updates): If you are updating existing records or preventing duplicate inserts, define a Coalesce field on the transform map. Coalescing means using one (or more) fields as a unique key to determine if a record already exists:

   • Open the transform map record and in the Field Maps related list, identify which field should be unique (e.g., an ID or email). For that field mapping, set Coalesce to true (there’s usually a checkbox or column you toggle).

   • If Coalesce is true on a field, the import will check the target table for an existing record with the same value in that field. If found, ServiceNow will update that record instead of inserting a new one; if not found, it will create a new record.

   • You can coalesce on multiple fields if needed (all specified fields must match an existing record to be considered a match). If you do not set any coalesce field, every row from the import will be treated as a new record (potentially creating duplicates on repeated imports).

   Example: if importing users, you might coalesce on the “Email” field so that if an email exists, the user record is updated rather than duplicated.

8. Run the Transform: Once field mapping and coalescing are set, you’re ready to import into the target table. From the Transform Map form, click Transform (sometimes shown as a related link like “Run Transform” or “Transform Data”). In the transform run screen, your import set and transform map should be selected by default; if not, select the appropriate Import Set (the data you loaded) and the Transform Map you created. Then click Transform to execute the import.

9. Verify Import Results: After the transform completes, ServiceNow will show how many inserts and updates occurred, and if any errors were encountered. Go to the target table’s list (e.g., if target was Incident, go to Incident > Open or type incident.list in the filter navigator) to confirm the data appears as expected. You can also check the import log (in the import set record or Transform history) for any skipped rows or errors.

   Common things to verify:

   • Did all rows get imported? (Compare with Excel count.)

   • Are fields correctly populated? (Spot-check a few records to ensure data landed in the right fields.)

   • If some rows were skipped or errors shown, click Import Set > Import Runs to review logs; errors might include things like “Missing mandatory field” or “Invalid reference” which you can address and re-run if needed.

     
     

By following these steps, the Import Set method lets you bring in Excel data methodically, mapping each field and controlling how data is inserted or updated. This standard method is powerful for large or repeated imports, or when you need to transform data (using scripts or complex mappings) during the import process.

Using Easy Import as an Alternative (User-Friendly Method)

ServiceNow’s Easy Import is a simplified way to import data directly from an Excel file into a table without manually creating import sets or transform maps. It’s ideal for quick, one-time imports or for admins who want a more guided experience. Easy Import essentially provides an Excel template and handles the import behind the scenes.

Here’s how to use it:

1. Open the Target Table’s List: Navigate to the list of records for the table you want to import into (e.g., if importing into the Facilities [cmn_location] table, go to Facilities list). Ensure you have admin rights for that table.

2. Initiate Easy Import: Right-click the header of the list (any column header will do) and look for the option Import (sometimes shown as Import Excel or Import Data). Click this option. ServiceNow will prompt you to upload a file and choose an import mode.

3. Choose Insert or Update Mode: In the Easy Import dialog, you typically have two choices:

   • Insert (add new records): Use this if you are only adding new records that don’t already exist in the table. You can directly upload your Excel file with the data.

   • Update (update existing records): Use this if you intend to update records that already exist in ServiceNow. When you choose update, you will usually be prompted to first Download Excel Template. This template will include a Sys ID column and other fields from the target table. You fill this template with your data (ensuring the Sys ID of each record you want to update is included, which the template provides) and then upload it back to ServiceNow.

4. Upload the Excel File: If inserting, select your Excel file and proceed. If updating, after preparing the template with changes, upload that Excel file. The Easy Import process will automatically try to map columns to fields. It only imports the columns present in your file (ignoring any others). Ensure your Excel column names exactly match the target field names for a seamless import. If they don’t, you may need to adjust the column names or create missing fields in the table before retrying.

5. Review and Confirm Import: The platform will process the file and show a preview or summary of what will happen. It might display how many records are detected for insert/update. Confirm to proceed with the import. Behind the scenes, ServiceNow will handle creating a temporary import set and transform for you, but you don’t have to configure those manually.

6. Validate the Outcome: Once Easy Import finishes, a summary of inserted/updated records is shown. Go to the table’s list view and verify the records. For updates, ensure the intended fields got updated correctly. For inserts, confirm all new records are present. If something went wrong (e.g., some rows skipped), the interface usually offers a log or error messages for any failed rows (such as “record not found for update” if a Sys ID didn’t match, or data type mismatches). You can correct the Excel file and run the Easy Import again if needed.

   
   

Easy Import is handy for its simplicity – especially if you only need specific columns or a quick data load without setting up a full Import Set pipeline. However, it’s typically used for smaller, ad-hoc imports. For complex or large data loads, or when you need scripting and advanced transformations, the standard Import Set method is more appropriate.

Troubleshooting Tips for Common Import Issues

Even with a clear process, you may encounter some hiccups when importing Excel data into ServiceNow. Here are some common issues and how to troubleshoot them:

• Data not appearing in target table: If the import completes but you don’t see data in the target table, first check the import set table. It’s possible the transform didn’t run or all records were skipped. Ensure the transform map is active and that you clicked “Transform” to move data from staging to target. Check the import run log for messages (navigate to Import Sets > Import Runs, find your run and view the Import Log). Errors here (like “No transform map found” or “All rows skipped”) will indicate what went wrong.

• Fields not mapped or wrong values: If certain fields in the target table are empty or incorrect after import, you likely have a mapping issue. Revisit the Transform Map’s field mappings to ensure each source field is mapped to the correct target field. If you used Auto Map, some fields might not have matched due to naming differences – use Mapping Assist to fix those. Also verify data types: e.g., trying to import text into a number field could cause the value to be ignored.

• Duplicate records created: This often happens if Coalesce was not set when it should have been. If you intended to update existing records, make sure you set a coalesce field and that the values in that field exactly match existing records. If you forgot to coalesce and ended up with duplicates, you might need to delete the extras and re-run with coalescing enabled. Always pick a field that uniquely identifies a record (like an ID or email).

• Import is successful but some rows skipped: Check the import log – ServiceNow will often flag rows it couldn’t import. Common reasons include violating a mandatory field (the row was missing a value for a field that’s required on the target table) or a reference field that didn’t match any existing record. For example, if importing incidents and a Category value in Excel doesn’t exist in ServiceNow, that row might be skipped or the category left blank. The solution is to either create those missing reference records/choices in ServiceNow or adjust the Excel data to match valid values, then import again.

• Excel file won’t upload or parse: If ServiceNow rejects the file upload, ensure the file format is supported (.xls, .xlsx, or .csv). Very large Excel files might exceed attachment size limits or cause performance issues. Consider splitting large files or converting them to CSV format. Also, remove any formulas or macros; import only raw data. If using Easy Import, ensure the Excel doesn’t have multiple header rows or merged cells which can confuse the parser.

• Transform map script errors: (For advanced use) If you wrote any transform scripts and the import fails, debug those by checking the Transform History (in Transform Map record) for script error messages. You may need to fix script logic or data assumptions.

  
  

By systematically checking each of these areas, you can usually pinpoint the issue and re-run the import. ServiceNow’s logs and import summary are your friends – they provide clues on what went wrong.

Best Practices for a Smooth Data Import

To ensure your Excel data import into ServiceNow goes as smoothly as possible, keep these best practices in mind:

• Prepare and clean your data: Before importing, clean up the Excel file. Remove duplicate rows that you don’t need, correct any obviously wrong data, and ensure consistency (e.g., date formats or text casing). Clean data will reduce errors during import.

• Match field names for auto-mapping: If possible, make your Excel column headers exactly match the ServiceNow field names (including case and spelling). This allows the Auto Map feature to map fields instantly, saving time and reducing human error in mapping. For example, use “Priority” as a column name rather than “Prio” if the target field is priority.

• Ensure reference data exists: If your data contains references to other tables (like user names, locations, category choices, etc.), load or create those reference records in ServiceNow before importing, or ensure they already exist. If they don’t, the import might skip those references or create incomplete data. Alternatively, include the reference record Sys ID in the Excel if you have it (especially for updates).

• Test with a small sample first: It’s wise to do a trial run in a non-production instance (or on a small subset of rows) before importing a huge file. This allows you to catch mapping mistakes or data issues on a small scale. Once the small import looks good, you can import the full dataset with confidence.

• Use Coalesce carefully: Decide beforehand if you need to update existing records or just insert new ones. Set coalesce on the appropriate field(s) for updates. If you only want to insert new records regardless of duplicates, leave coalesce off. Incorrect use of coalesce can unintentionally create duplicate records or overwrite existing data. Always select and double-check a field that uniquely identifies records (like ID or email) before importing.

• Don’t overload the system: For very large imports (thousands of rows), consider importing in batches or during off-peak hours. Large data loads can impact system performance. Monitor the import progress (ServiceNow will handle a lot of data, but it may take time). If performance is a concern, you can also schedule the import during a maintenance window.

• Review and disable notifications/business rules if needed: When importing data that might trigger business rules or notifications (for example, importing incidents could send emails if not careful), review whether those should be temporarily disabled. ServiceNow allows disabling business rules or setting Run Business Rules to false on the transform map if you want a “quiet” import. Use this cautiously – you might skip important logic. Often it’s best to allow business rules unless you have a specific reason not to (like avoiding a flood of notifications).

• Backup important data: If you’re updating existing records, consider exporting those records first as a backup (or doing the import in a sub-production instance to test). This way, if something goes wrong, you have the original data to reference or restore.

• Leverage ServiceNow documentation and support: If you’re unsure about a step, ServiceNow’s official documentation and community forums are valuable. They provide detailed guidance on Import Sets, transform maps, and troubleshooting specific scenarios. It’s better to double-check a detail (like how a field type should be formatted) than to guess and encounter errors later.

Following these best practices will help you avoid common pitfalls and ensure that your data import is successful, with minimal cleanup afterward.

Conclusion

Importing Excel data into ServiceNow tables doesn’t have to be a daunting task. In summary, the Import Set method offers a robust, two-step approach — first loading data into a staging table, then transforming it into the target table with full control over mapping and data handling. This method is ideal for complex or large imports, and for scenarios where you need to map fields meticulously or run scripts during import. The Easy Import method, on the other hand, provides a quick and user-friendly alternative for one-off or simple imports by letting you drop data directly into a table with minimal setup.

Key takeaways include the importance of accurate field mapping (using auto-match and mapping assist), the proper use of coalesce to update existing records, and the need to verify results after running an import. Always be prepared to troubleshoot by checking import logs and fix any data issues identified. By cleaning your data and using the tools ServiceNow provides, you can significantly reduce errors.

As a final recommendation, approach any data import with a plan: verify your Excel format, start with a small test, and only then move to full-scale import. With careful preparation and the guidelines outlined in this article, you can confidently import data into ServiceNow, harnessing its power to efficiently bring in information from external sources. Whether you’re populating a new application or updating your CMDB, following these steps will ensure a smooth and successful data import process. Happy importing!