How to remove garbage value while importing data using transform map

Shweta Kasbe1
Tera Contributor

‎01-12-2023 11:18 PM

How to remove garbage value while importing data using transform map.

For eg: I am having field location and in location i am importing which contains :-

100 South Charles Street, Baltimore,MD, ABC,101 Broadway East, Seattle,WA

whereas ABC is garbage value which should not be insert in location field. Please guide me this.

loca.png

 

0 Helpfuls
  • 672 Views
2 REPLIES 2

kmohammed
Kilo Guru

‎01-14-2023 10:14 AM

There are a few ways you can remove "garbage" values like "ABC" from the location field while importing data using a transform map in ServiceNow. Here are a few options:

Use a script in the transform map to remove the unwanted values. For example, you can use the replace() function to remove specific values from the location field before it is imported.

Use a regular expression to match and remove specific patterns of text in the location field. For example, you can use a regular expression to match any string of characters that appears after a comma, but before the next comma.

Use a custom function to validate and clean the data before importing. You can create a script include that contains a function that checks the location field for any unwanted values, and then remove them before importing the data.

Use data validation in the transform map. You can use the data validation feature in the transform map to validate the data and remove any unwanted values before importing them.

Use a data cleansing tool. you can also use a data cleansing tool like Talend, Informatica etc. which can help you to cleanse the data before importing it to ServiceNow.

It's a good practice to test your solution on a small sample of data before importing the entire data set.
It would be helpful if you can provide more information about the data you are importing, and the format of the data you are importing.

 

 

0 Helpfuls

kmohammed
Kilo Guru
In response to kmohammed

‎01-14-2023 10:15 AM

Here is an example of a script that you can use in a transform map to remove "garbage" values like "ABC" from the location field before it is imported:

 

// Use the replace() function to remove the unwanted value "ABC" from the location field
var location = current.location;
location = location.replace("ABC", "");
current.location = location;

 

 

This script uses the replace() function to search the location field for the string "ABC" and replace it with an empty string. This effectively removes the unwanted value from the field before it is imported.

You can also use regular expressions to match and remove specific patterns of text in the location field. Here is an example of a script that uses regular expressions to remove any string of characters that appears after a comma, but before the next comma:

 

// Use a regular expression to remove unwanted values from the location field
var location = current.location;
location = location.replace(/,.*?,/g, ",");
current.location = location;

 

 

This script uses the replace() function with a regular expression that matches any string of characters that appears after a comma and before the next comma. The g flag is used to replace all occurrences of the pattern.

In both of the above examples, it is important to test the scripts on a small sample of data before importing the entire data set to ensure that it works as expected without any issues.

Keep in mind that these are just examples and you may need to adapt them to fit your specific use case.
It would be helpful if you can provide more information about the data you are importing and the format of the data you are importing.

0 Helpfuls