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: 6,899Questions: 0Answers: 1,179

    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: 6,899Questions: 0Answers: 1,179

    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: 6,899Questions: 0Answers: 1,179

    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));
Sign In or Register to comment.