Reports Help - Uploading Data from Local (in-house) Databases

Uploading Data from Local (in-house) Databases

If you have any applications that stores data in any of the local databases like MS Access, MySQL, SQL Server, Sybase, PostgreSQL, Oracle, SQLite, DB2, DB2 AS/400 or any other database that runs behind the firewall (i.e in your local network),  you can use Upload Tool to pull data from your databases and upload the same into Zoho Reports. In this section, we will discuss how to upload data from your local databases into Zoho Reports. You can also read about how to configure the Upload tool to periodically upload/synchronize the data from your local databases into Zoho Reports. 

Configure Local Database Connection Settings

To upload data from local (in-house) databases, configure the proxy settings (if your network has a proxy server setup)  in the common_params. conf file, and then specify your local database connection settings in the configuration file database_connection_params.conf as described below.

Specify Local Database Connection Settings

You need to specify the connection setting about your in-house database behind firewall in this file. The following screenshot illustrates a typical database_connection_params.conf.

The following are the parameters that are to be given in database_connection_params.conf file: 

DBTYPE 

Specify your local database type. e.g: MySQL, SQL Server, Oracle, Sybase etc.,. (Refer to the next section which contains the list of possible values)

HOSTNAME  

Host name of your computer in which the database is running, i.e., the name of the computer where your database is running.

PORT

The port number used by the database server.  (Refer to the next section to know the default port number for different databases). 

USERNAME

Zoho Reports account login name.

PASSWORD

Password for accessing your Zoho Reports account.

DBNAME 

Your local database name from which the data is to be uploaded.

MDBFILENAME

In case you are going to upload data from MS Access database, then specify the complete path of MS Access database file name which contains the data.

e.g., MDBFILENAME=C:\\MSACCESS\\mdbfiles\\test.mdb.

The example in the above screenshot illustrates the connection settings for a MySQL database. You can fill similar values for Microsoft SQL Server, Sybase and Oracle.

Default Settings for different Databases

The following table captures the default connection settings that can be specified in the database_connections_params.conf for different database vendor type. If you are not using the default port provided by the database, then you need to specify the corresponding port for connecting.

DatabaseDBTYPEPORT (default)MDBFILENAMESQLITEFILENAME
MySQLmysql3306--
Oracleoracle1521--
SQL Serversqlserver1433--
Sybasesybase5000--
PostgreSQLpostgresql5432--
DB2db250000--
DB2-AS/400db2as40050000--
MS Accessmsaccess-Example:
C:\\MSACCESS\\mdbfiles\\Sales-Database.mdb  
-
SQLitesqlite--Example:
C:\\SQLITE\\sqlitefiles\\Sales-Database.db 

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. 

The following are the steps to setup the MySQL JDBC driver: 

  • Download the MySQL JDBC Driver from the following link:
 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. 

MS Access File path

In case you are going to upload data from MS Access, you need to specify the complete path of the MS Access database file which contains the data, for the parameter MDBFILENAME 

Example:MDBFILENAME=C:\\MSACCESS\\mdbfiles\\Sales.mdb. No other parameter is required for MS Access database.

Using SQL Server Windows Authentication

Windows Authentication can also be used to connect to your local SQL Server database. To do this, you need to provide the database name followed by the Windows domain name separated 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

Specify SQL SELECT Queries to be executed on local database

The database_sql_queries.xml file contains all the settings related to the SQL SELECT Queries that are to be executed in the in-house database for fetching the required data, along with settings about the Zoho Reports database and table names into which the data is to be uploaded. 

The following screenshot illustrates the database_sql_queries.xml file.

<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 from your in-house database you need to provide the necessary SQL SELECT queries that are to be executed in the database. These queries will be executed by the upload 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 also need the provide details about the Zoho Reports database into which the data that is fetched from executing the SQL SELECT query is to be uploaded. 

The following table explains all the parameters to be provided as part of the <Query> node.

