| | | |
 Uploading .... Upload Tool
Introduction
Zoho Reports Upload Tool is a downloadable standalone utility, that enables you to upload data from your local databases and other applications behind firewall into Zoho Reports. The tool can be used in two ways:
- Upload Data from CSV Files
You can bulk upload data in a CSV file into Zoho Reports using this tool. It is especially useful when you need to upload more than 500,000 rows or whose file size is greater than 10 MB (these import size limits are applicable when you use the Zoho Reports web user interface). The tool splits the CSV file into batches and uploads it to Zoho Reports, making it much more resilient to intermittent Internet connectivity outages or slow connections. It can be invoked in GUI and Console (command line) modes.
- Upload Data Directly from Databases behind Firewalls
You can pull data from application databases behind firewall and upload the same into Zoho Reports. The tool directly accesses your database, queries for the required data and uploads it to Zoho Reports. Databases supported currently are MS Acccess, MySQL, Microsoft SQL Server, Sybase, PostgreSQL and Oracle. Use the tool in either way as it best suits you. If you have data
Download Tool The Zoho Reports Upload Tool can be downloaded from the below URLs. The tool is supported in both Windows and Linux operating systems :
Linux Download: http://css.zohostatic.com/db/api/v3_0/m9/ZohoReportsUploadTool_Linux.zip
Configuration Files
Once downloaded, extract the zip files to a directory of your choice. The configuration settings for the tool are to be specified in the files under <Upload Tool>/conf/ directory. The conf directory consists of three files,
- common_params.conf: Contains parameters common to both CSV and Database upload.
- database_connection_params.conf: Contains database connection parameters like database type, name, host, port etc., To be configured if you want to connect to the local database to fetch data and upload the same into Zoho Reports.
- database_sql_queries.xml: Contains the SQL SELECT Queries to be executed in the local database to fetch data along with details about where to upload the data in Zoho Reports. To be configured if you want to connect to the local database to fetch data and upload the same into Zoho Reports.
Common Settings and Proxy Configuration Common Settings and Proxy Configuration
The tool runs in your environment and has to contact Zoho Reports service in the cloud for pushing data either from a CSV file or a database. There are some configuration settings which are common to both these methods. These configurations could be configured in the common_params.conf file.
In the configuration file common_params.conf, there are two parameters called REPORT_SERVER_URL and IAM_SERVER_URL. The tool will connect the REPORT_SERVER_URL (which is the Zoho Reports service URL) after being authenticated by the IAM_SERVER_URL (this is the URL of the Zoho Authentication server).
The default values for this two parameters are given below:
- IAM_SERVER_URL=https://accounts.zoho.com
- REPORT_SERVER_URL=https://reportsapi.zoho.com
If the upload process is executed by a shared user, then it is necessary to specify the below parameter:
- DBOWNERNAME=database_owner_username
If you are connected to the Internet through a proxy server (typically used in an Organization Network), you need to specify your proxy server parameters in the common_params.conf file. If you are using a Direct Internet connection, then you could ignore this setting.
Steps for providing the Proxy server settings:
- Open <Tools_Home>/conf/common_params.conf
- Set USEPROXY as true
- Specify PROXYHOST as <your_proxy_host> - This is the machine name or IP address of where the proxy server is running.
- Specify PROXYPORT as <your_proxy_port> - This is the port in which the proxy server is running
- Specify PROXYUSERNAME as <your_proxy_username> - Your user name to access the proxy server
- Specify PROXYPASSWORD as <your_proxy_password> - Your password to access the proxy server
If you are not using a Proxy server and are connected to the Internet Directly then set the parameter USEPROXY to false.
Other common import parameters can also be set in the common_params.conf file.
- ZOHO_AUTO_IDENTIFY: Set this as true if you want the tool to automatically identify the delimiter and text qualifier in the CSV file that is being imported. If set as false, you need to specify the delimiter and text qualifier explicitly. This parameter is applicable during CSV file upload.
- ZOHO_ON_IMPORT_ERROR: Instructs what Zoho Reports should do in case there is an error during import. Values are:
- ABORT - Abort the import operation
- SKIPROW - Skip importing the specific rows where error occurs
- SETCOLUMNEMPTY - Set the specific cell value alone as empty
- ZOHO_CREATE_TABLE: Set as true if the table name you specify (in which the data is to be uploaded) is to be created in Zoho Reports database, in case it does not exist already. Set as false when you already have the table created
- LINES_TO_SEND: Maximum number of rows/lines to be read and sent in a batch, from the CSV file/database at a time to Zoho Reports. Batching helps in splitting the data into manageable chunks and uploading the same into Zoho Reports, without loading/hogging the network with the entire dataset one shot. Useful when you have slow connections.
- ZOHO_MATCHING_COLUMNS: This parameter specifies the columns (comma separated for more than one column) to be matched if the import type is UPDATEADD (ie when you require to Add new rows and update existing ones ). This parameter will be considered only for CSV file Uploads using Console mode (discussed below).
 Import Parameters Below tabulated are the list of import parameters that could be configured in the common_params.conf file for uploading data from CSV files. For Uploading data from in-house databases (MS Access, MySQL, SQL Server, Sybase, PostgreSQL and Oracle), it is not necessary to specify these parameters in common_params.conf file. Parameters for specifying the Import Options These parameters are used to specify the various options when importing. Some of these parameters are mandatory and are highlighted and marked with star " * " . | Parameter | Possible Values | Description | | ZOHO_IMPORT_TYPE * | - APPEND
