Create an Application by Importing Data

Create an application by importing data

1. Preparing your source file

Zoho Creator supports importing data that's present in the following formats. However, it's best you prepare your data by referring to the guidelines below for a smooth import.

Importing data from spreadsheet and tabular formats

Zoho Creator supports importing data from .xls, .xlsx, .xlsm, .csv, .tsv, and .ods formats. Data import guidelines:

  • An application will be created based on the data you import.
  • Each sheet in your file will be referred to as a table in the import interface, and will become a form in the application.
  • Each row will become a record, and each column will become a field. Therefore the rows in a sheet and the cells in a column must contain similar data.
  • Column headers must be present in a row. It's alright if there are a few blank rows before it, as the import interface allows you to delete rows.
  • Blank rows will be imported. You may delete them before uploading your file, or manage them using the import interface's capabilities.
  • Delete the empty columns in all sheets. However, the import interface will allow you to deselect the columns you do not want to import.
  • Formulas in Excel files can be imported.
  • Data validations defined in the spreadsheet file will become Deluge script in the On Validate workflow block on the corresponding form.
  • For importing subform data: Parent and child data must be in separate sheets. The sheet containing the parent data is referred to as "parent table" or "main form", and the sheet containing the child data is referred to as its "subform".
  • For importing phone numbers, you must ensure that they contain the + character, valid area codes (for countries like US, UK), and the right number of digits (as per the respective country’s convention).
  • Limits:
    • A cell must contain at most 65,536 characters
    • A sheet can contain at most 70 columns
    • Your file can be up to 10 MB in size

Importing data from database files

Zoho Creator supports importing data from .accdb and .mdb files. Data import guidelines:

  • An application will be created based on the data you import.
  • Each table in your database file will become a form in the application.
  • For importing subform data: Parent and child data must be in separate tables. The table containing the parent data is referred to as "parent table" or "main form", and the table containing the child data is referred to as its "subform".
  • For importing phone numbers, you must ensure that they contain the + character, valid area codes (for countries like US, UK), and the right number of digits (as per the respective country’s convention).
  • Limits:
    • A cell must contain at most 65,536 characters
    • A table can contain at most 70 columns
    • Your file can be up to 20 MB in size

Importing a .ds file

DS is Zoho Creator's file format for storing an application's structure. A .ds file won't contain any data.

2. Uploading your file in Zoho Creator

  1. Access your Zoho Creator account's Home.
  2. Click Import a File from the top-right corner of the page.
  3. Upload your data that's in one of the supported file formats. The file can be fetched from:
    • Local storage of your device
    • URL - a publicly accessible direct download link
    • Cloud Storage - Google Drive, Box, OneDrive, Dropbox, Zoho Docs

Zoho Creator will read your file and take you to the import interface's Design tab.

3. The import interface

The import interface sports two tabs - Design and Data.

The Design tab displays your data's physical data model. In other words, it displays the tables present in your file, the columns present in each table, the field type mapped to each column, and the relationships between the tables. The Data tab displays your data, and offers a slew of smart capabilities that enable you to filter, refine, and cleanse the to-be imported data.

A nifty feature that the import interface brings is to Undo and Redo up to ten changes. You can also use the standard keyboard shortcuts to perform these operations.

4. Defining the to-be created application's aspects

​Set your application's display name

By default, your to-be created application's display name is set with your file's name. To customize it:

  1. Click the name in the top-left corner, then edit as required.
  2. Step off to save the change.

Define what's to happen on import error

The import interface enables brings to you a slew of capabilities to filter, refine, and cleanse data. However, to manage the error that may occur while the data is being imported in to the new application, you can tell Zoho Creator to either Skip the corresponding rows or Set empty value for the column.

  1. Click the Application Settings icon next to the application's display name. The Settings pane will slide in from the right.
  2. Select the required choice for On import error​.

Set your application's date format

