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:

To upload a local file, which is then parsed as CSV by the excellent Papa Parse library.
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).
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',
					footer: false,
					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.

CSV import

Other examples

Simple initialisation

Advanced initialisation

DataTables as an input

Dates and time

DataTables extensions

Inline editing

Bubble editing

API

Standalone

Styling

Plug-ins

DataTables