

CSV Module Reference¶

CSV Module in ESB Enterprise Integrator helps working with CSV payloads. This transforms a given payload into another type of payload according to your requirements. You can change the type of the output payload using these transformation configurations as well. You can send the payload to be transformed in multiple ways (e.g., POST request ).

The following transformations can be performed with this module.

CSV to CSV transformation¶

You can use the CSV to CSV transformation to convert a CSV payload into another CSV payload according to your requirements using the configurations given below.

Operation details¶

Name	Parameter	Value	Description
Header	headerPresent	Absent Present	Specify whether the CSV input has a header row
Separator	valueSeparator	Default : "," (comma)	Specify the separator to use in the CSV input. To use tab as the separator, use the value tab to this property. To use space, use the value space.
Skip Headers	skipHeader	true, false	This is available only if the value of the `headerPresent` property is set to `Present`. The default value is `false`.
Skip Data Rows	dataRowsToSkip		Specify the number of data rows to skip in the CSV. The default 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.
Order by Column	orderByColumn		Order the CSV content by values of the given column. If you want to specify the column by column index, provide the index of the column (Indexes are starting from 1). To specify the column by column name, give the column name within double quotes (e.g., "name"). Specifying the column by column name works only if the value of the `headerPresent` property is `Present`.
Sort Columns	columnOrdering	Ascending, Descending	This option is enabled if the `orderByColumn` has a value. This determines whether the CSV should be ordered ascendingly or descendingly according to the given column. The default value is `Ascending`.
Skip Columns	columnsToSkip		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 below in CSV Columns Skipper Query.
Custom Header	customHeader		Set a custom header to the output CSV payload. If this property not specified, the header for the output CSV is determined as follows, - If the value of the `headerPresent` is `Absent` , the output CSV would not have a header. - If the value of the `headerPresent` is `Present` and `skipHeader` is set as `true`, output CSV would not have a header. - If `headerPresent` is `Present` and `skipHeader` is set as `false`, output CSV would have the header of the input CSV.
Separator	customValueSeparator	Default : "," (comma)	Values separator to use in the output CSV.

Sample configuration¶

Given below is a sample request.

id,name,email,phone_number  
1,De witt Hambidge,dwitt0@newsvine.com,true  
2,Brody Dowthwaite,bdowthwaite1@delicious.com,false  
3,Catlin Drought,cdrought2@etsy.com,608-510-7991  
4,Kissiah Douglass,kdouglass3@squarespace.com,true  
5,Robinette Udey,rudey4@nytimes.com,true

A sample synapse configuration for the csvToCsv operation is shown below.

<CSV.csvToCsv>  
  <headerPresent>Present</headerPresent> 
  <skipHeader>true</skipHeader> 
  <dataRowsToSkip>1</dataRowsToSkip> 
  <orderByColumn>2</orderByColumn> 
  <columnOrdering>Ascending</columnOrdering> 
  <columnsToSkip>"phone_number"</columnsToSkip>   
  <customHeader>index,name,email</customHeader>               
</CSV.csvToCsv>

The following is the sample response, for the request given above.

index,name,email  
2,Brody Dowthwaite,bdowthwaite1@delicious.com  
3,Catlin Drought,cdrought2@etsy.com  
4,Kissiah Douglass,kdouglass3@squarespace.com  
5,Robinette Udey,rudey4@nytimes.com

CSV to JSON transformation¶

You can use the CSV to JSON transformation to convert a CSV payload into a JSON payload according to your requirements using the configurations given below.

Operation details¶

Name	Parameter	Value	Description
Header	headerPresent	Absent Present	Specify whether the CSV input has a header row
Separator	valueSeparator	Default : "," (comma)	Specify the separator to use in the CSV input. To use tab as the separator, use the value tab to this property. To use space, use the value space.
Skip Headers	skipHeader	true, false	This is available only if the value of the `headerPresent` property is set to `Present`. The default value is `false`.
Skip Data Rows	dataRowsToSkip		Specify the number of data rows to skip in the CSV. The default 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.
Empty Values	csvEmptyValues		Specify how to treat empty CSV values in the output JSON.
JSON Keys	jsonKeys		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 be used as the JSON keys.
Data Types	dataTypes		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.
Root JSON Key	rootJsonKey		If you need to wrap the JSON output inside a wrapper object, specify the key for the wrapper object.

Sample configuration¶

Given below is a sample request.

