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 · Accepted solution]

Hi @LavanyaB8479798 ,

Technical Analysis: The issue here is Data Quality in the target table, not the configuration of the Transform Map. The Transform Map logic is strict: logic cannot guess which of the existing "Latitude 7400 (empty number)" records should receive the update "7400-A".

Since you specified "Scripting is not preferred", there is no OOB configuration switch that says "Match on Name + Number OR match on Name + Empty Number".

The Best Practice Solution (Data Remediation): You must clean up the target table before running your main asset load. Trying to fix bad data during a load is risky and complex.

Step-by-Step Remediation Plan:

1. Export Existing Models:

   • Go to cmdb_hardware_product_model.

   • Filter for records where Model Number is empty.

   • Export to Excel (Include Sys ID and Name).

2. Update the Excel:

   • Fill in the correct Model Number for these rows in Excel (using VLOOKUP from your source data if needed).

3. Run a "Fix Import":

   • Create a simple Import Set with this remediation file.

   • Coalesce on Sys ID (This is the key!).

   • Map the Model Number.

   • Run the transform.

Result: Your existing database records will now have both Name and Number populated (e.g., "Latitude 7400 | 7400-A").

Final Step: Now, run your original "Import Product Models" map with the Double Coalesce (Name + Number).

• Since the database now matches the Excel data perfectly, the Transform Map will find the records and Update them instead of inserting duplicates.

Summary: Don't try to make the import logic complex to handle bad data. Fix the data first using SysID, then run the standard import logic.

If this guidance helps you strategize the cleanup, please mark it as Accepted Solution.

Best regards,
Brandão.