YUI Library Examples: DataSource Utility: Extracting data from an HTML table

DataSource Utility: Extracting data from an HTML table

DataSource supports using a table in markup as its source of truth.

This example illustrates how to create a "pass-thru" DataSource instance to leverage the DOM walking and parsing routines inside in order to extract the table's data into a JavaScript array structure.

View example in new window.

Total Amount Due: (click the Extract button)

Table in markup with data in it
Due Date	Account Number	Quantity	Amount Due
1/23/1999	29e8548592d8c82	12	$150.00
5/19/1999	83849	8	$60.00
8/9/1999	11348	1	-$34.99
1/23/2000	29e8548592d8c82	10	-$1.00
4/28/2000	37892857482836437378273	123	$33.32
1/23/2001	83849	5	-$15.00
9/30/2001	224747	14	$56.78

What we have and what we'll get

In this example, a table rendered in the page's markup will be parsed into an array, and stored in YAHOO.example.data.

The markup looks like this:


1	<table id="accounts">
2	<caption>Table in markup with data in it</caption>
3	<thead>
4	<tr>
5	<th>Due Date</th>
6	<th>Account Number</th>
7	<th>Quantity</th>
8	<th>Amount Due</th>
9	</tr>
10	</thead>
11	<tbody>
12	<tr>
13	<td>1/23/1999</td>
14	<td>29e8548592d8c82</td>
15	<td>12</td>
16	<td>$150.00</td>
17	</tr>
18	<tr>
19	<td>5/19/1999</td>
20	<td>83849</td>
21	<td>8</td>
22	<td>$60.00</td>
23	</tr>
24	...
25	</tbody>
26	</table>
	view plain \| print \| ?

The resulting data will look like this:


1	[
2	{
3	due : (Date instance),
4	account : '29e8548592d8c82',
5	quantity: 12,
6	amount : 150
7	},
8	{
9	due : (Date instance),
10	account : '83849',
11	quantity: 8,
12	amount : 60
13	},
14	...
15	]
	view plain \| print \| ?

Create a DataSource instance

The first step is to create the DataSource instance, seeding it with the <table> element, which we get via YAHOO.util.Dom.get. Configure the responseType and responseSchema to identify the mapping between columns and data key names (first column will be called "due").

We'll take advantage of the often-ignored second constructor parameter to configure the DataSource inline.


1	YAHOO.util.Event.onDOMReady(function () {
2
3	// Create some shortcuts
4	var DataSource = YAHOO.util.DataSource,
5	Event = YAHOO.util.Event,
6	Dom = YAHOO.util.Dom;
7
8	// this is where the data will go
9	YAHOO.example.data = null;
10
11	// Pass a configuration object as the second parameter
12	var myDataSource = new DataSource(Dom.get('accounts'), {
13	responseType : DataSource.TYPE_HTMLTABLE,
14	responseSchema : {
15	fields : [
16	{ key : 'due' },
17	{ key : 'account' },
18	{ key : 'quantity' },
19	{ key : 'amount' }
20	]
21	}
22	});
23
24	...
25	});
	view plain \| print \| ?

Parsing the data into correct data types

By default, DataSource will treat all table cell data as strings. We can add parsers to the field declarations to identify how the data should be stored. DataSource comes with a few parsers out of the box, but you can add your own custom parser by supplying a function as the parser in the field definition.

Another way to create a parser common across all DataSource instances is to add your parse function to the YAHOO.util.DataSource.Parser object. The property name you use can then be referenced as a string in the field's parser configuration.

We'll do this for the Amount column, since DataSource doesn't have a parser that can handle currency figures.


1	...
2
3	// this is where the data will go
4	YAHOO.example.data = null;
5
6	// Add a new parser to DataSource to parse numbers that are prefixed with
7	// currency symbols (or any other non-numeric characters)
8	DataSource.Parser.currency = function (cur) {
9	if (YAHOO.lang.isString(cur)) {
10	var neg = !cur.indexOf('-');
11	cur = (neg?-1:1) * cur.slice(neg).replace(/^[^0-9.]+\|,/g,'');
12	} else if (!YAHOO.lang.isNumber(cur)) {
13	return 0;
14	}
15
16	return cur;
17	};
18
19	var myDataSource = new DataSource(Dom.get('accounts'), {
20	responseType : DataSource.TYPE_HTMLTABLE,
21	responseSchema : {
22	fields : [
23	{ key : 'due', parser : 'date' },
24	{ key : 'account' },
25	{ key : 'quantity', parser : 'number' },
26	{ key : 'amount', parser : 'currency' } // use our new parser
27	]
28	}
29	});
30
31	...
	view plain \| print \| ?

Capture the data in the `sendRequest` callback

The next and final step is to issue a sendRequest, providing a simple success handler to assign the parsed response's results array to our desired destination.


1	// DataSource ignores the first parameter for HTML table instances
2	myDataSource.sendRequest(null,{
3	success : function (req,res) {
4	YAHOO.example.data = res.results;
5	}
6	});
	view plain \| print \| ?

