In Existing Table

You can also use Bulk APIs to import data into an existing specified table.

REQUEST URI

https://<hostname>:<webclient port>/restapi/v2/workspaces/<workspace-id>/views/<view-id>/data

Post

oauthscope: zrop.data.create

ACTION SPECIFIC PARAMETERS

 

Parameter Name		Description

 

FILE (or) DATA*

FILE - The file to be imported.

  • In the case of importing files multipart/form-data format should be used. (This is the default format used by html forms that contain file type fields used for uploading files)
  • Maximum allowed file size is 20 MB.


DATA - The string to be imported.

QUERY PARAMETERS

 

Parameter Name		Description
CONFIG*JSONObject
Config parameter specifications are available in the below section.

FIELDS FOR CONFIG JSON

KeyDescription
importType*

String
Controls the type of import.

  • 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.
fileType*

String
The format of the file to be imported.

  • csv
  • json
autoIdentify*Boolean
To specify whether to auto identify the CSV format.
onError

String
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 error column for the row to empty and continue importing.
matchingColumns
(mandatory only when the importType is updateadd)		JSONArray
The values in the columns to be matched will be used for comparison 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.

Sample: ["column1","column2"]
selectedColumnsJSONArray
Controls the columns that need to be imported.

Sample: ["column1","column2"]
skipTopInteger
Number of rows that are to be skipped from the top in the CSV file being imported.
thousandSeparator

Integer
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
decimalSeparator

Integer
Controls the action to be taken in case there is a decimal separator in the data.

  • 0 - DOT
  • 1 - COMMA
dateFormatString
Specify this in-case any date field is being imported and its format cannot be auto recognized by Analytics Plus.

Sample: dd-MMM-YYYY
Refer this link for more details about how to construct a custom date format.
columnDateFormatJSONObject
Specify this in case multiple date fields are being imported having different format each.
Column name as key and date format as value.

Sample: {"columnName1":"dd-MMM-YYYY","columnName2":"MM/dd/yyyy"}

CSV SPECIFIC ATTRIBUTES

Note : These attributes are mandatory if autoIdentify is set to false.

KeyDescription
commentCharChar
If the character mentioned is found at the beginning of the row, the csv row will be skipped.

Sample: #
delimiter

Integer
The delimiter character used for separating the fields in a row in the CSV.

  • 0 - COMMA
  • 1 - TAB
  • 2 - SEMICOLON
  • 3 - SPACE
quoted

Integer
The Text Qualifier.

  • 0 - NONE
  • 1 - SINGLE QUOTE
  • 2 - DOUBLE QUOTE

JSON SPECIFIC ATTRIBUTES

Note : These attributes are mandatory if autoIdentify is set to false.

KeyDescription
retainColumnNames

Boolean

Controls how the columns names are to be constructed from the JSON file.
Default value - false.

  • true - the final key attribute alone will be considered as column name.
  • false - 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

POSSIBLE ERROR CODES

7103 , 7138 , 7232 , 7248 , 7249 , 8046 , 8119 , 8504 , 8506 , 8513 , 8516

Sample Request:

CurlCsharpGoJavaPHPPythonNode JSDeluge
Copiedcurl https://<hostname>:<webclient port>/restapi/v2/workspaces/<workspace-id>/views/<view-id>/data ?CONFIG=<encoded_json_value> 
-F 'FILE=@/home/local/import.csv' 
-X 'POST' -H 'ZANALYTICS-ORGID: <org-id>' 
-H 'Authorization: Zoho-oauthtoken <access_token>'
Copiedusing System;
using System.Collections.Generic;
using zrop;
using System.Text.Json;

namespace zropTest
{
    class Program
    {
        long orgId = 55522777;
        long workspaceId = 35130000001055707;
        long viewId = 35730000007354002;

        public void ImportData(IAnalyticsClient ac)
        {
            string importType = "append";
            string fileType = "csv";
            bool autoIdentify = true;
            string filePath = "C:\\Users\\Administrator\\Downloads\\Sample.csv";
            IBulkAPI data = ac.GetBulkInstance(orgId, workspaceId);
            JsonElement importResult = data.ImportData(viewId, importType, fileType, autoIdentify, filePath, null);
            Console.WriteLine(importResult);
        }

