How to change data type of a column?

Change data type




Zoho DataPrep automatically identifies the data type of each column in your dataset during the time of import.

Sometimes due to multiple errors in the dataset, DataPrep may not correctly identify the data type, in which case you can manually override to change the data type of the column using the Change data type transform.

To change the data type of a column

1. Right-click the column name and select the Change data type  option from the context menu. 
         
2. The Select datatype dropdown in the Studio panel shows a list of datatypes with match accuracy.

Match accuracy is the percentage of data volume in the selected column that conforms to a data type.

3. DataPrep supports the following data types:
  1. Text
  2. Number
  3. Decimal
  4. Timestamp
  5. Positive number
  6. Date
  7. Date time
  8. Time
  9. Duration
  10. Currency
  11. Length
  12. Temperature
  13. Boolean
  14. Email
  15. URL
  16. List
  17. Map
  18. Percentage 
4 After selecting the datatype, choose the desired format from the available options. Click here  to know more about the format options.

Zoho DataPrep uses the format specified by the user to identify if the column data conforms to the data format specified and marks the column data as valid or invalid.

5. Enable the Add constraints toggle to set constraints and determine valid values in a column. The values that do not follow the constraints you have set will be categorised as invalid values in the column. However, they will not be removed. Click here to know more about constraints. 

6. If you want to set a column as mandatory, y ou can select the  Set as mandatory (not null)  checkbox.

7. You can also apply this transform along with constraints on multiple columns. Select the required columns using the  icon under  Columns to apply

8. Choose a datatype from the listed types and click Apply .



9.  You can also create your own data type by choosing Create custom data type  option.

Format options

  The following table covers the available format options for all the data types:

DATA TYPES

FORMAT OPTIONS

SUB-OPTIONS

Text

-

 -

Number

Thousands separator

None, Comma (,), Dot (.)

Decimal

Thousands separator

None, Comma (,), Dot (.)

Decimal separator

Comma (,), Dot (.)

Time

Time format

Click here to know the supported time formats.

Date

Date format

Click here to know the supported date formats.

Date time

Date time format

Click here to know the supported date time formats.

Timestamp

-

 -

Currency

Thousands separator

None, Comma (,), Dot (.)

Decimal separator

Comma (,), Dot (.)

Select unit

Click here to know the units supported for currency datatype.

Length

Thousands separator

None, Comma (,), Dot (.)

Decimal separator

Comma (,), Dot (.)

Temperature

Thousands separator

None, Comma (,), Dot (.)

Decimal separator

Comma (,), Dot (.)

Boolean

-

 -

Email

-

 -

URL

-

 -

List

-

 -

Map

-

 -

Duration

Duration format

Click here to know the supported duration formats.

Positive number

-

 -

Percentage

-

 -


To add a constraint

1. Enable the Add constraints toggle.

2. Select an option from the constraints section and enter the string.  The options are displayed based on the data type of the first column you have selected in the  Columns to apply  field. 



The following table lists all the available constraint options for these base data types:

Data type

Option

Sub-options

Text

Wildcard

Contains

 

 

Doesn't contain

 

 

Starts with

 

 

Doesn't start with

 

 

Ends with

 

 

Doesn't end with

 

 

In

 

 

Not in

 

 

Is

 

 

Is not

 

 

Patterns

 

 

Regex

 

String length comparators

Length greater than(>)

 

 

Length greater than or equal to(>=)

 

 

Length equal to(=)

 

 

Length not equal to()

 

 

Length lesser than(<)

 

 

Length less than or equal to(<=)

 

 

Length range

 

String length values

In

 

 

Not in

Number

Comparators

Greater than(>)

 

 

Greater than or equal to(>=)

 

 

Equal to(=)

 

 

Not equal to()

 

 

Less than(<)

 

 

Less than or equal to(<=)

 

 

Range

 

Individual values

In

 

 

Not in

Decimal

Comparators

Greater than(>)

 

 

Greater than or equal to(>=)

 

 

Equal to(=)

 

 

Not equal to

 

 

Less than(<)

 

 

Less than or equal to(<=)

 

 

Range

 

Individual values

In

 

 

Not in

 

Precision

NA

Date, Date time

Quarter

Q1, Q2, Q3, Q4

 

Month

January to December

 

Week

Week 1 to Week 53


Date
NA

 

Date & time

NA

 

Hours

0 to 23

 

Day of the week

Sunday to Saturday

 

Day of the month

1 to 31

 

Period(AM/PM)

AM, PM

 

Range

NA


3. When you add more than one constraint, the logical operators, AND or OR appear next to the constraints. You can click to toggle the logical operator between AND and OR.



Criteria expression

Using the logical operators, you can combine the constraints and apply logic to determine the rule of precedence. The final expression is displayed in the Criteria expression box. You can click Edit to alter the default expression using logical operators and parenthesis to specify the precedence or the sequential order as to which constraint should be evaluated first. Click Save after making the required changes. 

4. Click Apply .

If you select the Date, Datetime or Time types

You can select the required tiles and construct the Date, Datetime or the Time format. 

1. Click to select the different date and time components to build your custom format.

2. You can choose the 24-Hour format by selecting the hour-format icon which would appear at the right end when you hover your mouse over the text box.

