From Cloud Storage

Importing Data from Cloud Storage/Drive

Zoho Reports allows you to import data from CSV, Excel (XLS and XLSX), JSON, HTML and ziped files stored in different Cloud Storage/Drive such as Zoho Docs, Google Drive, Dropbox, Box and OneDrive, for advanced reporting analysis. You can periodically schedule importing data from your Cloud Storage/Drive. With this, you can have the latest data being synced into Zoho Reports, and the reports that you create over this data stay current. The below video tutors you on importing data from the cloud storage.

Importing Data from Cloud Storage/Drive

Follow the steps below to import data from cloud storage or drive.

  • Invoke the Import wizard by clicking the Import from Cloud Storage/ Drive option provided in any of the following contexts :
    • From Create Database option available in the Home page.

    • From the New Table page available (top-right corner, under the New > New Table toolbar menu) to import data into an existing database.
  • In the Import wizard that opens, specify a name for the database or table.

  • Specify description (optional) and tags (optional) in the respective fields.
  • Choose the cloud storage in the Cloud Storage Type drop-down.
  • Click Browse to proceed further.
  • The Cloud Picker - Select your file dialog will open. You will be prompt to authenticate yourself to get a list of files available in your cloud storage.
  • Click Authenticate button and then specify your login credentials of the cloud storage to continue with import process.

  • The files available in your accont will be listed. Select the file to be imported.

  • Select the required sheet to import. This section will open only when importing a Excel or HTML files and is not applicable for CSV and JSON import.

  • Step 3 would prompt you to provide the necessary settings for Zoho Reports to properly understand the data being imported. The following are the available options.
    • First Row Contains Column Names : If the first row of the data being imported contains the columns names, then choose Yes, if not No. Zoho Reports will try to intelligently identify whether the first row contains column names and set the option automatically. You can verify this and override/reset it accordingly.

      It is recommended that the first row contains the column names. This would enable Zoho Reports to use the same column names while creating the table to store the data imported.

      If the imported data does not contain the column names in the first row, then Zoho Reports will generate its own column names and create the table. You can edit these column names subsequently after the table has been created.

      Note:

      This option will be disabled while importing JSON files as it is mandatory to have first row as column header in JSON import.

    • Date Format of the column(s): Incase the imported data contains a date column, then you need to provide the date format of the same for Zoho Reports to correctly import it. By default, Zoho Reports tries to identify the date format of the given date column from the data provided and display the same in this field. If you find that to be incorrect or if Zoho Reports had failed to recognize any date column then you can set the date format by clicking on the link Choose matching date format... just below that date field. To know more about choosing the matching date format follow this link.
    • More Settings: Clicking this option will expand the dialog box and provide more import settings appropriate to the file type being imported.
      The following is the list of import setting. Depending on the file type of the data being imported, you will see the appropriate settings to customize the data.
      • Delimiter separating your column(s): Specify the delimiter character which separates the data in one column from the next. Comma would be the default. If not, you could choose what is relevant for your data set which could be any of Comma, Tab, Semicolon and Space.
      • Skip top row(s): Specify the number of rows to be skipped or ignored by Zoho Reports from the top of your data file being imported. This is optional and the default would be 0 i.e., no rows will be skipped.
      • Text Qualifier: If the string values in the data imported are enclosed within Single quote or Double quote, specify the same in this option. The default value is NONE.
      • Comment Character: If some rows in the data imported are commented and are to be ignored, then you need to specify the comment character that is used to indicate the same. Note that the specified comment character should be present as the first character in that row, if that row has to be ignored.
      • Thousand Separator: This option lets you to choose your own thousand separator to be used for datatypes like number, positive number, decimal number and percentage. The default value is NONE.
      • Decimal Separator: This option allows you to choose your own decimal separator to be used for datatypes like decimal number and percentage. The default value is DOT.
  • The Preview section displays the first 3 rows of the data being imported in a tabular format as shown below. You could do the following as needed.
    • Select columns that need to be inported into Zoho Reports table by selecting or unselecting the checkbox adjecent to the column header.
    • Verify the column names and rename it as needed by double clicking the corresponding column header.
    • Verify the column datatype and reset it as needed by choose the type from the drop list provided under each column. Ensure that you specify the right column type. Specifying a wrong type will generate errors on import and make Zoho Reports to ignore the value in that corresponding column while importing.

  • The On Import Errors allows you to specify how Zoho Reports should handle errors condition (in case it occurs) while importing data. The following are the possible options:
    • Set Empty Value for the Column (default) : If this option is set, Zoho Reports will set empty value to the corresponding column value which had problems while importing.
    • Skip Corresponding Rows : If this option is set, Zoho Reports will skip the corresponding rows in which an error occurs while importing.
    • Don't Import the data : If this option is set, Zoho Reports will not import any data provided, if any error occurs during importing.
  • Once you have set all the options, click Create to proceed Importing the data.The data will be imported and the Import Summary dialog will open, displaying the following details.
    • Column Details : Total number of columns in the input data and the total number of columns that where actually selected for import
    • Row Details : Total number of rows in the input data and the total number of succesfully imported rows in it.
    • Error/Warning Details : If there were any errors/warning generated during the Import process.

