Uploading Data from CSV Files - Console Mode
- Uploading Data
- Configure Import Settings
- Setting up Periodic Upload
- Viewing Data Online
- FAQ and Troubleshooting Tips
Invoking Upload Tool 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 tool from another application. In this section, we will discuss how to set up this tool for batch upload of CSV data in Command line mode .
Steps for running the CSV upload tool in console mode:
- Ensure that you have already created the database in Zoho Reports account into which the data is to be uploaded. To know how to create a database, click here
- 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 <Tools_Home> bin directory along with the required parameters. You have to 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 same order as given in the following snippet.
CSVUploadConsole.bat [file_name] [database_name] [table_name] [APPEND/UPDATEADD/TRUNCATEADD] <zohoreports_username> <zohoreports_password>"
- After successful execution, the data in the CSV file will get uploaded to the specified table in Zoho Reports database.
- The last upload details will be logged in the <Tool_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 Zoho Reports into which the CSV file is to be uploaded.|
|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 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.
|Import Type||Set one of the following as Import Type.
|User Name (zohoreports_username)||Your Zoho Reports account login name
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.
|Password (zohoreports_password)||Password for accessing your Zoho Reports account.|
The configuration file common_params.conf in /conf directory provides parameters that Upload Tool uses while uploading data. These 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 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.
Default value is false.
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
The above example indicates a date in the format 10-Nov-2013. Ensure you provide the appropriate notation to match your 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 tool should automatically identify the delimiter or not.
In case this parameter is set to false, then you need to specify the delimiter and text qualifier usin the parameters in the table below.
Default is 0.
The following is the list of parameter values and the corresponding decimal separator.
Default value is 0.
Default value is UTF-8.
Set this to true, the tool 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.
Default value is false.
This parameter controls the action to be taken in case there is an error during import.
Default value is SETCOLUMNEMPTY
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:
|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:
The following screenshot illustrates the common_params.conf file showing some of the import parameters:
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 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 upload tool command line batch file CSVUploadConsole.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 command line arguments for the batch file. i.e., full path of the CSV file to be uploaded, Zoho Reports Database name, Table name, Type of Import, Zoho Reports User Name and Password the same order as given in the following snippet.
- 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 or Mac, 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 the CSV data file is located in the right place for the tool to access it.
- In case you have deleted few records in the CSV file being uploaded, the only option to remove these records from Zoho Reports 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 Zoho Reports database, set the Import Type as UPDATEADD and specify the matching columns. The Upload Tool will compare the existing table in Zoho Reports 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.
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.
Frequently Asked Questions
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 file format 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 Zoho Reports Upload Tool.
2. Can I use my Yahoo / Gmail / Google Apps / Facebook user credentials to upload data using Upload Tool?
Currently, Zoho Reports Upload Tool does not support uploading data using Yahoo / Gmail / Google Apps / Facebook accounts credentials. The Upload Tool expects Zoho Username and Password to authenticate the user. So other login users need to generate a Zoho Username and Password to upload data into Zoho Reports database.
Follow the steps below to generate a Zoho Username and Password:
- Login into Zoho Reports using your Yahoo / Gmail / Google Apps / Facebook account credentials.
- Click the drop-down menu displayed with your user name at the top right corner.
- Click Account Settings menu item, this will open Zoho Accounts Profile page (You can also directly access/open this page using direct link https://accounts.zoho.com).
- In the Home tab, click Change Password link.
- Reset the password.
Now in Zoho Reports home page, you can find the Zoho Username at the top right corner. Use the new Zoho Username and Zoho Password in the Zoho Reports Upload Tool to upload the data into your account.
- Generating Zoho password will not affect signing into your account using your Yahoo / Gmail / Google Apps / Facebook account.
To upload the data of larger size, Zoho Reports Upload Tool splits the data and uploads as batches. The user can configure the number of rows to be sent for each batch by modifying the LINES_TO_SEND parameter in the common_params.conf file. By default this will be set to 5000.
Follow the steps given below to specify the batch size:
- Open /conf/common_params.conf file.
- Set the LINES_TO_SEND parameter to the number of lines you want to upload in each batch as given below.
- Save the configuration file and start uploading the data.
- It is mandatory that the 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 to be 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 to upload data, with Import Permission.
- For more details, refer to this FAQ section.
Steps to be followed by the shared user:
- Open /conf/common_params.conf file.
- Add the DBOWNERNAME parameter and set this to Database Owner's Zoho Reports Email id or User name
- Save the configuration file.
- Start uploading the data using the shared user's Zoho Reports credentials.
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?
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.
Problem 2: I get an error message as ‘out of memory’ while trying to upload the file. How can I 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 at the end of the variable JAVA_OPTS as below:
Windows: set JAVA_OPTS=%JAVA_OPTS% -XX:NewSize=48M -Xmx1024M
Linux/Mac: export JAVA_OPTS=$JAVA_OPTS -XX:NewSize=48M -Xmx1024M
- Save the file and start the upload process.
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?
Solution: To upload the data of larger size, Zoho Reports 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.
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?
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.
Problem 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.
- It is recommended to set columns with unique values as matching column.
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?
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.
Problem 7: I get error /ZohoReports/UploadTool/bin/UploadFromDB.sh: line 15: java: command not found.
Solution : This error occurs when UploadFromDB.sh/bat file is executed from folder other than <Tool_Home>/bin/. Use the following method to execute the UploadFromDB.sh/bat file:
- Go to the tool home by executing the below command:
- where <tool_path> is the Upload Tool installed location.
- From the <Tool_Home>/bin folder, run the executable file by executing the below command:
For Linux/Mac: UploadFromDB.sh <username> <password>
For Windows: UploadFromDB.bat <username> <password>
Problem 8: I get an error message as "Maximum Concurrent User Tickets Limit Exceeded". How to solve this?
Solution: When you access the service a session will be created, which will be deactivated once you logout or after 7 days. If you did not logout or access the service from multiple locations then multiple active sessions will be created for your account. Zoho service restricts a user to have maximum of 20 active sessions. In case you have exceeded this limit, you will not be allowed the access the service. To overcome this you need to close the active sessions.
Follow the steps below to close the current active sessions.
- Login to http://accounts.zoho.com with your Zoho account credentials
- Choose the Active Session under the Home tab.
- Remove all active sessions by clicking the close all other sessions.