Home  »  Supported Actions »  Importing Bulk Data

Importing Bulk Data

Tags:  

Importing Bulk Data




1. It is mandatory to use HTTPS in all API requests instead of HTTP request. HTTP is not supported

2. Always use https://reportsapi.zoho.com as the API request URL.


With the Zoho Reports API, you can add/update data in bulk. The data to be added/updated should be in CSV or JSON file formats.

Note:

  • The data should be sent via a https POST request
  • The parameters and the file should be encoded in multi-part/form-data format.(The format used by html forms that contain file type fields used for uploading files)

Importing Data

To import data you need to send an HTTPS POST request using the following URL format.

https://reportsapi.zoho.com/api/<zoho-login_email_address>/<database_name>/<table_name>?ZOHO_ACTION=IMPORT&authtoken=<your_auth_token>&ZOHO_OUTPUT_FORMAT=<XML/JSON>&ZOHO_ERROR_FORMAT=<XML/JSON>&ZOHO_API_VERSION=1.0

Data Sent as POST parameters.

The additional control parameters and the file should be encoded in multi-part/form-data format (The format used by html forms that contain file type fields used for uploading files).
Samples
The following code snippet illustrates importing data from a CSV file into Zoho Reports with POST parameters along with additional control parameters submitted from the HTML form. <html>
<html>
<body>
<form name="ZohoDBImportForm" ENCTYPE="multipart/form-data"method="post"action="https://reportsapi.zoho.com/api/abc@zoho.com/EmployeeDB/EmployeeDetails?
ZOHO_ACTION=IMPORT&ZOHO_API_VERSION=1.0&authtoken=g38sl4j4856guvncrywox8251sssds&ZOHO_OUTPUT_FORMAT=XML">
<input type="file" name="ZOHO_FILE" value="Browse"><br>
ZOHO IMPORT TYPE : <input type="text"name="ZOHO_IMPORT_TYPE" value="APPEND"><br>
ZOHO AUTO IDENTIFY : <input type="text" name="ZOHO_AUTO_IDENTIFY" value="true"><br>
ZOHO CREATE_TABLE : <input type="text" name="ZOHO_CREATE_TABLE" value="false" ><br>
ON IMPORT ERROR : <input type="text" name="ZOHO_ON_IMPORT_ERROR" value="ABORT"><br>
<input type="submit" name="submit" value="Upload">
</body>
</html>

Note: The CSV should contain the column names in the first row.

The following code snippet illustrates importing data from a JSON file into Zoho Reports with POST parameters along with additional control parameters submitted from the HTML form.
<html>
<body>
<form name="ZohoDBImportForm" ENCTYPE="multipart/form-data"method="post"action="https://reportsapi.zoho.com/api/abc@zoho.com/EmployeeDB/EmployeeDetails?
ZOHO_ACTION=IMPORT&authtoken=g38sl4j4856guvncrywox8251sssds&ZOHO_OUTPUT_FORMAT=JSON&ZOHO_API_VERSION=1.0">
<input type="file" name="ZOHO_FILE" value="Browse"><br>
ZOHO IMPORT FILE TYPE : <input type="text" name="ZOHO_IMPORT_FILETYPE" value="JSON"><br>
ZOHO IMPORT TYPE : <input type="text" name= "ZOHO_IMPORT_TYPE" value="APPEND"><br>
ZOHO AUTO IDENTIFY : <input type="text" name="ZOHO_AUTO_IDENTIFY" value="true"><br>
ZOHO CREATE TABLE : <input type="text" name="ZOHO_CREATE_TABLE" value="false" ><br>
ON IMPORT ERROR : <input type="text" name="ZOHO_ON_IMPORT_ERROR" value="ABORT"><br>
<input type="submit" name="submit" value="Upload">
</body>
</html>


Note:

  • You can import any type of JSON array with single or multiple objects.
  • In case you import file with multiple JSON objects separated by comma (not enclosed with square brackets), then the first object alone will be imported.
  • JSON object containing simple values without keys are not supported.
  • Refer to the supported JSON formats section, for more details on the JSON format that can be imported into Zoho Reports.


 Note


It is recommended to use Zoho Reports Login Email Address in the API URL instead of Zoho Username.

Specifying the action

In the query string of the url , the ZOHO_ACTION parameter should be "IMPORT". For other mandatory query string parameters such as ZOHO_OUTPUT_FORMAT, refer this link.

Note: Value of ZOHO_ACTION parameter should be in the same case(UPPER CASE) as given in this document.

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_FILE*

The file to import


ZOHO_IMPORT_FILETYPE

    CSV/JSON
Optional. Default value is CSV.
Format of the file to be imported. Supported formats are:
  • CSV
  • JSON

ZOHO_CREATE_TABLE

true/false.

Optional. Default is false.

In case it is true, then the table is created if the table referred in the URL doesn't exist in the specified database.

