Wrap Text Fields in Quotes for Export and Download

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Applies from version 5.9.12

Wrap Text Fields in Quotes for Exports and Downloads is a setting for Compose found in Admin Settings > Compose Settings. It was added to allow for seamless import of Compose query results into MS Excel keeping the correct values for the text-like data types when columns may have numeric values with leading zeros.

Note

Wrap Text Fields in Quotes for Exports and Downloads setting applies to the following data types:

CHAR  VARCHAR  LONGVARCHAR  CLOB  SQLXML  NCHAR

NCLOB  DATALINK  DISTINCT   LONGNVARCHAR   NVARCHAR

You need the role of the Server Admin to access and change this setting.

When columns of text-like types have numeric values that start with leading zeros, such zeros are known to be dropped by Microsoft Excel when query result sets exported from Alation are imported into this tool. For example, if a field of type varchar has value 000000000000234, it will be interpreted as a numeric field by MS Excel and displayed as 234.

To avoid having to fix the cell formatting in MS Excel and other desktop tools, you can enable pre-formatting of these data types using the Wrap Text Fields in Quotes for Exports and Downloads setting. This pre-formatting consists in “wrapping” the text-like data types in additional quote marks in the result set files exported or downloaded from Alation. When enabled,  such field values will be wrapped in a set of additional quote marks in the exported or downloaded file to prevent these values from being treated as a numeric type by MS Excel and other desktop applications.

To enable Wrap Text Fields in Quotes for Exports and Downloads,

  1. Go to Admin Settings > Compose Settings to open the Compose tab of the settings:

    ../../_images/Text_Wrap_01.png

  2. On the Compose tab, find the switch Wrap Text Fields in Quotes for Exports and Downloads. Click the switch to toggle it on:

    ../../_images/Text_Wrap_02.png

  1. Click Save the changes to apply the change:

    ../../_images/Text_Wrap_03.png

Examples

When this setting is enabled, your result set exports and downloads from Compose and the Catalog will wrap text-like fields into additional quotes. These added quotes will NOT appear in Excel sheets when you import the exported or downloaded result sets.

Here is an example of a query results exported to CSV with Wrap Text Fields in Quotes for Exports and Downloads enabled (assuming that inventory_code column is varchar type):

field_id,inventory_code
3065,"=""007370013197"""
3137,"=""006370013199"""
3283,"=""000524731198"""
3333,"=""000546731197"""
1467,"=""000647001319"""
3151,"=""000786519198"""

To compare, this is how the same export looks like with Wrap Text Fields in Quotes for Exports and Downloads disabled:

field_id,inventory_code
3065,007370013197
3137,006370013199
3283,000524731198
3333,000546731197
1467,000647001319
3151,000786519198