Import data to reports

Import data to reports

Zoho Creator allows you to import data from into the reports in your Creator application. Creator supports importing data from .xls, .xlsx, .xlsm, .csv, .tsv, .ods, .accdb, .mdb, .json and .numbers file formats. The required file can either be stored in your computer or stored in a cloud service (Dropbox, Google Drive, Zoho Docs, etc.).

To import data:

1. Access your Creator application. 

Navigate to the report to which the data has to be imported. Click on the More options button on the right corner and select the Import Data option.

2. Choose the required storage location

  • Local storage - Choose a file that is stored in your computer.
  • URL - Import a file from a URL by providing a publicly accessible direct download link.
  • Cloud service - Import file stored in cloud. Select the cloud service which contains your files from the left pane. After authentication you can choose the required file.
  • Paste - Allows you to paste data. Import data by pasting delimiter separated values. The four supported delimiter are comma(,), tab, semicolon(;), space, and pipe(|). Pasting of data up to 5MB is supported.

3.Import interface

In the next step the import interface will be shown with data from the selected file. The interface gives a preview of the records that will be created according to the specified mappings of the columns. You can edit the data that is displayed.
The import interface also offers a variety of smart capabilities that enable you to filter, refine, and cleanse the to-be imported data. Based on the data of the imported file, the format for a certain field will be added. If required, this format can be edited as per your requirement.

Creator offers a variety of smart capabilities that enable you to filter, refine, and cleanse the to-be imported data.

4.First row contains column headers

Creator will automatically try to detect if the first row of your imported file is comprised of headers or not. When the First row contains column headers option is checked, the columns in the field mappings section will be named after those column headers. If the option is unchecked then the first row values will be considered as actual data values.

  • Click on the Table Settings button adjacent to the required table.

  • Check the First row contains column headers option.

5. Map columns

In the Map columns section you can map fields from the imported file to those present in Creator. In the column mapping pane, the left side shows the columns of the imported file and the right side shows the fields in your Creator. Creator automatically tries to map the fields. You can remap the columns of the imported file to any field based on your requirement. To map a column, choose the field from the drop down list.

  1. Click on the Map columns button.

  2. To map a column choose the field from the drop down list.

  3. Mandatory fields: Make sure are mapped and you do not leave any mandatory fields blank in the import file. Do note that not mapping mandatory fields will lead to error while importing the file.
  4. Lookup field handling: The lookup fields can be imported based on both ID as well as value. Whether the import is based on Value or ID will be denoted in adjacent to the field. 

  5. Conflict resolutionCreator offers conflict resolution by highlighting warnings that may arise during field mapping. When conflict warning is displayed you can click on remap button to rectify the mistakes.
    • Decimal warning: When appropriate number of decimals are not present.
    • Field warning/data type mismatch- When incompatible data type is set for the column that is not appropriate for the data.

    • Lookup-not in parent warning: Some rows may fail during import when their values are not present in the lookup parent field. To resolve it is recommended to set empty values on import error.
    • Lookup-duplicate values: When parent lookup field either contains duplicate values or some values are not present in parent lookup fields.

6. Import settings

The import mode option found under the Settings allows you to choose how the data from the file should be imported to Creator. There are two types of import that Creator supports: Append rows and Update rows.

Note
  • Updating existing records overwrites data in the target file and cannot be undone. It is recommended to make a backup copy of the target file to which you’re importing data.
  • It is important to remember that values in the existing Creator report will be updated according to the contents of the imported file, even if the field for those matching records in the file is blank.

7. Define what's to happen on import error

There is a possibility that errors may occur while importing data. So Zoho creator allows you to choose how to proceed in such instances. Under the On import error section you choose the action to be done when error occurs while the data is being imported. You can choose to either Skip the corresponding rows or Set empty value for the column.

  1. Click the Settings button displayed near the top-left corner of the interface. The Settings pane will slide in from the right.
  2. Under the On import error section two options will be displayed.
    • Skip Corresponding Rows - If there is any conflict found in any of the row values, Zoho Creator will skip the corresponding rows.
    • Set Empty Values for the column - If there is any conflict found in any of the column values, Zoho Creator will set empty(NULL) values for the corresponding columns.
    • Do not import records - If there is any conflict found in any of the values then Zoho Creator will not import the data. This option is supported only when the number of records to be imported is less than 3000.

