Data types supported in Zoho DataPrep

Data types supported in Zoho DataPrep

Data type is a key attribute associated with a column using which the valid and invalid data are classified and data quality is calculated. In Zoho DataPrep, there are a variety of data types to suit your needs, but they are all built upon a few fundamental data types that we call base data types. All other data types are derived from these base data types. digits

The base data types available in Zoho DataPrep are given in the table below:

Base datatype

Max limit/Range

Formats/Units supported

Number

23 digits

All formats

Text

-

(?s).*

Decimal

38 precision and 15 scale

where,

  1. Precision is the number of digits in a number. Of the 38, maximum 23 digits can be placed to the left of the decimal point.

  2. Scale is the number of digits after the decimal point.

All formats

Date

1-1-1000 to 9-9-9999 


dd MMM, yyyy 

Click here to know more. 

Date-time

1-1-1000 00:00:00 to 9-9-9999 23:59:59

dd MMM, yyyy hh:mm:ss Click here to know more.

Duration

Days - 0 to 36500000

Hours - 00 to 24

Seconds - 00 to 59

Milliseconds - 0 to 999

Microseconds 0 to 999999

D.HH:mm:ss.SSSSSS.
Click here to know more.

From the above base data types the following data types are derived.

Derived datatype

Derived from

Max limit

Formats/Units supported

Currency

Decimal

38 precision and 15 scale

where,

  1. Precision is the number of digits in a number. Of the 38, maximum 23 digits can be placed to the left of the decimal point. 

  2. Scale is the number of digits after the decimal point.

 

£,$,₩,₨,﷼. Click here to know more.

Temperature

Decimal

38 precision and 15 scale

where,

  1. Precision is the number of digits in a number. Of the 38, maximum 23 digits can be placed to the left of the decimal point.

  2. Scale is the number of digits after the decimal point.

 

°C, °F, K

Note: A base unit is the fundamental unit in a system of measurement. In DataPrep, conversions between different units and the histogram representation of data distribution is applied on top of the base unit. For example, for the Temperature datatype, the base unit is celcius (°C).

 

List

Text

-

Array

Example: ["North", "South", "East", "West"]

Map

Text

-

Object

Example: {"Region":"North", "Product":"Furniture", "Customer Name":"John Brito", "Sales":"272"}

Boolean

Text

-

Yes, No, True, False

URL

Text

-

protocol://domain/path#locationhash?paramname=paramvalues

 

Example: https://help.zoho.com/portal/en/kb/dataprep

Email

Text

-

username@domain

A valid email address consists of an email prefix and an email domain.

The prefix appears to the left of the @ symbol and the domain at right.

 

Email prefix:

  1. Allowed characters: letters (a-z), numbers, underscores, periods, and dashes.

  2. An underscore, period, or dash must be followed by one or more letter or number.

             

Email domain:

  1. Allowed characters: letters, numbers, dashes.

  2. The last portion of the domain must be at least two characters. For example: .com, .org, .cc

 

Length

Decimal

38 precision and 15 scale

where,

  1. Precision is the number of digits in a number. Of the 38, maximum 23 digits can be placed to the left of the decimal point. 

  2. Scale is the number of digits after the decimal point.

 

cm, m, km, mm, mi, µm, nm, ft, yd, in, nmi

 

Note: A base unit is the fundamental unit in a system of measurement. In DataPrep, conversions between different units and the histogram representation of data distribution is applied on top of the base unit. For example, for the Length datatype, the base unit is meter (m). 

 

 

Positive Number

Number

23 digits

N>=0

where N is the number.

Percentage 

Decimal

38 precision and 15 scale

where,

  1. Precision is the number of digits in a number. Of the 38, maximum 23 digits can be placed to the left of the decimal point.

  2. Scale is the number of digits after the decimal point.

 

%

Timestamp

Datetime

 

-

millis 

Example: 1635509077

Number of milliseconds calculated since January 1, 1970 , 00:00:00 GMT. A negative number is the number of milliseconds before this date.

Time Datetime -HH:mm:ss
Click here to know more.


Note: You can also create your own datatype to measure the data quality of organization specific data such as, employee ID, asset ID, etc., and validate them. Use the Create custom datatype option available as part of the Change datatype transform to create your own datatype.

Data types and supported transforms 

In Zoho DataPrep, some transforms can only be applied over certain data types. Let us look into the transforms available exclusively for each datatype.