In case its false, no table is created even if the table refered in the URL does not exist in the database.

ZOHO_SELECTED_COLUMNS

List of comma separated column names.

E.g.,: Name, Department
Optional.

Specify the columns to be imported into the Zoho Reports table from the data being uploaded.
Note: Incase of JSON files you need to specify the column names capturing the full JSON tree heirrachy eg., employee.Name, employee.Department

ZOHO_IMPORT_TYPE*

  • APPEND
  • TRUNCATEADD
  • UPDATEADD

APPEND - Appends the data into the table.

TRUNCATEADD - Deletes all exisiting rows in the table and adds the imported data as 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.,: 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 comparision 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_IMPORT_JSON_RETCOLNAMES

true/false.

Optional. Default value is false.

This parameter is applicable only for importing JSON files. This defines how the columns names are to be constructed from the JSON file.

  • If set to true, then the final key attribute alone will be considered as column name.
  • If set to false, then the column name will be constructed by appending all the parent attributes separated by dot (.). This will result in column names which captures the full JSON tree hierarchy eg., employee.Name, employee.Department

ZOHO_DATE_FORMAT

Format of the date.

E.g., dd-MMM-YYYY

Optional

The format of date value. Specify this incase any date field is being imported and its format cannot be auto recognized by Zoho Reports.

ZOHO_AUTO_IDENTIFY*

true/false

Used to specify whether to auto identify the CSV format.

ZOHO_SKIPTOP

<number>

Optional

Number of rows that are to be skipped from the top in the CSV file being imported.

ZOHO_THOUSAND_SEPARATOR 0/1/2/3 Optional. Default is 0.
This parameter controls the action to be taken in case there is a thousand separator in the data.
0 - COMMA
1 - DOT
2 - SPACE
3 - SINGLE QUOTE
ZOHO_DECIMAL_SEPARATOR 0/1 Optional. Default is 0.
This parameter controls the action to be taken in case there is a decimal separator in the data.
0 - DOT
1 - COMMA

ZOHO_ON_IMPORT_ERROR*

  • ABORT
  • SKIPROW
  • SETCOLUMNEMPTY

This parameter controls the action to be taken incase there is an error during import.

ABORT - Incase of any error, abort the whole import.

SKIPROW - In case of any error, skip that specific row(s) which has the problem and continue importing the rest.

SETCOLUMNEMPTY - In case of any error, set the value of the errored column for the row to empty and continue importing.




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.

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.

0 - None

1 - SINGLE QUOTE

2 - DOUBLE QUOTE

JSON Format Details

The following parameters need to be specified while importing data from JSON file.

Sample Success Response

JSON

The following is a sample response in JSON format in case of successful import.

{
" response":
{"uri": "/api/abc@zoho.com/EmployeeDB/EmployeeDetails",
"action": "IMPORT",
"result":
{
"importSummary":
{
"totalColumnCount":3,
"selectedColumnCount":3,
"totalRowCount":50,
"successRowCount":48,
"warnings":0,
"importOperation": "created",
"importType": "APPEND"
},
"columnDetails":
{
"Name": "Plain Text",
"Date Of Birth": "Date",
"Salary": "Number"
},
"importErrors": "[Line: 5 Field: 3] a1213 -WARNING: Invalid Number value"
}
}
}

XML

The following is a sample response in XML format in case of successful import.

<?xml version="1.0" encoding="UTF-8" ?>

<response uri="/api/abc@zoho.com/EmployeeDB/EmployeeDetails" action="IMPORT">
<result>
<importSummary>
<totalColumnCount>3 </totalColumnCount>
<selectedColumnCount>3</selectedColumnCount>
<totalRowCount>50</totalRowCount>
<successRowCount>48</successRowCount>
<warnings>0</warnings>
<importOperation>created</importOperation><importType>APPEND</importType>
</importSummary>

<columnDetails>
<column datatype="Plain Text">Name </column>
<column datatype="Date">Date Of Birth</column>
<column datatype="Number">Salary</column>
</columnDetails>


<!-- The first 100 errors are alone sent -->
<importErrors>
[Line: 5 Field: 3] a1213 -WARNING: Invalid Number value
</importErrors>


</result>
</response>

Karl (Guest) 1864 - days ago 
Is it possible to build a CSV string in VB.Net and bulk upload that way via POST methods? Saves an windows application builder creating a separate process of saving a file to the computer.
ruydiaz 1283 - days ago 
The keys in the response.result object are not quoted. This is invalid JSON, since according to spec, keys should be surrounded in double quotes. This is causing Ruby's JSON gem to fail when trying to parse the response, throwing a JSON::ParserError - unexpected token.

Would it be possible to get these keys properly quoted? Otherwise I have to do some regex string replacements to fix this.
Manohar Nixon 1282 - days ago 
Hi ruydiaz,

