Import data from files and feeds

 Importing Data from Files and Feeds 

Often you would already have data locally stored in tabular file formats like CSV, XLS (Excel), JSON and HTML files. The data in such formats could also be available as a URL or Web feed or even some application generated. ManageEngine Analytics Plus offers easy import and copy-paste options of such data for jump starting your reporting and analysis. You can import the data into ManageEngine Analytics Plus by clicking the link Import Excel, CSV, HTML provided in any of the following contexts :

  1. From Import your data option available in the Home page.
  2. From Create -> New Table / Import data option found in the Side Panel.

Step 1: Choose the File Type to Import

On clicking the Import Excel, CSV, HTML, JSON, an import dialog will pop-up. The first screen of the dialog will be different depending on context from where you invoked this dialog.

If you had invoked this from under Create workspace, the first screen will look like the following. In this screen, you need to provide the workspace name (unique and mandatory), description (optional) and tags (optional) describing the workspace. Other options are common and described below:

If you had invoked this from New Table option, the first screen will look like the following. In this screen, you need to provide the Table name (unique within the workspace and mandatory), description (optional) and tags (optional) describing the table. Other options are common.

Currently ManageEngine Analytics Plus supports importing data from the following sources.

  • Comma Seperated Value (CSV)
  • Excel (XLS) /SXC
  • HTML / XML
  • Tabular Text files
  • JSON
  • OData feeds

Choose the Data Location from where the input data file is to be loaded. You can choose any of the following:

  • Pasted Data: Choose this option if you wish to copy and paste the data from the source file. Copy and paste the data from the necessary file into the text area given below. In the case of a HTML file format, you need to copy and paste the data along with the HTML tags in the file. Ignoring it will make the import to fail.
  • Local Drive: Choose this option if you wish to load the data file from your local machine. On choosing this option, Browse and load the necessary file from your local machine.
  • Web URL:Choose this option if you have the data file residing in a Web URL. You can import data from publicly accessible or from URL with basic authentication. Choose the appropriate tab and then provide the full path to the URL in the field provided.
    • URL - Choose this to import data from a publicly accessible URL
    • URL With Authentication - Choose this to import data from URL with basic authentication. On choosing this, you need to provide username and password to access data from the URL.

Once you have provided the data input, click on the Next button at the bottom to proceed to the next step.

Uploading files exceeding 100MB or 500,000 rows:

  • ManageEngine Analytics Plus 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 10MB. Incase if the file size is greater than or equal to 10MB, then you can Zip the file and upload the same.
  • If you wish to upload more data in a batch, you can use 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.
  • How do I do it? 

Install and Setup the agent in your local machine to pull data from any of the above said sources. The agent directly accesses your database, queries for the required data and uploads it to ManageEngine Analytics Plus. You can also set up automatic execution of the agent in a scheduled interval and keep the data synchronized with ManageEngine Analytics Plus.

Importing HTML files 

In case you have chosen to import an HTML or Excel file, you will see the list of tables that are present in the input file. You need to choose the table/sheet from which the data has to be imported. You can only import one table/sheet at a time. Once you had chosen the table/sheet, click on Next button to proceed to the next step. If there had been no tables in the input HTML file, then you would not be able to proceed to the next step until you provide a valid input.

Importing Excel files

ManageEngine Analytics Plus allows you to import multiple sheets from an Excel workbook at one go. Select the sheets you need to import in the Step 1: Import your Data page, and proceed with the import. Each new sheet will be stored as an individual table in the workspace. 

Importing Hidden rows/columns in Excel

When importing data from Excel file, if the excel table contains hidden rows or columns, only the visible rows will be imported in to the ManageEngine Analytics Plus table by default. However, ManageEngine Analytics Plus also allows you to choose to import the hidden rows/columns by providing corresponding options under Import Hidden Rows/Columns option in this step of the wizard. Options provided are:

  • Import Hidden Rows: By selecting this option, any hidden rows in the excel sheet will be imported.
  • Import Hidden Columns: By selecting this option, any hidden columns in the excel sheet will be imported.

Importing JSON file 

ManageEngine Analytics Plus supports importing any type of array with single or multiple object. It is mandatory that the first row should contain column headers for importing JSON file.

Note: In case you import file with multiple JSON objects separated by comma (not enclosed with Square Brackets), then the first object alone will be imported. Also JSON object containing simple values without keys are supported.

Importing OData feeds

To import from OData feeds, choose the Data Location as Web in the Step 1: Import your Data page. You have two options to import data from OData feeds. 

  • URL - Choose this to import data from a URL that doesn't need any authentication.
  • URL with authentication - Choose this to import data from an URL that requires a username and password for access. On choosing this, you need authenticate to access the URL by passing the username and password as additional parameters. The user credentials will be encrypted and saved for subsequent imports.  

Importing MS Access Database files

You can import data from MS access database into Analytics Plus using local drives, FTP servers, cloud drives, and web links of MS Access Database.

Step 2: (Step 3 in case of Excel/HTML): Specify the Import Settings:

In this step you would be prompted to provide the necessary settings for ManageEngine Analytics Plus to properly understand the data being imported. 

