Incident import fails in incident management due to duplicate records

QMS MasterControl Quality Excellence
, , , , , , , ,
1

We’re migrating legacy incident data from Excel into MC 2022.1 incident management module and hitting duplicate record errors. Our Excel file has about 850 incidents from the past 3 years, but the import keeps failing with “Duplicate Incident ID” errors even though we’ve checked the spreadsheet multiple times.

The issue appears related to incident ID deduplication - some incidents in our legacy system had the same reference numbers across different departments. We also suspect problems with user assignments since not all legacy usernames match our current master user list. Additionally, the date formats vary between MM/DD/YYYY and DD-MM-YYYY in different columns.

Here’s a sample of the SQL error we’re seeing:

ERROR: duplicate key value violates unique constraint
KEY (incident_id)=(INC-2023-0045) already exists
FAILED: 127 records rejected due to constraint violations

How can we properly deduplicate incident IDs, validate user references, and standardize the legacy data formats before import? Need this resolved for our upcoming compliance audit.

2

Here’s a comprehensive solution addressing all three focus areas:

Data Deduplication for Incident IDs: Create a pre-import deduplication script that identifies and resolves duplicates systematically. In your Excel staging file, add a column called ‘Unique_Incident_ID’ using this formula: =IF(COUNTIF($A$2:A2,A2)>1, A2&"-"&COUNTIF($A$2:A2,A2), A2). This appends a suffix to duplicates (e.g., INC-2023-0045-2, INC-2023-0045-3). Store the original ID in a custom field called ‘Legacy_Incident_ID’ to maintain traceability. Before import, run this validation query:

SELECT incident_id, COUNT(*)
FROM staging_incidents
GROUP BY incident_id
HAVING COUNT(*) > 1;

Reference Validation Against User Master: Export your MC user master to Excel and create a validation worksheet. Use VLOOKUP to match legacy usernames: =IFERROR(VLOOKUP(B2,UserMaster!A:B,2,FALSE),"NO_MATCH"). For the 40 mismatched users, create a mapping table with three columns: Legacy_User, Current_MC_User, Mapping_Reason. Document whether users were renamed, merged, or replaced by department managers. Import this mapping into a custom MC table for audit purposes. Never use placeholder accounts - instead map to actual current users with proper documentation in the incident notes field.

Standardizing Legacy Data Formats: For dates, create three helper columns in Excel: Extract_Date (using DATEVALUE), Validate_Date (checking if result is valid), and ISO_Date (using TEXT function to convert to YYYY-MM-DD). Apply this formula: =TEXT(DATEVALUE(C2),"YYYY-MM-DD"). For mixed formats, use nested IF statements to detect format and convert accordingly. Also standardize text fields - trim whitespace, convert status values to MC-accepted values (map “Closed” to “Complete”, etc.), and ensure all required fields have non-null values.

Pre-Import Checklist:

  1. Run deduplication script and verify zero duplicates in staging table
  2. Validate 100% of user references against current master - document all mappings
  3. Convert all dates to ISO format and validate date ranges are reasonable
  4. Test import with 50 sample records in MC sandbox environment
  5. Generate import summary report showing original vs transformed values
  6. Create migration documentation package for compliance audit including mapping tables and transformation rules

This approach ensures data integrity while maintaining full audit traceability for your compliance review.

3

Good point about hidden rows. I filtered the Excel and found 23 duplicate entries that weren’t visible. Still stuck on the user validation though - we have about 40 legacy usernames that don’t match current MC users. Should we create placeholder accounts or map them to generic ‘Legacy User’ accounts? Also concerned about maintaining audit trail integrity if we change the incident IDs.

4

The date format inconsistency is a bigger problem than it seems. MC expects ISO format (YYYY-MM-DD) for incident dates. Use Excel formulas to standardize all date columns before import. For the duplicate IDs, I recommend creating a new unique identifier column that combines incident_id + department_code + year. This preserves traceability while ensuring uniqueness across the system.

5

For the date standardization, here’s a quick approach - in Excel add a helper column with this formula to convert all formats to ISO: =TEXT(A2,“YYYY-MM-DD”). Copy down and paste values. Regarding duplicate IDs, you could also check if the duplicates are actually different incident types that happened to get the same number in legacy system. Sometimes what looks like a duplicate is actually a CAPA vs Incident with same ID.