        static void Main(string[] args)
        {
            string clientId = "1000.xxxxxxx";
            string clientSecret = "xxxxxxx";
            string refreshToken = "1000.xxxxxxx.xxxxxxx";

            try
            {
                IAnalyticsClient ac = new AnalyticsClient(clientId, clientSecret, refreshToken);
                Program obj = new Program();
                obj.ImportData(ac);
            }
            catch (ServerException ex)
            {
                Console.WriteLine("Server exception - " + ex.GetErrorMessage());
            }
            catch (Exception ex)
            {
                Console.WriteLine("Other exception - " + ex.Message);
            }
        }
    }
}
Copiedpackage main

import (
    "fmt"
    ZAnalytics "zoho/pkg/analyticsclient"
)

var(
    clientId = "1000.xxxxxxx"
    clientSecret = "xxxxxxx"
    refreshToken = "1000.xxxxxxx.xxxxxxx"

    orgId = "55522777"
    workspaceId = "35130000001055707"
    viewId = "35730000007354002"
)

func ImportData(ac ZAnalytics.Client) {
    config := map[string]interface{}{}
    importtype := "append"
    filetype := "csv"
    autoidentify := "true"
    filepath := "/home/local/admin/Files/Sales.csv"
    bulk := ZAnalytics.GetBulkInstance(&ac, orgId, workspaceId)
    result, err := bulk.ImportData(viewId, importtype, filetype, autoidentify, filepath, config)

    if(err != nil){
        fmt.Println("Error - " + err.ErrorMessage)
    }else{
        fmt.Println(result)
    }
}

func main() {

    ac := ZAnalytics.GetAnalyticsClient(clientId, clientSecret, refreshToken)
    ImportData(ac)

}

Copiedimport com.zoho.analytics.client.*;
import org.json.*;

public class Test {

    private long orgId = 55522777l;
    private long workspaceId = 35130000001055707l;
    private long viewId = 35730000007354002l;

    public static void main(String args[]){

        String clientId = "1000.xxxxxxx";
        String clientSecret = "xxxxxxx";
        String refreshToken = "1000.xxxxxxx.xxxxxxx";

        Test tObj = new Test();
        AnalyticsClient ac = new AnalyticsClient(clientId, clientSecret, refreshToken);

        try {
            tObj.importData(ac);
        }
        catch (ServerException ex) {
            System.out.println("Server exception - ErrorCode : " + ex.getErrorCode() + ", ErrorMessage : "  + ex.getErrorMessage());
        }
        catch (ParseException ex) {
            System.out.println("Parser exception - ErrorMessage : "  + ex.getResponseMessage());
        }
        catch (Exception ex) {
            System.out.println("Other exception - ");
            ex.printStackTrace();
        }
    }

    public void importData(AnalyticsClient ac) throws Exception {
        String importType = "TRUNCATEADD";
        String fileType = "csv";
        boolean autoIdentify = true;
        String filePath = "/home/local/admin/Files/Sales.csv";
        JSONObject config = new JSONObject();
        BulkAPI data = ac.getBulkInstance(orgId, workspaceId);
        JSONObject response = data.importData(viewId, importType, fileType, autoIdentify, filePath, config);
        System.out.println(response);
    }
}
Copied<?php

    require 'AnalyticsClient.php';

    class Test
    {
        public $ac = NULL;
        public $client_id = "1000.xxxxxxx";
        public $client_secret = "xxxxxxx";
        public $refresh_token = "1000.xxxxxxx.xxxxxxx";

        public $org_id = "55522777";
        public $workspace_id = "35130000001055707";
        public $view_id = "35730000007354002";

        function __construct() {
            $this->ac =  new AnalyticsClient($this->client_id, $this->client_secret, $this->refresh_token);
        }

        function importData() {
            $import_type = "append";
            $file_type = "csv";
            $auto_identify = "true";
            $file_path = "/home/local/admin/Files/Sales.csv";
            $bulk = $this->ac->getBulkInstance($this->org_id, $this->workspace_id);
            $response = $bulk->importData($this->view_id, $import_type, $file_type, $auto_identify, $file_path);
            print_r($response);
        }
    }

    $test_obj = new Test();

    try {
        $test_obj->importData();
    }
    catch(ServerException $se) {
        echo "Server exception : " . $se->getErrorMessage() . "\n";
    }
    catch(IOException $ioe) {
        echo "IO exception : " . $ioe->getErrorMessage() . "\n";
    }
    catch(ParseException $pe) {
        echo "Parser exception : " . $pe->getErrorMessage() . "\n";
    }
    catch(Exception $e) {
        echo "Exception : " . $e->getErrorMessage() . "\n";
    }
