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:

Text
Number
Decimal
Timestamp
Positive number
Date
Date time
Time
Duration
Currency
Length
Temperature
Boolean
Email
URL
List
Map
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	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 (.)
Length	Decimal separator	Comma (,), Dot (.)
Temperature	Thousands separator	None, Comma (,), Dot (.)
Temperature	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:

You have to choose at least one tile from the Day, Month, and Year tiles.
You can use the characters, 'Z' and 'T' only once in a datetime and a time format.
The tiles that follow the character, 'T' must be related to time such as Hours, Mins, Sec.
A datetime and time format must contain an Hours tile.
In a datetime format, you can use either the character, 'Z ' or any one of the T.zone tiles.
You can use any one of the T.zone tiles in a time format.
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:

The format cannot be empty.
You can use a string only once in a format.

For example, in the duration format, 301.201.21:12:00, the duration is repeated twice which makes it an invalid duration format.

You can use either microsecond or millisecond in a duration format and not both.

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.

SEE ALSO
Create your own custom data type

Manage custom data types

Data types supported in Zoho DataPrep

Date and time formats supported in Zoho DataPrep

Duration formats supported in Zoho DataPrep

Currency units supported in Zoho DataPrep

Zoho CRM Training Programs

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

Redefine the way you work

with Zoho Workplace

Start your free trial

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.

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

Zoho Workerly Home
Forums
Connect With Us:

Zoho Recruit Home
Forums
Connect With Us:

Start tracking your website metrics today

Use PageSense to understand what your visitors are looking for, how they are behaving, and where they are facing problems on your website.

Ready to improve your website's performance?

Install the PageSense code snippet on your site in a matter of minutes and start collecting in-depth data about the website visitors to grow your business.

Track and measure every business goal

Set up goals in PageSense to measure every single action performed by visitors on your website like button or link clicks, form submissions, and page engagements.

Understand the customer journey on your website

Create funnels in PageSense to quickly see which pages visitors use to enter your website, where they navigate to next, and which pages they decide to leave without converting.

Visualize your visitor's behavior with color codes

Set up heatmaps in PageSense to see where users have clicked more, how far they've scrolled, and on which parts of a page they've spent the most time using color-coded patterns in reports.

Measure customer engagement with your web forms

Use form analytics in PageSense to see how people interact with different fields in your form, whether they complete the form successfully or not, and where exactly they drop out on your form.

Record real visitor interactions on your website

Use session recordings in PageSense to watch a video of all the visitor actions performed on your website including the pages they navigate, the buttons they click, the UX issues they face, and more.

Test and optimize webpages for better conversions

Run A/B or Split URL tests in PageSense to figure out which version of your web page works best for your business and results in the best conversion rate.

Give each customer a unique website experience

Use personalization in PageSense to deliver customized versions of your website for every individual customer based on their demographics, local weather, browsing history, and more.

Grow your business through customer feedback

Run polls on your website using PageSense to understand what your customers think about your products/services and what needs improvement on your site.

Send timely updates that customers love

Use web push notifications in PageSense to schedule and notify your customers about an upcoming flash sale, product releases, promotional coupons, and a lot more that can spark conversions on your website.

Advertise your business to website visitors

Use pop-ups in PageSense to instantly grab the attention of visitors by showing attractive signup offers, coupon code discounts, or email newsletters that can eventually convert them into subscribers.

Access more advanced settings in PageSense

Use PageSense's advanced features like creating mutually exclusive groups, enabling cross-domain tracking, configuring customized project JS, and more to get deeper insights about your website.

Try easy-to-use extensions

Download the PageSense extension app available for your web browser with a few clicks and start collecting all of your required website metrics in real time.

Discover your favorite integrations with PageSense

Get a deeper look at your website's data by seamlessly integrating PageSense with a host of popular third-party apps like Google Analytics, Mixpanel, Intercom, and more.

Quick Links	Workflow Automation	Data Collection
Web Forms	Enterprise	Begin Data Collection
Interactive Forms	Workplace	Data Collection App
Offline Forms	Customer Service	Accessible Forms
Digital Forms	Marketing	Forms for Small Business
HTML Forms	Education	Zoho Forms for Enterprise
Contact Forms	E-commerce
Lead Generation Forms	Healthcare
Wordpress Forms	Customer onboarding
No Code Forms	Construction
Free Forms	Travel
Prefill Forms	Non-Profit
Intake Forms
Form Designer
Card Forms
Assign Forms
Translate Forms
Electronic Forms
Notification Emails for Forms
Holiday Forms
Form to PDF
Email Forms CRM Forms Embeddable Forms Drag and Drop form builder

Zoho Pagesense Resources

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.

New to Zoho Survey?

Access Zoho Survey

Latest Feature Updates

Explore our Pricing & Plans

Zoho Survey Resources

Android Mobile Surveys

Insurvey Blogs

Insight Blogs

Tips & Tricks

New to Zoho Social?

Manage your brands on social media

Access Zoho Social

Zoho Desk Resources

Desk Community Learning Series
Digest
Functions
Meetups
Kbase
Resources
Glossary
Desk Marketplace
MVP Corner
Word of the Day

Zoho Sheet Resources

New to Zoho Forms?

Latest Feature Updates

Explore our Pricing & Plans

Zoho Forms Resources

Secure your business

communication with Zoho Mail

Get started

Mail on the move with

Zoho Mail mobile application

Go Mobile

Stay on top of your schedule

at all times

Get started

Carry your calendar with you

Anytime, anywhere

Get started

Latest Announcements

New to Zoho Sign?

Zoho Sign Resources

Sign, Paperless!

Sign and send business documents on the go!

Get Started Now

Zoho SalesIQ Resources

New to Zoho TeamInbox?

Zoho TeamInbox Resources

Connect with us

New to Zoho ZeptoMail?

LEARN MORE

Latest Feature Updates

Zoho DataPrep Resources

New to Zoho DataPrep?

Access Zoho DataPrep

Zoho DataPrep Demo

Get a personalized demo or POC

Design. Discuss. Deliver.

Create visually engaging stories with Zoho Show.

Get Started Now

New to Zoho Workerly?

Access Zoho Workerly

New to Zoho Recruit?

Access Zoho Recruit

New to Zoho CRM?

Signup Now

Access Zoho CRM

Latest Feature Updates

New to Zoho Projects?

Access Zoho Projects

New to Zoho Sprints?

Access Zoho Sprints

You are currently viewing the help articles of Sprints 1.0. If you are a user of 2.0, please refer here.

You are currently viewing the help articles of Sprints 2.0. If you are a user of 1.0, please refer here.

New to Zoho Assist?

Access Zoho Assist

New to Bigin?

Signup Now

Access Bigin

Latest Feature Updates

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 ...