Analytics » Import/Upload Data into Virtutem Analytics » Importing Data from Cloud Storage/Drive

Importing Data from Cloud Storage/Drive

Virtutem Analytics 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 Virtutem Analytics, and the reports that you create over this data stay current.

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

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

  • Step 3 would prompt you to provide the necessary settings for Virtutem Analytics 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.   Virtutem Analytics 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 Virtutem Analytics 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 Virtutem Analytics 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):   In case the imported data contains a date column, then you need to provide the date format of the same for Virtutem Analytics to correctly import it. By default, Virtutem Analytics 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 Virtutem Analytics 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 Virtutem Analytics from the top of your data file being imported. This is optional and the default would be   0   ie 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 Virtutem Analytics 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 Virtutem Analytics to ignore the value in that corresponding column while importing.

    • The   On Import Errors   allows you to specify how Virtutem Analytics 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, Virtutem Analytics will set empty value to the corresponding column value which had problems while importing.
      • Skip Corresponding Rows :   If this option is set, Virtutem Analytics will skip the corresponding rows in which an error occurs while importing.
      • Don't Import the data :   If this option is set, Virtutem Analytics 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
  • Virtutem Analytics 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@virtutem.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 Virtutem Analytics 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. Virtutem Analytics 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 Virtutem Analytics 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 for scheduling.

      Note : Schedule time need to be specified in GMT +0:00 timezone.

    • Notify me after every   - In case of import failure you will be notified. Specify after how many failures you need to be notified.