Wrap Text Fields in Quotes for Export and Download¶
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.
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,
Go to Admin Settings > Compose Settings to open the Compose tab of the settings:
On the Compose tab, find the switch Wrap Text Fields in Quotes for Exports and Downloads. Click the switch to toggle it on:
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