Exporting Columnar Format

You can export DQL results in columnar format as csv, xls or xlsx files by specifying format=csv, format=xls or format=xlsx query parameters.

Exporting data in columnar format requires specifying an Export Spec which specifies the columns to export. Export spec specifies how to export an entity's json in a columnar format and is passed to the DQL query as exportspec parameter.

Export Spec format

Export spec can be specified in a simple format has the form fieldName,customName,numValues. Only the fieldName is mandatory.

• fieldName - the path to the field. You can use a simple dot notation skills.name or a single JsonPath spec to specify the field name such as $.name. For more advanced options, see JsonPath Filtering.
• customName - the column name. If not specified, it defaults to fieldName.
• numValues - for multi-value fields (json arrays), this specifies the number of values to include in the output. The special value all includes all values. This is ignored when a JsonPath is specified.

Multiple colums can be exported with a semicolon-delimited list of export specs. Example: $.name,Name;$.summary,Summary;$.skills.name,Skills;

Export Spec examples

Here are some examples to get you started.

Export fields with default field names

Export name and homepageUri. Column names are not specified so they are the same as field name:

$.name;$.homepageUri

Export fields with custom field names

Export ceo.name as CEO Name and summary as Summary columns:

$.ceo.name,CEO Name;$.summary,Summary;

Export multi-value fields

Export multi-value fields as comma separated values:

$.competitors[*].type,Competitors Type;$.competitors[*].name,Competitors Name

$.technographics[*].categories,technographics_categories

📘

For multi-value fields, the default separator is a comma (,). You can specify a different separator using the exportseparatorquery parameter.