If needed you could choose to schedule this import by clicking the Would you like to schedule this import periodically? link.

On closing this dialog, the new table with the imported data will open.

Note:

  • Zoho Reports has the following restrictions on the size of Data Import. You can upload a maximum of 500,000 rows at a time into a Table. And the file size should not exceed 50 MB. In case if the file size is greater than or equal to 50 MB, then you can Zip the file and upload the same.
  • You can also import data from files and feeds using the Import Data from Files and Feeds option. Click here to read more on this.
  • If you wish to import more data, you can download them as CSV files and them upload in batches using the Upload tool. This is a packaged downloadable tool installable in your machine. You can use this via a GUI mode to upload the data or from a command line mode for scheduled upload of data.
  • For further assistance, contact us at support@zohoreports.com.

Scheduling Import from Cloud Storage/Drive

You could setup scheduled imports data from your cloud storage using the Would you like to schedule this import periodically? link in the Import Data Details dialog or from an existing table. You can schedule the data import from a Web URL for an existing table by following the steps given below:

  • Open the required table.
  • Invoke Import > Refetch/Schedule Import.
  • In the Schedule Import Details dialog that opens, click Edit.

Import Schedule Settings Dialog

The Schedule Settings dialog enables you to schedule data imports from your cloud storage periodically.

This dialog provides the following options:

  • Table Name - Displays the table name for which you schedule the import.This is not editable.
  • Data URL - Displays the file name with extension and the Cloud Storage name.
  • Sheet/Table Name - Choose the sheet to be imported. Preview of the selected sheet is also available. This option is applicable only while importing Excel (XLS and XLSX), Google Drive Spreadsheet and HTML files.
  • How do you want to Import? - Choose how do you want to Import from the drop-down list. Available options are:
    • Add records at the end - Use this option to append imported records to the end of the table .
    • Delete existing records and add - Use this option to delete entire records in Zoho Reports table and add the new records into the table.
    • Add records and replace if already exists - Use this option to update the already existing records in the table with the new values and append the new records at the end of the table.
    • Add records, replace existing and delete missing record - Use this option to update the already existing records with the new values, append new records to the end of the table and delete records that are not available in the file but present only in the table.

      Note: When Add records and replace if already exists or Add records, replace existing and delete missing record is selected, it is mandatory to specify matching columns using the Select the column(s) to match existing records option. You can choose one or more columns as matching columns. Zoho Reports will use the values in these columns as the key to match the existing records with the new records being imported. Hence the matching column(s) combination should uniquely identify each record in the table. Once a match is identified, then Zoho Reports will update that existing record in the table with the new record values present in the data being Imported. In case there is no match found, those records will be appended to the end of the table.

  • Schedule Settings-This group box provides options to schedule the import.
    • Repeat- Specify the schedule interval from the drop-down list. Available options are.
      • Every 'N' hours - Choose this option to schedule the import every 'N' hours i.e., every 2 hour or 6 hours. On choosing this option, specify the time interval to import the data.
      • Every Day - Choose this option to schedule the import at a particular time everyday. On choosing this option, specify the time to import data.
      • Weekly Once - Choose this option to schedule the import once in a week on a particular day. On choosing this option, specify the day of the week and the time to import the data.
      • Monthly Once - Choose this option if you want to schedule the import once in a month on a particular day. On choosing this option, specify the day of the month and the time to import data.

Note:

By default schedule time will be specified in your local  timezone.  You can also change this using the Select TimeZone option.

Importing Data into an Existing Table