Loading Data
Overview
Tabulator row data is defined as an array of objects, that can either be passed as an array or retrieved as a JSON formatted string via AJAX from a URL.
The data can contain more columns that are defined in the columns options, these will be stored with the rest of the data, but not rendered to screen.
An example JSON data set:
[ {id:1, name:"Billy Bob", age:"12", gender:"male", height:1, col:"red", dob:"", cheese:1}, {id:2, name:"Mary May", age:"1", gender:"female", height:2, col:"blue", dob:"14/05/1982", cheese:true}, {id:3, name:"Christine Lobowski", age:"42", height:0, col:"green", dob:"22/05/1982", cheese:"true"}, {id:4, name:"Brendon Philips", age:"125", gender:"male", height:1, col:"orange", dob:"01/08/1980"}, {id:5, name:"Margret Marmajuke", age:"16", gender:"female", height:5, col:"yellow", dob:"31/01/1999"}, {id:6, name:"Billy Bob", age:"12", gender:"male", height:1, col:"red", dob:"", cheese:1}, {id:7, name:"Mary May", age:"1", gender:"female", height:2, col:"blue", dob:"14/05/1982", cheese:true}, {id:8, name:"Christine Lobowski", age:"42", height:0, col:"green", dob:"22/05/1982", cheese:"true"}, {id:9, name:"Brendon Philips", age:"125", gender:"male", height:1, col:"orange", dob:"01/08/1980"}, {id:10, name:"Margret Marmajuke", age:"16", gender:"female", height:5, col:"yellow", dob:"31/01/1999"}, ]
Data Mutation
Note: if you have defined any mutator functions in your column definition array, these will be applied to your data as it is being parsed into the table. (see Mutators for more details)
Row Index
A unique index value should be present for each row of data if you want to be able to programmatically alter that data at a later point, this should be either numeric or a string. By default Tabulator will look for this value in the id field for the data. If you wish to use a different field as the index, set this using the index option parameter.
var table = new Tabulator("#example-table", { index:"age", //set the index field to the "age" field. });
Load Data From Array/JSON
You can pass an array directly to the table using the setData method.
var tableData = [ {id:1, name:"Billy Bob", age:"12", gender:"male", height:1, col:"red", dob:"", cheese:1}, {id:2, name:"Mary May", age:"1", gender:"female", height:2, col:"blue", dob:"14/05/1982", cheese:true}, ] table.setData(tableData);
The setData method returns a promise, this can be used to run any other commands that have to be run after the data has been loaded into the table. By running them in the promise you ensure they are only run after the table has loaded the data.
table.setData(tableData) .then(function(){ //run code after table has been successfully updated }) .catch(function(error){ //handle error loading data });
Set Initial Data Array
If you want your table to be created already containing data, then you can pass the array into the data option in the table constructor.
var tableData = [ {id:1, name:"Billy Bob", age:"12", gender:"male", height:1, col:"red", dob:"", cheese:1}, {id:2, name:"Mary May", age:"1", gender:"female", height:2, col:"blue", dob:"14/05/1982", cheese:true}, ] var table = new Tabulator("#example-table", { data:tableData, //set initial table data columns:[ {title:"Name", field:"name"}, {title:"Age", field:"age"}, {title:"Gender", field:"gender"}, {title:"Height", field:"height"}, {title:"Favourite Color", field:"col"}, {title:"Date Of Birth", field:"dob"}, {title:"Cheese Preference", field:"cheese"}, ], });
Load data using AJAX
If you wish to retrieve your data from a remote source you can set the URL for the request in the ajaxURL option.
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL });
JSON Response
Tabulator expects the server to JSON formatted array of row data objects.
Trigger Ajax Load
You can also load data at any point by ajax by passing the url to the setData function and it will perform the AJAX request for you. The URL can be absolute or relative.
table.setData("http://www.getmydata.com/now");
If you have already set the URL using the ajaxURL option in the table constructor then you can trigger a reload of the data at any point by calling the setData function without any arguments.
table.setData();
Default Request Headers
By default Tabulator will send the following headers with any ajax request:
Header | Value |
---|---|
X-Requested-With | XMLHTTPRequest |
Accept | application/json |
Additional headers may be sent depending on your choice of ajaxContentType
Any credentials stored in cookies with the page will also be sent with the request.
URL Parameters
If you wish to pass parameters with your request you can pass them as an object into the ajaxParams option.
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL ajaxParams:{key1:"value1", key2:"value2"}, //ajax parameters });
Real Time Parameters
If you would like to generate the parameters with each request you can instead pass a callback to the ajaxParams option. This function will be called every time a request is made and should return an object containing the request parameters
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL ajaxParams: function(){ return {key1:"value1", key2:"value2"}; } });
Set Data Parameters
If you wish to pass parameters when using the setData you can either include them in-line with the url string, or as an optional second parameter to the function. In the latter case they should be provided in the form of an object with key/value pairs.
table.setData("http://www.getmydata.com/now", {key1:"value1", key2:"value2"});
Request Methods
By default Tabulator will make all ajax requests using the HTTP GET request method. If you need to use a different request method you can pass this into the ajaxConfig option
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL ajaxConfig:"POST", //ajax HTTP request type });
You can also pass the request method into the third argument of the setData function
table.setData("http://www.getmydata.com/now", {}, "POST"); //make a post request
Content Type
When using a request method other than "GET" Tabulator will send any parameters with a content type of form data. You can change the content type with the ajaxContentType option. This will ensure parameters are sent in the format you expect, with the correct headers.
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL ajaxConfig:"POST", //ajax HTTP request type ajaxContentType:"json", // send parameters to the server as a JSON encoded string });
The ajaxContentType option can take one of two values:
- "form" - send parameters as form data (default option)
- "json" - send parameters as JSON encoded string
If you want to use a custom content type then you can pass a content type formatter object into the ajaxContentType option. this object must have two properties, the headers property should contain all headers that should be sent with the request and the body property should contain a function that returns the body content of the request
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL ajaxConfig:"POST", //ajax HTTP request type ajaxContentType:{ headers:{ 'Content-Type': 'text/html', }, body:function(url, config, params){ //url - the url of the request //config - the fetch config object //params - the request parameters //return comma list of params:values var output = []; for (var key in params){ output.push(key + ":" + params[key]) } return output.join(","); }, } });
Advanced Configuration
If you need more control of the request you can pass a fetch configuration object into the ajaxConfig option:
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL ajaxConfig:{ method:"POST", //set request type to Position headers: { "Content-type": 'application/json; charset=utf-8', //set specific content type }, } });
Or if you are using the setData function, into its third argument
var ajaxConfig = { method:"POST", //set request type to Position headers: { "Content-type": 'application/json; charset=utf-8', //set specific content type }, }; table.setData("http://www.getmydata.com/now", {}, ajaxConfig); //make ajax request with advanced config options
A full list of the available properties for the fetch configuration object can be found on the Fetch API Documentation.
Note: You MUST NOT set any of the following options in the advanced config option as they are set by Tabulator and needed for correct operation of the library:
- url
- async
- dataType
- success
- error
Cross Origin Requests
If you are making ajax requests to URL's not on the same origin as your site, Tabulator will attempt to setup the CORS headers for you to allow the request to succeed.
Depending on the configuration of the server the request is being made to Tabulator may not be able to guess the correct headers to set for the request to succeed, If this happens you should look at the error presented in the console as it will help you to determine what values need to be set.
When trouble shooting an ajax configuration, the following are the key config variables that you will likely need to alter to make the request work (with the default values that Tabulator uses when generating a cors request):
var ajaxConfig = { mode:"cors", //set request mode to cors credentials: "same-origin", //send cookies with the request from the matching origin headers: { "Accept": "application/json", //tell the server we need JSON back "X-Requested-With": "XMLHttpRequest", //fix to help some frameworks respond correctly to request "Content-type": 'application/json; charset=utf-8', //set the character encoding of the request "Access-Control-Allow-Origin": "http://yout-site.com", //the URL origin of the site making the request }, };
A full details on each of these can be found on the Fetch API Documentation.
Options Request
When a CORS request is being made, an OPTIONS request will first be made to the server to check whether the connection is possible before the GET request happens. If you see this in your developer console this is correct behaviour and not a bug.
Ajax Response Format
Tabulator expects a JSON encoded array of row objects as the response from an ajax request:
[ {"id":1, "name":"bob", "age":"23"}, {"id":2, "name":"jim", "age":"45"}, {"id":3, "name":"steve", "age":"32"} ]
Altering The Response
Tabulator expects the response to an ajax request to be a JSON encoded string representing an array of data objects. If you need to pass other data back in your request as well, you can use the ajaxResponse callback to process the returned data before it is passed to the table. The return value of this callback should be an array of row data objects.
var table = new Tabulator("#example-table", { ajaxResponse:function(url, params, response){ //url - the URL of the request //params - the parameters passed with the request //response - the JSON object returned in the body of the response. return response.tableData; //return the tableData property of a response json object }, });
Get Current Ajax URL
You can retrieve the current AJAX URL of the table with the getAjaxUrl function.
var url = table.getAjaxUrl();
Note: This function will return the url set on the ajaxURL property or the latest url set with the setData function, it will not include any pagination, filter or sorter parameters
Aborting an Ajax Request
The ajaxRequesting callback is called just before an AJAX request is made, if you want to abort the request for any reason you can return a value of false from the function.
var table = new Tabulator("#example-table", { ajaxRequesting:function(url, params){ return false; //abort ajax request }, });
Generating Custom Request URL
If you need more control over the url of the request that you can get from the ajaxURL and ajaxParams properties, the you can use the ajaxURLGenerator property to pass in a callback that will generate the URL for you.
The callback should return a string representing the URL to be requested.
var table = new Tabulator("#example-table", { ajaxURLGenerator:function(url, config, params){ //url - the url from the ajaxURL property or setData function //config - the request config object from the ajaxConfig property //params - the params object from the ajaxParams property, this will also include any pagination, filter and sorting properties based on table setup //return request url return url + "?params=" + encodeURI(JSON.stringify(params)); //encode parameters as a json object }, });
Ajax Filtering
If you would prefer to filter your data server side rather than in Tabulator, you can use the filterMode option to send the filter data to the server instead of processing it client side
var table = new Tabulator("#example-table", { filterMode:"remote", //send filter data to the server instead of processing locally });
An array of filters objects will then be passed in the filters parameter of the request, the name of this parameter can be set in the dataSendParams option, in the pagination module.
The array of filter objects will take the same form as those returned from the getFilters function:
[ {field:"age", type:">", value:52}, //filter by age greater than 52 {field:"height", type:"<", value:142}, //and by height less than 142 ]
If a custom filter function is being used then the type parameter will have a value of "function".
If the table is not currently filtered then the array will be empty.
Ajax Sorting
If you would prefer to sort your data server side rather than in Tabulator, you can use the sortMode option to send the sort data to the server instead of processing it client side
var table = new Tabulator("#example-table", { sortMode:"remote", //send sort data to the server instead of processing locally });
An array of sorters objects will then be passed in the sorters parameter of the request, the name of this parameter can be set in the dataSendParams option, in the pagination module.
The array of sorter objects will take the same form as those returned from the getSorters function:
[ { column:column, field:"age", dir:"asc" }, { column:column, field:"height" dir:"desc" } ]
If the table is not currently sorted then the array will be empty.
Overriding the Request Promise
The ajax module uses an inbuilt function to generate the ajax request and pass the data back into Tabulator. If you want to replace the inbuilt ajax functionality to route the request to another data source, for example a JS or Realm database, you can use the ajaxRequestFunc option.
This option expects a function that returns a promise. the promise should pass through the expected Tabulator formatted data array or data object on success, and should pass back an error on failure.
The function will be passed three arguments. The first is the requested URL. The second is the config object from the ajaxConfig option or from the setData function (usually structured for use in fetch, but you can put whatever you like into this). The third is the params object set in the ajaxParams option or from the setData function, this will also include the sorter and filter arrays if ajax sorting or filtering is enabled.
function queryRealm(url, config, params){ //url - the url of the request //config - the ajaxConfig object //params - the ajaxParams object //return promise return new Promise(function(resolve, reject){ //do some async data retrieval then pass the array of row data back into Tabulator resolve(data); //if there is an error call this function and pass the error message or object into it reject(); }); } var table = new Tabulator("#example-table", { ajaxRequestFunc:queryRealm, });
Note: when using the ajaxRequestFunc option the ajaxURLGenerator will no longer be called, you will need to handle any URL manipulation in your function.
Server Side Code (PHP)
When using ajax loading, the server should respond with a JSON encoded string representing an array of row objects. An example in PHP can be seen below:
//build data array $data = [ [id=>1, name=>"Billy Bob", age=>"12", gender=>"male", height=>1, col=>"red", dob=>"", cheese=>1], [id=>2, name=>"Mary May", age=>"1", gender=>"female", height=>2, col=>"blue", dob=>"14/05/1982", cheese=>true], [id=>3, name=>"Christine Lobowski", age=>"42", height=>0, col=>"green", dob=>"22/05/1982", cheese=>"true"], [id=>4, name=>"Brendon Philips", age=>"125", gender=>"male", height=>1, col=>"orange", dob=>"01/08/1980"], [id=>5, name=>"Margret Marmajuke", age=>"16", gender=>"female", height=>5, col=>"yellow", dob=>"31/01/1999"], ]; //return JSON formatted data echo(json_encode($data));
Loading Errors
When making an ajax request there are two different ways to detect errors with the request. In both cases if you are using the built in request promise then these callbacks will be passed the Fetch Response Object as their first argument, which allows access to the response content, status code, etc.
Promise Catch
table.setData("http://mydata.com/data") .then(function(){ //run code after table has been successfully updated }) .catch(function(error){ // error - Fetch response object });
Event
The dataLoadError event is triggered there is an error response to a load request. This event is passed the Fetch Response Object as its first argument, which allows access to the response content, status code, etc.
table.on("dataLoadError", function(error){ //error - the returned error object });
Callbacks
A range of callbacks are available for tracking progress of ajax data loading. See the Ajax Callbacks section for more information.
Progressive Ajax Loading
If you are loading a lot of data from a remote source into your table in one go, it can sometimes take a long time for the server to return the request, which can slow down the user experience.
To speed things up in this situation Tabulator has a progressive load mode, this uses the pagination module to make a series of requests for part of the data set, one at a time, appending it to the table as the data arrives. This mode can be enable using the progressiveLoad option. No pagination controls will be visible on screen, it just reuses the functionality of the pagination module to sequentially load the data.
With this mode enabled, all of the settings outlined in the Ajax Documentation are still available
There are two different progressive loading modes, to give you a choice of how data is loaded into the table.
Load Mode
In load mode the table will sequentially add each page of data into the table until all data is loaded.
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL progressiveLoad:"load", //sequentially load all data into the table });
By default tabulator will make the requests to fill the table as quickly as possible. On some servers these repeated requests from the same client may trigger rate limiting or security systems. In this case you can use the progressiveLoadDelay option to add a delay in milliseconds between each page request.
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL progressiveLoad:"load", //enable progressive loading progressiveLoadDelay:200 //wait 200 milliseconds between each request });
Scroll Mode
In scroll mode Tabulator will initially load enough data into the table to fill the visible area of the table plus the scroll margin.
Whenever the user scrolls down vertically, if they are with the the scroll margin of the bottom of the table an ajax request will be triggered for the next page worth of data.
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL progressiveLoad:"scroll", //load data into the table as the user scrolls });
The progressiveLoadScrollMargin property determines how close to the bottom of the table in pixels, the scroll bar must be before the next page worth of data is loaded, by default it is set to twice the height of the table.
var table = new Tabulator("#example-table", { ajaxURL:"http://www.getmydata.com/now", //ajax URL progressiveLoad:"scroll", //enable progressive loading progressiveLoadScrollMargin:300 //trigger next ajax load when scroll bar is 300px or less from the bottom of the table. });
Scroll Margin Size
to ensure a good user experience, you should make sure you have a reasonably large scroll margin, to give your users room to scroll while the data is being loaded from the server.
Returned Response Data
When using progressive loading the data returned from the server will need to be formatted for pagination.
{ "last_page":15, //the total number of available pages (this value must be greater than 0) "data":[ // an array of row data objects {"id":1, "name":"bob", "age":"23"} //example row data object ] }
All the usual pagination options, such as paginationSize, dataSendParams etc can be used in this mode, you can find out more about the options and expected response in the Remote Pagination Documentation
Page Size
For the best user experience, it is recommended that the number of records sent per page be at least enough to fill the height of the table.
Blocking Progressive Load
Calling any of the setData or updateData type functions will cause any active progressive loading to halt to prevent an uncontrolled mix of local and remote data
Import Custom Data
Import From Local File
You can let the user choose a file from their local disk by using the import function. It will present the user with a standard file open dialog where they can then choose the file to load into the table.
The first argument of the function is the importer that will parse the file and convert it into an array of row data, this can either be a string representing one of the built in importers or a function for a custom importer. If this argument is missing, the import module will default to using the value of the importFormat option.
The second argument is the value for the accept attribute of the file input, and is used to restrict the files that the user can pick, this argument will accept any of the values valid for the accept field of an input element . If this argument is missing the user will be able to pick any file type in the file picker.
table.import("json", ".json") .then(() => { //file successfully imported }) .catch(() => { //something went wrong })
The import function returns a promise that resolves when the data has been successfully loaded into the table
Import From Data
If you already have the formatted data from a file and don't need to present the user with the a file picker then you can use the importFormat option to tell Tabulator how to import data into the table when it is passed into the data option or the setData function.
The importFormat option can take any of the built in importers or a function for a custom importer.
Importing With Data Option
This can be used to import custom data when the table is loaded.
//define some CSV data var csvData = `"Oli", "London", "23" "Jim", "Mancheser", "53"`; //define table var table = new Tabulator("#example-table", { data:csvData, importFormat:"csv", columns:[...], });
Importing With Data Option Using Auto Columns
With autoColumns enabled, you can build the table entirely from CSV data, as long as the first row of the data contains the column titles
//define some CSV data var csvData = `"Name", "Location", "Age" "Oli", "London", "23" "Jim", "Mancheser", "53"`; //define table var table = new Tabulator("#example-table", { data:csvData, importFormat:"csv", autoColumns:true, });
Calling the setData or import functions on a table with this setup will result in it parsing the column headers again from any future import
Importing With setData Function
This can be used to import custom data at any point after the table has loaded.
//define some CSV data var csvData = `"Oli", "London", "23" "Jim", "Mancheser", "53"`; //define table var table = new Tabulator("#example-table", { importFormat:"csv", }); //load data at some point later table.setData(csvData);
Built In Importers
Tabulator comes with a number of preconfigured importers, which are outlined below.
Note: For a guide to adding your own importers to this list, have a look at the Extending Tabulator section.
JSON
The json importer will load a JSON formatted file into the table.
table.import("json", ".json");
Data Format
The data must be stored as a valid json string matching the the structure of an array of objects as defined in the Load Data from Array section.
CSV
The csv importer will load a csv formatted file into the table.
table.import("csv", ".csv");
CSV files can contain a column title row, as long as the titles match the column titles the row will be safely ignored by Tabulator
As data contained in a CSV is arranged in simple columns, each column in the CSV will be loaded in order and matched to a column of a corresponding index in the table.
Data Format
The data must be stored as a valid csv format, with rows separated with a carriage returns and columns separated by commas.
Auto Columns
If the autoColumns option is enabled on the table, then the first row of the CSV data should be the column titles.
Array
The array importer will load an array of row arrays into the table, it is intended for use loading JavaScript arrays of arrays into the table, not JSON formatted strings.
//define some array data var arrayData = [ ["Name", "Age", "Likes Cheese"], //column header titles ["Bob", 23, true], ["Jim", 44, false], ] //define table var table = new Tabulator("#example-table", { data:arrayData, importFormat:"array", autoTables:true, });
Auto Columns
If the autoColumns option is enabled on the table, then the first row of the CSV data should be the column titles.
Custom Importers
As well as the built-in importers you can define a importer using a custom importer function.
The importer function accepts one argument, a string of the text content of the file being imported.
The function can return one of two options. An array of row objects as defined in the Load Data from Array section.
Or a two dimensional array of rows containing columns, this will then be used by Tabulator to infer the columns from their position in the array. If the autoColumns option is enabled on the table, then the first row of the array should be the column titles.
//define custom importer function customJsonImporter(fileContents){ return JSON.parse(fileContents); } //trigger import using custom importer table.import(customJsonImporter, ".json");
File Readers
When loading a file using the import function, Tabulator reads in the file using a File Reader.
By default Tabulator will read in the file as plain text, which is the format used by all the built in importers. If you need to read the file data in a different format then you can use the importReader option to instruct the file reader to read in the file in a different format.
var table = new Tabulator("#example-table", { importReader:"buffer", //read imported file as buffer });
The available readers are:
- text - Read file as plain text
- buffer - Read file as ArrayBuffer
- binary - Read file as raw binary in string format
- url - Read file as data url
Load Data from HTML Table
You can create a Tabulator table directly from an HTML table element. You can define the columns in the usual way with the columns option, or you can set them as th elements in the thead of a table.
Any rows of data in the tbody of the table will automatically be converted to tabulator data in displayed in the resulting table.
If you define the width attribute in a table header cell, this will be used to set the width of the column in Tabulator.
<table id="example-table"> <thead> <tr> <th width="200">Name</th> <th>Age</th> <th>Gender</th> <th>Height</th> <th>Favourite Color</th> <th>Date of Birth</th> </tr> </thead> <tbody> <tr> <td>Billy Bob</td> <td>12</td> <td>male</td> <td>1</td> <td>red</td> <td></td> </tr> <tr> <td>Mary May</td> <td>1</td> <td>female</td> <td>2</td> <td>blue</td> <td>14/05/1982</td> </tr> </tbody> </table>
var table = new Tabulator("#example-table", {});
Note: Tabulator can only parse simple tables. Tables using multiple header rows, colspan or rowspan attributes will cause the import to fail.
Import Table Options from Attributes
You can set options parameters directly in the HTML by using tabulator- attributes on the table and th elements, these will then be set as configuration options on the table.
Setting option on table:
<table id="example-table" tabulator-movableRows="true"> <thead> <tr> <th>Name</th> </tr> </thead> <tbody> <tr> <td>Billy Bob</td> </tr> </tbody> </table>
Setting option on row:
<table id="example-table"> <thead> <tr> <th tabulator-align="center">Name</th> </tr> </thead> <tbody> <tr> <td>Billy Bob</td> </tr> </tbody> </table>
Note: This approach can only be used for text, numeric and boolean options, for callbacks and functions you will need to use the constructor object outlined below.
Complex Options Setup
If you need to set up any column options on the imported table you can declare these in table constructor as you would do for any other table. It is important to note that the title parameter for each column definition must match the text in the table element's header cell exactly for the imported data to link to the correct column.
If you use a column definition array in the Tabulator constructor, the order of columns in this array will take priority over the order of the columns in the table element.
var table = new Tabulator("#example-table", { movableRows:true, //example option (enable movable rows) columns:[ //set column definitions for imported table data {title:"Age", sorter:"number"}, {title:"Height", sorter:"number"}, {title:"Date of Birth", sorter:"date"}, ], });