Skip duplicate entries on import csv - Editor

Skip duplicate entries on import csv - Editor

yskapellyskapell Posts: 44Questions: 12Answers: 3
edited December 2022 in Free community support

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?

This question has accepted answers - jump to:

Answers

  • colincolin Posts: 15,095Questions: 1Answers: 2,581

    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

  • yskapellyskapell Posts: 44Questions: 12Answers: 3

    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?

  • colincolin Posts: 15,095Questions: 1Answers: 2,581

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

    Colin

  • yskapellyskapell Posts: 44Questions: 12Answers: 3

    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));
    
  • yskapellyskapell Posts: 44Questions: 12Answers: 3

    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

  • allanallan Posts: 61,100Questions: 1Answers: 9,964 Site admin

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

    dashboard.php line 133 > injectedScript:166
    

    Allan

  • yskapellyskapell Posts: 44Questions: 12Answers: 3
    edited December 2022

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

  • yskapellyskapell Posts: 44Questions: 12Answers: 3

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

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

  • yskapellyskapell Posts: 44Questions: 12Answers: 3

    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

  • yskapellyskapell Posts: 44Questions: 12Answers: 3
    Answer ✓

    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.

  • yskapellyskapell Posts: 44Questions: 12Answers: 3

    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

  • kthorngrenkthorngren Posts: 19,983Questions: 26Answers: 4,703
    edited January 2023

    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

  • yskapellyskapell Posts: 44Questions: 12Answers: 3

    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ō,ΕΠΙΘ,πολύ

  • kthorngrenkthorngren Posts: 19,983Questions: 26Answers: 4,703
    Answer ✓

    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

  • yskapellyskapell Posts: 44Questions: 12Answers: 3

    Hello Kevin,

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

Sign In or Register to comment.