Skip duplicate entries on import csv - Editor

yskapell

Hello and Merry Christmas all,

I need a little bit of your help.

what I need is when I import a csv when a line has not changed to skip, if the same line has changes to update, if it is a new one to create a new record.

I find https://datatables.net/forums/discussion/59401/duplicate-entry-error-when-importing-with-changes but it did not work.

I have done it on database level (without the update) but I do not know the variables.

Any idea?

colin

That thread you linked to would be the place to start. The code is there so it just need tailoring to your specific environment. When you say it doesn't work, could you expand on that and say what doesn't work, posting your code and any errors you're seeing,

Colin

yskapell

Hello Colin,

Indeed I did some tailoring.

I post the error on the original thread.

Do you want to paste my code here or on the original thread?

colin

I'd say post here, as slightly different issue to the other thread,

Colin

yskapell

Hello collin,

My code is the below

// 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();
 
    var toUpdate = []; // array of records to update
    var toUpdateIds = []; // array of the IDs of the records to update
    var toCreate = []; // array of the records to create
 
    var table = $('#vocabulary').DataTable();
    var fieldData;
 
    // Function to split the records in the import file into those to update and those to create
    function parseCSV(PKField) {
        // first_name is column position 0 in our test environment
        var colData = table.column(1).data({
            order: 'index'
        }).toArray();
        var pos;
 
        for (var j = 0; j < csv.length; j++) {
            pos = colData.indexOf(csv[j][PKField]);
            if (pos > -1) {
                // it exists, so we need to add to update list
                toUpdate.push(csv[j]);
                toUpdateIds.push(pos);
            } else {
                // doesn't exist, so add to create list
                toCreate.push(csv[j]);
            }
        }
    }
 
    // function that updates the existing records
    function updateRecords() {
        if (toUpdate.length > 0) {
            editor.edit(toUpdateIds, {
                title: 'Confirm update',
                buttons: 'Update',
                message: 'Click the <i>Submit</i> button to confirm the update of ' + toUpdate.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 = fieldData[field.name()];
 
                for (var j = 0; j < toUpdate.length; j++) {
                    field.multiSet(table.row(toUpdateIds[j]).id(), toUpdate[j][mapped]);
                }
            }
        }
    }
 
    // function to create the records (same as the example on website)
    function createRecords() {
        if (toCreate.length > 0) {
            editor.create(toCreate.length, {
                title: 'Confirm import',
                buttons: 'Submit',
                message: 'Click the <i>Submit</i> button to confirm the import of ' + toCreate.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 = fieldData[field.name()];
 
                for (var j = 0; j < toCreate.length; j++) {
                    field.multiSet(j, toCreate[j][mapped]);
                }
            }
        }
    }
 
    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.
        fieldData = data;
 
        // using first_name as the PK in this example
        parseCSV(data[editor.field('ideogram').name()]);
 
        // If any records to update, do them first
        if (toUpdate.length > 0) {
            // and then queue the creation for after the updates have been submited
            editor.one('submitComplete', function() {
                createRecords();
            });
            updateRecords();
        } else {
            // nothing to update, so just jump in and create the records
            createRecords();
        }
    });
}

(function($) {
    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor({
            ajax: 'editor/extension/staff.php',
            table: '#vocabulary',
            fields: [ {
                label: "Ideogram:",
                name: "ideogram"
            }, {
                label: "pinyin:",
                name: "pinyin"
            }, {
                label: "type:",
                name: "type"
            }, {
                label: "meaning:",
                name: "meaning"
            }
        ]
        });
 
        // 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) {
                                uploadEditor.field('csv').error('CSV parsing error: ' + results.errors[0].message);
                            } else {
                                uploadEditor.close();
                                selectColumns(editor, results.data, results.meta.fields);
                            }
                        }
                    });
                }
            }]
        });
 
 
        var table = $('#vocabulary').DataTable({
            dom: 'Bfrtip',
            ajax: 'editor/extension/staff.php',
            scrollY: true,
            columns: [
                { data: "ideogram" },
                { data: "pinyin" },
                { data: "type" },
                { data: "meaning" }
            ],
            select: true,
            lengthChange: false,
            scrollX: true,
            scrollCollapse: true,
            buttons: [{
                    extend: 'create',
                    editor: editor
                },
                {
                    extend: 'edit',
                    editor: editor
                },
                {
                    extend: 'remove',
                    editor: editor
                },
                {
                    extend: 'collection',
                    text: 'Export',
                    buttons: ['csv']
                },
                {
                    text: 'Import CSV',
                    action: function() {
                        uploadEditor.create({
                            title: 'CSV file import'
                        })
                    }
                }
            ]
        })
    });
 
    }(jQuery));