At this point the data is available in YAHOO.example.data.

Change into a pass-thru

Since the DataSource is only needed for this one operation, and only one method is being executed, we don't even need to store the instance in a variable. Just call sendRequest on the result of the DataSource constructor.


1	// No need to store the DataSource
2	new DataSource(Dom.get('accounts'), {
3	responseType : DataSource.TYPE_HTMLTABLE,
4	responseSchema : {
5	// Describe the object keys each record will have and what type
6	// of data to parse into the respective values
7	fields : [
8	{ key: 'due', parser: 'date' },
9	{ key: 'account' },
10	{ key: 'quantity', parser: 'number' },
11	{ key: 'amount', parser: 'currency' } // use our new parser
12	]
13	}
14	}).sendRequest(null,{
15	success : function (req,res) {
16	YAHOO.example.data = res.results;
17	}
18	});
	view plain \| print \| ?

Full Code Listing

Below is a full code listing for this example, including the code for the buttons, making use of the extracted data.

JavaScript


1	YAHOO.util.Event.onDOMReady(function () {
2
3	// Shortcuts
4	var DataSource = YAHOO.util.DataSource,
5	Event = YAHOO.util.Event,
6	Dom = YAHOO.util.Dom;
7
8	YAHOO.example.data = null; // this is where the data will go
9
10	// Add a new parser to DataSource to parse numbers that are prefixed with
11	// currency symbols (or any other non-numeric characters)
12	DataSource.Parser.currency = function (cur) {
13	if (YAHOO.lang.isString(cur)) {
14	var neg = !cur.indexOf('-');
15	cur = (neg?-1:1) * cur.slice(neg).replace(/^[^0-9.]+\|,/g,'');
16	} else if (!YAHOO.lang.isNumber(cur)) {
17	return 0;
18	}
19
20	return cur;
21	};
22
23	Event.on('demo_load','click',function (e) {
24
25	// Here's the pass-thru DataSource. Instantiate and immediately call
26	// sendRequest, passing a simple assignment function as the callback's
27	// success handler
28	new DataSource(Dom.get('accounts'), {
29	responseType : DataSource.TYPE_HTMLTABLE,
30	responseSchema : {
31	// Describe the object keys each record will have and what type
32	// of data to parse into the respective values
33	fields : [
34	{ key: 'due', parser: 'date' },
35	{ key: 'account' },
36	{ key: 'quantity', parser: 'number' },
37	{ key: 'amount', parser: 'currency' } // use our new parser
38	]
39	}
40	}).sendRequest(null,{
41	success : function (req,res) {
42	YAHOO.example.data = res.results;
43	}
44	});
45
46	YAHOO.log(YAHOO.lang.dump(YAHOO.example.data), "info", "example");
47
48	// The work is done and data is populated. Update the UI to allow for
49	// referencing the data structure.
50	Dom.get('demo_load').disabled = true;
51	Dom.get('demo_go').disabled = false;
52
53	Dom.get('report').innerHTML = "Table data loaded. Click <em>Get Total Amount Due</em>.";
54	});
55
56	Event.on('demo_go','click', function (e) {
57	var data = YAHOO.example.data \|\| [],
58	total = 0,
59	i;
60
61	// Proof that we have a populated data object
62	for (i = data.length - 1; i >= 0; --i) {
63	total += data[i].amount;
64	}
65
66	Dom.get('report').innerHTML = '$' + total.toFixed(2);
67	});
68
69	});
	view plain \| print \| ?

YAHOO.util.Event.onDOMReady(function () {

// Shortcuts
var DataSource = YAHOO.util.DataSource,
    Event = YAHOO.util.Event,
    Dom   = YAHOO.util.Dom;

YAHOO.example.data  = null; // this is where the data will go

// Add a new parser to DataSource to parse numbers that are prefixed with
// currency symbols (or any other non-numeric characters)
DataSource.Parser.currency = function (cur) {
    if (YAHOO.lang.isString(cur)) {
        var neg = !cur.indexOf('-');
        cur = (neg?-1:1) * cur.slice(neg).replace(/^[^0-9.]+|,/g,'');
    } else if (!YAHOO.lang.isNumber(cur)) {
        return 0;
    }

return cur;
};

Event.on('demo_load','click',function (e) {

// Here's the pass-thru DataSource.  Instantiate and immediately call
    // sendRequest, passing a simple assignment function as the callback's
    // success handler
    new DataSource(Dom.get('accounts'), {
        responseType : DataSource.TYPE_HTMLTABLE,
        responseSchema : {
            // Describe the object keys each record will have and what type
            // of data to parse into the respective values
            fields : [
                { key: 'due',      parser: 'date' },
                { key: 'account' },
                { key: 'quantity', parser: 'number' },
                { key: 'amount',   parser: 'currency' } // use our new parser
            ]
        }
    }).sendRequest(null,{
        success : function (req,res) {
            YAHOO.example.data = res.results;
        }
    });
    
    YAHOO.log(YAHOO.lang.dump(YAHOO.example.data), "info", "example");

// The work is done and data is populated. Update the UI to allow for
    // referencing the data structure.
    Dom.get('demo_load').disabled = true;
    Dom.get('demo_go').disabled   = false;

Dom.get('report').innerHTML = "Table data loaded. Click <em>Get Total Amount Due</em>.";
});

Event.on('demo_go','click', function (e) {
    var data  = YAHOO.example.data || [],
        total = 0,
        i;

// Proof that we have a populated data object
    for (i = data.length - 1; i >= 0; --i) {
        total += data[i].amount;
    }

Dom.get('report').innerHTML = '$' + total.toFixed(2);
});

});