3. You can click on the grip portion of the tile (grip icon that appears on hovering your mouse over) and move around the tiles to rearrange them.

4. A live preview of your date, datetime or a time column is shown as you build your format.



The date, datetime or the time format constructed is invalid if one or more of the following conditions are not met:
  1. You have to choose at least one tile from the Day, Month, and Year tiles.
  2. You can use the characters, 'Z' and 'T' only once in a datetime and a time format.
  3. The tiles that follow the character, 'T' must be related to time such as Hours, Mins, Sec.
  4. A datetime and time format must contain an Hours tile.
  5. In a datetime format, you can use either the character, 'Z ' or any one of the T.zone tiles.
  6. You can use any one of the T.zone tiles in a time format.
  7. You can choose only one format from the Day, Month, Year, Hours, Mins, and Sec tiles.
DataPrep allows only the following delimiters while constructing a date or a date-time format.

Date and Datetime delimiters
/ : - , . \s T Z
Time delimiters
/ : - , . \s Z

Note: \s denotes a single whitespace. Please provide an actual space when you provide the input. 

If you select the Duration data type

You can select a predefined format from the Duration only or Duration with days sections, choose to modify it, or construct formats using available format strings and delimiters. You can use the modulo % symbol to separate the format strings. Click here to know more about duration formats.

For example, %D.%H:%m:%s.%SSSSSS where %D is number of days, %H is number of hours, %m is number of minutes, %s is number of seconds, and %SSSSSS is number of microseconds.



A duration format is invalid if one or more of the following conditions are not met:
  1. The format cannot be empty. 
  2. You can use a string only once in a format. 
    1. For example, in the duration format, 301.201.21:12:00, the duration is repeated twice which makes it an invalid duration format. 
  3. You can use either microsecond or millisecond in a duration format and not both.
    1. For example, in the duration format, 12 days 1 hours 2 minutes 1.911 seconds 1.910001 seconds, both microseconds and milliseconds are used which renders it an invalid duration format.


    Zoho CRM Training Programs

    Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

    Zoho CRM Training
      Redefine the way you work
      with Zoho Workplace

        Zoho DataPrep Personalized Demo

        If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.

        Zoho CRM Training

          Create, share, and deliver

          beautiful slides from anywhere.

          Get Started Now


            Zoho Sign now offers specialized one-on-one training for both administrators and developers.

            BOOK A SESSION








                                    You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.




                                        Manage your brands on social media

                                          Zoho Desk Resources

                                          • Desk Community Learning Series


                                          • Digest


                                          • Functions


                                          • Meetups


                                          • Kbase


                                          • Resources


                                          • Glossary


                                          • Desk Marketplace


                                          • MVP Corner


                                          • Word of the Day


                                            Zoho Marketing Automation

                                              Zoho Sheet Resources

                                               

                                                  Zoho Forms Resources


                                                    Secure your business
                                                    communication with Zoho Mail


                                                    Mail on the move with
                                                    Zoho Mail mobile application

                                                      Stay on top of your schedule
                                                      at all times


                                                      Carry your calendar with you
                                                      Anytime, anywhere




                                                            Zoho Sign Resources

                                                              Sign, Paperless!

                                                              Sign and send business documents on the go!

                                                              Get Started Now




                                                                      Zoho TeamInbox Resources



                                                                              Zoho DataPrep Resources



                                                                                Zoho DataPrep Demo

                                                                                Get a personalized demo or POC

                                                                                REGISTER NOW


                                                                                  Design. Discuss. Deliver.

                                                                                  Create visually engaging stories with Zoho Show.

                                                                                  Get Started Now









                                                                                                      • Related Articles

                                                                                                      • Creating custom data type

                                                                                                        Custom data types are used to validate organization-specific data, such as employee ID, invoice ID, shipment tracking ID, or asset ID. By creating a custom data type, you can set a standard for your organization-specific columns in your data. This ...
                                                                                                      • Column Explorer

                                                                                                        The Column Explorer in Zoho DataPrep allows you to search, navigate and control the viewing options of the columns in a dataset. You can search and filter columns with invalid or missing data and fix them quickly. This helps you focus on the columns ...
                                                                                                      • Change date format

                                                                                                        DataPrep offers a wide range of predefined date and time formats. Choose to apply a predefined datetime format from the template or create your own custom format. Your custom format is saved to the template and can be used again in the future. To ...
                                                                                                      • Change case

                                                                                                        You can change the case of texts in a column using one of the following options: Upper case (E.g., ZOHO IS THE OPERATING SYSTEM FOR YOUR BUSINESS) Lower case (E.g., zoho is the operating system for your business) Title case (E.g., Zoho is the ...
                                                                                                      • Context menu

                                                                                                        Zoho DataPrep offers various options in the context menu to perform column level operations. A few basic options here are Sort, Rename, Delete and Move. To access the context menu, right click the column name and click any one of the icons to perform ...
                                                                                                        Wherever you are is as good as
                                                                                                        your workplace

                                                                                                          Resources

                                                                                                          Videos

                                                                                                          Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                                                                          eBooks

                                                                                                          Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                                                                          Webinars

                                                                                                          Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                                                                          CRM Tips

                                                                                                          Make the most of Zoho CRM with these useful tips.



                                                                                                            Zoho Show Resources