yskapell

When I run it I get the below error on firefox

Error in parsing value for ‘max-height’.  Declaration dropped. dashboard.php

jQuery.Deferred exception: n.aoColumns[r] is undefined Qt/<@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:37287
each@https://code.jquery.com/jquery-3.6.0.js:385:19
Qt@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:37250
R/r<@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:47231
map@https://code.jquery.com/jquery-3.6.0.js:467:13
R@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:47178
y@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:23396
u@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:23644
Jt@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:32708
t@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:8492
C/<@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:8578
each@https://code.jquery.com/jquery-3.6.0.js:385:19
each@https://code.jquery.com/jquery-3.6.0.js:207:17
C@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:3487
P.fn.DataTable@https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js:4:86116
@https://learnchinese.kaiqiaozhi.space/admin/dashboard.php line 133 > injectedScript:166:38
mightThrow@https://code.jquery.com/jquery-3.6.0.js:3766:29
Deferred/then/resolve/</process<@https://code.jquery.com/jquery-3.6.0.js:3834:12
setTimeout handler*Deferred/then/resolve/<@https://code.jquery.com/jquery-3.6.0.js:3872:16
fire@https://code.jquery.com/jquery-3.6.0.js:3500:31
add@https://code.jquery.com/jquery-3.6.0.js:3559:7
Deferred/then/<@https://code.jquery.com/jquery-3.6.0.js:3892:24
Deferred@https://code.jquery.com/jquery-3.6.0.js:3983:9
then@https://code.jquery.com/jquery-3.6.0.js:3877:20
jQuery.fn.ready@https://code.jquery.com/jquery-3.6.0.js:4072:4
@https://learnchinese.kaiqiaozhi.space/admin/dashboard.php line 133 > injectedScript:120:17
@https://learnchinese.kaiqiaozhi.space/admin/dashboard.php line 133 > injectedScript:209:3
DOMEval@https://code.jquery.com/jquery-3.6.0.js:133:12
domManip@https://code.jquery.com/jquery-3.6.0.js:6114:15
append@https://code.jquery.com/jquery-3.6.0.js:6250:10
html/<@https://code.jquery.com/jquery-3.6.0.js:6344:18
access@https://code.jquery.com/jquery-3.6.0.js:4171:8
html@https://code.jquery.com/jquery-3.6.0.js:6311:10
jQuery.fn.load/<@https://code.jquery.com/jquery-3.6.0.js:10398:9
fire@https://code.jquery.com/jquery-3.6.0.js:3500:31
fireWith@https://code.jquery.com/jquery-3.6.0.js:3630:7
done@https://code.jquery.com/jquery-3.6.0.js:9796:14
send/callback/<@https://code.jquery.com/jquery-3.6.0.js:10057:17
EventHandlerNonNull*send@https://code.jquery.com/jquery-3.6.0.js:10076:18
ajax@https://code.jquery.com/jquery-3.6.0.js:9690:15
jQuery.fn.load@https://code.jquery.com/jquery-3.6.0.js:10384:10
@https://learnchinese.kaiqiaozhi.space/admin/dashboard.php:78:19
dispatch@https://code.jquery.com/jquery-3.6.0.js:5430:27
add/elemData.handle@https://code.jquery.com/jquery-3.6.0.js:5234:28
EventListener.handleEvent*add@https://code.jquery.com/jquery-3.6.0.js:5282:12
on/<@https://code.jquery.com/jquery-3.6.0.js:5182:16
each@https://code.jquery.com/jquery-3.6.0.js:385:19
each@https://code.jquery.com/jquery-3.6.0.js:207:17
on@https://code.jquery.com/jquery-3.6.0.js:5181:14
on@https://code.jquery.com/jquery-3.6.0.js:5906:10
jQuery.fn[name]@https://code.jquery.com/jquery-3.6.0.js:10745:10
@https://learnchinese.kaiqiaozhi.space/admin/dashboard.php:76:23
mightThrow@https://code.jquery.com/jquery-3.6.0.js:3766:29
Deferred/then/resolve/</process<@https://code.jquery.com/jquery-3.6.0.js:3834:12
setTimeout handler*Deferred/then/resolve/<@https://code.jquery.com/jquery-3.6.0.js:3872:16
fire@https://code.jquery.com/jquery-3.6.0.js:3500:31
fireWith@https://code.jquery.com/jquery-3.6.0.js:3630:7
fire@https://code.jquery.com/jquery-3.6.0.js:3638:10
fire@https://code.jquery.com/jquery-3.6.0.js:3500:31
fireWith@https://code.jquery.com/jquery-3.6.0.js:3630:7
ready@https://code.jquery.com/jquery-3.6.0.js:4110:13
completed@https://code.jquery.com/jquery-3.6.0.js:4120:9
EventListener.handleEvent*@https://code.jquery.com/jquery-3.6.0.js:4136:11
@https://code.jquery.com/jquery-3.6.0.js:36:10
@https://code.jquery.com/jquery-3.6.0.js:40:4
 undefined jquery-3.6.0.js:4050:18