id,name,email,phone_number  
1,De witt Hambidge,dwitt0@newsvine.com,true  
2,Brody Dowthwaite,bdowthwaite1@delicious.com,false  
3,Catlin Drought,cdrought2@etsy.com,608-510-7991  
4,Kissiah Douglass,kdouglass3@squarespace.com,true  
5,Robinette Udey,rudey4@nytimes.com,true

A sample synapse configuration for the csvToJson operation is shown below.

<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>

The following is the sample response, for the request given above.

{
    "results": [
        {
            "index": 2.0,
            "name": "Brody Dowthwaite",
            "email": "bdowthwaite1@delicious.com"
        },
        {
            "index": 3.0,
            "name": "Catlin Drought",
            "email": "cdrought2@etsy.com"
        },
        {
            "index": 4.0,
            "name": "Kissiah Douglass",
            "email": "kdouglass3@squarespace.com"
        },
        {
            "index": 5.0,
            "name": "Robinette Udey",
            "email": "rudey4@nytimes.com"
        }
    ]
}

CSV to XML transformation¶

You can use the CSV to XML transformation to convert a CSV payload into a XML payload according to your requirements using the configurations given below.

Operation details¶

Name	Parameter	Value	Description
Header	headerPresent	Absent Present	Specify whether the CSV input has a header row
Separator	valueSeparator	Default : "," (comma)	Specify the separator to use in the CSV input. To use tab as the separator, use the value tab to this property. To use space, use the value space.
Skip Headers	skipHeader	true, false	This is available only if the value of the `headerPresent` property is set to `Present`. The default value is `false`.
Skip Data Rows	dataRowsToSkip		Specify the number of data rows to skip in the CSV. The default 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.
Skip Columns	columnsToSkip		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 below in CSV Columns Skipper Query.
Root Element Group			You can use the properties under this group to config the root XML element of the output XML payload. You can find the following properties under the root element group.
Tag	rootElementTag		Name of the XML tag of the root element. The default value is root.
Namespace	rootElementNamespace		Namespace of the root element.
Namespace URI	rootElementNamespaceURI		Namespace URI of the root element.
Group Element Group			The properties under this group are for configuring the group elements of the output XML payload. You can find the following properties under the group element group.
Tag	groupElementName		Name of the XML tag of the group element. The default value is group.
Namespace	groupElementNamespace		Namespace of the group element.
Namespace URI	groupElementNamespace		Namespace URI of the group element.

Sample configuration¶

Given below is a sample request.

id,name,email,phone_number  
1,De witt Hambidge,dwitt0@newsvine.com,true  
2,Brody Dowthwaite,bdowthwaite1@delicious.com,false  
3,Catlin Drought,cdrought2@etsy.com,608-510-7991  
4,Kissiah Douglass,kdouglass3@squarespace.com,true  
5,Robinette Udey,rudey4@nytimes.com,true

A sample synapse configuration for the csvToXml operation is shown below.

<CSV.csvToXml>
    <headerPresent>Present</headerPresent>
    <skipHeader>true</skipHeader>
    <columnsToSkip>"phone_number"</columnsToSkip>
    <tagNames>index,name,email</tagNames>
    <rootElementTag>results</rootElementTag>
    <groupElementTag>result</groupElementTag>
</CSV.csvToXml>

The following is the sample response, for the request given above.

<results>
    <result>
        <index>1</index>
        <name>De witt Hambidge</name>
        <email>dwitt0@newsvine.com</email>
    </result>
    <result>
        <index>2</index>
        <name>Brody Dowthwaite</name>
        <email>bdowthwaite1@delicious.com</email>
    </result>
    <result>
        <index>3</index>
        <name>Catlin Drought</name>
        <email>cdrought2@etsy.com</email>
    </result>
    <result>
        <index>4</index>
        <name>Kissiah Douglass</name>
        <email>kdouglass3@squarespace.com</email>
    </result>
    <result>
        <index>5</index>
        <name>Robinette Udey</name>
        <email>rudey4@nytimes.com</email>
    </result>
</results>

JSON to CSV transformation¶

You can use the JSON to CSV transformation to convert a JSON payload into a CSV payload according to your requirements using the configurations given below.

Operation details¶

Name	Parameter	Value	Description
CSV Header	customHeader		Set a custom header to the output CSV payload. If this property is not specified, the key values of the input would be used as the output CSV headers.

Sample configuration¶

Given below is a sample request.

