Issue when importing and transforming data: Field entry has same name but different capitalization

[AmirAli]

I import data using the Load Data module, from an excel doc -> create import set table -> auto-mapping fields -> transform

I have a field called 'Division' in my table that has options with the same values but different capitalization. For example EVERGREEN and Evergreen. Same spelling but different capitalization. When I perform a search for Division is evergreen it autocomplete and shows EVERGREEN first then Evergreen.

The data in the excel document is 'Evergreen' but after completing the load data process it populates to 'EVERGREEN' which ends up changing some other fields that rely on the Division as well. Is there anyway to fix this issue and populate the table with the correct Division data in the excel, without renaming the Divisions?

[Community Alums]

I think you are missing Coalesce on the unique field.

Regards

Suman P.

[AmirAli]

From what I understand Coalesce is used to prevent collisions when performing a transform to update data. What I am try to do is import data from an excel doc and create new records. The problem is that when I perform the transformation and import the data, the Division field changes the value from Evergreen to EVERGREEN.

This Division field has both Evergreen and EVERGREEN as choice options but since EVERGREEN is listed first in the table choice list, it is selected first when using a transform map. That is because the imported and transformed data does not check for capitalization.

Would coalesce still help in this situation or would there be another solution? 

[Sandeep Rajput · Accepted solution]

@AmirAli Use the "strict equality operator (===)" to compare strings based on their values and character case. 

Combine this with Transform Event scripts https://developer.servicenow.com/dev.do#!/learn/learning-plans/utah/new_to_servicenow/app_store_lear...

 and transform the correct values by using strict equality operator (===) in the transform script.

[AmirAli]

Thank you Sandeep, 

I believe this could fix the issue but might increase the transaction time when importing large sets of data. Is there a way to set the default behaviour of the transform map to use strictly equal comparisons?