Setting the data format's paramount as this will tell Zoho Creator how to interpret the date values in your file. To set the date format:

  1. Click the Application Settings icon next to the application's display name. The Settings pane will slide in from the right.
  2. Select the required value from the dropdown below Date Format.

Select / Deselect a table

By default, all the tables in your file are selected. You can deselect tables if required. Deselected tables (and consequently the data within them) will not imported in the to-be created application. To deselect/select a table:

  • From the Design​ tab, uncheck/check the required table. 
  • From the Data tab:
    1. Click the down arrow of the required table.
    2. Uncheck/check the Include this table checkbox.

Rename a table

By default, the table's name is set with the sheet's name (when you've uploaded a spreadsheet) or the table's name (when you've uploaded a database file). A table's name becomes the corresponding form's display name after import. To rename a table:

  • From the Design tab:
    1. Click on the table's name and edit as required.
    2. Step off to save the change.
  • From the Data tab:
    1. Click the down arrow of the required table.
    2. Click on the table's name and edit as required.
    3. Step off to save the change.

Select / Deselect a column

By default, all the columns are selected. You can deselect columns if required. Deselected columns (and consequently the data within them) will not imported in the to-be created application. Uncheck/check a column to deselect/select it, respectively.

Rename a column

By default, the column's name is set with what's there in the file. A column's name becomes the corresponding field's display name after import. To rename a column:

  • From the Design tab:
    1. Click on the column's name and edit as required.
    2. Step off to save the change.
  • From the Data tab, you may simply click on the column's name and edit as required. Alternatively:
    1. Click the down arrow of the required column, then click Customize Field.
    2. Edit the column's name as required.
    3. Step off to save the change.

5. Importing data in to specific fields

Zoho Creator offers many types of fields – each meant to capture a specific kind of data. When you import data in to Zoho Creator to create an app, all columns are automatically set with an appropriate field type. To change a column's field type:

  1. Click the down arrow next to the required column, then select Customize Field.
  2. Set Field type with the required value. Data can be imported in to all field types except add notes, integration, file upload, audio, video, image, and signature.

Refer to the following sections:

Importing subform data

In Zoho Creator, Subform is a field that best suited in maintaining data that's in a master-detail relationship. For example, in an order management scenario, the Orders and Order Line Items have a master-detail or parent-child relationship where every order can have multiple line items in it.

To tell Zoho Creator that the data in a table contains child data, and provision it as a subform (in the main form) in the to-be created application:

  1. Click the down arrow next to the required table, then check Make it a subform.
  2. Select the table that contains its parent data. This'll become the parent or main form after the import operation.
  3. Select the column in the main form that identifies each row uniquely.
    Note: Columns set as lookup fields will not appear in this dropdown. Navigate to the Design tab to delete the lookup-based relationship, and then perform this instruction set again.
  4. Select the column that's the parent row identifier in the current table.

Importing names

In Zoho Creator, Name is a composite field made up of the Prefix, First Name, Last Name, and Suffix subfields. Importing names is supported in both cases:

Case 1: When the full name is present in one column, you have to:

  1. Select the column containing name data.
  2. Click the down arrow next to it, then select Customize Field.
  3. Set the Field type to Name. The Split icon will appear in the column header.
  4. Click the Split icon. The system will split the names as follows:
    • The white space character will be used to separate a name into prefix, first name, last name, and suffix, in that order.
    • Only Mr.Ms., and Mrs. will be considered as prefix. The period (.) at the end is a must for identifying the prefix.

Case 2: When the name is split across multiple columns, you have to:

  1. Select the column containing first name data.
  2. Click the down arrow next to it, then select Customize Field.
  3. Set the Field type to Name. The column will be mapped to the First Name subfield.
  4. Select the columns containing the other subfield data.

Importing addresses

In Zoho Creator, Address is a composite field made up of the Address Line 1, Address Line 2, City/District, State/Province, Postal Code, and Country subfields. Importing addresses is supported in both cases:

Case 1: When the complete address is present in one column, you have to:

  1. Select the column containing address data.
  2. Click the down arrow next to it, then select Customize Field.
  3. Set Field type to Address. The Split icon will appear in the column header.
  4. Click the Split icon. The system will split the address data into the subfields using comma (,). All text present before the first comma will be considered Address Line1, and so on.

Case 2: When the name is split across multiple columns, you have to:

  1. Select the column containing first name data.
  2. Click the down arrow next to it, then select Customize Field.
  3. Set Field type to Name. The column will be mapped to the First Name subfield.
  4. Select the columns containing the other subfield data.

Importing phone numbers

  1. Select the column containing phone numbers.
  2. Click the down arrow next to it, then select Customize Field.
  3. Set Field type to Phone.

Importing boolean data

Often you'd have a column containing values such as true/false, 0/1, yes/no, Y/N. These are referred to as boolean values. Zoho Creator sports the Decision Box field to store such data.

If a column contains only "true" and "false" values, Zoho Creator will itself associate it with the decision box field. In case of other boolean values, Zoho Creator will associate the column with a Radio field. To tell Zoho Creator that a column contains boolean data:

  1. Select the required column, then click Customize Field.
  2. Set Field type to Decision Box​. Zoho Creator will list the boolean values present in the column.
  3. Map which value is to be considered true and which false.
  4. Optionally, set the to-be created field's Initial value​ property.

6. Cleansing the data

Sort data

Based on a column, you can sort the data in a table in the ascending or descending order. The sorting order you apply will not be carried in to the application. This is to let you view data in the required order. Sorting data can help before you perform the data refining operations.

To sort data:

  1. Click the down arrow of the required column.
  2. Select the required sorting order.

Remove rows

The import interface allows you to delete rows from the tables. You may find need to remove rows when your sheet (when you've uploaded a spreadsheet) starts with a few blank rows, or when you wish to delete rows that satisfy a criteria (which you can find out by filtering the data)

  1. Select one or multiple records.
  2. Right-click, then select Remove rows. A banner will appear indicating the numbers of rows that were deleted.

Tip: The import interface allows you to Undo and Redo up to changes.

Filter data

Filtering the data in a table can help before you perform the data refining operations. Only the filtered data will be imported in to the to-be created application. To filter the data in a table:

  1. Select the required table.
  2. Click Filter.
  3. Set the required criteria. The criteria can contain up to 20 sub-criteria.

Find and replace data

You can find a value across a table or in one of its columns, and replace it with another. This option's available in the Data tab.

7. Refining the data

The import interface brings to you the following smart data refining operations.

  • Fix Inconsistent Spellings - This enables you in identifying spelling inconsistencies and typographical errors. It'll display Clusters that are nearly similar in terms of number of characters, phonetics, and character repetition. You can modify the values in a cluster with the suggested value, or with a value of your choice.
  • Fix Missing Values - This enables you in finding if a column contains empty cells, and fix them in any of the following ways:
    • Fill empty cells with the value present in their preceding row
    • Fill empty cells with a value of your choice
    • Fill empty cells with the most-occurring value in that column
    • Remove the rows that do not contain a value in that column
  • Split Column - This enables you to split the data in a column in to two. You'll need to define what delimiter (such as whitespace, comma, or hyphen) is to be used to split the data.
  • Merge Columns - This enables you to merge the data in two or more columns, in the order they are selected. Optionally, you can define if a separator (such as whitespace, comma, or hyphen) is to be added between the values or the to-be merged columns. You'll get to preview the merged value.
  • Explore - This shows the frequency distribution of values in a column, which can help in deciding if the data's to be modified by find and replace or use Fix Inconsistent Spellings operation.

8. Creating the application

  1. After you're done viewing and refining your data, click Create from the top-right corner of the import interface. A pop-up showing the progress will appear:
  2. Click Records imported successfully in the pop-up to view the data import's summary.
  3. Click Access Application to access the newly created application.

Share this post : FacebookTwitter

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

Write to us: support@zohocreator.com