ParameterDescription
dbnameThe Zoho Reports database name into which the data is to be uploaded after executing the SQL Query. 
 

Note: 

Ensure that the database is already available in Zoho Reports account. If it does not existcreate the database before executing this upload to avoid failure.
tablenameThe Zoho Reports table name into which the data is to be uploaded after executing the SQL Query.
Ensure that the specified table with similar column structure is already created in the Zoho Reports Reporting Database.

In case the table does not exist, then you can set the Upload Tool to create the table and then upload the data into this table using the ZOHO_CREATE_TABLE parameter available in common_params.conf file. Refer to the Configure Import Settings section to know more about the ZOHO_CREATE_TABLE parameter.
importtypeSet one of the following as Import Type.
  • APPEND - appends the data to the end of the table.
  • UPDATEADD - updates existing data records and appends new data records. For this you need to configure ZOHO_MATCHING_COLUMNS in common_params.conf in the conf directory.
  • TRUNCATEADD - Deletes the existing data and adds new data.
matchingcolsThis is applicable only when the importtype is set to UPDATEADD. 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.
selectedcolsThe column names separated by comma. Only these columns are uploaded from the resultant query data into the online database.

Leave this as "" (empty) if you want all the columns to be uploaded.
skiptopThe number of rows to be skipped from the top in the resultant query data before being uploaded. 

Leave this as "" (empty) if you want all the rows from the resultant query data to be uploaded. 
sql_querySpecify 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 &lt; and '>' symbol in the criteria should be replaced with &gt; 

Example: select * from employee where age &gt; 25

This query fetches all the record from the employee table whose age is greater than 25

You can specify any number of SQL SELECT queries using the <Query> XML Element as described above. 

Configure Import Settings

The configuration file common_params.conf in /conf directory provides parameters that Upload Tool uses while uploading data. These import settings help Zoho Reports 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 your local database. 

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.

ParameterPossible ValuesDescription
ZOHO_CREATE_TABLE
  • True
  • False

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

LINES_TO_SEND 

Optional 
Upload Tool splits the CSV file into batches for uploading. This parameter specifies the number of row for each batch.
Default value is 5000.

ZOHO_THOUSAND_SEPARATOR
  • 0
  • 1
  • 2
  • 3

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

ZOHO_DECIMAL_SEPARATOR
  • 0
  • 1

Optional 
This parameter specifies the decimal separator for the numeric data.

The following is the list of parameter values and the corresponding decimal separator.

  • 0 - DOT
  • 1 - COMMA

Default value is 0.

SET_EMPTY_FORNULL   
  • True
  • False
This parameter is used to specify whether empty value or null should be added in Zoho Reports table when the uploaded database contains null value.
  • True -The null values in the local database server will be added as empty values in Zoho Reports table.
  • False - the null values in the local database server will be added as null in Zoho Reports table.

Default value is false.
ZOHO_CANOVERRIDE
  • True
  • False

Optional 

In case another import is triggered when this import is in process you can use this parameter to specify whether the new import can override this import. 

  • True - The tool will stop the current import process and overwrite the table with new import.
  • False - The tool will not disturb the current import process and will cancel the new import request.

Default value is false.

ZOHO_ON_IMPORT_ERROR
  • ABORT
  • SKIPROW
  • SETCOLUMNEMPTY

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

Executing the Upload Tool

Once you have configured the parameters related to the SQL SELECT Queries and Import setting, you can run the UploadFromDB.bat / UploadFromDB.sh file to perform the upload. To execute the tool: 

  • Run the UploadFromDB.bat / UploadFromDB.sh  file under <Tools_Home>bin directory along with the required parameters. You have to provide Zoho Reports User Name and Password in the order shown below. 
 UploadFromDB.bat <zohoreports_username> <zohoreports_password>"

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 rolled back.
  • If you have registered in Zoho Reports using other login such as Google Apps, Gmail, Facebook & Yahoo, then you cannot use these credentials to upload data using Upload Tool. In this case you need to generate a Zoho Username and Password to upload data. To know how to generate Zoho User credential, click here.