Uncaught TypeError: n.aoColumns[r] is undefined
    jQuery 15
    <anonymous> https://learnchinese.kaiqiaozhi.space/admin/dashboard.php line 133 > injectedScript:166
    jQuery 9
    <anonymous> https://learnchinese.kaiqiaozhi.space/admin/dashboard.php line 133 > injectedScript:120
    <anonymous> https://learnchinese.kaiqiaozhi.space/admin/dashboard.php line 133 > injectedScript:209
    jQuery 14
    <anonymous> https://learnchinese.kaiqiaozhi.space/admin/dashboard.php:78
    jQuery 9
    <anonymous> https://learnchinese.kaiqiaozhi.space/admin/dashboard.php:76
    jQuery 13
jquery.dataTables.min.js:4:37287

Thank you

allan

Suggests to me that there is a mismatch in the columns defined. What is on this line?

dashboard.php line 133 > injectedScript:166

Allan

yskapell

It is a div on which I load the page I need via ajax

yskapell

I found the issue and I tried to import a csv file.

Well it import all the records and did not check for duplicate

yskapell

If it is not easy to skip duplicates on csv import, then the other way to do it is on database level, but I will need to know the variables for the sql query

yskapell

Hello all,

I fixed your code for a better check of duplicate entries.

indexof will always return -1 if the record exists in an array so I changed

          pos = colData.indexOf(csv[j][PKField]);
            if (pos > -1) {
                // it exists, so we need to add to update list
                toUpdate.push(csv[j]);
                toUpdateIds.push(pos);
            } else {
                // doesn't exist, so add to create list
                toCreate.push(csv[j]);
            }

to

        for (var j = 0; j < csv.length; j++) {
            if (colData.includes(csv[j][PKField]) !== -1) {
                console.log("Data found");
                continue;
            } else {
                // doesn't exist, so add to create list
                toCreate.push(csv[j]);
            }

As result I get in console "Data found" 3 times. I do not need to update the entries that already exists.

yskapell

It is not working as expected. If you have a new value in csv it will not try to insert it on the table.

the results:

POS: -1 ID found: 1371 
POS: -1 ID found: 1450 
POS: -1 ID found: 1485 
POS: -1 ID found: 99999 

last one is new as you can see

kthorngren

Please build a test case showing what you currently have with an example CSV file that replicates the issue. This will allow us to help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

You can start with this base Editor example:
http://live.datatables.net/guwafemu/178/edit

Kevin

yskapell

Hello all,

I have add my code here http://live.datatables.net/guwafemu/328/edit

On the csv I have 2 new rows but it add only the last one.

My CSV is the below

id,ideogram,pinyin,type,meaning
1329,多,duō,ΕΠΙΘ,πολύ
1330,多,duō,ΕΠΙΘ,πολύ
1331,多,duō,ΕΠΙΘ,πολύ
1332,多,duō,ΕΠΙΘ,πολύ
5,多,duō,ΕΠΙΘ,πολύ
6,多,duō,ΕΠΙΘ,πολύ

kthorngren

I fixed a few issues with the test case. You had some extra HTML (Datatables related) that made the test case confusing. You didn't load the PapaParse library and I removed the Editor ajax config:

//ajax: "editor/extension/staff.php",

Now the test case runs.

The first problem in your code is you are getting the data from column 0 which is the select checkbox column. You need to change to column 1 like this:

                function parseCSV(PKField) {
                    var colData = table.column(1).data().toArray();

THe other problem is you are overwriting the toCreate variable being used to create the new rows. I commented out this code in the parseCSV() function and changed to this:

                        if ( ! colData.includes(get_CSV_ID) ) {
                          toCreate.push(csv[j]);  // Create this record
                          console.log(toCreate);
                          colData.push(get_CSV_ID);  // Keep track of created records in case of duplicates in CSV
                    
                        }

The colData.push(get_CSV_ID) statement is used to make sure duplicates in the CSV aren't added to the table.

It seems to be working now:
http://live.datatables.net/guwafemu/331/edit

Kevin

yskapell

Hello Kevin,

I add the fix to my code and I get to import 6 instead of 2!!!