Datatype

Transforms exclusive to the data type

Formats / Units supported

Default format

 

Base data types

 

Number

Change format, Round off, Extract from number

All formats

Comma (,) is used as the default thousand separator.

Text

Cluster and Merge, Change Case, Extract from text, Sentiment analysis, Language detection, Keyword extraction

(?s).*

.*

Decimal

Change format, Round off

All formats

Comma (,) is used as the default thousand separator and dot (.) is used as the default decimal separator.

Date 

Change date format, Extract from date

yy, yyyy, MM, MMM, MMMM, dd, EEE, EEEE. Click here to know more. 

dd MMM, yyyy

Date-time 

Change date format, Extract from date

HH, hh, mm, m, ss, s, SSS, a. Click here to know more.

dd MMM, yyyy hh:mm:ss

DurationChange duration format
 D.HH:mm:ss.SSSSSS
Click here to know more.
D.HH:mm:ss.SSSSSS

 

Derived datatypes

 

Currency

-

£,$,₩,₨,﷼. Click here to know more

Comma (,) is used as the default thousand separator and dot (.) is used as the default decimal separator.

Temperature

Convert units

°C, °F, K

 

Dot (.) is used as the default decimal separator.

List

Extract from List

Array

Example:Region= ["North", "South", "East", "West"]

-

Map

Extract from Map

Object

Example:Sales={"Region":"North", "Product":"Furniture", "Customer Name":"John Brito", "Sales:272"}

-

Boolean

-

^(?i)(true|false)$

-

URL

Extract from URL 

protocol://domain/path

Example: https://help.zoho.com/portal/en/kb/dataprep

protocol://domain/path

Email

Extract from Email

username@domain

A valid email address consists of an email prefix and an email domain.

The prefix appears to the left of the @ symbol and the domain appears to the right. For example, username@example.com

 

Email prefix:

  1. Allowed characters: letters (a-z), numbers, underscores, periods, and dashes.

  2. An underscore, period, or dash must be followed by one or more letter or number.

             

Email domain:

  1. Allowed characters: letters, numbers, dashes.

  2. The last portion of the domain must be at least two characters. For example: .com, .org, .cc

 

username@domain

Length

Convert units

cm, m, km, mm, mi, µm, nm, ft, yd, in, nmi

 

Comma (,) is used as the default thousand separator and dot (.) is used as the default decimal separator.

Positive Number

-

N>=0

where N is the number.

Comma (,) is used as the default thousand separator.

Percentage 

-

Comma (,) is used as the default thousand separator and dot (.) is used as the default decimal separator.

Timestamp 

-

millis

Example: 1635509077

Number of milliseconds calculated since January 1, 1970 , 00:00:00 GMT. A negative number is the number of milliseconds before this date.

TimeChange time format, Extract from timeHH:mm:ss 
Click here to know more
HH:mm:ss


    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

                                                                                    • Duration formats supported in Zoho DataPrep

                                                                                      Duration is the difference between two time values. A duration format can be formed using different format strings such as D, HH, SSSSSS, etc. In Zoho DataPrep, the default format for the duration data type is D.HH:mm:ss.SSSSSS.  Zoho DataPrep ...
                                                                                    • Units supported for currency datatype

                                                                                      Currency is a data type that is derived from the decimal data type. For example, India uses the currency, Indian Rupee. In Zoho DataPrep, there are a variety of data types to suit your needs. Click here to know more about the data types supported in ...
                                                                                    • Zoho DataPrep's Home page

                                                                                      The home page gives you a brief overview of the workspaces you own, where they are located, and the workspaces that are shared with you. From Zoho DataPrep's home page, you can manage your Zoho DataPrep account, create and manage workspaces. The ...
                                                                                    • Import data from Zoho CRM

                                                                                      Zoho DataPrep allows you to seamlessly bring in your sales data from Zoho CRM using the Zoho CRM connector. Using the connector, you can import your sales data from CRM to perform data cleanup, data migration, and data backup in more than one way.  ...
                                                                                    • Zoho DataPrep - FAQ

                                                                                      1. What is Zoho DataPrep? Zoho DataPrep is an advanced self-service data preparation tool that helps organizations model, cleanse, prepare, enrich and organize large volumes of data from multiple data sources to serve data analytics and data ...
                                                                                    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