Analytics » Upload Tool » Upload Tool - FAQ and Troubleshooting
1. Can I upload Excel files using Upload Tool
Currently,the Upload Tool supports uploading only data in CSV (Comma Separated Value)file formats. As an alternative, you can export the Excel file into CSV fileformat and then upload the data.
Follow the steps below to export Excel files into CSV:
· Open the Excel file.
· Click File -> Save As.
· Save the file in CSV format.
· Now upload the newly saved CSV file using Virtutem Analytics Upload Tool.
2. Can I use my Yahoo / Gmail / Google Apps / Facebookuser credentials to upload data using Upload Tool?
Currently, VirtutemAnalytics Upload Tool does not support uploading data using Yahoo / Gmail/ Google Apps / Facebook accounts credentials. The Upload Tool expects VirtutemAnalytics Username and Password to authenticate the user. So other login users need to generate a Virtutem Analytics Username and Password to upload data into Virtutem Analytics database.
Followthe steps below to generate a Virtutem Analytics Username andPassword:
· Login into Virtutem Analytics Reports using your Yahoo /Gmail / Google Apps / Facebook account credentials.
· Click the drop-down menu displayed with your user name at the topright corner.
· Click Account Settings menu item,this will open Virtutem Analytics Accounts Profile page(You can also directly access/open this page using direct link https://accounts.Virtutem.com ).
· In the Home tab, click ChangePassword link.
· Reset the password.
Now in VirtutemAnalytics home page, you can find the Virtutem Analytics Usernameat the top right corner. Use the new Virtutem Analytics Username and VirtutemAnalytics Password in the Virtutem Analytics Upload Tool to upload the data into your account.
3. How to increase/ decrease the batch size for upload?
To upload the data of larger size, Virtutem Analytics Upload Tool splits the data and uploads as batches. The user can configure the number of rows to besent for each batch by modifying the LINES_TO_SEND parameterin the common_params.conf file. By defaultthis will be set to 5000.
Followthe steps given below to specify the batch size:
· Open /conf/common_params.conf file.
· Set the LINES_TO_SEND parameter tothe number of lines you want to upload in each batch as given below.
LINES_TO_SEND=<numberof rows to be uploaded in a batch>
Example
LINES_TO_SEND=7000
· Save the configuration file and start uploading the data.
Note: |
|---|
It is mandatory that each batch size should not exceed 20 MB or 100,000 records. |
4. Can I allow the shared user to upload the data into my table?
Yes, you can allow your shared users to upload data into your tables using Upload Tool. Follow the steps given below to do this.
Steps tobe followed by you who is the Admin or Database Owner :
· Login into database owner's account.
· Share the table, in which you want to allow your shared user toupload data, with Import Permission.
· For more details, refer to this FAQ section .
Steps tobe followed by the shared user:
· Open /conf/common_params.conf file.
· Add the DBOWNERNAME parameter and setthis to Database Owner's Virtutem Analytics Email id or User name
Example:
DBOWNERNAME="patriciab
· Save the configuration file.
· Start uploading the data using the shared user's Virtutem Analytics credentials.
1. Can I use my Windows authentication to connect to SQL Server database and fetch records to upload into Virtutem Analytics ?
Yes, you can use Windows Authentication to connect to your SQL Server database and fetch the necessary records to be uploaded into your Virtutem Analytics account.
To dothis, you need to provide the database name followed by the Windows domain nameseparated by semi-colon ' ; ' for the parameter DBNAME in the database_connections_params.conf file.
Syntax:
DBNAME=<your_database_name>;domain=<your_domain_name>
Where
<your_database_name>- This is the database name in SQL Server to connect
<your_domain_name>- The Windows domain name that you use.
Example:
DBNAME=sqlserverdbname;domain=workgroup
2. Can Upload tool be used to upload data from remotely hosted databases (SQL Server, Oracle, MySQL, Sybase,PostgreSQL, DB2 and DB2-AS/400) into Virtutem Analytics ?
Yes, you can upload data from remotely hosted databases (running in a remote machine) into VirtutemAnalytics using Upload Tool,provided a network connection via Java Database Connectivity (JDBC) Driver could be established between the remote database server and the machine in which you have installed the Upload Tool.
The connection setting for the hosted database should be done in the same way as you do for a local database. For the "HOSTNAME" and "PORT" parameters,you need to specify the remote hosted database server IP address or host nameand the corresponding port number. To know how to specify connection setting,refer to Specify Database Connection Settings section
3. How to connect to a database in a particular SQL Server Instance and upload the data into Virtutem Analytics Online Reporting Database?
You can connect to a particular SQL Server Instance and upload the data into Virtutem Analytics Online Reporting Database by following the below steps, in addition to configuring the common and database connection configurations and SQL Queries.
DBNAME=<your_sql_server_db_name>;instance=<your_sql_server_instance>
Example
DBNAME="MySQLServerDB;instance=MySQLServerInstance
4. Is it possible to fetch data from multiple database using Upload Tool?
Yes, it is possible to set up the Virtutem Analytics upload tool to fetch data from multiple databases. By default, the Upload tool is designed to upload data fromone database in an upload process. However, we can modify the configuration files and executable bat or sh files to upload data from more than one database.
To accomplish the above set up, you need to create different set of configuration files required for each database.
For example, let's say you have two different databases in SQL Server to be connected for data upload. We should have one set of configuration files forSQL Server Database One and another set ofconfiguration files for Database Two.
If you are working on Windows environment you can download the modified sample configuration and batch (UploadFromDB.bat) files from this download link.Similar changes are required for the UploadFromDB.sh shell script if you needto set up these configuration on a Linux/Mac environment.
Follow the below steps once you have downloaded the sample and extracted the same.
(i.e., "<UPLOAD_TOOL_HOME>/ VirtutemAnalytics /Uploadtool/conf")o database_connections_params1.confo database_sql_queries1.xmlo database_connections_params2.confo database_sql_queries2.xml
( i.e., "<UPLOAD_TOOL_HOME> VirtutemAnalytics /UploadTool/bin"). The.bat file has been modified to upload the two different databases with thegiven two different configurations.
Thefollowing are the steps to configure two databases in the Upload tool:
Configurationfor SQL-Sever Database One:
- Provide the SQL server database connection propertiesin
- database_connections_params1.conf ",
- database_connections_params1.conf ",
- Define the necessary SQL server's queries in "database_sql_queries1.xml ".
Configuration2for SQL-Server Database Two:
- Provide the SQL Server database connection params in
- database_connections_params2.conf " ,
- database_connections_params2.conf " ,
- Define the necessary SQL Server queries in " database_sql_queries2.xml ".
Once the above setup is done, execute the modified batch files copied to the bin directory. This will fetch data from both the databases in SQL Server andupload that into Virtutem Analytics .
You can follow the same model if you want to fetch data from different databases such as SQL Server and Oracle and upload that into Virtutem Analytics .
5. Is it mandatory to follow similar column names in Virtutem Analytics table as available in my local database?
No. You can follow a different column names in Virtutem Analytics s table from what is there in your local database and still upload data using Upload Tool. Follow the steps to upload the data from your local database into the corresponding column in Virtutem Analytics table.
- Open <Tool_Home>/conf/database_sql_queries.xml
- Specify the query as given below to upload data from localdatabase into the corresponding column in Virtutem Analytics table.
<Queries><Query dbname="" TestDB1 " tablename="" TestTable1 "importtype="APPEND" matchingcols="""" selectcols="""skiptop="">" SELECT column1-local-db AS column1- virtutemanalytics -table , column2-local-db AS column2-virtutemanalytics -table , column3-local-db AS column3- virtutemanalytics -table FROM databasetable1
</Query> </Queries></Query> </Queries>
The above query will pull the data from column1-local-db, column2-local-db and column2-local-db fromyour local database databasetable1 and import theminto the column in column1- virtutemanalytics -table, column1-virtutemanalytics -table and column1- virtutemanalytics -table in the Virtutem Analytics table.
- Save the file and start the upload process.
6. How to upload data with different date format from the in-house/local databases into Virtutem Analytics ?
Virtutem Analytics Upload Tool expect the date format of the data being uploaded as dd/MM/yyyy HH:mm:ss. In case you are using a custom date format in your local database, then you need to convert your date value to the required format in your SQL SELECT Query using the DATE_FORMAT SQL function.
To convert the data format you need to set a date column to the DATE_FORMAT SQL function and specify the required default date format as given below.
DATE_FORMAT(< date_field >, '%d/%m/%Y 00:00:00')
In case your date column is set as string field, then you need to convert the data type using DATE function as given below.
DATE_FORMAT(DATE(<date_field>), '%d/%m/%Y 00:00:00')
1. I get " The host did not accept the connection within timeout of 15000 ms"while trying to upload data using Upload Tool. How to solve it?
Solution: This could be because of improper proxy server settings. If you are connecting the Internet through a proxy server,ensure that you have configured the correct proxy server details in the common_params.conf file. For more details,refer to the Setup: Common Settings and Proxy Configuration section.
2. I get an error message as ‘ out of memory’ while trying to upload the file. How can solve this error?
Solution: This could be because, the default memory allocated to the Java Virtual Machine (which executes the Upload tool) in your machine was not sufficient to import a large file using our Upload Too. Hence it throws the"Out of Memory". Follow the given steps to resolve this issue:
- Open the file setEnv.bat/ setEnv.sh available in the directory <Upload_Tool_Home>/bin
- Add the property - Xmx1024 atthe end of the variable JAVA_OPTS as below:
Windows: set JAVA_OPTS=""%JAVA_OPTS%"-XX:NewSize=48M -Xmx1024M
Linux/Mac: exportJAVA_OPTS="$JAVA_OPTS" -XX:NewSize=48M -Xmx1024MWindows: set JAVA_OPTS=""%JAVA_OPTS%"-XX:NewSize=48M -Xmx1024M
Linux/Mac: exportJAVA_OPTS="$JAVA_OPTS" -XX:NewSize=48M -Xmx1024M
- Save the file and start the upload process.
3. I get "Error!!! Sorry, you cannot upload files that exceed 50MB in size"while uploading data using Upload Tool. How to overcome this?
Solution: To up load the data of larger size, Virtutem Analytics Upload Tool splits the data and uploads them as batches. It is important that the batch size should not exceed 20 MB or 100,000 records per batch. You can increase or decrease the lines/records to send for each batch in common_params.conf file.
Follow the steps given below to set the lines to be uploaded in a batch:
- Open <Tool_Home>/conf/common_params.conf file
- Change the parameter LINES_TO_SEND=<no_of_lines>
- Save the configuration file and start uploading the data
For more details, refer to #Q3.
4. I get an error message as " Another import is in progress in this table started by the user 'User name' at 'time of import'." How to overcome this?
Solution: You will get the above message when more than one import process is running on the same table at the same time. Ensure that no other user is importing into the same table before initiating the progress.
5. I get a message as " ZOHO_MATCHING_COLUMNS is not present in the request parameters list". How to solve it?
Solution: You will get the above error message when you have set the import type to UPDATEADD and have not specified any value for the ZOHO_MATCHING_COLUMNS parameter. 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.
| Note: |
|---|
| It is recommended to set columns with unique values as matching column. |
6. I get "Error!!! Column " Column_Name" is present in match columns but not in selected columns" while uploading data. How to overcome this?
Solution: You will get the above error when the column specified for the ZOHO_MATCHING_COLUMNS parameter is not available in the table. When you have set Import Type to UPDATEADD , it is mandatory to specify matching column. These columns will be used to check whether a record already exist in the table. Ensure that you have specified a valid column name for matching column before uploading data to avoid failure.
7. I get error /VirtutemAnalytics/UploadTool/bin/UploadFromDB.sh:line 15: java: command not found.
Solution: This error occurs when UploadFromDB.sh/bat fileis executed from folder other than <Tool_Home>/bin/ . Usethe following method to execute the UploadFromDB.sh/bat file:
- Go to the tool home by executing the below command:
cd/<tool_path>/ virtutemanalytics /UploadTool/bin/
where<tool_path> is the Upload Tool installed location.
- From the <Tool_Home>/bin folder,run the executable file by executing the below command:
ForLinux/Mac: UploadFromDB.sh<username> <password>
For Windows: UploadFromDB.bat <username> <password>
8. I get an error message as " Maximum Concurrent User Tickets Limit Exceeded". How to solve this?
Solution: When you access theservice a session will be created, which will be deactivated once you logout orafter 7 days. If you did not logout or access the service from multiplelocations then multiple active sessions will be created for your account.Virtutem service restricts a user to have maximum of 20 active sessions. Incase you have exceeded this limit, you will not be allowed the access theservice. To overcome this you need to close the active sessions.
Follow the steps below to close the current active sessions.
- Login to http://accounts.virtutem.com with your Virtutem account credentials
- Choose the Active Session under the Home tab.
- Remove all active sessions by clicking the close all other sessions .
Uploading datafrom CSV file
Problem 1: The length of thecolumn names are not more than 100 characters, but receiving the message: "Sorry, Column name should not exceed 100 characters". How to solvethis?
Solution: This couldvbe because the Column Delimiters (delimiter character which separates one column from another in the data was identified incorrectly. Follow the stepsbelow to resolve this issue:
- Open the common_params.conf file available in <Tool_Home>/conf directory.
- Set the ZOHO_AUTO_IDENTIFY parameter to false .
- Add the followingparameters and set them to appropriate delimiter:
- ZOHO_DELIMITER (Specify anyone of the delimiter character,based on how the columns are delimited in your data. Where 0 is COMMA, 1 isTAB, 2 is SEMICOLON and 3 is SPACE. )
Example: ZOHO_DELIMITER =0
- ZOHO_QUOTED (Specify anyone of the value based on whether text/string values in your data are quoted by any character. Where 0 is NONE, 1 is SINGLE QUOTE and 2 is DOUBLE QUOTES)
Example: ZOHO_QUOTED=0
- Save the common_params.conf file and start the upload process.
For more details on these parameters, click here.
1. I get " Could not Find File '(unknown)' error while trying to import data from MSACCESS 2007 to Virtutem Analytics. How to overcome this?
Solution: This error occurs when the Upload Tool was notable to access the .mdb file that you have mentioned. Ensure that the file exists in the specified local
2. While uploading data from MySQL database, I got the error "Value '0000-00-00'cannot berepresented as java.sql.Date". How can I overcome this error?
Solution: This error could occur when the table in your local MySQLdatabase, from which you are fetching data, contains a date column with'NULL' value. When queried MySQL will return the value as '0000-00-00' bydefault. Since this is an invalid value, the MySQL JDBC driver will throw thiserror.
You can overcome this error using the database_connections_params.conf file available in the virtutemanalyticsUdToolconf . While configuring thelocal connection setting, in the DBNAME specify your database namefollowed by the ?zeroDateTimeBehavior=convertToNull& property. This will convertthis invalid data and adds null value in the VirtutemAnalytics table.
Example
DBNAME=Sales-Database ?zeroDateTimeBehavior=convertToNull&.