Parsing of CSV files in CDM
Summarize
Summary of Parsing of CSV files in CDM
The CDM CSV parser in ServiceNow enables seamless importing and exporting of configuration data using CSV files by converting data between CSV and JSON formats. This facilitates easy identification, validation, and management of configuration data within the CDM editor or list view, enhancing data handling efficiency for customers.
Show less
Note that starting with the Washington DC release, DevOps Config is being prepared for deprecation and will no longer be installed on new instances, though it remains supported for existing environments.
Key Features
- CSV to JSON Parsing: When importing CSV files, each record is converted into a separate JSON Configuration Data Item (CDI) node under the data node, while metadata such as delimiter and headers is stored under the metadata node.
- Customizable Parsing Attributes: Customers can specify CSV parsing configurations including:
- Delimiter: Character that separates fields in the CSV (default is comma).
- containsHeader: Indicates whether the first CSV row is a header (default true).
- Headers: Optionally provide header names if the CSV lacks a header row.
- securedHeaders: List of header fields whose values are encrypted in JSON for security.
- Validation: Imported CSV data is validated within CDM to ensure proper deployment readiness.
- JSON to CSV Conversion: When exporting data, the original CSV format (including delimiter, headers, and secured fields) is preserved by utilizing the metadata node, maintaining data consistency.
Practical Use
ServiceNow customers can upload existing configuration data stored in CSV files with custom headers and delimiters, which the CDM CSV parser accurately converts into JSON for editing and deployment. Sensitive data fields can be secured automatically during this process. Conversely, when exporting configuration data, the system converts JSON back to CSV, preserving original formatting and security settings.
This functionality simplifies configuration data management by supporting standard CSV format (RFC 4180) and offering flexibility in handling headers and delimiters, making it easier to integrate external data sources into ServiceNow CDM applications.
Configuration Highlights
Key parameters control the parsing behavior:
- delimiter: Defines the CSV field separator.
- containsHeader: Indicates if the CSV includes a header row.
- headers: Specifies header names when the CSV lacks headers.
- securedHeaders: Marks fields whose values should be encrypted in JSON.
Providing these parameters enables precise control over how CSV data is parsed and rendered in CDM.
Next Steps
- Prepare CSV files with appropriate headers and delimiters tailored to your configuration data.
- Use the CDM CSV parser to import these files, ensuring metadata attributes are correctly set to reflect your data structure and security needs.
- Validate imported data in the CDM editor before deployment.
- When exporting, confirm that both data and metadata nodes are present to maintain original CSV formatting.
- For complex configuration data, consider adding necessary nodes in your CDM application to accommodate the imported data structure.
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.