Reputation: 303
HTML table with 250,000 rows - a better way?
I have a csv file with 250,000 rows (Date, Company, Account, Amount) - exported from Excel. This is updated monthly.
I am looking to load the data into a html table to display, based on a search / filter - ideally with auto complete - searching the Company field. This would then only display the data for that company.
250k seems too much to load into a table and then filter the output - so... what is the best way to go about this? DataTables keeps coming up - but I don't know where to start with getting the data from the datafile.
Upvotes: 3
Views: 1252
Answers (5)
Reputation: 5689
Given a CSV file like this:
Date,Company,Account,Amount
2017-01-16,Jast-Bergnaum,Sales,94.31
2017-02-08,Upton and Sons,Research and Development,11.01
2016-12-21,Medhurst-Erdman,Business Development,74.67
2016-10-10,"Skiles, Hermiston and Goodwin",Marketing,77.0
2016-10-04,"Moore, Deckow and King",Legal,13.86
2017-02-12,Volkman-Jerde,Business Development,32.79
2017-03-21,Purdy Group,Accounting,33.18
2016-12-27,"Bernier, Rice and Toy",Support,4.73
2016-09-28,Corkery-Orn,Legal,41.73
2017-04-05,Zemlak-Fisher,Product Management,28.03
Then this code should do what you need:
let inputElement = document.getElementById("input");
let tableData = null;
let example = null;
let handleFiles = event => {
if ($.fn.DataTable.isDataTable("#example")) {
example.destroy();
$('#example').empty();
}
alasql('SELECT * FROM FILE(?,{headers:true})', [event], (data) => {
tableData = data;
createTable();
});
};
let createTable = () => {
example = $("#example").DataTable({
"columns": [{
"title": "Date",
"data": "Date"
}, {
"title": "Company",
"data": "Company"
}, {
"title": "Account",
"data": "Account"
}, {
"title": "Amount",
"data": "Amount"
}],
"serverSide": true,
"ajax": (data, callback, settings) => {
let fieldNames = [];
let whereClause = [];
let filterQuery = "SELECT count(*) AS recordsFiltered FROM ?";
let orderBy = [];
for (let i of data.columns) {
fieldNames.push(i.data);
}
let query = `SELECT ${fieldNames.join(", ")} FROM ?`;
if (data.search.value !== "") {
query += " WHERE ";
filterQuery += " WHERE ";
for (let i of data.columns) {
whereClause.push(i.data + " LIKE '%" + data.search.value + "%'");
}
filterQuery += whereClause.join(' OR ');
query += whereClause.join(' OR ');
}
query += " ORDER BY ";
for (let i of data.order) {
orderBy.push(fieldNames[i.column] + " " + i.dir.toUpperCase());
}
query += orderBy.join(', ');
query += " LIMIT " + data.length + " OFFSET " + data.start;
let returnData = {
"draw": data.draw,
};
alasql('SELECT count(*) AS recordsTotal FROM ?', [tableData], (data, err) => {
returnData.recordsTotal = data[0].recordsTotal;
alasql(filterQuery, [tableData], (data, err) => {
returnData.recordsFiltered = data[0].recordsFiltered;
alasql(query, [tableData], (data, err) => {
returnData.data = data;
callback(
returnData
)
});
});
});
}
})
};
inputElement.addEventListener("change", handleFiles, false);
Thanks for the problem, it's been fun working up a solution. I've tried it with files with 300000 rows and things seem to be working fine, there's a slight delay but it's not huge and it loads the data really quickly on upload of the files.
Hope that helps. Working JSFiddle here. Just use the CSV file above to test it.
For a static file in the same directory this should work:
let tableData = null;
let example = null;
$.blockUI();
let timer = {
start: moment()
};
alasql(`
SELECT
*
FROM FILE("MOCK_DATA.csv", {
headers:true
})`, [event], data => {
tableData = data;
$.unblockUI();
timer.end = moment();
let duration = moment.duration(timer.end.diff(timer.start));
console.log("timer", timer);
console.log("duration", duration.asSeconds());
createTable();
});
let createTable = () => {
example = $("#example").DataTable({
"columns": [
{
"title": "Date",
"data": "Date"
}, {
"title": "Company",
"data": "Company"
}, {
"title": "Account",
"data": "Account"
}, {
"title": "Amount",
"data": "Amount"
}
],
"serverSide": true,
"ajax": (data, callback, settings) => {
let filterQuery = `
SELECT
count(*) AS recordsFiltered
FROM
?${data.search.value !== "" ? `
WHERE
${data.columns.map(i => `${i.data}
LIKE
'%${data.search.value}%'`).join(`
OR
`)};` : `;`}`;
let query = `
SELECT
${data.columns.map(i => i.data).join(`,
`)}
FROM
?${data.search.value !== "" ? `
WHERE
${data.columns.map(i => `${i.data}
LIKE
'%${data.search.value}%'`).join(`
OR
`)}` : ``}
ORDER BY
${data.order.map(i => data.columns.map(i => i.data)[i.column] + " " + i.dir.toUpperCase()).join(`,
`)}
LIMIT
${data.length}
OFFSET
${data.start};`;
let returnData = {
"draw": data.draw,
};
alasql(`
SELECT
count(*) AS recordsTotal
FROM
?`, [tableData], (data, err) => {
returnData.recordsTotal = data[0].recordsTotal;
alasql(filterQuery, [tableData], (data, err) => {
returnData.recordsFiltered = data[0].recordsFiltered;
alasql(query, [tableData], (data, err) => {
returnData.data = data;
callback(
returnData
)
});
});
});
}
})
};
Upvotes: 1
Reputation: 5689
I'd think about using a combination of local storage and PapaParse, using WebSQL.
- Upload the CSV to the page using the FileSystem API.
- Parse the data with PapaParse.
- Store the parsed data using something like the now depreciated WebSQL.
- Use WebSQL to populate your Table.
Heavy hit to start with but after that, you're away.
Looks like a fun project though! Lucky you! :-D
I've had a play and come up with this:
let mydb = null;
let tableMetaData = {};
let inputElement = document.getElementById("input");
let elem = document.getElementById("myBar");
let width = 0;
let csvs = ["text/csv", "application/vnd.ms-excel"];
let intervalId = null;
let example = null;
elem.style.width = '0%';
//Test for browser compatibility
if (window.openDatabase) {
/*
* Create the database the parameters are:
* 1. the database name
* 2.version number
* 3. a description
* 4. the size of the database (in bytes) 14 * 1024 x 1024 = 14MB
*/
mydb = openDatabase(
"accounts_db",
"0.1",
"A Database of accounts",
14 * 1024 * 1024);
} else {
alert("WebSQL is not supported by your browser!");
}
let handleFiles = () => {
let file = inputElement.files[0];
if ($.fn.DataTable.isDataTable("#example")) {
example.destroy();
$('#example').empty();
}
elem.style.width = '0%';
if (window.File && window.FileReader && window.FileList && window.Blob) {
if (file) {
if (csvs.indexOf(file.type) === -1) {
alert("Please only upload CSV files.")
} else {
mydb.transaction((t) => {
t.executeSql("DROP TABLE accounts",
null,
t => {
t.executeSql("CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY AUTOINCREMENT, Date TEXT, Company TEXT, Account TEXT, Amount REAL)",
null,
null,
null);
},
t => {
t.executeSql("CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY AUTOINCREMENT, Date TEXT, Company TEXT, Account TEXT, Amount REAL)",
null,
null,
null);
}
);
});
$.blockUI();
let timer = {
start: moment()
};
Papa.parse(file, {
"download": true,
"header": true,
"dynamicTyping": true,
"complete": results => {
tableMetaData.total = results.data.length;
tableMetaData.columns = results.meta.fields.map(c => ({
"title": c,
"data": c,
"visible": c.toLowerCase() !== "id",
"default": ""
}));
tableMetaData.columns.unshift({
"title": "id",
"data": "id",
"visible": false,
});
for (let record of results.data) {
mydb.transaction(
t => {
t.executeSql("INSERT INTO accounts (Date, Company, Account, Amount) VALUES (?, ?, ?, ?)", [
record.Date,
record.Company,
record.Account,
record.Amount
],
t => {
let query = "SELECT COUNT(*) AS total FROM accounts";
t.executeSql(
query,
null,
(t, result) => {
elem.style.width = ((~~result.rows[0].total / tableMetaData.total) * 100) + '%';
if (~~result.rows[0].total === tableMetaData.total) {
clearInterval(intervalId);
$.unblockUI();
timer.end = moment();
let duration = moment.duration(timer.end.diff(timer.start));
console.log("timer", timer);
console.log("duration", duration.asSeconds());
createTable();
}
},
null
);
},
null
);
}
);
}
}
});
}
}
}
};
let createTable = () => {
example = $("#example").DataTable({
"columns": tableMetaData.columns,
"serverSide": true,
"ajax": (data, callback, settings) => {
let fieldNames = [];
let whereClause = [];
let filterQuery = "SELECT count(*) AS recordsFiltered FROM accounts";
let orderBy = [];
for (let i of data.columns) {
fieldNames.push(i.data);
}
let query = `SELECT ${fieldNames.join(", ")} FROM accounts`;
if (data.search.value !== "") {
query += " WHERE ";
filterQuery += " WHERE ";
for (let i of data.columns) {
whereClause.push(i.data + " LIKE '%" + data.search.value + "%'");
}
filterQuery += whereClause.join(' OR ');
query += whereClause.join(' OR ');
}
query += " ORDER BY ";
for (let i of data.order) {
orderBy.push(fieldNames[i.column] + " " + i.dir.toUpperCase());
}
query += orderBy.join(', ');
query += " LIMIT " + data.length + " OFFSET " + data.start;
let returnData = {
"draw": data.draw,
};
mydb.transaction((t) => {
t.executeSql(
`SELECT count(*) AS recordsTotal FROM accounts`,
null,
(t, result) => {
returnData.recordsTotal = result.rows[0].recordsTotal;
t.executeSql(
filterQuery,
null,
(t, result) => {
returnData.recordsFiltered = result.rows[0].recordsFiltered;
t.executeSql(
query,
null,
(t, result) => {
returnData.data = [];
for (let i = 0; i < result.rows.length; i++) {
returnData.data.push(result.rows[i]);
}
callback(
returnData
)
},
null
)
},
null
)
}
)
});
}
})
};
inputElement.addEventListener("change", handleFiles, false);
As I thought though, it's a huge hit populating the WebSQL table, especially as you can't do anything with the data until it's all there so you need to check. Once it's loaded though, it's really very fast - much faster than I would've expected and faster than my other answer using AlaSQL.
I guess it's a trade-off though. Faster data loading using AlaSQL with slightly slower queries or slower loading and lightning fast queries with WebSQL. I guess that the AlaSQL method wins though, the queries are slower but the speed of loading the data more than makes up for it.
Working JSFiddle here.
Upvotes: 0
Reputation: 509
In the html add a blank table...
<table id="datatable">
<thead>
<th>Your coloumn headers</th>
</thead>
<tbody>
</tbody>
</table>
Then in the javascript make it a datatable, (you will need to include the jquery and datatable libraries if you haven't already). You can then also load the data file with ajax. This example is loading a html source, not csv, but the principle is similar...
$(document).ready(function() {
var table = $('#datatable').DataTable();
table.draw();
// Use AJAX to append rows to the table...
$.get(urlOfYourSourceFile,function( data ) {
if(data.indexOf('<tr') == 0) {
table = $("#applicant_table").DataTable();
table.rows.add($(data)).draw();
}
});
}
Upvotes: 0
Reputation: 1962
It seems that your scenario is a client that wants to export an excel file, that they update constantly, and be able to keep it up to date. I have seen quite a few times, while this is not ideal I understand that the situation is not going to change a lot right now.
What I will do will be to use datatables, as yo say, and then apply that file as the source of the datatables. For that there are plenty of examples out there (csv2table comes to mind)
Upvotes: 0
Reputation:
Your best bet is to load the data into some database and look at using JavaScript to filter and to organize it for you.
Asynchronous Ajax will help you plenty with the auto-complete when searching.
Upvotes: 1
Related Questions
- HTML. JQuery. Tables with 4 000 rows and more
- Rendering Huge Dataset from API to Table
- how to do server-side processing on large dataset on jquery datatables
- DataTables performance for big table
- HTML Table with thousands of rows
- How to optimize jquery datatable to load large data efficiently (10k-50k rows)?
- Best way to construct big html table
- rendering large server-side datasets in jquery datatables
- How to use DataTables with 100000 records?
- How to improve performance/speed of DOM jquery DataTable with 9,999 rows?