Duplicate Entry error when importing with changes.

Duplicate Entry error when importing with changes.

Mr Arslan JawedMr Arslan Jawed Posts: 9Questions: 6Answers: 1

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.

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

    Colin

  • Mr Arslan JawedMr Arslan Jawed Posts: 9Questions: 6Answers: 1

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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();
            }
        });
    }
    
  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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.christophecarrara.christophe Posts: 6Questions: 2Answers: 0

    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?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • yskapellyskapell Posts: 47Questions: 14Answers: 3

    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?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
Sign In or Register to comment.