Export Data
The data present in a table can be deleted using this API.
REQUEST URI
https://<ZohoAnalytics_Server_URI>/api/<OwnerEmail>/<WorkspaceName>/<ViewName>
Post
oauthscope: ZohoAnalytics.data.read
COMMON PARAMETERS
| Parameter | Possible Values | Description |
|---|---|---|
| ZOHO_ACTION | EXPORT | This parameter specifies the action to be performed by the API request.Note: Value of ZOHO_ACTION parameter should be in the same case(UPPER CASE) as given in this document. |
| ZOHO_OUTPUT_FORMAT | CSV / JSON / XML / PDF / HTML / IMAGE | This parameter specifies the output format for the response. |
| ZOHO_ERROR_FORMAT | XML/JSON | Specifies the output format for the response in case an error occurs when trying to process the request. |
| ZOHO_API_VERSION | 1.0 | The API version of Zoho Analytics based on which the application(/service) has been written. This parameter allows the Zoho Analytics to handle applications based on the older versions.The current API version is 1.0. |
AUTHORIZATION
To make authenticated API request, append the access token in Authorization request header.
| Header Name | Value | Description |
|---|---|---|
| Authorization | Zoho-oauthtoken<space><access_token> | The Access token provides a secure and temporary access to Zoho Analytics API's. Each access token will be valid only for an hour, and can be used only for the set of operations that is described in the scope. |
ACTION SPECIFIC PARAMETERS
| Parameter | Possible Values | Description |
|---|---|---|
| ZOHO_CRITERIA (optional) | Criteria | If that parameter is not sent, then all the rows are exported. If criteria is sent the rows matching the criteria alone are exported.For more details about the format for the criteria refer this link. |
| ZOHO_SQLQUERY (optional) | SQL Query | Literal SQL Query can be used as criteria. Note:Shared users are not allowed to use this parameter. |
| GENERATETOC (optional - only for dashboards) | true / false | By default it will be false. True - To generate Table Of Contents. |
| ZOHO_DASHBOARD_LAYOUT (optional - only for dashboards) | 0 or 1 | 0 - For Each Report in New Page 1 - For Layout as in Dashboard |
ADDITIONAL OPTIONAL PARAMETERS.
All the parameters that all defined below are optional one.
CSV FORMAT
| Parameter | Possible Values | Description |
|---|---|---|
| ZOHO_DELIMITER | Value between 0 - 3 0 - COMMA 1 - TAB 2 - SEMICOLON 3 - SPACE | The delimiter character used for separating the fields in a row in the CSV. |
| ZOHO_RECORD_DELIMITER | Value between 0 - 2 0 - DOS 1 - UNIX 2 - MAC | The record delimiter (newline character) to use. |
| ZOHO_QUOTED | Value between 0 - 1 0 - SINGLE 1 - DOUBLE | The quote character to use for quoting the values. |
| ZOHO_INCLUDE_HEADER | true / false | true - To include the column names in the first row of the CSV exported. false - To not include the column names in the CSV exported. |
| ZOHO_SHOW_HIDDENCOLS | true / false | Controls where the columns that have been hidden in the table/report have to be exported. true - To include the hidden columns of the table/report in the data exported false - To not include the hidden columns of the table/report in the data exported. |
XML FormatPOSSIBLE ERROR CODES
| Parameter | Possible Values | Description |
|---|---|---|
| ZOHO_SHOW_HIDDENCOLS | true / false | Controls where the columns that have been hidden in the table/report have to be exported. true - To include the hidden columns of the table/report in the data exported false - To not include the hidden columns of the table/report in the data exported. |
HTML Format
| Parameter | Possible Values | Description |
|---|---|---|
| ZOHO_SHOW_HIDDENCOLS | true / false | Controls where the columns that have been hidden in the table/report have to be exported. true - To include the hidden columns of the table/report in the data exported false - To not include the hidden columns of the table/report in the data exported. |
PDF Format
| Parameter | Possible Values | Description |
|---|---|---|
| ZOHO_PAPERSIZE | Value between 0 - 5 0 - LETTER 1 - LEGAL 2 - TABLOID 3 - A3 4 - A4 5 - AUTO | The size of the paper. |
| ZOHO_SHOW_TITLE | Value between 0 - 2 0 - AT TOP 1 - AT BOTTOM 2 - NONE | Controls the title positioning. |
| ZOHO_SHOW_DESC | Value between 0 - 2 0 - AT TOP 1 - AT BOTTOM 2 - NONE | Controls the description positioning. |
| ZOHO_EXPORT_LANGUAGE | Value between 0 - 4 0 - ENGLISH 1 - CHINESE 2 - JAPANESE 3 - EUROPEAN 4 - KOREAN | PDF will be rendered using the specified language Default - 0 (ENGLISH) |
| ZOHO_PAPERSTYLE | Portrait / Landscape | |
| ZOHO_SHOW_HIDDENCOLS | true / false | Controls where the columns that have been hidden in the table/report have to be exported.true - To include the hidden columns of the table/report in the data exportedfalse - To not include the hidden columns of the table/report in the data exported. |
| ZOHO_SELECTED_COLUMNS | List of comma separated column names | Controls the column names that need to be exported. If it is not given then all the columns, in the table/report, are exported. |
| Margin Settings: | ||
| ZOHO_TOPMARGIN ZOHO_BOTTOMMARGIN ZOHO_LEFTMARGIN ZOHO_RIGHTMARGIN | Decimal values between 0 to 1 | The margin in inches for that edge. Can be decimal between 0 to 1 (like 0.5). |
| Header/Footer Settings: | ||
| ZOHO_HEAD_LEFT ZOHO_HEAD_RIGHT ZOHO_HEAD_CENTER ZOHO_FOOT_LEFT ZOHO_FOOT_RIGHT ZOHO_FOOT_CENTER | Value between 0 - 5 0 - Leave it blank 1 - Include Title 2 - Current Date/Time 3 - Include Page number in the format “Page #” 4 - Include page number in the format “Page # Of #” 5 - CUSTOM - Include custom text in footer | The header or footer value that needs to be generated for each page at that particular position. |
| Custom Header/Footer value | ||
| ZOHO_HEAD_LEFT_TEXT ZOHO_HEAD_RIGHT_TEXT ZOHO_HEAD_CENTER_TEXT ZOHO_FOOT_LEFT_TEXT ZOHO_FOOT_RIGHT_TEXT ZOHO_FOOT_CENTER_TEXT | Custom text. | If any of the header/footer setting is 5 (.ie, CUSTOM) then the corresponding custom value/text should be passed. |
IMAGE Format
| Parameter | Possible Values | Description |
|---|---|---|
| ZOHO_WIDTH | <number> | The width of the image . |
| ZOHO_HEIGHT | <number> | The height of the image |
| ZOHO_TITLE | true / false | Controls whether the title of the report is to be added to the image. true - Include the title. false - Do not include title. |
| ZOHO_DESCRIPTION | true/false | Controls whether the description of the report is to be added to the image. true - Include the description. false - Do not include description. |
| ZOHO_LEGEND | true / false | Controls whether the legend is to be included in the image generated. true - Include the legend in the image. false - Do not include the legend in the image. |
| ZOHO_IMAGE_FORMAT | png / jpg | The format of the exported image. It could be either in PNG or JPG formats. |
JSON Format
| Parameter | Possible Values | Description |
|---|---|---|
| ZOHO_VALID_JSON | true / false | By default it will be false. True - Returns a valid JSON data (with JSON escaping) False - Returns a JSON data with JS escaping. |
| ZOHO_SHOW_HIDDENCOLS | true / false | Controls where the columns that have been hidden in the table/report have to be exported. true - To include the hidden columns of the table/report in the data exported false - To not include the hidden columns of the table/report in the data exported. |
| ZOHO_CALLBACK_FUNCTION | Name of the json callback function | Processes JSON response elsewhere in the JavaScript code on the page |
| KEY_VALUE_FORMAT | true / false | By default it will be false. True - Returns JSON data as ColumnName - Value pair. { "data":[ {"Rank":"1","Country":"United States","Gold":"46"}, {"Rank":"2","Country":"Great Britain","Gold":"27"}, {"Rank":"3","Country":"China","Gold":"26"} ]}(This JSONArray alone can directly feed to the Import API). False - Returns JSON data in traditional way. { "response":{ "uri":"\/api\/email\/WorkspaceName\/ViewName", "action":"EXPORT", "result":{ "column_order":["Rank","Country","Gold"], "rows":[ ["1","United States","46"], ["2","Great Britain","27"], ["3","China","26"] ] } }} |
Export Using SQL:
Zoho Analytics has implemented the Zoho CloudSQL technology as an extension to its HTTP Web API. Using the
Refer to the documentation on Zoho Analytics CloudSQL for more details on how to use SQL SELECT query to fetch data from Zoho Analytics.
Sample Request:
Copiedcurl -d "ZOHO_ACTION=EXPORT&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML
&ZOHO_API_VERSION=1.0"
-H "Authorization:Zoho-oauthtoken <access_token>"
https://analyticsapi.zoho.com/api/EmailAddress/WorkspaceName/ViewNameCopiedusing ZReports;
namespace Test
{
class Program
{
public IReportClient GetClient()
{
IReportClient RepClient = new ReportClient(CLIENT_ID, CLIENT_SECRET, REFRESH_TOKEN);
return RepClient;
}
public void Export(IReportClient RepClient)
{
string tableURI = RepClient.GetURI(EMAIL, DBNAME, TBNAME);
Dictionary<string, object> resObj = RepClient.ExportDataAsDictionary(tableURI, "\"Region\"='West'", null);
Object[] columns = (Object[])resObj["column_order"];
Object[] rows = (Object[])resObj["rows"];
}
static void Main(string[] args)
{
Program obj = new Program();
IReportClient rc = obj.GetClient();
obj.Export(rc);
}
}
}
Copiedpackage main
import (
"fmt"
"zoho/pkg/reportclient"
)
var (
email = "Email Address"
dbname = "Workspace Name"
tbname = "Table Name"
clientid = "************"
clientsecret = "************"
refreshtoken = "************"
)
func exportdata() {
url := reportclient.GetUri(email, dbname, tbname)
outputformat := "pdf"
filename := "sample"
params := map[string]string{}
err := reportclient.ExportData(url, filename, outputformat, params)
if err != nil {
fmt.Println(err.ErrorMessage)
fmt.Println(err.ErrorCode)
fmt.Println(err.Action)
fmt.Println(err.HttpStatusCode)
} else {
fmt.Println("Success")
}
}
func main() {
reportclient.SetOAuthToken(clientid, clientsecret, refreshtoken)
exportdata()
}Copiedimport com.adventnet.zoho.client.report.*;
public class Sample {
String email = "Email Address";
String dbname = "Workspace Name";
String tbname = "Table Name";
String clientId = "************";
String clientSecret = "************";
String refreshToken = "************";
File csvFile = new File("samples/StoreSales.csv");
private ReportClient rc = new ReportClient(clientId, clientSecret, refreshToken);
public void exportdata() throws Exception {
String uri = rc.getURI(email, dbname, tbname);
rc.exportData(uri, "CSV", csvFile, null, null);
}
public static void main(String[] args) throws Exception {
Sample obj = new Sample();
obj.exportdata();
}
}Copied<?php
require 'ReportClient.php';
$EMAIL_ID = "Email Address";
$DB_NAME = "Workspace Name";
$TABLE_NAME = "Table Name";
$CLIENT_ID = "************";
$CLIENT_SECRET = "************";
$REFRESH_TOKEN = "************";
$report_client_request = new ReportClient($CLIENT_ID, $CLIENT_SECRET, $REFRESH_TOKEN);
$uri = $report_client_request->getURI($EMAIL_ID, $DB_NAME, $TABLE_NAME);
$output_format = "CSV";
$report_client_response = $report_client_request->exportData($uri, $output_format);
$file = "dummy/" . $TABLE_NAME . "." . $output_format;
file_put_contents($file, $report_client_response, FILE_APPEND);
?>
Copiedfrom __future__ import with_statement
from ReportClient import ReportClient
import sys
class Sample:
LOGINEMAILID = "Email Address"
CLIENTID = "************"
CLIENTSECRET = "************"
REFRESHTOKEN = "************"
DATABASENAME = "Employee"
TABLENAME = "Employee"
rc = None
rc = ReportClient(REFRESHTOKEN, CLIENTID, CLIENTSECRET)
def exportdata(self, rc):
uri = rc.getURI(self.LOGINEMAILID, self.DATABASENAME, self.TABLENAME)
fileobj = open("/home/sample.csv", "rw+")
rc.exportData(uri, "CSV", fileobj)
fileobj.close()
obj = Sample()
obj.exportdata(obj.rc)
Copiedvar nodelib = require('./ZAnalyticsClient');
const fs = require('fs');
var clientId = '************';
var clientSecret = '************';
var refreshtoken = '************';
var emailId = 'EmailAddress';
var workspaceName = 'WorkspaceName';
var viewName = 'ViewName';
nodelib.initialize(clientId, clientSecret, refreshtoken).then(() => {
var params = {};
var uripath = nodelib.getUri(emailId, workspaceName, viewName);
var outputFormat = 'csv';
nodelib.exportData(outputFormat, uripath, params).then((response) => {
fs.writeFileSync(viewName + '.' + outputFormat, response);
}).catch((error) => {
console.log('Error: ' + error.message);
});
}).catch((error) => {
console.log('Authentication Error: ' + error);
});
Copiedemail = zoho.encryption.urlEncode("");
workspaceName = zoho.encryption.urlEncode("");
viewName = zoho.encryption.urlEncode("");
paramsMap = Map();
oauthParams = Map();
headers = Map();
// AUTHENTICATION PARAMS
oauthParams.put("client_id", "********");
oauthParams.put("client_secret", "********");
oauthParams.put("refresh_token", "********");
oauthParams.put("grant_type", "refresh_token");
tokenInfo = invokeurl[url: "https://accounts.zoho.com/oauth/v2/token" type: POST parameters: oauthParams];
if (tokenInfo.containKey("access_token")) {
accessToken = tokenInfo.get("access_token");
headers.put("Authorization", "Zoho-oauthtoken ".concat(accessToken));
} else {
info tokenInfo;
return;
}
// COMMON PARAMS
paramsMap.put("ZOHO_ACTION", "EXPORT");
paramsMap.put("ZOHO_OUTPUT_FORMAT", "JSON");
paramsMap.put("ZOHO_ERROR_FORMAT", "JSON");
paramsMap.put("ZOHO_API_VERSION", "1.0");
response = invokeurl[url: "https://analyticsapi.zoho.com/api/" + email + "/" + workspaceName + "/" + viewName type: POST parameters: paramsMap headers: headers];
info response;
Sample Response:
Copied{"response":
{"uri": "/api/EmailAddress/WorkspaceName/TableName",
"action": "EXPORT",
"result":
{"column_order":["Name","Department","Date Of Birth"],
"rows":
[["John","Finance","12 May 1972"],
["Joan","Admin","15 June 1975"]]}}}Copied<?xml version="1.0"encoding="UTF-8" ?>
<responseuri="/api/EmailAddress/WorkspaceName/TableName"action="EXPORT">
<result>
<rows>
<row>
<columnname="Name">Gary</column>
<columnname="Date Of Birth">12-Jun-1980</column>
<columnname="Basic">10000</column>
<columnname="Country">USA</column>
</row>
<row>
<columnname="Name">John</column>
<columnname="Date Of Birth">12-Jun-1981</column>
<columnname="Basic">10000</column>
<columnname="Country">Canada</column>
</row>
<row>
<columnname="Name">Joan</column>
<columnname="Date Of Birth">12-Jun-1982</column>
<columnname="Basic">10000</column>
<columnname="Country">Mexico</column>
</row>
</rows>
</result>
</response>