Import Set Row not populating data from CSV files

[mangeshbodh]

I am Facing an issue when processing the Integration.

Setup: 

Run Nightly Job which will load data from SFTP CSV to target table.

• Created Source table
• Created Staging table manually.
• Created Transform Map.(around 30 fields, out of those 20 are direct mapping and 10 are generated through calculation using source script)
• Created Data Source
• Attached the Transform Map to Data Source
• Created Scheduled Data Import and attached the data source to this.

Issue:

Recently, the CSV file added a new column which we had not defined in transform map. 

Rest all fields are same, delimiter is same. when the nightly job is executed, it runs creating an import set and the import set rows are not populated correctly, it causes the OnBefore script to ignore the row and the data is not processed. 

I am trying to debug this and not able to get to the root cause of how to address this, any suggestions how we can fix it ?

[Tanushree Maiti]

Hi @mangeshbodh 

Blank import set rows created from a CSV are typically due to formatting issues in the source file, such as blank lines or extra commas.

So you can try with following:

1. Validate your csv file to  identify hidden blank lines, extra commas, or inconsistent formatting.

2. Validate your mapping, coalesce, choice action once.

2. Use an onBefore Transform Script: To ignore empty records during the transform process (if they manage to load into the staging table), you can add a script to your transform map. This script checks if a coalesce (key) field is empty and, if so, tells ServiceNow to ignore the import set row.For example, to skip a row if the name field is empty:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
    if (source.u_name == "") { // Replace 'u_name' with your actual source field name        ignore = true;
    }
})(source, map, log, target);