8. Set the date format

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

  1. Click the Settings button displayed near the top-right corner of the interface. The Settings pane will slide in from the right.
  2. Select the required value from the dropdown below Date Format.

The application has capabilities to recognize date based on locale. A date such as dd.mm.yyyy may not be recognized as date if locale is US and so on import also it will be treated as text. Henceforth, all possible date and time formats will be recognized. If date format is different across the columns then they can be refined into a common format by Standardize Date and Time option under Refine data section

9. Cleansing data

Creator offers you to cleanse the to-be imported data by offering:

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 be carried in along with the imported data. This is to let you view data in the required order. Sorting data will be help before you perform the data refining operations.

  1. Click the down arrow of the required column.
  2. In the drop down list select the required sorting order.

Filter data

The filter option allows you to narrow down data. Once filter is applied only the filtered data will be imported in to the to-be created imported data.

  1. Click on Filter option.

  2. Set the required criteria. The criteria can contain up to 20 sub-criteria. Now click Filter button.

Find and replace

The find and replace allows you to find and replace data.

  1. Click on the Find and replace button
  2. Enter the criteria for the find and replace operation.

10. Refine data

The refine data option allows to you the following smart data refining operations. The available options are:

  • 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 and Edit - This shows the frequency distribution of values in a column, which can help in deciding if the data is to be modified by find and replace or use Fix Inconsistent Spellings operation.
  • Standardize Date and Time - This enables you to standardize the Date/Date-Time columns to a common format. With this multiple date formats can be uploaded and standardized. An option to choose the 'Pivot year' will also be available. This pivot year is helpful in the case where the year value has only two digits. In such cases the two-digit year value xx smaller than the pivot year will be expanded into 20xx, and year values larger and equal than the pivot year will be expanded into 19xx.


  • Note:
    • If a column is mapped to Date/Date-time field, Standardise Date/Date-Time warning will begiven if the date format of the importing values doesn't match with the date format set for the application.
    • If Standardise Date warning is ignored by the user, then the date values will be automatically standardised based on the date order set in Settings. Ignoring Standardise Date will cause import errors only when the importing data has multiple date formats across the columns. Each column has to be standardised individually with an appropriate date order.
  • Standardize Phone Numbers - This enables you to standardize the phone numbers as per the international E. 164 notation. You'll be able to do one of the following:
    • Prepend a particular country's international calling code to all the phone numbers in the selected column. For example, if you select United States as the country, +1 will be prepended to all the phone numbers in the selected column. Similarly, +61 will be prepended to phone numbers associated with Australia.
    • Prepend the international calling code of the country present in another column of the same sheet. The country data must either be country names, or their standard two-letter or three-letter ISO 3166 codes. For example, if a row were to contain "222-555-019" in the phone number column and "United States" in the country column, it will be changed to "+12225550191".


    • Note:  If a column is mapped to a Phone Number field, then a warning to Standardise Phone Number will be provided to add appropriate country code to the values. The values without country code will result in record import failure
  • Fix duplicate rows - This operation enables you to find and remove duplicate records across one or more columns in your sheet or table. In the remaining duplicate records to be deleted, you can include/exclude rows. When you exclude a row, that row will be greyed out and retained, whereas the remaining duplicate rows will be deleted. The following GIF shows how to include/exclude record looks like:

    • Exact match looks for rows that contain the same (case-sensitive) values in the selected column
    • Similar match looks for rows that contain values in the selected column that are similar barring case-sensitivity, punctuation, ordering of words, and white spaces
    • Fuzzy match looks for rows that contain values in the selected column that are similar, barring typos, phonetic similarities, and transposition errors (where characters are swapped such as "ei" vs "ie" in the word field)
    • For text-based and phone number columns, you can choose to look for duplicates using any of the three matches: exactsimilar and fuzzy.
    • For columns mapped to number, date, time and date-time field types, all three matches will display the same set of duplicate clusters.
    • Upon selecting the required cluster of duplicates, the corresponding rows will be displayed on the left. You can set one of the rows as the master record, which will be highlighted in the UI.
    • Upon clicking Remove Duplicates button, all rows except the master record of each cluster and those rows that you exclude will be deleted.