- TRUNCATEADD
- UPDATEADD
| APPEND - Appends the CSV file rows with the existing rows in the table TRUNCATEADD - Deletes all the rows of the table and adds the CSV file rows as a new entry UPDATEADD - Updates the row if the mentioned column values are matched, else a new entry will be added | | ZOHO_MATCHING_COLUMNS** | List of comma separated column names.
E.g.,: ZOHO_MATCHING_COLUMNS=Name,Department | - Should be passed only when the ZOHO_IMPORT_TYPE is UPDATEADD.
- The values in the columns to be matched will be used for comparison to check whether data row(s) being imported matches with an existing row(s) in the table.
- The existing rows in the table that match will be updated with values from data imported. The remaining rows are appended to the table as new rows.
| | ZOHO_CREATE_TABLE | true/false. | Optional. Default is false. In case it is true, then the table is created if the table name provided (in which the data is to be uploaded) does not exist in the specified database. In case its false, if the table name provided (in which the data is to be uploaded) already exists in the database. | | ZOHO_ON_IMPORT_ERROR * | - ABORT
- SKIPROW
- SETCOLUMNEMPTY
| This parameter controls the action to be taken in case there is an error during import. ABORT - Aborts the whole import if an error occurs. SKIPROW - In case of any error, skips that specific row(s) which has the problem and continue importing the rest. SETCOLUMNEMPTY - In case of any error, sets the value of the specific column(s) of a row in which error occured, to empty and continue importing. | | ZOHO_AUTO_IDENTIFY * | true/false | Set this as true if you want the tool to automatically identify the delimiter (ZOHO_DELIMITER parameter) and text qualifier (ZOHO_QUOTED parameter) in the CSV file that is being imported. If set as false, you need to specify the delimiter and text qualifier explicitly. This parameter is applicable during CSV file upload. Refer to the following table which describes the parameters that need to be set if ZOHO_AUTO_IDENTIFY is set to false. | | ZOHO_SKIPTOP | <number> | Optional Number of rows that are to be skipped from the top in the CSV file being imported. | | ZOHO_DATE_FORMAT | Format of the date.
E.g., ZOHO_DATE_FORMAT=dd-MMM-yyyy | Optional The format of date value. Specify this in case any date field is being imported and its format cannot be auto recognized by Zoho Reports. Below page explains how to construct date format string:
http://reports.zoho.com/import/ImportHelp.jsp | CSV Format Details These parameters need to be specified if the ZOHO_AUTO_IDENTIFY is set to false. | Parameter
| Possible Values | Description | | ZOHO_COMMENTCHAR | <character> | Comment Character. If the character mentioned is found at the beginning of the row, the CSV row will be skipped.
Example: ZOHO_COMMENTCHAR=$ | | ZOHO_DELIMITER | 0 / 1 / 2 / 3 | Delimiter which separates the values in the file. 0 - if the delimiter is COMMA
1 - if the delimiter is TAB
2 - if the delimiter is SEMICOLON
3 - if the delimiter is SPACE | | ZOHO_QUOTED | 0 / 1 / 2 | The Text Qualifier, if any, which surrounds string values in the CSV file. 0 - None
1 - SINGLE QUOTE
2 - DOUBLE QUOTE | Uploading Data from CSV File - GUI Mode Steps for running the CSV upload tool in GUI mode.Uploading Data from CSV File - GUI Mode - Make sure that the Zoho Reports database into which the data is to uploaded is already present in http://reports.zoho.com. If not please create the same before proceeding further.
- Invoke the CSVUploadGUI.bat / CSVUploadGUI.sh file under <Tools_Home>in directory
- Provide the required inputs in the GUI
- Click on the Upload button
- All your data from the CSV file will get uploaded to the specified database and table in Zoho Reports.
The parameters that are to be given in the GUI above
- File: Path and name of the CSV file from which data is to be uploaded
- Database Name: Name of the database in the Zoho Reports account into which the csv file's data is to be uploaded. Make sure the database is already present in Zoho Reports. If it is not an existing database, create a blank database with the same name in your Zoho Reports account
- Table Name: The name of the database table into which the csv file data is to be loaded
- User Name / Password: Credentials that you use for logging into your Zoho Reports account
- Import Type: Choose from the three options,
- Add at the end - The data in the csv file gets added (appended) as new rows in the table
- Delete existing and add - Deletes all existing rows in the table and adds the csv data as new rows
- Add, replace if already exists - The values in the 'Columns to match' field will be used for comparison to check whether data rows being imported matches with the existing rows in the table. The matching rows will be updated with values from data imported. The remaining rows are appended to the table as new rows.
- Columns to match: To be given only if the Add, replace if already exists option is chosen. Specify the columns to be matched seprating them with a comma
Uploading Data from CSV File - Console Mode You could also invoke the Upload tool in console mode to upload the CSV data. This option would be helpful in cases where you want to upload data periodically using a scheduler or when you have to invoke the tool from another application.
Steps for running the CSV upload tool in console mode
- Make sure that the Zoho Reports database into which the data is to uploaded is already present in http://reports.zoho.com. If not please create the same before proceeding further.
- Run the CSVUploadConsole.bat / CSVUploadConsole.sh file under <Tools_Home>bin directory along with the required parameters
All your data from the CSV file should get uploaded to the specified database/table in Zoho Reports.
Following are the mandatory parameters that should be given as arguments when running the CSVUploadConsole.bat/sh file:
Parameter Name
| Description
| File Name
| The CSV file location to be uploaded | Database Name
| The name of the database in Zoho Reports to which the CSV file is to be uploaded
Note: The database should already be present in reports.zoho.com in your account. If its not present, first create the database in your account before executing this upload to avoid failure. | Table Name
| The table name in which the CSV file is to be uploaded
| | Import Type | Can be any one of APPEND/UPDATEADD/TRUNCATEADD. - APPEND - appends the CSV data to the end of the table.
- UPDATEADD - updates existing data and appends new data. For this you also need to configure ZOHO_MATCHING_COLUMNS in "common_params.conf" under the "conf" directory.
- TRUNCATEADD - Deletes the existing data and adds new data.
| User Name
| Your Zoho Reports account login name | | Password | Password for accessing your Zoho Reports account. |
The last uploaded details will be logged under <Tool_Home>/logs/csvupload_serverout.txt file.
Uploading Data from in-house Databases If you have any applications that stores data in any of the databases like MS Access, MySQL, SQL Server, Sybase, PostgreSQL or Oracle, running behind the firewall, you can use this option to pull data from such databases and upload the same into Zoho Reports.
Other than the common settings like proxy server's in the common_params. conf file, database related information are to be specified in the other two configuration files namely database_connection_params.conf & database_sql_queries.xml. These configurations are to be done before you use the tool to fetch data from databases.
Specify Database connection Settings in database_connection_params.conf:
You need to specify the connection setting about your in-house database behind firewall in this file. A typical database_connection_params.conf looks like below:
The example above is for a MySQL database. You can fill similar values for Microsoft SQL Server, Sybase and Oracle.
Setting up JDBC Driver for MySQL:
If you plan to use the Upload tool to fetch data from your local MySQL databases and upload it into Zoho Reports, then you need to setup the MySQL JDBC driver for the tool to work. For all other supported databases the corresponding JDBC drivers are bundled along with the tool, by default.
Follow the steps given below to setup the MySQL JDBC driver:
- Download the MySQL JDBC Driver from the link below:
http://mysql.spd.co.il/Downloads/Connector-J/mysql-connector-java-3.0.17-ga.tar.gz - Extract the downloaded file (using tools like Winzip, tar etc.,)
- Copy the JDBC driver file "mysql-connector-java-3.0.17-ga-bin.jar" into the Upload tool directory <Tools_Home>\lib
Once the JDBC driver is set, you can connect the Upload tool to any local MySQL database to fetch the required records and upload the same into Zoho Reports.
For MS Access:
You need to specify the MS Access database file name which contains the data with full file path, for the parameter MDBFILENAME
e.g.: MDBFILENAME=C:\\MSACCESS\\mdbfiles\\test.mdb. No other parameter is required for MS Access database
SQL Server:
Windows Authentication can also be used to connect to SQL Server database. Please refer the link to know more.
Default Settings for different Databases:
The following table captures the default connection settings that can be specified in the database_connections_params.conf for the different databases. You might need to modify the settings like PORT, if you are not using the default provided by the database.
Database
| DBTYPE
| PORT (default)
| MDBFILENAME
| MySQL
| mysql
| 3306
| -
| Oracle
| oracle
| 1521
| -
| SQL Server
| sqlserver
| 1433
| -
| Sybase
| sybase
| 5000
| -
| PostgreSQL
| postgresql
| 5432
| -
| MS Access
| msaccess
| -
| C:\\MSACCESS\\mdbfiles\\test.mdb (Example) | Database Query settings in database_sql_queries.xml:
This file contains all settings related to the SQL SELECT Queries that are to be executed in the in-house database for fetching data, along with settings about the Zoho Reports database and table names into which the data is to be uploaded.
A typical database_sql_queries.xml file looks like below :
<Query dbname="zoho_reports_dbname" tablename="zoho_reports_tablename" importtype="APPEND / TRUNCATEADD / UPDATEADD" matchingcols="matching_cols_for_updateadd" selectcols="columns_to_import_from_csv" skiptop="number_of_rows_to_be_skipped">sql_query_ to_be_executed</Query>
To fetch the data in your in-house database you need to provide the necessary SQL SELECT queries that are to be executed in the database. These queries would be executed by the tool in your local database to fetch the necessary records and upload the same into Zoho Reports according to the settings provided.
You can provide any number of SQL SELECT queries to execute. Each query is to be provided in a <Query> </Query> XML element. Multiple queries can be grouped within the <Queries> </Queries> element.
As part of the <Query> node, you need to also specify the details about the Zoho Reports database into which the data that is fetched from executing the SQL SELECT query is to be uploaded.
All the parameters to be provided as part of the <Query> node are explained in the table below:
Parameter
| Description
| | dbname | The Zoho Reports database name into which the data is to be uploaded after executing the SQL Query.
Note: The database should already be present in reports.zoho.com in your account. If its not present, first create the database in your account before executing this upload to avoid failure. | | tablename | The Zoho Reports table name into which the data is to be uploaded after executing the SQL Query | | importtype | Allowed values are APPEND / TRUNCATEADD / UPDATEADD.
APPEND - Appends the resultant data as new records into the given Zoho Reports table
TRUNCATEADD - Drops all the existing data in the Zoho Reports table and adds the resultant query data as new records to the table
UPDATEADD - Appends the new data. If the existing values (in the Zoho Reports table) for the matching columns matches with new the data row fetched from the query execution, then the corresponding row in Zoho Reports table will be updated with the new values.
The matching columns to be used are to be specified for the parameter matchingcols explained below
| | matchingcols | The column names separated by comma. This parameter should be given if the import type is UPDATEADD. If the resultant query data row matches with this matching column values, the corresponding row will be updated.
| | selectedcols | The column names separated by comma. Only these columns are uploaded from the resultant query data into the online database.
Leave this "" if you want all the columns to be uploaded.
| | skiptop | The number of rows to be skipped from the top in the resultant query data before being uploaded.
Leave this "" if you want all the rows from the resultant query data to be uploaded.
| | sql_query | Specify the SQL SELECT query to be executed in the local database for fetching the necessary data.
Please note that the '<' symbol in the criteria should be replaced with < and '>' symbol in the criteria should be replaced with >
Example: select * from employee where age > 25
This query fetches all the record from the employee table whose age is greater than 25
|
You can specify any number of SQL queries using the <Query> XML Element as described above. Once the settings have been saved in the configuration files, you can run the UploadFromDB.bat / UploadFromDB.sh file to perform the upload.
Note: In case there is any failure in executing any of the given SQL Query, then the upload tool will abort the execution of that query and the ones that follow. Whatever queries that had been successfully executed and uploaded into Zoho Reports till then will not be rolledback
The details of the last upload executed will be logged under <Tool_Home>/logs/dbuploadserverout.txt file.
You can also execute this database upload periodically using a Scheduler to keep the data in Zoho Reports synchronized with your database. Read more in the next section.
Setting up Periodic Upload of Data You can setup a automatic periodic schedule to upload the data from your in-house application / database or from a CSV file such that the data in Zoho Reports remains current. With this mechanism you could have the latest data from your application synced periodically into Zoho Reports, to ensure that the reports that you create over this data stay current.
The periodic upload can be setup using the Operating System Scheduler feature as explained below. You could setup a periodic schedule for both the CSV Console mode utility as well as the Database Upload utility described above.
Setting up Schedule In Windows Operating System:
- Open Scheduled Tasks following the Windows menu path 'Start -> Settings -> Control Panel -> Scheduled Tasks'
- Click on 'Add Scheduled Task' to open the "Schedule Task" Wizard
- Click on 'Browse' button and select the appropriate upload tool command line batch file CSVUploadConsole.bat (for CSV upload) or UploadFromDB.bat (for uploading from database). Ensure you have provided the necessary settings in the Upload tool configuration files.
- You need to provide the appropriate command line arguments for the batch file.
- For UploadFromDB.bat: Provide the Zoho Reports Login User Name and Password as arguments as in the example below:
- UploadFromDB.bat arvindnatarajan testing
- For CSVUploadConsole.bat: Provide full path of the CSV file to be uploaded, Zoho Reports Database name, Table name, Type of Import, Zoho Reports User Name and Password in the given order. Refer to the example below:
- CSVUploadConsole.bat C:\CSV\demo.csv DemoDB DemoTable APPEND arvindnatarajan testing
- Specify other information, like time of schedule etc., required for scheduling the task
- Save the task
In Linux Operating System:
- In Linux, you can use crontab command for scheduling the migration process using the command line script CSVUploadConsole.sh (for CSV upload) or UploadFromDB.sh (for uploading from database). (Checkout Simple Help on Linux Crontab command)
- Ensure you have provided the necessary setting in the Upload tool configuration files.
Points to consider, if you are using the upload tool for periodic upload: - Users have to take care of pulling out the latest data from the application or database, since the last upload into Zoho Reports. Also ensure that this data is located in the right place for the tool to access it. Incase of database upload you need to provide the appropriate SQL Select query to pull the latest data since last upload.
- If any data gets deleted in your application/database, getting that updated in Zoho Reports would be tricky. Only option is to drop all the existing data and add the entire data set once again
- If data gets modified in your application/database, you need to ensure to pull those data from your application/database and upload them into Zoho Reports with the option "Update and Add". This will update existing records in Zoho Reports with the latest one and append all other new records.
Viewing the data online - Login to http://reports.zoho.com
- Click on the corresponding Database name under My Databases
- Click on the corresponding table on the Left Hand Side (LHS) to view the uploaded data.
Frequently Asked Questions - How to allow the shared user to upload the data to the shared table
- How to increase / decrease the batch size for upload
- Can I upload Excel files using Upload Tool
- How to connect to a particular SQL Server Instance
- Can I use my Yahoo / Gmail / Google Apps / Facebook username and password (used to login into my Zoho Account) to upload data using Zoho Reports Online Reporting Database?
- I want to upload the values of column "column1" from my local database to "newcolumn1" of Zoho Reports table. I want to rename the column "column1" to "newcolumn1" in the query result. How can I rename the column while uploading the data from my local database?
- Can Upload tool be used to upload data from remotely hosted databases (SQL Server, Oracle, MySQL, Sybase, PostgreSQL) into Zoho Reports?
- While uploading data from MySQL database, I got the error "Value '0000-00-00' can not be represented as java.sql.Date". How can i overcome this error?
- Can I use my Windows authentication to connect to SQL Server database and fetch records to upload into Zoho Reports?
1. How to allow the shared user to upload the data to the shared table?
Follow the below steps to allow the shared user to upload the data using Upload Tool:
Steps to do by database owner:
- Login into database owner's account
- Share the table to the shared user with Import Permission
Steps to do by shared user:
- Download the tool
- Open <Tool_Home>/conf/common_params.conf file
- Add the below parameter:
- DBOWNERNAME=<dbownername>
- Save the configuration file
- Start uploading the data using shared users credentials (username and password
2. How to increase / decrease the batch size for upload?
To upload the data of larger size, Zoho Reports Upload Tool splits the data and uploads as batches. It is necessary that the batch size should not exceed 10 MB or 100000 records. The user can configure the number of rows to be sent for each batch by changing the LINES_TO_SEND parameter in common_params.conf file.
The steps are mentioned below:
- Open <Tool_Home>/conf/common_params.conf file
- Change the parameter:
- Save the configuration file
- Start uploading the data
3. Can I upload Excel files using Upload Tool
No. Currently, the user can upload only CSV files. We recommend you to follow the below alternate way to upload Excel files:
- Open the excel file
- Export the excel file as CSV
- Save the file as CSV
- Now upload the newly saved CSV file using Zoho Reports Upload Tool
4. How to connect to a database in particular SQL Server Instance and uploads the data into Zoho Reports Online Reporting Database?If the user wants to connect to a particular SQL Server Instance and uploads the data into Zoho Reports Online Reporting Database, in addition to configuring the common and database connection configurations and SQL Queries, the following steps need to be followed: - Open database_connection_params.conf file found under <Tool_Home>/conf directory
- Edit the param DBNAME as below:
DBNAME=<your_sql_server_db_name>;instance=<your_sql_server_instance>
Example: DBNAME=MySQLServerDB;instance=MySQLServerInstance - Save the file and start the upload process
5. Can I use my Yahoo / Gmail / Google Apps / Facebook username and password (used to login into my Zoho Account) to upload data using Zoho Reports Online Reporting Database?
Currently, Zoho Reports Upload Tool does not support signin using Yahoo / Gmail / Google Apps / Facebook accounts. The Upload Tool expects Zoho Username and Password for signing into Zoho. Kindly follow the below steps to generate Zoho Password for your Zoho account (created using yahoo/google/facebook accounts): - Login into Zoho using your Yahoo / Gmail / Google Apps / Facebook account
- Go to:
- Click, "Change Password" link found under the 'Home' tab
- Now set the password for Zoho
Now you can use your Zoho Username (found at the top right corner of the Zoho Reports window) and Zoho password in the Zoho Reports Upload Tool to upload the data from your local in-house database / CSV file into Zoho Reports online reporting database. Please note that setting password for Zoho will not affect signing in using your Yahoo / Gmail / Google Apps / Facebook account.
6. I want to upload the values of column "column1" from my local database to "newcolumn1" of Zoho Reports table. I want to rename the column "column1" to "newcolumn1" in the query result. How can I rename the column while uploading the data from my local database?
Kindly follow the below steps to change the column name in the resultant data from your local database.
1. Open <Tool_Home>/conf/database_sql_queries.xml
2. Specify the column alias for the column that you want to rename.
Example:
<Queries>
<Query dbname="TestDB1" tablename="TestTable1" importtype="APPEND" matchingcols="" selectcols="" skiptop="">
SELECT column1 AS newcolumn1, column2 AS newcolumn2, column3 AS newcolumn3 FROM databasetable1
</Query>
</Queries>
3. Save the file and start the upload process
7. Can Upload tool be used to upload data from remotely hosted databases (SQL Server, Oracle, MySQL, Sybase, PostgreSQL) into Zoho Reports?
Yes, the Upload tool can be used to upload data from remotely hosted databases (SQL Server/Oracle/MySQL/Sybase/PostgreSQL) into Zoho Reports. The Upload tool can connect to the hosted database (running in a remote machine), provided it could establish a network connection via JDBC (Java Database Driver) to the remote database server from 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 described under the section "Specify Database Connection Settings" in this document. For the "HOSTNAME" parameter in the database_connection_params.conf file, you need to specify the remote hosted database server IP address or host name and the corresponding port number in which the database is running for the parameter "PORT".
8. While uploading data from MySQL database, I got the error "Value '0000-00-00' can not be represented as java.sql.Date". How can i overcome this error?
The above error is caused because of the following reason. If a table in your local MySQL database, that you are fetching data from, contains a date column and if it contains any 'NULL' value, then MySQL returns the value as '0000-00-00' when queried. This value is not a proper date value, hence this error is thrown by the MySQL JDBC driver.
You can overcome this error by appending the value ?zeroDateTimeBehavior=convertToNull& after the database name in the database_connections_params.conf present in the ZohoReportsUdToolconf. An example configuration would look like DBNAME=Test_DB?zeroDateTimeBehavior=convertToNull&.
9. Can I use my Windows authentication to connect to SQL Server database and fetch records to upload into Zoho Reports?
Yes, you can use Windows Authentication to connect to your SQL Server database and fetch the necessary records to be uploaded into your Zoho Reports account.
To do this, you need to provide the database name followed by the Windows domain name separated by ' ; ' for the parameter DBNAME in the file database_connections_params.conf.
Syntax:
DBNAME=<your_database_name>;domain=<your_domain_name>
<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
Save the file and start the upload process. Troubleshooting Tips
Problem 1: I get "UnknownHostException : accounts.zoho.com" while trying to upload the data using Upload Tool Solution: This could occur when your computer is not able to resolve the host name of the servers http://accounts.zoho.com and http://reports.zoho.com provided for the parameters IAM_SERVER_URL and REPORT_SERVER_URL respectively. To solve it Provide the direct IP Address of this servers in the common_params.conf file. REPORT_SERVER_URL = 74.201.154.132
IAM_SERVER_URL = 74.201.154.172
Note: The above IP address can change anytime. Incase you face problems in connectivity due to this please contact support@zohoreports.com
Problem 2: I get "The host did not accept the connection within timeout of 15000 ms" while trying to upload data using Upload Tool Solution: This could be because of improper proxy server settings. If you are connecting the Internet through a proxy server, ensure you have configured the correct proxy server details in common_params.conf file.
Incase you face any other problems using the Upload tool, please do contact Zoho Reports Technical support at support@zohoreports.com
Problem 3: The length of the column names are not more than 100 characters, but receiving the message:
Sorry, Column name should not exceed 100 characters
Solution: This could be because of the DELIMITER was identified wrongly as the data contains more than one possible delimiters. Follow the below steps to resolve this issue:
- Open common_params.conf file found under <Tool_Home>/conf directory
- Configure ZOHO_AUTO_IDENTIFY=false
- Add the below parameters:
- ZOHO_DELIMITER=0 / 1 / 2 / 3 (Where 0 is COMMA,1 is TAB,2 is SEMICOLON,3 is SPACE)
- ZOHO_QUOTED=0 / 1 / 2 (Where 0 is NONE,1 is SINGLE QUOTE,2 is DOUBLE QUOTES)
- Save the common_params.conf file and start the upload process
Problem 4: I get "Error!!! Sorry, you cannot upload files that exceed 10MB in size" while uploading data using Upload Tool
Solution: To upload the data of larger size, Zoho Reports Upload Tool splits the data and uploads as batches. It is important that the batch size should not exceed 10 MB or 100000 lines. You can increase or decrease the lines to send for each batch in common_params.conf file.
Follow the steps below:
- Open <Tool_Home>/conf/common_params.conf file
- Change the parameter:
- LINES_TO_SEND=<no_of_lines>
- Save the configuration file
- Start uploading the data
|
|
|
| | | |
|
Thanks!!
You can set the DATE FORMAT as below:
1. Open common_params.conf file under <Tool_Home>/conf/ directory.
2. Add the below text in a new line:
ZOHO_DATE_FORMAT=<your_date_format>
Example:
ZOHO_DATE_FORMAT=dd/MM/yyyy
3. Then start upload using the tool
If the DATE values in your data could not be identified by Zoho Reports or if there is a mismatch between the mentioned DATE FORMAT and the date values in your data, Zoho Reports throws IMPORT ERROR.
If the parameter ZOHO_ON_IMPORT_ERROR set as ABORT, then the import process will be stopped if import error occurs.
You could also set SETCOLUMNEMPTY for ZOHO_ON_IMPORT_ERROR parameter, if you want to set the value as EMPTY, if import error occurs. If this parameter set as SKIPROW, then the row which has import error will not be imported.
For more details on import parameters, kindly refer the below link:
http://zohoreportsapi.wiki.zoho.com/Importing-CSV-File.html#ParametersforspecifyingtheImportOptions
Hope this clears your question.
Best Regards,
Manohar Nixon S
Zoho Reports
Logs indicate:
TESTRUN :: false
2009-09-18 13:09:15.718 :: Connection Props are :
DBTYPE :: msaccess
HOSTNAME ::
USERNAME ::
PASSWORD ::
PORT ::
DBNAME ::
MDBFILENAME :: C:\Matt\zoho\PivotTable.mdb
CONNECTION URL :: jdbc:odbc:DRIVER=Microsoft Access Driver (*.mdb); DBQ=C:\Matt\zoho\PivotTable.mdb;UserCommitSync=Yes; Threads=3; SafeTransactions=0; PageTimeout=5; MaxScanRows=8; MaxBufferSize=2048; DriverId=281;
TOTAL NUMBER OF QUERIES :: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Logging in...
Logged in successfully...
Exception occured while establishing connection to the database server
java.lang.RuntimeException: Exception occured while establishing connection to the database server
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.parseXMLAndImportData(ZohoReportUploadDataFromDB.java:292)
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.main(ZohoReportUploadDataFromDB.java:66)
Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.parseXMLAndImportData(ZohoReportUploadDataFromDB.java:281)
... 1 more
From the error message you got, we guess that this error occurred because of the mdb file mentioned is not found in the given location. If the file is available, please check whether the file(*.mdb file) has read permission.
Kindly check the above and if you are still facing the issue lets have an online meeting to resolve the issue. Send us your contact number to support [at] zohoreports [dot] com. We will call you and assist you in resolving the issue.
Thanks & Regards,
Manohar Nixon S
Zoho Reports
C:\>rem $Id: UploadFromDB.bat,v 1.6 2009/04/10 14:00:48 manoharnixon Exp $
"Usage : ZohoReports\UploadTool\bin\UploadFromDB.bat <username> <password>"
C:\>C:\ZohoReports\UploadTool\bin\UploadFromDB.bat sa adidas
C:\>rem $Id: UploadFromDB.bat,v 1.6 2009/04/10 14:00:48 manoharnixon Exp $
Could Not Find C:\temp\queryOutput_*
Exception in thread "main" java.lang.NoClassDefFoundError: com/adventnet/zoho/cl
ient/report/tool/ZohoReportUploadDataFromDB
Caused by: java.lang.ClassNotFoundException: com.adventnet.zoho.client.report.to
ol.ZohoReportUploadDataFromDB
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
Could not find the main class: com.adventnet.zoho.client.report.tool.ZohoReportU
ploadDataFromDB. Program will exit.
Sorry for the issue you have faced.
Could you please go to <Tool_Home>/bin/ directory and then execute:
UploadFromDB.bat <you_zoho_username> <your_zoho_password>
Please get back to support [at] zohoreports [dot] com if you have further clarifications.
Best Regards,
Manohar Nixon S
Zoho Reports
I tried with a ZOHO_MATCHING_COLUMNS by table like this:
ZOHO_MATCHING_COLUMNS=field1table1,field2table1
ZOHO_MATCHING_COLUMNS=field1table2,field2table2
Also I tried with
ZOHO_MATCHING_COLUMNS=field1table1,field2table1,field1table2,field2table2
and not work
Can anyone help me?
Currently you cannot specify multiple table information in a single common_params.conf file. However, you can have more than one common_params.conf file associated for different tables.
For example, you can have two common_params.conf file(say common_params_for_table1.conf, common_params_for_table2.conf) within the <Tool_Home>/conf directory. Within the specific conf files you can have the corresponding table's ZOHO_MATCHING_COLUMNS alone. In this case of multiple conf files, you can edit the file bin/run.sh and change the parameter "-DCONFFILE=conf/common_params.conf" based on the table(use the corresponding conf filename).
We believe that the above will help you. You can also send us your queries to support[at]zohoreports[dot]com. We will be more than happy to assist you.
Regards,
Saminathan,
Zoho Reports
/ZohoReports/UploadTool/bin/UploadFromDB.sh: line 15: java: command not found
Can anyone help me?
This issue will occur when the UploadFromDB.sh/bat file is executed from other than <Tool_Home>/bin/ folder. So, kindly follow the below steps:
1. Go to the tool home by executing the below command:
cd /<tool_path>/ZohoReports/UploadTool/bin/
where <tool_path> is where you have installed the Upload Tool.
2. From <Tool_Home>/bin folder, run the executable file by executing the below command:
For Linux:
sh UploadFromDB.sh <username> <password>
For Windows:
UploadFromDB.bat <username> <password>
We believe that this will resolve the issue. Kindly get back to support [at] zohoreports [dot] com for further clarifications or still the problem persists.
Thanks for your time. Have a great day!
Best Regards,
Manohar Nixon S
Zoho Reports
The log are:
TESTRUN :: false
2010-05-25 19:31:37.098 :: Connection Props are :
DBTYPE :: msaccess
HOSTNAME ::
USERNAME ::
PORT ::
DBNAME ::
MDBFILENAME :: C:\Access\LOGDATA.mdb
CONNECTION URL :: jdbc:odbc:DRIVER=Microsoft Access Driver (*.mdb); DBQ=C:\Access\LOGDATA.mdb;UserCommitSync=Yes; Threads=3; SafeTransactions=0; PageTimeout=5; MaxScanRows=8; MaxBufferSize=2048; DriverId=281;
TOTAL NUMBER OF QUERIES :: 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Logging in...
Logged in successfully...
:::::::: ZOHO REPORTS IMPORT PARAMETERS :::::::::
Zoho Reports Database Name :: MyDB1
Zoho Reports Table Name :: mytable1
Import Type :: APPEND
Matching Columns ::
Skip Top ::
Selected Columns ::
Query to be exectuted on local database ::
select * from dbtable1
2010-05-25 19:31:40.864 :: Executing query #1...
Exception in fetching data from db server
Exception occured while executing queries and uploading data
java.lang.RuntimeException: Exception occured while executing queries and uploading data
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.parseXMLAndImportData(ZohoReportUploadDataFromDB.java:368)
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.main(ZohoReportUploadDataFromDB.java:66)
Caused by: java.lang.RuntimeException: Exception in fetching data from db server for query
select * from dbtable1
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.getQueryAsCSV(ZohoReportUploadDataFromDB.java:234)
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.parseXMLAndImportData(ZohoReportUploadDataFromDB.java:322)
... 1 more
Caused by: java.sql.SQLException: [Microsoft][Controlador ODBC Microsoft Access] El motor de base de datos Microsoft Jet no puede encontrar la tabla o consulta de entrada 'dbtable1'. Asegúrese de que existe y que su nombre está escrito correctamente.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.getQueryAsCSV(ZohoReportUploadDataFromDB.java:185)
... 2 more
Please help.
Kindly follow the below steps to resolve the issue:
1. After entering the mdb file in database_connection_params.conf file, you need to configure the database_sql_queries.xml file.
2. Open <Tool_Home>/conf/database_sql_queries.xml
3. Configure the XML as below:
<Queries>
<Query dbname="MyDB1" tablename="mytable1" importtype="APPEND" matchingcols="" selectcols="" skiptop="">
select * from dbtable1
</Query>
</Queries>
where,
MyDB1 is the name of the database in your Zoho Reports account
mytable1 is the table name where the result of the query should be stored
The Query (select * from dbtable1) is the query that should be executed in your .mbd file. i.e., you need to give your own query with the table name that is present in your .mdb file.
We believe that the above said is clear. Kindly get back to support [at] zohoreports [dot] com for further clarifications in this regard.
Best Regards,
Manohar Nixon S
Zoho Reports
Thanks.
Thank you for contacting us. Sorry for the delayed response. Somehow we missed your comment.
Please send the exact error message which you have received during the upload process. If possible kindly send the screenshot of error message which will help us to understand the issue. Also send us the log files which are found within the directory "UploadTool_Home]/logs". You can send the information and files to support [at] zohoreports [dot] com.
Thanks and Regards
Senthilvel N
Zoho Reports
Thank you for contacting us. Please find below the steps to upload the data using upload tool:
1. Download the tool from the link : https://reports.wiki.zoho.com/Upload-Tool.html (If you are windows user download the tool for windows. If you are a linux user download the linux tool)
2. Extract the ZIP tool.
3. Goto the bin folder. ZohoReports\UploadTool\Bin
4. Start the upload process by executing CSVUploadGUI command.
5. It will open a window like in the below link. https://reports.wiki.zoho.com/Upload-Tool.html#Upload-Data-GUI
6. Refer the above link for uploading data.
We can provide you with a demo for "How to use the upload tool". Kindly send your email address and contact number to support [at] zohoreports [dot] com. We will initiate the call to discuss on this.
Thanks and Regards
Senthilvel N
Zoho Reports
http://www.mysql.com/downloads/connector/j/mysql-connector-java-com-3.0.16-ga.tar.gz.
BUT the tool doesnt work unless you change the name of the JDBC driver file to "mysql-connector-java-3.0.17-ga-bin.jar"
Raj
2011/12/01 17:07:52 IST:Error Occured in uploadData. Error Code: 7103, Error Message: Database not found! Please check whether the database exists
2011/12/01 17:07:52 IST:2011-12-01 17:07:52.17 ::
2011/12/01 17:07:52 IST:Exception occured while executing queries and uploading data
2011/12/01 17:07:52 IST:java.lang.RuntimeException: Exception occured while executing queries and uploading data
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.parseXMLAndImportData(ZohoReportUploadDataFromDB.java:412)
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.main(ZohoReportUploadDataFromDB.java:71)
Caused by: java.lang.RuntimeException: Exception occured while uploading data: Database not found! Please check whether the database exists
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.parseXMLAndImportData(ZohoReportUploadDataFromDB.java:405)
... 1 more
Caused by: java.lang.RuntimeException: Database not found! Please check whether the database exists
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.uploadData(ZohoReportUploadDataFromDB.java:463)
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.parseXMLAndImportData(ZohoReportUploadDataFromDB.java:391)
... 1 more
Caused by: ServerException (HttpStatusCode 400 Error Code: 7103 , URI: /api/subrat_cts/CDR_IN/at_cdr_activity_raw, Action: IMPORT, Message: Database not found! Please check whether the database exists)
at com.adventnet.zoho.client.report.BaseClient.sendRequest(BaseClient.java:115)
at com.adventnet.zoho.client.report.ReportClient.sendRequest(ReportClient.java:1231)
at com.adventnet.zoho.client.report.ReportClient.sendImportRequest(ReportClient.java:962)
at com.adventnet.zoho.client.report.ReportClient.importTblData(ReportClient.java:945)
at com.adventnet.zoho.client.report.ReportClient.importDataInBatches(ReportClient.java:823)
at com.adventnet.zoho.client.report.tool.ZohoReportUploadDataFromDB.uploadData(ZohoReportUploadDataFromDB.java:450)
... 2 more
Please suggest me
Nothing happen when I lanch DBupload.bat.
I want to sync a ms access base with Zoho Reports
Thanks