The settings that are to be provided are decribed below: 

    • First Row Contains Column Names: If the first row of the data being imported contains the columns names, then choose Yes, if not No. ManageEngine Analytics Plus 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 ManageEngine Analytics Plus 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 ManageEngine Analytics Plus will generate its own column names and create the table. You can edit these column names subsequently after the table has been created.

    • 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 ManageEngine Analytics Plus to correctly import it. By default, ManageEngine Analytics Plus 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 ManageEngine Analytics Plus 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 CSV Settings: If you are importing CSV or Excel data then you would see the More CSV Settings link. Clicking on this will expand the dialog box and provide more settings which are described below:

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

    2. Skip top row(s): Specify the number of rows to be skipped or ignored by ManageEngine Analytics Plus from the top of your data file being imported. This is optional and the default would be 0 ie no rows will be skipped.

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

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

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

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

To change a Column Name, double click on the corresponding column header. This will switch the header into edit mode. You can then provide the required name and press the Enter key to set the same.

To change the Column type, 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 ManageEngine Analytics Plus to ignore the value in that corresponding column while importing.

On Import Errors:

You could specify how ManageEngine Analytics Plus should handle errors condition (incase it occurs) while importing data. The following are the possible options:

  • Set Empty Value for the Column (default): If this option is set, Reports Central will set empty value to the corresponding column value which had problems while importing.

  • Skip Corresponding Rows: If this option is set, Reports Central will skip the corresponding rows in which an error occurs while importing.

  • Don't Import the data: If this option is set, ManageEngine Analytics Plus will not import any data provided, if any error occurs during importing.

In case any error occurs during import, the details of the same would be shown in the Import Summary dialog (refer the next section below) which would be shown on Import process completion.

Once you have set all the options, click Create button provided to proceed Importing the data.

Step 3: Import Summary

Once Reports Central imports the input data provided, it will bring up a Import Summary Dialog as shown below, capturing the status of the Import process just completed.

The details that would be shown include:

  • 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/warnings generated during the Import process, it would be listed under the Details section.

On closing the Summary dialog, ManageEngine Analytics Plus will display the newly created table with the data loaded in the same. 

Scheduling Import from Web Feeds

You can schedule periodic data sync from web feeds using the steps given below:

  • Login to your Analytics Plus account. 
  • Invoke the Import wizard using the Create option in your homepage or the New Table option available (top-right corner, under the New -> New Table toolbar menu).
  • In Step 1: Import your data, click the Web radio button.

  • You will have two options to import data from a web URL. 
    • URL - Choose this to import data from a URL that doesn't need any authentication.
    • URL With Authentication - Choose this to import data from an  URL that requires a username and password for access. On choosing this, you need to authenticate to access the URL either by providing username and password as additional parameters. The user credentials will be encrypted and saved for subsequent imports.  

  • Verify the data type of the columns in your table in Step 2: Import your data and click Create.   

  • In the Imported Data Summary page, choose the Schedule this import option to set up periodic data sync. 

  • In the Schedule import settings page, choose your scheduling options and click Save. To know more about import settings, click here. 

Scheduling import from local files 

You can schedule periodic data sync from local files using the shared folder functionality. 

  • Add the file you wish to import (CSV, Excel, XML, JSON or text files) to the Shared folder present in the <Analytics Plus home> directory. 
  • Login to your Analytics Plus account.
  • Click Files & Feeds in your home screen. 
  • In Step 1: Import your data, click the Web radio button. Select URL, and enter the location of your file as https://<Analytics Plus server hostname>:8443/shared/filename.csv. For example: https://BI-server:8443/shared/Sales.csv ('BI-server' is the hostname of the Analytics Plus server and 'Sales' is the name of the file to be imported and 'csv' the file type). Click Next

  • Verify the data type of the columns in your table in Step 2: Import your data and click Create.  

  • In the Imported data summary page, choose the Schedule this import option to set up periodic data sync. 

  • In the Schedule import settings page, choose your scheduling options and click Save. To know more about import settings, click here. 

Note: To schedule data sync from local files, you need to store your files in the <Analytics Plus home>\Shared directory, and regularly update the data for the sync to work.

Import Scheduling Options

The following are the sceduling options available in ManageEngine Analytics Plus:

  • Table Name - Displays the table name for which you schedule the import. This is not editable.

  • File Type - Displays the file type from which the data is imported (e.g., CSV, XLS etc., ). This is not editable.

  • URL- Provide the Web URL from which the data is to be imported.

    • URL - Choose this to import data from a URL that doesn't need any authentication.
    • URL With Authentication - Choose this to import data from an  URL that requires a username and password for access. On choosing this, you need authenticate to access the URL either by providing username and password as additional parameters. The user credentials will be encrypted and saved for subsequent imports.  

  • How do you want to Import? - Choose how do you want to import from this 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 ManageEngine Analytics Plus 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 Analytics Plus 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. ManageEngine Analytics Plus 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 ManageEngine Analytics Plus will update that existing record in the table with the new record values present in the data being Imported. Incase 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 Time Zone option.

Share this post : FacebookTwitter