Export data source data using a REST API
Introduction
Bold BI’s REST API provides a seamless way to programmatically interact with the platform. This includes the capability to export data from a data source starting from version v7.11 and onwards.
REST API for Obtaining an Access Token
Before making any changes, it is necessary to authenticate with the Bold BI server to obtain an access token. This token must be included in the header of all API requests to ensure they are authorized.
Authentication Endpoint
| Field | Value |
|---|---|
| URL | http://{domain}/bi/api/site/{sitename}/token |
| Method | POST |
| Content-Type | application/json |
Request Body Schema
{
"username": "string",
"password": "string",
"grant_type": "password"
}
username: The email address of the user.password: The password of the user.grant_type: The type of credentials used to authorize the request for an access token. The value allowed ispassword.
Response
{
"access_token": "string",
"token_type": "string",
"expires_in": "string",
"Email": "string"
}
Example
| Request | Response |
|---|---|
Get the data source Id
Option 1: Get the Data source from UI
- Select the three dots located on the data source within the data source page, and then click on the Info option.
- Please refer to the screenshot below and note down the item ID.
Option 2: Get the Data sources from REST API
Users who have created the data sources can view the lists of data sources, Get DataSources
Utilize the REST API to export the data source data
By utilizing this API, you are able to export the data source to CSV, Excel, and JSON formats based on the data source ID.
API Endpoint
- URL:
http://{domain}/bi/api/site/{sitename}/v5.0/datasource/export?offset=1&limit=1 - Method:
Post - Content-Type:
application/json - Authorization:
Bearer <access_token>
Params
Pagination parameters are,
| Field | Description |
|---|---|
offset |
The default value is 0. |
limit |
The default value is 10000. The maximum size of the limit is 10000. |
The pagination parameter is supported only for the JSON data type. For CSV and Excel data sources, exporting is based on the ExcelExportAPILimit setting in the appsettings.json file. By default, this value is set to 500000. Depending on the export size, this may impact memory usage. Bold BI recommends a limit between 1,00,000 and 5,00,000, though it can be increased at your own risk.
Raw Body
{
"datasourceId": "string",
"fileType": "string",
"columns": [],
"dashboardParameter":
{
"Parameter1": [],
"Parameter2": [],
}
}
| Field | Description |
|---|---|
datasourceId |
The unique ID of the data source. To locate the data source ID you want to update, go to the data source listing page and select the “info” option for the specific data source. The ID will be displayed as “item ID” in the information dialog. |
fileType |
Either JSON or CSV or EXCEL. |
columns |
By default, all columns are exported. Specify the number of columns that can be exported. |
dashboardParameter |
It is an array of optional values. The given dashboard value will be substituted when exporting the data source. |
Response with different file types
JSON Type
CSV Type
Excel Type
Save Response to a file
- To download the export file in the required format, please click on the Save to a file in Save Response option.
- Save the file into your local machine.
How to utilize the Dashboard parameter
It is supported for both SQL-related & Web related data sources, such as Live and Extract, as well as for Web API live data source. It is applicable in the following cases:
- Data Filter.
- CodeView Mode.
- URL, Header, Parameter for Web live data source.
- Connection Properties for SQL live data source.
Using a dashboard parameter created in a different data source is not supported. The parameter is accessible in the dashboard but is not supported by the data source server.
Dashboard Parameters
Dashboard Parameter in Data Filters
Query Designer Page
Export API
Used in CodeView Mode
Dashboard parameters and pagination support are available in the API from version 8.1 onwards.
Conclusion
By following these steps, you can successfully export the data source data in CSV/JSON/Excel format using Bold BI’s REST API. This allows for better organization and management of your data within Bold BI.
Additional References
