Export

Tags:  

Export

Using this API users can export/pull data from tables or reports (pivots, charts etc.,) in different formats.

Sample

Exports the records of all the employees in the finance department in CSV Format.

URL
http://reports.zoho.com/api/demouser/EmployeeDB/EmployeeDetails?ZOHO_ACTION=EXPORT
&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML
&ZOHO_API_KEY=hewfdrbgs&ticket=gsssds&ZOHO_API_VERSION=1.0

Additional Control Parameters Sent as POST parameters.

&ZOHO_CRITERIA=(%22Department%22%3D'Finance')&ZOHO_DELIMITER=0

Note: ZOHO_CRITERIA is encoded for the url format. Value without encoding is below:

ZOHO_CRITERIA=("Department" = 'Finance')


Query String Parameters

In the query string of the URL include the following parameters

  • ZOHO_ACTION parameter should be "EXPORT".

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

  • ZOHO_OUTPUT_FORMAT should be the required format. The supported formats are
    • CSV
    • XML
    • JSON
    • HTML
    • PDF
    • IMAGE (applicable only for Charts)

For explanation about other mandatory query string parameters such as ZOHO_ERROR_FORMAT, refer to this link.

Criteria

If the ZOHO_CRITERIA 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.

Response Formats

CSV Format

To export in CSV format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be CSV

Additional parameters (preferably to be sent in the body of the POST request).

Parameter
Possible Values
Description
ZOHO_OUTPUT_FORMAT CSV
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.


Sample Success Response:

The following is a sample Export of data in a Table, in CSV format:

"Name","Department","Date Of Birth"
"John","Finance","12 May 1972"
"Joan","Admin","15 June 1975"


JSON Format

To export in JSON format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be JSON

Additional parameters (preferably to be sent in the body of the POST request).

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.

ZOHO_CALLBACK_FUNCTION Name of the json callback function Processes JSON response elsewhere in the JavaScript code on the page

Sample Success Response:

The following is a sample Export of data in a Table, in JSON format:

{

"response":
{
"uri": "/api/demouser/EmployeeDB/EmployeeDetails",
"action": "EXPORT",
"result":
{
column_order:["Name","Department","Date Of Birth"],
rows:[
["John","Finance","12 May 1972"],
["Joan","Admin","15 June 1975"]
]
}
}
}


XML Format

To export in XML format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be XML

Additional parameters (preferably to be sent in the body of the POST request).

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.

Sample Success Response:

The following is a sample Export of data in a Table, in XML format

<?xml version="1.0" encoding="UTF-8" ?>
<response uri="/api/demouser/EmployeeDB/EmployeeDetails" action="EXPORT">
<result>
<rows>
<row>
<column name="Name">Gary</column>
<column name="Date Of Birth">12-Jun-1980</column>
<column name="Basic">10000</column>
<column name="Country">USA</column>
</row>
<row>
<column name="Name">John</column>
<column name="Date Of Birth">12-Jun-1981</column>
<column name="Basic">10000</column>
<column name="Country">Canada</column>
</row>
<row>
<column name="Name">Joan</column>
<column name="Date Of Birth">12-Jun-1982</column>
<column name="Basic">10000</column>
<column name="Country">Mexico</column>
</row>
</rows>
</result>
</response>


HTML Format

To export in HTML format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be HTML

Additional parameters (preferably to be sent in the body of the POST request).

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

To export in PDF format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be PDF

Additional parameters (preferably to be sent in the body of the POST request).

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_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 exported

false - 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 Decimal values between 0 to 1 The margin in inches for that edge. Can be decimal between 0 to 1 (like 0.5).
ZOHO_BOTTOMMARGIN
ZOHO_LEFTMARGIN
ZOHO_RIGHTMARGIN
Header/Footer Settings:
ZOHO_HEAD_LEFT 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.
ZOHO_HEAD_RIGHT
ZOHO_HEAD_CENTER
ZOHO_FOOT_LEFT
ZOHO_FOOT_RIGHT
ZOHO_FOOT_CENTER
Custom Header/Footer value
ZOHO_HEAD_LEFT_TEXT Custom text. If any of the header/footer setting is 5 (.ie, CUSTOM) then the corresponding custom value/text should be passed.
ZOHO_HEAD_RIGHT_TEXT
ZOHO_HEAD_CENTER_TEXT
ZOHO_FOOT_LEFT_TEXT
ZOHO_FOOT_RIGHT_TEXT
ZOHO_FOOT_CENTER_TEXT


IMAGE Format

To export in IMAGE format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be IMAGE. You can only export Charts in Image format.

Additional parameters (preferably to be sent in the body of the POST request).

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_SHOWLEGEND 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 Callbacks - JSONP:

Zoho Reports API supports Callback function for handling API responses asynchronously. This is supported in Export API calls, when the output format is chosen as JSON.

The Export API method call, with JSON output format, supports an additional parameter called ZOHO_CALLBACK_FUNCTION to enable callback. Using the Callback function, developers can invoke the Export API request inside the <script> tag and process the JSON response using the CALLBACK function elsewhere in the JavaScript code on the page.

Example:

1) Define the callback function

 

<script>
function sampleFunction(resp)
{
var response = resp.response;
var result = response.result;
var rows = result.rows;
for(i=0;i<rows.length;i++)
{
var row = rows[i];
console.log(row);
...
}
...
}
</script>

 

2) Invoke the api request with output format as JSON and ZOHO_CALLBACK_FUNCTION=sampleFunction

 

<script src="http://reports.zoho.com/api/<username>/<DB>/<Table>?ZOHO_ACTION=EXPORT&ZOHO_OUTPUT_FORMAT=JSON&ZOHO_ERROR_FORMAT=XML&ZOHO_API_KEY=<key>&ticket=<ticket>&ZOHO_API_VERSION=1.0&ZOHO_CALLBACK_FUNCTION=sampleFunction">
</script>

 

When the page is loaded, the response will be returned as,

 

sampleFunction(
{
"response":
{
"uri": "/api/demouser/EmployeeDB/EmployeeDetails",
"action": "EXPORT",
"result":
{
column_order:["Name","Department","Date Of Birth"],
rows:[
["John","Finance","12 May 1972"],
["Joan","Admin","15 June 1975"]
]
}
}
} );

 

Export Using SQL:

Zoho Reports has implemented the Zoho CloudSQL technology as an extension to its HTTP Web API. Using the Zoho Reports HTTP API, users can query the database by providing SQL queries.

Refer to the documentation on Zoho Reports CloudSQL for more details on how to use SQL SELECT query to fetch data from Zoho Reports.



0 Comments  Show recent to old
Post a comment

 RSS of this page

rtttr   rb