[
    {
        "id": "1",
        "name": "De witt Hambidge",
        "email": "dwitt0@newsvine.com",
        "phone_number": "true"
    },
    {
        "id": "2",
        "name": "Brody Dowthwaite",
        "email": "bdowthwaite1@delicious.com",
        "phone_number": "false"
    },
    {
        "id": "3",
        "name": "Catlin Drought",
        "email": "cdrought2@etsy.com",
        "phone_number": "608-510-7991"
    },
    {
        "id": "4",
        "name": "Kissiah Douglass",
        "email": "kdouglass3@squarespace.com",
        "phone_number": "true"
    },
    {
        "id": "5",
        "name": "Robinette Udey",
        "email": "rudey4@nytimes.com",
        "phone_number": "true"
    }
]

A sample synapse configuration for the jsonToCsv operation is shown below.

<CSV.jsonToCsv>
    <customHeader>index,name,email,number</customHeader>
</CSV.jsonToCsv>

The following is the sample response, for the request given above.

index,name,email,number
1,De witt Hambidge,dwitt0@newsvine.com,true
2,Brody Dowthwaite,bdowthwaite1@delicious.com,false
3,Catlin Drought,cdrought2@etsy.com,608-510-7991
4,Kissiah Douglass,kdouglass3@squarespace.com,true
5,Robinette Udey,rudey4@nytimes.com,true

XML to CSV transformation¶

You can use the XML to CSV transformation to convert a XML payload into a CSV payload according to your requirements using the configurations given below.

Operation details¶

Name	Parameter	Value	Description
CSV Header	customHeader		Set a custom header to the output CSV payload. If this property is not specified, Key values of the input would be used as the output CSV headers.

Sample configuration¶

Given below is a sample request.

<root>
    <group>
        <id>1</id>
        <name>De witt Hambidge</name>
        <email>dwitt0@newsvine.com</email>
        <phone_number>true</phone_number>
    </group>
    <group>
        <id>2</id>
        <name>Brody Dowthwaite</name>
        <email>bdowthwaite1@delicious.com</email>
        <phone_number>false</phone_number>
    </group>
    <group>
        <id>3</id>
        <name>Catlin Drought</name>
        <email>cdrought2@etsy.com</email>
        <phone_number>608-510-7991</phone_number>
    </group>
    <group>
        <id>4</id>
        <name>Kissiah Douglass</name>
        <email>kdouglass3@squarespace.com</email>
        <phone_number>true</phone_number>
    </group>
    <group>
        <id>5</id>
        <name>Robinette Udey</name>
        <email>rudey4@nytimes.com</email>
        <phone_number>true</phone_number>
    </group>
</root>

A sample synapse configuration for the xmlToCsv operation is shown below.

<CSV.xmlToCsv>
  <customHeader>index,name,email,number</customHeader>
</CSV.xmlToCsv>

The following is the sample response, for the request given above.

index,name,email,number
1,De witt Hambidge,dwitt0@newsvine.com,true
2,Brody Dowthwaite,bdowthwaite1@delicious.com,false
3,Catlin Drought,cdrought2@etsy.com,608-510-7991
4,Kissiah Douglass,kdouglass3@squarespace.com,true
5,Robinette Udey,rudey4@nytimes.com,true

CSV Columns Skipper Query¶

The columnsToSkip (Skip Columns) property in CSV to JSON, CSV to XML, CSV to CSV operations supports a simple query language to configure the skipping columns.

Queries¶

Single Column¶

The column selection query can be a single column representing one column in the CSV. You can represent a column with its index or using the header name for that column.

Column index : Column indexes are starting from 1. You can give a single column index as the column skipper query. E.g., 3
Column name: You can specify a column using its name. Note that, this feature work only if the value of the headerPresent property is Present. You can give the column name within double quotations in the columns skipper query. (E.g., "email")

Multiple Columns¶

You can select multiple columns by combining them with a comma (,).

1,2,3,

"name":"email"

3:"email"

Element Range¶

You can specify a range of columns in the query. Use colon character (:) to define a range.

1:5

"name":"email"

3:"email"

You can use asterisk symbol to represent the last column in case you don't know the number of columns. For example if you want to skip all the columns from column 3, then use the following query,

3:*

Group Elements¶

You can use opening and closing brackets to define a group of elements. A few examples are shown below.

(1:5)

(3,4,"name")

2,3,("name":*)

Not Syntax¶

You can use the exclamation mark (!) to exclude columns from columns skipper. For example, if you want to skip all the columns from 5 to 10 but want to include 7^th column, can use the query given below

5:10,!7

The following is an additional example of how you can use this.

3:*,!(10:"email")

Top