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. 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 categorized as invalid values in the column. However, they will not be removed. Click here to know more about constraints. 

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

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

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



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. 

    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


            Get started with Zoho Sign

            in a few quick steps!

            Download Help Guide





                      Still can't find what you're looking for?

                      Write to us: support@zohoforms.com


                            




                            

                        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 SalesIQ Resources



                                                  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

                                                                                    • 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 ...
                                                                                    • Convert units

                                                                                      DataPrep offers special operations to meet unique requirements with transforming data.  For example, if you need to share your dataset with different regions which use different standards of measurements, you can u se the Convert units special ...
                                                                                    • Change number format

                                                                                      DataPrep allows different regional formats for numbers.  For example, in India, one million is written as 10,00,000 whereas, in the United States, it is written as 1,000,000. To change the format of numerical values Right-click the numerical column ...
                                                                                    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