Import Excel Data into alm_hardware and cmdb_hardware_product_model

[LavanyaB8479798]

Hi Team,

I am trying to import hardware asset data from an Excel sheet and need guidance on how to handle field mapping across two different tables.

Excel Sheet Fields
PO Number

Serial Number

Life Cycle Stage

Status

Model

Model Number

Useful Life

Warranty Expiration Date

Purchased Date

Target Table Mapping
Fields to be populated in alm_hardware:

PO Number

Serial Number

Life Cycle Stage

Status

Model (reference field)

Warranty Expiration Date

Purchased Date

Fields that belong to cmdb_hardware_product_model:

Model Number

Useful Life

Requirement
Import data from a single Excel file

Populate Model Number and Useful Life into cmdb_hardware_product_model

Map the corresponding model reference into alm_hardware

Remaining fields should be directly mapped into alm_hardware

[Itallo Brandão]

Hi @LavanyaB8479798 ,

This is a classic data import scenario. Since ServiceNow Import Sets target a single "Target Table" per Transform Map, you cannot natively map to two tables in a single map record without complex scripting.

The Best Practice (Low-Code) approach is to create Two Separate Transform Maps that both run against the same Import Set.

Here is the step-by-step architecture to achieve this:

The Strategy: "Two Maps, Ordered Execution"

You will load the Excel file once into the staging table. Then, you will run two transformations sequentially:

1. Map 1 (First): Creates/Updates the Product Models (to ensure the reference exists).

2. Map 2 (Second): Creates/Updates the Assets and links them to the models created in step 1.

Implementation Steps

Step 1: Load Data

1. Create your Data Source and load the Excel file.

2. This creates your Import Set Table (e.g., u_imp_hardware_load).

Step 2: Create Transform Map #1 (Product Models)

• Name: Import Product Models

• Source Table: u_imp_hardware_load (Your staging table)

• Target Table: cmdb_hardware_product_model

• Order: 100 (This ensures it runs first)

• Field Maps:

  • Model (Excel) -> name (Target) [Coalesce = True]

  • Model Number (Excel) -> model_number (Target)

  • Useful Life (Excel) -> useful_life (Target)

  • (Note: Coalescing on Name ensures you don't create duplicates if the model already exists).

Step 3: Create Transform Map #2 (Hardware Assets)

• Name: Import Hardware Assets

• Source Table: u_imp_hardware_load (Same table!)

• Target Table: alm_hardware

• Order: 200 (This ensures it runs second)

• Field Maps:

  • Serial Number (Excel) -> serial_number (Target) [Coalesce = True]

  • PO Number (Excel) -> po_number (Target)

  • Status / Substatus -> Map appropriately.

  • The Crucial Link:

    • Model (Excel) -> model (Target).

    • Since Map #1 just ran, the system will look up the string from your Excel in the Model table, find the record we just created/updated, and create the link automatically.

Summary

By setting the Order field, you ensure the dependency is resolved (The Model exists) before the Asset tries to reference it. This is cleaner and easier to maintain than writing an onBefore script to insert models manually.

If this guidance helps you structure your import, please mark it as Accepted Solution.

Best regards,
Brandão.

[LavanyaB8479798]

In our scenario, Model Name alone cannot be used as a coalesce field, because one model can have multiple model numbers. For example:

Model Name: Latitude 7400

Model Numbers: 7400-A, 7400-B, 7400-C

In the Excel file:

The source provides model.name (not the display value)

Multiple rows may contain the same model name and model number

Example: 40 asset rows with the same Latitude 7400 and same model number

Because of this:

If we coalesce only on Model Name, ServiceNow cannot uniquely identify a single model record

This can result in multiple inserts or incorrect updates

During Transform Map #2, the asset model reference may not resolve correctly, even though the model was updated in Transform Map #1 that time again either new model created or pick the existing random model it store into alm_hardware table

[Itallo Brandão]

Hi Lavanya,

That is an excellent catch. You are absolutely correct—if distinct models share the same Name but have different Model Numbers, coalescing on Name alone will cause the system to overwrite the existing record rather than creating a new variation.

Here is the corrected approach to handle this Composite Key scenario:

Part 1: Fixing the Model Import (Map #1)

To ensure ServiceNow recognizes "Latitude 7400 + 7400-A" and "Latitude 7400 + 7400-B" as two different records, you must use Multi-Field Coalesce.

In your "Import Product Models" Transform Map:

1. Open the Field Map for Model (Name). Set Coalesce = True.

2. Open the Field Map for Model Number. Set Coalesce = True.

Result: ServiceNow will now query using Name AND Model Number.

• If both match -> Update.

• If Name matches but Number differs -> Insert New.

Part 2: Fixing the Asset Association (Map #2)

This is the tricky part. Since the Model Name ("Latitude 7400") is no longer unique, the standard reference field mapping in the Asset map will be confused (it might pick 7400-A when it should pick 7400-B).

You must use a Source Script for the model field in the "Import Hardware Assets" map to look up the exact match.

1. Open the Field Map for the model field.

2. Change Use source script to True.

3. Use the following script to find the specific Model SysID based on both columns:

answer = (function transformEntry(source) {
    var modelName = source.u_model; // Replace with your actual Excel column name
    var modelNum = source.u_model_number; // Replace with your actual Excel column name

    var grModel = new GlideRecord('cmdb_hardware_product_model');
    grModel.addQuery('name', modelName);
    grModel.addQuery('model_number', modelNum);
    grModel.setLimit(1); // Performance optimization
    grModel.query();

    if (grModel.next()) {
        return grModel.sys_id; // Returns the specific model created in Map #1
    }
    return ""; // If not found, leave empty
})(source);

Summary: By using a Double Coalesce in the first map and a Scripted Lookup in the second map, you ensure total data integrity for models with identical names but unique part numbers.

If this refined solution solves the duplicate issue, please mark it as Accepted Solution.

Best regards,
Brandão.