Data import CSV File Pipe delimited with data encapsulated in double quotes

[dvelloriy]

Hello All,

I need to import a CSV (pipe delimited) file into ServiceNow.

Below is the format. Is it possible to load this into ServiceNow? Once this is loaded this should be stored in staging table and then we will transform into the respective target table

I tried loading it via data source however it is throwing an error and created just 1 column. we need 3 separate columns and data going into respective columns. Please advise.

"firstname"|"lastname"|"city"

"Lorne"|"Cleo"|"Portland"

[Riya Verma]

Hi @dvelloriy ,

Hope you are doing great.

To import the CSV file into ServiceNow -  we can utilize a combination of data sources and transform maps. Here's a step-by-step guide to help you achieve this:

1. Create a Staging Table First with same structure as the CSV file, with three separate columns to accommodate the data. You can create the table by navigating to "System Definition" -> "Tables" and clicking on "New" to define the table schema.
2. Create a Data Source Next, we'll create a data source to define the import settings for the CSV file. Go to "System Import Sets" -> "Data Sources" and click on "New" to create a new data source. Specify the appropriate settings, including the source file format (CSV), delimiter (pipe "|"), and the staging table you created in the previous step.
3. Create a Transform Map Now to map the columns from the CSV file to the respective fields in the staging table. Go to "System Import Sets" -> "Transform Maps" and click on "New" to define a new transform map. In the mapping configuration, ensure that you map each column from the CSV file to the appropriate field in the staging table.

var transformMap = new GlideImportSetTransformer();
transformMap.setSourceName('csv_column1'); // Replace 'csv_column1' with the actual CSV column name
transformMap.setTargetName('staging_table.field1'); // Replace 'staging_table.field1' with the appropriate field in the staging table
transformMap.save();

4. Import the CSV File Now that you have defined the data source and transform map, you can import the CSV file. Go to "System Import Sets" -> "Load Data" and select your data source and the CSV file you want to import. Click on "Load Data" to initiate the import process.

Once the import is complete, the data from the CSV file will be loaded into the staging table with the correct column separation as defined in the transform map.

5. Transform the Data into Target Table(s) Lastly, you can create business rules or scripts to transform the data from the staging table into the respective target table(s) based on your business requirements. This can be achieved by performing data manipulations, validations, and transferring the data from the staging table to the target table(s).

[dvelloriy]

Hi Riya

This was not my question. i know the import process. However my question is how we need to import the data which is pipe delimited and encapsulated with double quotes.

[Riya Verma]

Hi @dvelloriy ,

Hppe you are doing great. You can use transform map script to concatenate 3 column data into 1 column in target table. 

Let us say in target table - column is ABC then we can write transform scripting referencing below code :

(function runTransformScript(source, target, map, log, isImport) {
  // Concatenate the values of 'column1' and 'column2' and store the result in 'concatenatedColumn'
  var Concatenated value = gs.getMessage(' "{0 }" || "{1}" ||  "{2}" ', [source.first_name,source.last_name,source.city])

  // Set the value of 'concatenatedColumn' in the target table
  target.ABC= concatenatedColumn;

})(source, target, map, log, isImport);