Uploading Data from CSV Files - Console Mode
Invoking Analytics Plus agent using the Command line mode (console mode) will be useful when you want to upload data periodically using a scheduler or when you have to invoke the agent from another application. In this section, we will discuss how to set up this agent for batch upload of CSV data in Command line mode .
Steps for running the CSV Analytics Plus agent in console mode:
Ensure that you have already created the database in the ManageEngine Analytics Plus account into which the data is to be uploaded.
Configure the proxy settings and the required Import settings in the common_params.conf file. Read more about these configuration settings from the Proxy Settings and Import Settings sections.
Run the CSVUploadConsole.bat / CSVUploadConsole.sh file under <Agent_Home> bin directory along with the required parameters. You have to provide full path of the CSV file to be uploaded, ManageEngine Analytics Plus Database name, Table name, Type of Import, ManageEngine Analytics Plus User Name and Password in the same order as given in the following snippet.
Using username and password:
|CSVUploadConsole.bat <host_name> <port_no> <file_name> <database_name> <table_name> <APPEND/UPDATEADD/TRUNCATEADD><manageengineanalytics_username> <manageengineanalytics_password>|
Using authtoken in the configuration file:
To upload data without specifying user email address and password on initiating an upload, you can specify user email address in the key USER_EMAIL_ADDRESS and authtoken in the key AUTHTOKEN in the file common_params.conf under agent_home/conf
- Steps to generate authtoken
- Authtoken can be generated by launching the URL- <Accounts URL>/apiauthtoken/create?SCOPE=ZROP/reportsapi
- Accounts URL can be found in the application console, under Help icon --> Product info --> Miscellaneous details
- Eg- https://steve-0430:8210/apiauthtoken/create?SCOPE=ZROP/reportsapi
CSVUploadConsole.bat <host_name> <port_no> <file_name> <database_name> <table_name> <APPEND/UPDATEADD/TRUNCATEADD>
Specifying authtoken in the script:
- To upload data using auth token instead of using ManageEngine Analytics password, use the following format
|CSVUploadConsole.bat <host_name> <port_no> <file_name> <database_name> <table_name> <APPEND/UPDATEADD/TRUNCATEADD><analyticsplus_emailaddress> -A <authtoken>|
- If you have been made as a database owner and need to import data into that particular database, the <manageengineanalytics_dbowner_mailaddress> has to be specified in the following format
CSVUploadConsole.bat <host_name> <port_no> <file_name> <database_name> <table_name> <APPEND/UPDATEADD/TRUNCATEADD><analyticsplus_username> <analyticsplus_password> -D <analyticsplus_dbowner_mailaddress>
- If you have been made as a database owner and need to import data into that particular database using auth token, use the following format
| CSVUploadConsole.bat <host_name> <port_no> <file_name> <database_name> <table_name> <APPEND/UPDATEADD/TRUNCATEADD>
<analyticsplus_username> -A <authtoken> -D <analyticsplus_dbowner_mailaddress>
After successful execution, the data in the CSV file will get uploaded to the specified table in ManageEngine Analytics Plus database.
The last upload details will be logged in the <Agent_Home>/logs/csvupload_serverout.txt file.
The following are the mandatory parameters that should be given as arguments when running the CSVUploadConsole.bat/sh file:
|File Name (file_name)||The CSV file location to be uploaded.|
|Database Name (database_name)||The name of the database in ManageEngine Analytics Plus into which the CSV file is to be uploaded.
Note: Ensure that the database is already available in ManageEngine Analytics Plus account. If it does not exist create the database before executing this upload to avoid failure.
|Table Name (table_name)||The table name into which the CSV file 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.
|Import Type||Set one of the following as Import Type.
- APPEND - appends the CSV data to the end of the table.
- UPDATEADD - updates existing data and appends new data. For this you need to configure ZOHO_MATCHING_COLUMNS in common_params.conf in theconf directory.
- TRUNCATEADD - Deletes the existing data and adds new data.
|User Name||Your ManageEngine Analytics Plus account login name|
|Password||Password for accessing your ManageEngine Analytics Plus account.|
Configure Import Settings
The configuration file common_params.conf in /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 - Console mode.
- 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. If 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. If 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 nu
er of row for each batch.
Default value is 10000 rows.
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.
|ZOHO_DATE_FORMAT||Format of the date.|
ZOHO_DATE_FORMAT = dd-MMM-yyyy
Note: 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 other than the default date format in ManageEngine Analytics Plus, then you can use this parameter to specify the date format.
|ZOHO_MATCHING_COLUMNS** ||List the column names separated by comma.|
E.g.,: ZOHO_MATCHING_COLUMNS = Name, Department
This parameter is mandatory when the ZOHO_IMPORT_TYPE is set to UPDATEADD. Set this parameter to column names based on which the existing records in the table need to be matched. If the record already exists in the table then it will be replaced with the new values in the uploaded CSV file. Remaining rows will be added at the end of the table.
This parameter specifies whether the agent should automatically identify the delimiter or not.
- True -The agent 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. The following table lists the parameters to specify the delimiter and text qualifier.
In case this parameter is set to false, then you need to specify the delimiter and text qualifier using the parameters in the table below.
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 is 0.
This parameter specifies the File Encoding Format of the input file.
Default value is UTF-8.
If you are uploading a CSV file contains 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 the new import can override this import.
- 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
Setting up Periodic Upload of Data
Using Analytics Plus agent you can periodically schedule uploading data from a CSV file. With this mechanism, you can have the latest data from your application synced into ManageEngine Analytics Plus, and the reports that you create over this data stay current.
You can setup a periodic schedule for the CSV Console mode utility using the Operating System Scheduler feature as explained below
Setting up Schedule in Windows Operating System:
Click Start -> Settings -> Control Panel ->Scheduled Tasks.
Click Add Scheduled Task. The "Schedule Task" wizard will open.
Click Action > Create Task to open the Create Task dialog.
Open Action Tab and then click New.
Click Browse button and select the Analytics Plus agent command line batch file CSVUploadConsole.bat in the Program/Script field. Ensure that you have provided the necessary settings in the Analytics Plus agent configuration files.
In the Add Argument field enter the command line arguments for the batch file. i.e., full path of the CSV file to be uploaded, ManageEngine Analytics Plus Database name, Table name, Type of Import, ManageEngine Analytics Plus User Name and Password the same order as given in the following snippet.
CSVUploadConsole.bat localhost 8443 C:\CSVdemo.csv DemoDB DemoTable APPEND firstname.lastname@example.org password
Setting up Schedule in Linux Operating System:
In Linux, you can use the crontab command for scheduling the migration process using the command line script CSVUploadConsole.sh. (Checkout Simple Help on Linux Crontab command)
Ensure you have provided the necessary setting in the Analytics Plus agent configuration files.
Points to consider, while scheduling periodic upload using Command line mode:
It is the users, responsibility to ensure that the latest data is uploaded into ManageEngine Analytics Plus by setting the schedule interval accordingly. Also ensure that the CSV data file is located in the right place for the agent to access it.
In case you have deleted few records in the CSV file being uploaded, the only option to remove these records from ManageEngine Analytics Plus database is to set the Import Type as TRUNCATEADD. This will delete all the records in the table and then add the data available in the CSV file.
In case you have modified few records in the CSV file being uploaded, then to get this modified in ManageEngine Analytics Plus database, set the Import Type as UPDATEADD and specify the matching columns. The Analytics Plus agent will compare the existing table in ManageEngine Analytics Plus with the CSV file being uploaded based on the matching columns. If the record already exists in the table then it will be replaced with the new values available in the CSV being uploaded. And the rest of the data will be appended at the end.
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.