Editor example 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

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

// Use a global for the submit and return data rendering in the examples. // Don't do this outside of the Editor examples! var editor; // 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 $.fn.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.' }); 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 = data[ field.name() ]; for ( var j=0 ; j<csv.length ; j++ ) { field.multiSet( j, csv[j][mapped] ); } } } ); } $(document).ready(function() { // Regular editor for the table editor = new $.fn.dataTable.Editor( { ajax: "../../controllers/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 $.fn.dataTable.Editor( { fields: [ { label: 'CSV file:', name: 'csv', type: 'upload', ajax: function ( files ) { // 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 { uploadEditor.close(); selectColumns( editor, results.data, results.meta.fields ); } } }); } } ] } ); $('#example').DataTable( { dom: 'Bfrtip', ajax: "../../controllers/staff.php", columns: [ { data: 'first_name' }, { data: 'last_name' }, { data: "position" }, { data: "office" }, { data: "start_date" }, { data: "salary", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) } ], select: true, 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', ] } ); } );

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 processing for this table is shown below. Please note that this is just an example script using PHP. Server-side processing scripts can be written in any language, using the protocol described in the DataTables documentation.