Change Column Data Type
While transforming your data, you may need to change the data type of one or more columns.
For example, data of String type may be the easiest to manipulate. Since there are no mismatched values for String data type, you may wish to change a column's data type to this baseline type.
Data types that you see in the Transformer page represent types that are understood by the product.
When data is imported from a separate datastore, Designer Cloud may apply internal data types to the data. These types may differ from the original data typing in the source. As needed, the inferring of data types can be disabled at the file, connection, or global level. For more information, see Disable Type Inference.
When data is published from the product to a separate datastore, these types may be mapped to different data types in the target. For more information, see Type Conversions.
Astuce
You can use the Change Column Type transformation to override the data type inferred for a column. However, if a new transformation step is added, the column data type is re-inferred, which may override your specific typing. You should consider applying Change Column Type transformations as late as possible in your recipes.
For more information on the available data types, see Supported Data Types.
Change Type
You can change a column's data type in one of the following ways:
Change through Transform Builder
You can change data type for a single column or multiple columns through the Transform Builder. You can use a transformation like the following, which changes the columns LastName
, FirstName
, and Address
to String data type.
Transformation Name | |
---|---|
Parameter: Column 1 | LastName |
Parameter: Column 2 | FirstName |
Parameter: Column 3 | Address |
Parameter: New Type | String |
Note
You can lock the data type for columns to prevent it from being updated when the data is transformed in subsequent steps.
Note
When specifying a data type by name, you must use the internal value for the data type. The value in the column menu is the display name for the type.
For more information, see Valid Data Type Strings.
Lock Data Type
You can lock a column's data type through the Transform Builder. When a column's data type is locked, the data type is no longer automatically checked and updated by the Cloud Portal.
Astuce
If you do not wish to have the data types modified, you can add a transformation to lock all of them in a single step. Details are below.
Via Transform Builder
In the Search panel, enter
lock column type
.From the Columns drop-down, select any one of the following options:
Multiple: Select one or more columns from the drop-down list.
Range: Specify a start column and ending column. All columns inclusive are selected.
All: Select all columns in the dataset.
Note
This option locks all the column's data type.
Advanced: Specify the columns using a comma-separated list. You can combine multiple and range options under Advanced. Example:
c1,c3,c5~c8
Specify the other parameters.
To add the step to your recipe, click Add.
Example - lock a column's data type
This transformation locks the column data type:
Transformation Name | |
---|---|
Parameter: Columns | Multiple |
Parameter: Column 1 | Store_Nbr, Whse_Name |
Example - lock the data types for all columns
This transformation locks the data types for all columns:
Astuce
Many transformations support the Advanced option for column selection. You can specify column ranges, including all columns using the asterisk (*
) wildcard. See the following.
Transformation Name | |
---|---|
Parameter: Columns | Advanced |
Parameter: Column 1 | * |
Unlock Data Type
You can unlock a column's data type by following any one of these methods:
Via Transform Builder
In the Transformer Builder, you can select unlock to the current type option to apply the unlock feature to one or more columns.
This transformation unlocks the column data type:
Transformation Name | |
---|---|
Parameter: Columns | Multiple |
Parameter: Column 1 | Store_Nbr, Whse_Name |
Change Datetime Data Type
If you are changing a column's data type to Datetime, you must also select a format string to apply to the column.
Via Transform Builder
In the Transformer Builder, you can apply a specific transformation to format one or more columns to Datetime data type, using a specific format.
Astuce
You can use the following transformation to change the format of a Datetime column.
This transformation looks like the following:
Transformation Name | |
---|---|
Parameter: Columns | Multiple |
Parameter: Column 1 | myDate |
Parameter: New Type | Date/Time |
Parameter: Date/time Type | month*dd*yyyy*hh:MMaX |