Parsing of CSV files in CDM
Summarize
Summary of Parsing of CSV files in CDM
The CDM CSV parser in the Australia release enables ServiceNow customers to upload and manage configuration data from CSV files within the Config Data Model (CDM) framework. It converts CSV data into JSON format, facilitating easy identification, validation, and editing of configuration data in the CDM editor or list view.
Show less
Note that starting with the Washington D.C. release, DevOps Config is being deprecated but will continue to be supported on existing instances.
Key Features
- CSV to JSON Parsing: Upload CSV files with custom headers and delimiters; the parser converts each CSV record into individual indexed JSON nodes under a data node, while metadata about the CSV format is stored separately.
- Data Format Attributes: Control parsing with attributes such as delimiter (default comma), containsHeader (indicates if first row is header), headers (custom header list if no header row), and securedHeaders (fields with encrypted values).
- Metadata Management: Metadata node stores parsing parameters, enabling consistent interpretation and validation of imported CSV data.
- CSV Export: When exporting config data back to CSV, the parser uses the data and metadata nodes to restore the original CSV format, including headers, delimiters, and secured fields.
Practical Application
ServiceNow customers can upload existing configuration data stored in CSV files into CDM, ensuring that key-value pairs are accurately extracted and displayed. The parser supports complex CSV content, including quoted fields with delimiters and escaped characters. Secured fields can be masked during import to protect sensitive information.
This functionality enables smooth import, validation, editing, and export of configuration data, maintaining data integrity and format consistency throughout the lifecycle.
Configuration Attributes and Usage
- delimiter: Defines the character separating fields (default is comma).
- containsHeader: Indicates if the first CSV row is a header; if false, a headers array must be provided.
- headers: Array of header names used when containsHeader is false; must match the number of record fields.
- securedHeaders: Headers whose values are encrypted in the JSON output to protect sensitive data.
Expected Outcome
By using the CDM CSV parser, customers can efficiently manage CSV-based configuration data with accurate parsing, secure handling of sensitive fields, and seamless round-trip conversion between CSV and JSON formats within the CDM environment.
The CDM CSV parser enables parsing of data in CSV files, so when you upload config data from a Comma-Separated Values (CSV) file into CDM, you can easily identify the data format attributes and config data in the CDM editor or list view.
- Uploading existing config data from a CSV file with custom header and delimiter attributes to parse them as appropriate key-value pairs in JSON.
- Differentiating data format attributes (metadata) from the config data items (data) in the CDM editor.
- Validating imported CSV data in CDM for deployment.
- Converting config data from JSON format to CSV format with attributes in the original format.
Parsing of CSV to JSON during import
- Adds each record from the CSV file as a CDI in separate indexed nodes under the data node in JSON format.
- Adds the data format attributes used during CSV upload under the metadata node.
The query parameter dataFormatAttributes includes attributes for determining the delimiter, headers, first row as header, and secured headers.
Table 1. Attributes for the CDM CSV parser Attribute name Description delimiter Character to separate out each header field as a key and the record field as a value of a CDI. You can specify a valid character as the delimiter. Default:
"delimiter": ",".containsHeader Option to determine whether the first row in the CSV file is considered as the header row. This attribute is optional. - true: The CSV file contains the first row as the header.
- false: The CSV file doesn't have the first row as the header. In this case, you must provide the headers in the headers attribute.
Default:
"containsHeader": "true".headers List of header fields for record fields in the CSV file. These headers are converted to the key names of the CDIs in the JSON format. Note:This attribute is applicable only if the containsHeader attribute is set to false.If your CSV file doesn't have the first row with headers, you can provide an array of headers. The number of headers must match the number of record fields. Example:
"headers": ["FirstName","LastName","Location","Salary"].Default: An empty array:
"headers": [].securedHeaders List of header fields for which the value for all records is displayed in the encrypted format in the JSON format. This attribute is optional. You can provide an array of headers that must be secured. The name of the secured headers must match the name of the headers in the headers attribute or CSV file. Example:
"securedHeaders": ["Salary"].Default: An empty array:
"securedHeaders": [].
Conversion of CSV to JSON format
- Data format attributes
- Note:This parameter is optional. If you don't provide values, the default values of the attributes are used.
{ "delimiter": ",", "containsHeader": "true", "headers": [], "securedHeaders": ["salary"] } - CSV format
//Sample CSV file content: no header in the first row; each line represents a record FirstName,LastName,Location,Salary David,Ben,NYC,1000 Jakes D,Ron,IRE,1220 "George, R",Martin,US,12120 "Antony, ""Ron",Mak,US,1210- JSON format
//data node for the records from CSV - each record row converted to individual nodes with fields separated by delimiter character converted to individual items of the node{ "data": { "0": { "FirstName": "David", "LastName": "Ben", "Location": "NYC", "Salary": "*****" }, "1": { "FirstName": "Jakes D", "LastName": "Ron", "Location": "IRE", "Salary": "*****" }, "2": { "FirstName": "George, R", //the name has a delimiter char so the full name is enclosed within " " "LastName": "Martin", "Location": "US", "Salary": "******" }, "3": { "FirstName": "Antony, \"Ron", //the last name has "" so a \ is added to include " in the last name "LastName": "Mak", "Location": "US", "Salary": "*****" } }, //metadata added for the data format attributes "metadata": { "containsHeader": "true", "delimiter": ",", "headers": "FirstName�LastName�Location�salary", "securedHeaders": "Salary" } }
Parsing of JSON to CSV during export
- Config data items within the data node are converted to individual record rows in the CSV file.
- Data format attributes within the metadata node are used to define the delimiter, header, and secret header information in the CSV file.