The details of the last upload executed will be logged in the file <Tool_Home>/logs/dbuploadserverout.txt

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 / Synchronization

Using Upload Tool you can periodically schedule uploading data from a CSV file. With this mechanism, you can have the latest data from your application synced into Zoho Reports, and the reports that you create over this data stay current. 

You can setup a periodic schedule for both the CSV Console mode utility as well as the Database Upload 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 upload tool command line batch file UploadFromDB.bat in the Program/Script field. Ensure that you have provided the necessary settings in the Upload tool configuration files. 
  • In the Add Argument field enter the following command line arguments.
UploadFromDB.bat <zohoreports_username> <zohoreports_password> 

Example

 UploadFromDB.bat  eduardo password

  • Specify the time of schedule in the Start in field.
  • Click OK to save the task

Setting up Schedule in Linux or Mac 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 Upload tool 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 Zoho Reports by setting the schedule interval accordingly. Also ensure that you have provided the appropriate SQL Select query to pull the data. 
  • In case you have deleted few records in the tables in your local database being uploaded, the only option to remove these records from Zoho Reports database is to set the Import Typeas TRUNCATEADD. This will delete all the records in the corresponding  table in  Zoho Reports and then add the data newly fetched records from the local database into Zoho Reports.  
  • In case you have modified few records in the tables in your local database being uploaded, then to get this modified in Zoho Reports database set the Import Type as UPDATEADD and specify the matching columns.  The Upload Tool will compare the records in the corresponding table in Zoho Reports with the data being uploaded from your local database table based on the matching columns. If the record already exists in the Zoho Reports table then it will be replaced with the new values available from the local database. If not, it will be added as new records in Zoho Reports.

Viewing the Data Online

  • To view the data that you have uploaded,
  • Login to http://reports.zoho.com
  • 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.  

FAQ and Troubleshooting Tips

Frequently Asked Questions 

1. Can I upload Excel files using Upload Tool

2. Can I use my Yahoo / Gmail / Google Apps / Facebook user credentials to upload data using Upload Tool?

3. How to increase / decrease the batch size for upload?

4. Can I allow the shared user to upload the data into my table?

5. Can I use my Windows authentication to connect to SQL Server database and fetch records to upload into Zoho Reports?

6. Can Upload tool be used to upload data from remotely hosted databases (SQL Server, Oracle, MySQL, Sybase, PostgreSQL, DB2 and DB2-AS/400) into Zoho Reports?

7. How to connect to a database in a particular SQL Server Instance and upload the data into Zoho Reports Online Reporting Database?

8. Is it possible to fetch data from multiple database using Upload Tool?

9. Is it mandatory to follow similar column names in Zoho Reports table as available in my local database?

10. How to upload data with different date format from the in-house/local databases into Zoho Reports?

Troubleshooting Tips

Problem 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?

Problem 2: I get an error message as ‘out of memory’ while trying to upload the file. How can I solve this error?

Problem 3: I get "Error!!! Sorry, you cannot upload files that exceed 50MB in size" while uploading data using Upload Tool. How to overcome this?

Problem 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?

Problem 5: I get a message as "ZOHO_MATCHING_COLUMNS is not present in the request parameters list". How to solve it?

Problem 6: I get "Error!!! Column "Column_Name" is present in match columns but not in selected columns" while uploading data. How to overcome this?

Problem 7: I get an error message as "Maximum Concurrent User Tickets Limit Exceeded". How to solve this?

Problem 8: I get "Could not Find File '(unknown)' error while trying to import data from MSACCESS 2007 to Zoho Reports. How to overcome this?

Problem 9: While uploading data from MySQL database, I got the error "Value '0000-00-00' cannot be represented as java.sql.Date". How can I overcome this error?