Parsing of CSV files in CDM
Summarize
Summary of Parsing of CSV files in CDM
The CDM CSV parser allows ServiceNow customers to upload and manage configuration data from CSV files efficiently. It converts CSV data to JSON format, enabling easy identification of data attributes and configuration items within the CDM editor or list view. Note that DevOps Config is being prepared for deprecation; it will be hidden in new instances but supported in existing ones.
Show less
Key Features
- CSV to JSON Conversion: Upload CSV files with custom delimiters and headers, converting them to key-value pairs in JSON format.
- Data Format Attributes: Differentiate between metadata and configuration data in the CDM editor.
- Validation: Validate imported CSV data before deployment.
- CSV Export: Convert JSON data back to CSV format while preserving the original attributes.
Key Outcomes
By using the CDM CSV parser, customers can:
- Efficiently import configuration data and ensure it is correctly formatted for use in CDM.
- Utilize metadata to define how data is structured and displayed, enhancing clarity and usability.
- Maintain data integrity during the import and export process, facilitating smoother workflows.
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.