We acknowledge that the property name in the JSON Response we sent is not properly quoted. We will fix this in the very near future. Thank you very much for pointing out this issue.

As of now, kindly parse the JSON response using javascript eval function or JSONObject class (which does not mandate a quoted response). We will let you know once we have fixed this in the service. Kindly bear with us till then.

Best Regards,
Manohar Nixon S
Zoho Reports
Curt R. Germundson 1256 - days ago 
The requirement to use file input makes it impossible to do any kind of automation with this web method. The "file" input type requires the user to browse or manually enter the filename in the browser before it is submitted. Another vote for a way to send the data as a text string.
senthilvel.n 1253 - days ago 
Hi ruydiaz,

We have fixed the JSON response issue(double quotes for the key) which was posted above and updated our service with the fix. Kindly try the same and let us know your feedbacks if any.

Regards,
Senthilvel N
Zoho Reports
schenz 1019 - days ago 
I am creating table on upload, but the wrong data type is being used. The first set of records have a numberic value in a vield, so that data type is being selected, but later in csv file I have alpha characters in that field, and I get errors on importing. How do I tell it that the column should be created as text?
prameena.s 923 - days ago 
Sorry for the inconvenience caused. There is no option to specify the datatype when importing via Zoho Reports API / Upload tool. But you can overcome this scenario by importing into Zoho Reports using the User Interface once. In our User Interface, we have an option to choose the datatype for each column in your file during the import process. Kindly follow the below steps to do this.

1. Login into Zoho Reports, Open the import wizard ( "Import Excel, CSV, HTML, Google docs,.." ), upload your file.
2. Click "Next" button to go to the next screen of import wizard (i.e step 2 of 2), there you can see the preview table.
3. In that table, the first row will be header row ( i.e., Column names ) and the second row will be the datatype which is auto identified by our Zoho Reports system. There you can change the column datatype to "Text" for the column you want to change. Then, continue the import process. To know more about the import process, kindly go through https://reports.wiki.zoho.com/Import-Data.html

Once you have created your table from Zoho Reports UI with proper setup for the first time, further uploads using our API will be imported properly as you required.
santilh 708 - days ago 
Hi, trying to submit a CSV via Google apps script. Can you respond to this question in the forum? https://forums.zoho.com/topic/upload-csv
Pedro 661 - days ago 
Trying to use the python ReporClient bindings to post a 5Mb file and get an error: The file content is not "multipart/form-data" format.
This error does not happen when posting a small 100K file.

How should I upload a file of this size?
Neil Giarratana 7 - days ago 
The standard JSON format coming out of a rails to_json method is 2014-01-04T12:15:00.000Z but that doesn't seem to be recognized by the Zoho engine. Does the import api method take standard JSON dates? Is there a specific ZOHO_DATE_FORMAT that should work for this?
Neil Giarratana 7 - days ago 
Figured out how to work around it in Ruby as detailed in the readme here: https://github.com/neilsmind/zoho_reports

Essentially, I needed to temporarily turn off standard json dates as follows:
ActiveSupport::JSON::Encoding.use_standard_json_time_format = false

then run .to_json and then set the value back to true
janani.t 6 - days ago 
Hello Neil Giarratana,

We are glad to know that you were able to resolve this and import your data successfully. Please do let us know when your were in need of any help regarding reporting solutions.

Sincerely,
Janani | Zoho Cares
Neil Giarratana 3 - days ago 
So - it turns out that my earlier assertion that we can workaround the problem using ActiveSupport is a really bad idea for a number of reasons including thread safety. However, there is REALLY good news to that (which should have already occurred to me)...when using ActiveSupport library to_json method, leave out the ZOHO_DATE_FORMAT and it imports beautifully from the ISO 8601 standard format.
Neil Giarratana 3 - days ago 
So - it turns out, I was wrong about it working...it imports the dates as strings instead of dates.

Here is my question to ZOHO Cares: What is the ZOHO_DATE_FORMAT for an ISO 8601 string? Example date coming out of my system: 2014-01-04T12:15:00.000Z
janani.t 2 - days ago 
Hello Neil Giarratana,

Please do set the ZOHO_DATE_FORMAT parameter to yyyy-MM-dd'T'HH:mm:ss.SSS'Z' format. This would identify your data as date and import will be proper. However, currently we do not support to display the millisecond part of the data and hence it will be displayed as SSS'Z'.

To overcome this, once the data is imported, please do format the column by right clicking on the column and choose 'Format Column'. In the 'Format Column' dialog, choose one of the supported date format. On further imports your data will be imported properly and displayed in the new format you have set in Zoho Reports table.

We believe this resolves your problem. Have any further requirement, please do write to us at support@zohoreports.com.

Sincerely,
Janani | Zoho Cares
Post a comment

Your Name or E-mail ID (mandatory)

Note: Your comment will be published after approval of the owner.





 RSS of this page