CSV import

This is one of the more complex examples in the Editor suite, but it shows the power of using the Editor's multi-row editing capability. Here we make use of three different Editor instances:

  1. To upload a local file, which is then parsed as CSV by the excellent Papa Parse library.
  2. Select which columns from the CSV file you wish to apply to each column in your DataTable columns (allowing for the CSV file to be in a different order from the DataTable).
  3. A "normal" Editor instance which is used to submit the multiple rows from the CSV file in a single request. This can of course be used to create, edit and delete rows on an individual basis by the end user like any other Editor instance.

For ease of use in the demonstration, we make use of the Buttons extension to allow CSV export of data from the table, which can then immediately be imported using the import functionality provided here. Select all and Select none buttons are also available which is handy for deleting the data from the whole table, before an import (if you so wish).

First name Last name Position Office Start date Salary
First name Last name Position Office Start date Salary
  • Javascript
  • HTML
  • CSS
  • Ajax
  • Server-side script
  • Comments

The Javascript shown below is used to initialise the table shown in this example:

// Display an Editor form that allows the user to pick the CSV data to apply to each column function selectColumns(editor, csv, header) { var selectEditor = new DataTable.Editor(); var fields = editor.order(); for (var i = 0; i < fields.length; i++) { var field = editor.field(fields[i]); selectEditor.add({ label: field.label(), name: field.name(), type: 'select', options: header, def: header[i] }); } selectEditor.create({ title: 'Map CSV fields', buttons: 'Import ' + csv.length + ' records', message: 'Select the CSV column you want to use the data from for each field.', onComplete: 'none' }); selectEditor.on('submitComplete', function (e, json, data, action) { // Use the host Editor instance to show a multi-row create form allowing the user to submit the data. editor.create(csv.length, { title: 'Confirm import', buttons: 'Submit', message: 'Click the <i>Submit</i> button to confirm the import of ' + csv.length + ' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.' }); for (var i = 0; i < fields.length; i++) { var field = editor.field(fields[i]); var mapped = DataTable.util.get(field.name())(data); for (var j = 0; j < csv.length; j++) { field.multiSet(j, csv[j][mapped]); } } }); } // Regular editor for the table var editor = new DataTable.Editor({ ajax: '../php/staff.php', table: '#example', fields: [ { label: 'First name:', name: 'first_name' }, { label: 'Last name:', name: 'last_name' }, { label: 'Position:', name: 'position' }, { label: 'Office:', name: 'office' }, { label: 'Start date:', name: 'start_date', type: 'datetime' }, { label: 'Salary:', name: 'salary' } ] }); // Upload Editor - triggered from the import button. Used only for uploading a file to the browser var uploadEditor = new DataTable.Editor({ fields: [ { label: 'CSV file:', name: 'csv', type: 'upload', ajax: function (files, done) { // Ajax override of the upload so we can handle the file locally. Here we use Papa // to parse the CSV. Papa.parse(files[0], { header: true, skipEmptyLines: true, complete: function (results) { if (results.errors.length) { console.log(results); uploadEditor .field('csv') .error('CSV parsing error: ' + results.errors[0].message); } else { selectColumns(editor, results.data, results.meta.fields); } // Tell Editor the upload is complete - the array is a list of file // id's, which the value of doesn't matter in this case. done([0]); } }); } } ] }); $('#example').DataTable({ ajax: '../php/staff.php', columns: [ { data: 'first_name' }, { data: 'last_name' }, { data: 'position' }, { data: 'office' }, { data: 'start_date' }, { data: 'salary', render: DataTable.render.number(null, null, 0, '$') } ], layout: { topStart: { buttons: [ { extend: 'create', editor: editor }, { extend: 'edit', editor: editor }, { extend: 'remove', editor: editor }, { extend: 'csv', text: 'Export CSV', className: 'btn-space', exportOptions: { orthogonal: null } }, { text: 'Import CSV', action: function () { uploadEditor.create({ title: 'CSV file import' }); } }, { extend: 'selectAll', className: 'btn-space' }, 'selectNone' ] } }, select: true });
// Display an Editor form that allows the user to pick the CSV data to apply to each column function selectColumns(editor, csv, header) { let selectEditor = new DataTable.Editor(); let fields = editor.order(); for (let i = 0; i < fields.length; i++) { let field = editor.field(fields[i]); selectEditor.add({ label: field.label(), name: field.name(), type: 'select', options: header, def: header[i] }); } selectEditor.create({ title: 'Map CSV fields', buttons: 'Import ' + csv.length + ' records', message: 'Select the CSV column you want to use the data from for each field.', onComplete: 'none' }); selectEditor.on('submitComplete', function (e, json, data, action) { // Use the host Editor instance to show a multi-row create form allowing the user to submit the data. editor.create(csv.length, { title: 'Confirm import', buttons: 'Submit', message: 'Click the <i>Submit</i> button to confirm the import of ' + csv.length + ' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.' }); for (var i = 0; i < fields.length; i++) { let field = editor.field(fields[i]); let mapped = DataTable.util.get(field.name())(data); for (let j = 0; j < csv.length; j++) { field.multiSet(j, csv[j][mapped]); } } }); } // Regular editor for the table const editor = new DataTable.Editor({ ajax: '../php/staff.php', table: '#example', fields: [ { label: 'First name:', name: 'first_name' }, { label: 'Last name:', name: 'last_name' }, { label: 'Position:', name: 'position' }, { label: 'Office:', name: 'office' }, { label: 'Start date:', name: 'start_date', type: 'datetime' }, { label: 'Salary:', name: 'salary' } ] }); // Upload Editor - triggered from the import button. Used only for uploading a file to the browser const uploadEditor = new DataTable.Editor({ fields: [ { label: 'CSV file:', name: 'csv', type: 'upload', ajax: function (files, done) { // Ajax override of the upload so we can handle the file locally. Here we use Papa // to parse the CSV. Papa.parse(files[0], { header: true, skipEmptyLines: true, complete: function (results) { if (results.errors.length) { console.log(results); uploadEditor .field('csv') .error('CSV parsing error: ' + results.errors[0].message); } else { selectColumns(editor, results.data, results.meta.fields); } // Tell Editor the upload is complete - the array is a list of file // id's, which the value of doesn't matter in this case. done([0]); } }); } } ] }); const table = new DataTable('#example', { ajax: '../php/staff.php', columns: [ { data: 'first_name' }, { data: 'last_name' }, { data: 'position' }, { data: 'office' }, { data: 'start_date' }, { data: 'salary', render: DataTable.render.number(null, null, 0, '$') } ], layout: { topStart: { buttons: [ { extend: 'create', editor: editor }, { extend: 'edit', editor: editor }, { extend: 'remove', editor: editor }, { extend: 'csv', text: 'Export CSV', className: 'btn-space', exportOptions: { orthogonal: null } }, { text: 'Import CSV', action: function () { uploadEditor.create({ title: 'CSV file import' }); } }, { extend: 'selectAll', className: 'btn-space' }, 'selectNone' ] } }, select: true });

In addition to the above code, the following Javascript library files are loaded for use in this example:

    The HTML shown below is the raw HTML table element, before it has been enhanced by DataTables:

    This example uses a little bit of additional CSS beyond what is loaded from the library files (below), in order to correctly display the table. The additional CSS used is shown below:

    button.btn-space { margin-left: 1em; }

    The following CSS library files are loaded for use in this example to provide the styling of the table:

      This table loads data by Ajax. The latest data that has been loaded is shown below. This data will update automatically as any additional data is loaded.

      The script used to perform the server-side interaction for this demo is shown below. This server uses PHP, so the PHP script is shown, however our download packages include the equivalent script for other platforms, including .NET and Node.js. Server-side scripts can be written in any language, using the protocol described in the Editor documentation.

      Other examples