?>
Copiedfrom __future__ import with_statement
from AnalyticsClient import AnalyticsClient
import sys
import json

class Config:

    CLIENTID = "1000.xxxxxxx";
    CLIENTSECRET = "xxxxxxx";
    REFRESHTOKEN = "1000.xxxxxxx.xxxxxxx";

    ORGID = "55522777";
    WORKSPACEID = "35130000001055707";
    VIEWID = "35730000007354002";

class sample:

    ac = AnalyticsClient(Config.CLIENTID, Config.CLIENTSECRET, Config.REFRESHTOKEN)

    def import_data(self, ac):
        import_type = "truncateadd"
        file_type = "csv"
        auto_identify = "true"
        file_path = "/home/local/admin/Files/Sales.csv"
        bulk = ac.get_bulk_instance(Config.ORGID, Config.WORKSPACEID)
        result = bulk.import_data(Config.VIEWID, import_type, file_type, auto_identify, file_path)        
        print(result)

try:
    obj = sample()
    obj.import_data(obj.ac);

except Exception as e:
    print(str(e))

Copiedvar analyticsClient = require('./AnalyticsClient');

var clientId = '1000.xxxxxxx';
var clientSecret = 'xxxxxxx';
var refreshtoken = '1000.xxxxxxx.xxxxxxx';
var orgId = '55522777';
var workspaceId = '35130000001055707';
var viewId = '35730000007354002';

var ac = new analyticsClient(clientId, clientSecret, refreshtoken);

var importType = 'truncateadd';
var fileType = 'csv';
var autoIdentify = true;
var filePath = '/home/local/admin/Files/Sales.csv';
var bulk = ac.getBulkInstance(orgId, workspaceId);
bulk.importData(viewId, importType, fileType, autoIdentify, filePath).then((response) => {
    console.log(response);

}).catch((error) => {
    console.log('errorCode : '+error.errorCode);
    console.log('errorMessage : '+error.errorMessage);
});
CopiedorgId = "55522777";
workspaceId = "35130000001055707";
viewId = "35730000007354002";

headersMap = Map();
headersMap.put("ZANALYTICS-ORGID",orgId);
fileResponse = invokeurl
[
  url :""
  type :GET
];
file = fileResponse.toFile("Sample.csv");
file.setParamName("FILE");
config = Map();
config.put("importType","append");
config.put("fileType","csv");
config.put("autoIdentify","true");
parameters = "CONFIG=" + zoho.encryption.urlEncode(config.toString());
response = invokeurl
[
  url :"https://<hostname>:<webclient port>/restapi/v2/workspaces/" + workspaceId + "/views/" + viewId + "/data" + "?" + parameters
  type :POST
  headers:headersMap
  files:file
  connection:"analytics_oauth_connection"
];
info response;

Download client libraries : C# | GO | JAVA | PHP | PYTHON | NodeJS

Sample value for CONFIG parameter:

Copied{
  "importType": "append",
  "fileType": "csv",
  "autoIdentify": "true"
}

Sample Response

CopiedHTTP/1.1 200 OK
Content-Type:application/json;charset=UTF-8

{
    "status": "success",
    "summary": "Import data",
    "data": {
        "importSummary": {
            "importType": "APPEND",
            "totalColumnCount": 7,
            "selectedColumnCount": 7,
            "totalRowCount": 755,
            "successRowCount": 755,
            "warnings": 0,
            "importOperation": "updated"
        },
        "columnDetails": {
            "Date": "Date",
            "Region": "Plain Text",
            "Product Category": "Plain Text",
            "Product": "Plain Text",
            "Customer Name": "Plain Text",
            "Sales": "Currency",
            "Cost": "Currency"
        },
        "importErrors": ""
    }
}