Uploading Data from CSV Files - GUI Mode
Analytics Plus agent can be invoked using Command line mode (console mode) or with a user interface (GUI). GUI mode can be useful if you are not much familiar with the command line interface. In this section, we will discuss how to set up this tool for batch upload of CSV data with a user interface.
The Analytics Plus agent will be useful, when you need to upload more than 500,000 rows or whose file size is greater than 100 MB (these import size limits are applicable when you use the ManageEngine Analytics Plus web user interface). This tool splits the data in the CSV file into batches and uploads it to ManageEngine Analytics Plus, making it much more resilient to intermittent Internet connectivity outages or slow connections
Steps for running the CSV Analytics Plus agent in GUI mode.
Step 1: Ensure that you have already created the database in ManageEngine Analytics Plus account into which the data is to be uploaded.
Step 2: Configure the proxy settings and the required Import settings in the common_params.conf file. Read more about these settings from
Step 3: Invoke the CSVUploadGUI.sh (for Linux) or CSVUploadGUI.bat (for Windows) file under <agent_Home>bin directory. The CSV Upload Utility dialog will open.
Step 4: Provide the required inputs in the GUI as shown below.
Step 5: Click the Upload button.
All your data from the CSV file will get uploaded to the specified table and in ManageEngine Analytics Plus database.
The following table contains the parameters that are to be specified in the GUI above.
|File||Specify the complete path of the CSV file from which data is to be uploaded.|
|Database Name||Specify the name of the database in the ManageEngine Analytics Plus account into which the CSV file's data is to be uploaded. Ensure that the database is already available in ManageEngine Analytics Plus. In case the database does not exist, create a blank database with the same name in your ManageEngine Analytics Plus account.|
|Table Name||Specify the name of the database table into which the CSV file's data is to be uploaded. Ensure that the specified table with similar column structure is already created in the ManageEngine Analytics Plus workspace.|
In case the table does not exist, then you can set the Analytics Plus agent to create the table and then upload the data into this table using the ZOHO_CREATE_TABLE parameter available in common_params.conf file.
|User Name||Specify your ManageEngine Analytics Plus account login name.|
|Password||Specify the ManageEngine Analytics Plus account password.|
|Import Type||Choose one of the following three options as the Import Type. |
- Add at the end - The data in the CSV file will be added (appended) at the end of the table.
- Delete existing and add - All existing rows in the table will be deleted and then the data in the CSV file will be added.
- Add, replace if already exists- Existing rows that are modified will be updated and new rows will be appended at the end of the table. (In this case ensure you provide the appropriate input for the field Columns to match described below).
|Columns to match||This is applicable only when the Import type is set to Add, replace if already exists. Specify column ( or a combination of columns) whose value will uniquely identify each record in that table. If the record already exists in the table then it will be replaced with the new values in the CSV file being uploaded. Otherwise the data will be added at the end of the table.|
- The last uploaded details will be logged under <agent_Home>/logs/csvupload_serverout.txt file.
ManageEngine Analytics Plus agent provides more advanced settings that can be configured while uploading data from CSV files. You can learn about these import settings in the following section.
Configure Import Settings
The configuration file common_params.conf in the /conf directory provides parameters that Analytics Plus agent uses while uploading data. These settings help ManageEngine Analytics Plus properly understand the data being imported.
The following table lists the import parameters that can be configured in the common_params.conf file for uploading data from CSV files using the GUI mode.
Note: You will not find all the below given parameters present in the configuration file (except mandatory ones). In case you do not find them, add them into the file and provide the required value as specified below.
This parameter specifies whether to create the table or throw error, if the specified table does not exist in the database.
- True - New table will be created in the specified database with the said name and then the data will be imported. In case the table already exists in the database, then the data will be imported into it.
- False - The data will be imported into the specified table. In case the table does not exist in the specified database then it throws an error.
Default value is false.
Analytics Plus agent splits the CSV file into batches for uploading. This parameter specifies the number of row for each batch.
Default value is 5000.
This parameter specifies whether the first row of the CSV file is the column header or not.
- True - First row contains the column header names.
- False - First row does not contain the column headers. In this case it is mandatory to specify the column header names for the parameter ZOHO_COLUMN_HEADER_NAMES (described next).
Default is true.
|ZOHO_COLUMN_HEADER_NAMES||Column Names with comma separator|
This parameter is mandatory when the ZOHO_FIRST_ROW_HEADER is set to false
Specify the column headers/names in the same order as the data exists in the CSV file. The column names provided here will be considered as the header of the CSV file.
Format of the date.
The above example indicates a date in the format 10-Nov-2013. Ensure you provide the appropriate notation to match your date format.
In case you are using a custom date format, in the data being uploaded, other than the default date format in ManageEngine Analytics Plus, then you can use this parameter to specify the date format.
This parameter specifies whether theagent should automatically identify the delimiter or not.
- True -Theagent will automatically identify the delimiter (ZOHO_DELIMITER parameter) and text qualifier (ZOHO_QUOTED parameter) in the CSV file that is being imported. By default this will be set to true.
- False - You need to specify the delimiter and text qualifier explicitly.
In case this parameter is set to false, then you need to specify the delimiter and text qualifier
This parameter specifies the number of top rows that are to be skipped in the CSV file being imported.
This parameter specifies the thousand separator for the numeric data.
The following is the list of parameter values and the corresponding thousand separator.
- 0 - COMMA
- 1 - DOT
- 2 - SPACE
- 3 - SINGLE QUOTE
Default is 0.
This parameter specifies the decimal separator for the numeric data.
The following is the list of parameter values and the corresponding decimal separator.
Default value is0.
This parameter specifies the File Encoding Format of the input file.
Default value is UTF-8.
If you are uploading a CSV file which contains Byte Order Mark (BOM), then you can use this parameter to identify and remove BOM Characters.
Set this to true, the agent will identify the BOM and remove the BOM from the file. You can also specify the BOM type (UTF-16LE,UTF-16BE), if you know the type.
In case another import is triggered for the same table when this import is in process, then you can use this parameter to specify whether this import process can override the other import running.
- True- The agent will stop the current import process and overwrite the table with new import.
- False- The agent will not disturb the current import process and will cancel the new import request.
Default value is false.
This parameter controls the action to be taken in case there is an error during import.
- ABORT - In case of any error, the import will be aborted.
- SKIPROW - In case of any error,the specific row(s) in which error occurred will be skipped and then continue importing the rest of the data.
- SETCOLUMNEMPTY - In case of any error, the value of the specific cell(s) in which error occurred will be set to empty and then continue importing the rest of the data.
Default value is SETCOLUMNEMPTY
The following table lists the parameters that need to be configured to set the delimiter when ZOHO_AUTO_IDENTIFY is set to false.
The CSV row will be skipped, If the comment character is found at the beginning of the row.
The above will skip the rows starting with $ character while importing.
|ZOHO_DELIMITER||Specifies the delimiter which separates the values in the file. The following is the list of parameter values and the corresponding delimiter:
- 0 - COMMA
- 1 - TAB
- 2 - SEMICOLON
- 3 - SPACE
|ZOHO_QUOTED||Specifies the Text Qualifier which surrounds string values in the CSV file, if available. The following is the list of parameter values and the corresponding text qualifier:
- 0 - None
- 1 - SINGLE QUOTE
- 2 - DOUBLE QUOTE
The following screenshot illustrates the common_params.conf file showing some of the import parameters:
Viewing the data in the Application
To view the data that you have uploaded
- Login to https://<analyticsplus-hostname>:<port>
- Click on the corresponding Database name under My Databases.
- Click on the corresponding table at the Left to open the table and view the uploaded data.