To refine data:

  1. Click on the Refine data button.
  2. In the Refine data pane choose the required operation from the drop down list.

  3. Now choose the required column and perform the data refining operation.

11. Smart suggestions with Zia

Under the Smart suggestions section Zia(Zoho's Intelligent Assistant) will provide suggestions to you based on problems that may be present in the data that has been uploaded from the file. Zia provides suggestions to a handful of problems which are as follows:

  • Duplicate rows at table level
  • Inconsistencies in data
  • Multiple data types
  • Missing values
  • Phone number without standardization
  • Name and Address without standardization
  • Date/Date-time/Time without standardization
  • Data with un-escaped HTML entities
  • Values enclosed in quotes

To work with Zia's suggestions:

  1. Click on the Smart suggestions button. In the Smart suggestions pane on the list of suggestions by Zia will be displayed.

12. Importing data

Once you've configured your settings and field mappings, you're ready to import the data from the file.

  1. Click the Import button to start importing data into your report. This may take a few seconds to a few minutes depending on the amount of information you're importing.

  2. Once the import process is complete a popup will be displayed with the import summary. Errors if any will be shown.

  3. Click on the Import summary button to view a detailed summary of the success/failure of the records that were imported.

  4. Click on Access the report to view the report.

What's new in the New Import Interface:

  1. The new interface allows Addition and Updation of the data provided necessary permissions have been granted.
    • Adding and Updating during the import process will be permitted only if the AccessEdit and Import permissions has been granted for the form. The Updation of data also depends on the grant of Modify All permission and the Role hierarchy.
    • Updating data during the import process will not be allowed if the Edit permission for the particular form has not been granted.
    • The import data function will work only if Access permission has been granted for the particular form.
  2. Import of files with any kind of script execution is currently supported only when the number of records to be imported are less than 3000.
  3. By default, the workflows will not be triggered in the new import interface. Instead, they can be enabled from the Import Settings section.
  4. Do not import records option under On Import Error section will be available only when the number of records to be imported are less than 3000.

Points to remember:

  1. You can import data from the .xls, .xlsx, .xlsm, .csv, .tsv, .ods, .accdb, .mdb, .json and .numbers file formats.
  2. The Name field can either be present as a single column or as a composite field made up of Prefix, First Name, Last Name, and Suffix subfields. Creator supports importing data in both scenarios.
  3. Similarly Address can either be a single column or a composite field made up of the Address Line 1, Address Line 2, City/District, State/Province, Postal Code, and Country subfields. Creator supports importing data in both scenarios.
  4. If the file has columns containing values such as true/false then Creator will automatically associate it with the Decision box field. In case other boolean values are present then they will be associated with Radio field.

Limitations:

  1. There is no limit on the number of rows that can be imported provided the file size is less than 100 MB.
  2. Importing Subform data is currently not supported.
  3. The new import interface will not available for customer portal users.
  4. Import of files with any kind of script execution is currently supported only when the number of records to be imported are less than 3000.
  5. Do not import records option under On Import Error section will be available only when the number of records to be imported are less than 3000.

Related Topics

  1. Export data from reports
  2. Exporting reports in mobile devices
  3. Exporting reports in tablet devices


    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

                                                                                    • Understand reports

                                                                                      A report is a compilation of data collected using a form in the created application. Zoho Creator incorporates reports as one of the components to complete the purpose of the application that you create. A report collates and presents information to ...
                                                                                    • Export data from reports

                                                                                      Exporting records help you obtain or download the records in your report into a specific format for a purpose outside of the Zoho Creator environment. Creator enables you to export your reports in a few simple steps so that you can access them ...
                                                                                    • Permission based reports and a consolidated report

                                                                                      Requirement             Set visibility of forms and reports based on teams. Management should be able to view all the reports consolidated in to one. Use Case   A firm tracks its suppliers details using the Supply Management app. The app has four ...
                                                                                    • Understand spreadsheet report

                                                                                      The spreadsheet report enables you to view records in a table grid arrangement. The information is distributed into rows and columns, with the fields occupying the column headers and the field values occupying the rows below. This report behaves like ...
                                                                                    • Understand list report

                                                                                      A list report enables you to present or view your records in the traditional list format. It organizes records in different layouts that make the data comprehensible. By default, the records are presented in a table grid as rows of information, ...
                                                                                    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