HTML


1	<div id="demo">
2	<button type="button" id="demo_load">Extract the table data</button>
3	<button type="button" id="demo_go" disabled="disabled">Get Total Amount Due</button>
4	<p>Total Amount Due: <em id="report">(click the Extract button)</em></p>
5
6	<table id="accounts">
7	<caption>Table in markup with data in it</caption>
8	<thead>
9	<tr>
10	<th>Due Date</th>
11	<th>Account Number</th>
12	<th>Quantity</th>
13	<th>Amount Due</th>
14	</tr>
15	</thead>
16	<tbody>
17	<tr>
18	<td>1/23/1999</td>
19	<td>29e8548592d8c82</td>
20	<td>12</td>
21	<td>$150.00</td>
22	</tr>
23	<tr>
24	<td>5/19/1999</td>
25	<td>83849</td>
26	<td>8</td>
27	<td>$60.00</td>
28	</tr>
29	<tr>
30	<td>8/9/1999</td>
31	<td>11348</td>
32	<td>1</td>
33	<td>-$34.99</td>
34	</tr>
35	<tr>
36	<td>1/23/2000</td>
37	<td>29e8548592d8c82</td>
38	<td>10</td>
39	<td>-$1.00</td>
40	</tr>
41	<tr>
42	<td>4/28/2000</td>
43	<td>37892857482836437378273</td>
44	<td>123</td>
45	<td>$33.32</td>
46	</tr>
47	<tr>
48	<td>1/23/2001</td>
49	<td>83849</td>
50	<td>5</td>
51	<td>-$15.00</td>
52	</tr>
53	<tr>
54	<td>9/30/2001</td>
55	<td>224747</td>
56	<td>14</td>
57	<td>$56.78</td>
58	</tr>
59	</tbody>
60	</table>
61	</div>
	view plain \| print \| ?

<div id="demo">
    <button type="button" id="demo_load">Extract the table data</button>
    <button type="button" id="demo_go" disabled="disabled">Get Total Amount Due</button>
    <p>Total Amount Due: <em id="report">(click the Extract button)</em></p>

<table id="accounts">
        <caption>Table in markup with data in it</caption>
        <thead>
            <tr>
                <th>Due Date</th>
                <th>Account Number</th>
                <th>Quantity</th>
                <th>Amount Due</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>1/23/1999</td>
                <td>29e8548592d8c82</td>
                <td>12</td>
                <td>$150.00</td>
            </tr>
            <tr>
                <td>5/19/1999</td>
                <td>83849</td>
                <td>8</td>
                <td>$60.00</td>
            </tr>
            <tr>
                <td>8/9/1999</td>
                <td>11348</td>
                <td>1</td>
                <td>-$34.99</td>
            </tr>
            <tr>
                <td>1/23/2000</td>
                <td>29e8548592d8c82</td>
                <td>10</td>
                <td>-$1.00</td>
            </tr>
            <tr>
                <td>4/28/2000</td>
                <td>37892857482836437378273</td>
                <td>123</td>
                <td>$33.32</td>
            </tr>
            <tr>
                <td>1/23/2001</td>
                <td>83849</td>
                <td>5</td>
                <td>-$15.00</td>
            </tr>
            <tr>
                <td>9/30/2001</td>
                <td>224747</td>
                <td>14</td>
                <td>$56.78</td>
            </tr>
        </tbody>
    </table>
</div>

Configuration for This Example

You can load the necessary JavaScript and CSS for this example from Yahoo's servers. Click here to load the YUI Dependency Configurator with all of this example's dependencies preconfigured.

YUI Logger Output:

Logger Console

INFO 622ms (+0) 3:43:17 AM:
LogReader  instance0
LogReader initialized

INFO 622ms (+1) 3:43:17 AM:

Get

Appending node: ../../../2.x/build/event-mouseenter/event-mouseenter-min.js

INFO 621ms (+0) 3:43:17 AM:

Get

attempting to load ../../../2.x/build/event-mouseenter/event-mouseenter-min.js

INFO 621ms (+621) 3:43:17 AM:
Get
_next: q0, loaded: undefined

INFO 0ms (+0) 3:43:17 AM:
global
Logger initialized

DataSource Utility Examples:

More DataSource Utility Resources:

User's Guide (external)
API Documentation