csv_to_json.md

CSV to JSON Transformation

[Overview] [Operation details] [Sample configuration]

Overview

You can use the csvToJson operation to transform a CSV payload in to a JSON payload.

Operation details

Properties

• headerPresent (Header):
  Specify whether the CSV input has a header row. This accepts Absent or Present as values. The Default value is Absent.
• valueSeparator (Separator):
  Specify the separator to use in the CSV input. Default is the , (comma). To use tab as the separator, use the value tab to this property. To use space, use the value space. This property accepts true or false as values. The Default is false. This is available only if the value of the headerPresent property is set to Present.
• columnsToSkip (Skip Columns):
  Specify columns to skip from the CSV payload. You can specify the columns as comma-separated values. This property supports more complex queries also, you can find full specifications here.
• dataRowsToSkip (Skip Data Rows):
  Specify number of data rows to skip. The Default value is 0.
  • If headerPresent is Present, then data rows are the rows excluding the first row.
  • If headerPresent is Absent, then data rows are the rows starting from the first row.
• csvEmptyValues (Empty Values): Specify how to treat empty CSV values in the output JSON.
• jsonKeys (JSON Keys): If you need to set custom keys in the JSON output, specify JSON keys in this property. Use a set of comma-separated strings as JSON keys. (eg: name,email). If this property is not specified, JSON keys of the output are determined as follows,
  • If the value of the headerPresent is Absent , JSON keys would be autogenerated (eg: key-1, key-2).
  • If the value of the headerPresent is Present, CSV header values would use as the JSON keys.
• dataTypes (Data Types) : This property represents the data types of JSON fields. Supporting data types are, String, Boolean, Integer and Number. The input for this property is a JSON. It is easy to config this with the Integrations Studio Property view . For this property, the property view provides a table with columns, "Column Name Or Index", "Is Column Name" and "Data Type". "Column Name Or Index" column accepts an index or name of a CSV column which you need to change the data type in the output JSON. "Is Column Name" gives you a dropdown with values Yes and No. The Default is Yes. The value Yes means, you have input a column name in the Column Name Or Index column. No means, you have given an index in the Column Name Or Index column. "Data Type" column represents the output data type.
• rootJsonKey (Root JSON Key): If you need to wrap the JSON output inside a wrapper object, specify the key for the wrapper object.

Sample configuration

Sample request

Following is a sample CSV request that can be handled by the csvToJson operation.

id,name,email,phone_number
1,De witt Hambidge,[email protected],true
2,Brody Dowthwaite,[email protected],false
3,Catlin Drought,[email protected],608-510-7991
4,Kissiah Douglass,[email protected],true
5,Robinette Udey,[email protected],true

Sample Configuration

Given below is a sample configuration for the csvToJson operation.

<CSV.csvToJson>
    <headerPresent>Present</headerPresent>
    <skipHeader>true</skipHeader>
    <columnsToSkip>"phone_number"</columnsToSkip>
    <dataRowsToSkip>1</dataRowsToSkip>
    <csvEmptyValues>Null</csvEmptyValues>
    <jsonKeys>index,name,email</jsonKeys>
    <dataTypes>[{"Column Name Or Index":"id","Is Column Name":"Yes","Data Type":"Number"},{"Column Name Or Index":"2","Is Column Name":"No","Data Type":"String"}]</dataTypes>
    <rootJsonKey>results</rootJsonKey>
</CSV.csvToJson>

Sample response

Following is a sample response from the operation.

{
    "results": [
        {
            "index": 2.0,
            "name": "Brody Dowthwaite",
            "email": "[email protected]"
        },
        {
            "index": 3.0,
            "name": "Catlin Drought",
            "email": "[email protected]"
        },
        {
            "index": 4.0,
            "name": "Kissiah Douglass",
            "email": "[email protected]"
        },
        {
            "index": 5.0,
            "name": "Robinette Udey",
            "email": "[email protected]"
        }
    ]
}