Duplicate Entry error when importing with changes.

Mr Arslan Jawed

Hello,

I get Duplicate Entry error when I re-import data with some changes. Is there any way to update the record if changed when importing.

colin

Is this with Editor? I assume you have a unique key which is being imported again.

Colin

Mr Arslan Jawed

Yes, Is there any way to update the record when importing.

colin

I'm going to dump code here - this is something we did for a customer a while back. It's doing what you want, with the unique key in column 22. When you import, if a record already exists, the script modifies it; if the record doesn't exist it creates a new one.

I won't explain much about it here, as our intention is to write a blog post describing this in the next month - but hopefully this will get you going in the right direction. The code is comment so should make sense.

// 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 = $('#LM_Invoicing_programs').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(22).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('concat').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();
        }
    });
}

colin

and part 2:

(function($) {
    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor({
            ajax: '/LM_programss',
            table: '#LM_Invoicing_programs',
            fields: [{
                    "label": "Partner Short Code:",
                    "name": "partner_short_code"
                },
                {
                    "label": "Vendor Name:",
                    "name": "vendor_name"
                },
                {
                    "label": "Vendor ID:",
                    "name": "vendor_id"
                },
                {
                    "label": "Station:",
                    "name": "station"
                },
                {
                    "label": "SP Type:",
                    "name": "sp_type",
                    "type": "radio",
                    "options": [
                        "SP",
                        "Station"
                    ]
                },
                {
                    "label": "Zone:",
                    "name": "zone",
                    "type": "radio",
                    "options": [
                        "North",
                        "South", "East + Central", "West"
                    ]
                },
                {
                    "label": "State:",
                    "name": "state"
                },
                {
                    "label": "City:",
                    "name": "city"
                },
                {
                    "label": "City Type:",
                    "name": "city_type"
                },
                {
                    "label": "PAN Number:",
                    "name": "pan_number"
                },
                {
                    "label": "GSTIN:",
                    "name": "gstin",
                    "type": "radio",
                    "def": "29",
                    "options": ["29"]
                },
                {
                    "label": "Partner Address Invoice:",
                    "name": "partner_address_invoice"
                },
                {
                    "label": "Pincode:",
                    "name": "pincode"
                },
                {
                    "label": "Partner Email:",
                    "name": "partner_email"
                },
                {
                    "label": "Partner Contact:",
                    "name": "partner_contact"
                },
                {
                    "label": "HSN SAC:",
                    "name": "hsn_sac"
                },
                {
                    "label": "PO Number:",
                    "name": "po_number"
                },
                {
                    "label": "Amazon GSTIN:",
                    "name": "amazon_gstin"
                },
                {
                    "label": "Supplier Site:",
                    "name": "supplier_site"
                },
                {
                    "label": "GL Code:",
                    "name": "gl_code"
                },
                {
                    "label": "Requester:",
                    "name": "requester"
                },
                {
                    "label": "Invoice Trigger:",
                    "name": "invoice_trigger",
                    "type": "radio",
                    "options": [
                        "first",
                        "second",
                        "hold"
                    ]
                },
                {
                    "label": "Concat:",
                    "name": "concat"
                },
            ]
        });

        // 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 = $('#LM_Invoicing_programs').DataTable({
            dom: 'Bfrtip',
            ajax: '/LM_programss',
            scrollY: true,
            columns: [{
                    "data": "partner_short_code"
                },
                {
                    "data": "vendor_name"
                },
                {
                    "data": "vendor_id"
                },
                {
                    "data": "station"
                },
                {
                    "data": "sp_type"
                },
                {
                    "data": "zone"
                },
                {
                    "data": "state"
                },
                {
                    "data": "city"
                },
                {
                    "data": "city_type"
                },
                {
                    "data": "pan_number"
                },
                {
                    "data": "gstin"
                },
                {
                    "data": "partner_address_invoice"
                },
                {
                    "data": "pincode"
                },
                {
                    "data": "partner_email"
                },
                {
                    "data": "partner_contact"
                },
                {
                    "data": "hsn_sac"
                },
                {
                    "data": "po_number"
                },
                {
                    "data": "amazon_gstin"
                },
                {
                    "data": "supplier_site"
                },
                {
                    "data": "gl_code"
                },
                {
                    "data": "requester"
                },
                {
                    "data": "invoice_trigger"
                },
                {
                    "data": "concat"
                }
            ],
            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));

carrara.christophe

Hi Colin,
Before posting a case (I am getting the following error message: Uncaught TypeError: table.column(...).data(...) is undefined), I was wondering if there was a plan to publish something on the update on upload.
As well would you have any code for delete on upload?

colin

column().data() is in the main DataTables library - so it would be worth checking your libraries to make sure they're loaded correctly, and also check the column() is able to return the column you're expecting (maybe the selector is incorrect so that's returning undefined perhaps).

We don't have any code examples for that. When would you expect the delete to kick in?

Colin

yskapell

Hello Colin,

I used your code to check for duplicate entries when I import CSV and I get the below error

jquery-3.6.0.js:4059 Uncaught TypeError: Cannot read properties of undefined (reading 'sWidth')
    at HTMLTableCellElement.<anonymous> (jquery.dataTables.min.js:4:37316)
    at Function.each (jquery-3.6.0.js:385:19)
    at jQuery.fn.init.Qt (jquery.dataTables.min.js:4:37250)
    at jquery.dataTables.min.js:4:47231
    at Function.map (jquery-3.6.0.js:467:13)
    at R (jquery.dataTables.min.js:4:47178)
    at y (jquery.dataTables.min.js:4:23396)
    at u (jquery.dataTables.min.js:4:23644)
    at Jt (jquery.dataTables.min.js:4:32708)
    at t (jquery.dataTables.min.js:4:8492)

Can you help?

colin

See thread here for follow up: https://datatables.net/forums/discussion/comment/215850#Comment_215850

Colin