How import table from file and rewrite or ignore existed

klymov.inekon

Hello. I create an import from csv and xls file like as editor example. It work fine. But I've met a problem that if I try to upload file with existed primary key - editor give an error. So I create a buttons on dialog "rewrite" and "ignore" repeatable.
I've try to change logic on frontend from editor.create(csv.length, false) to editor.edit(csv.length, false) but it work only with one row.
Button "action" logic can't access upload data to check if values already exist. Anyway it would be better make logic from backend to ignore or update existed data.
how I need to write logic for editor?

rf1234

Sorry, I tried to understand you question, but failed to do so. I even used deepl.com and translated your text into a different language - unsuccessfully so ... The result was incomprehensible as well.

Suggestion: Would you mind writing your problem analysis in your own language and then use e.g. deepl.com to translate it into English, please. Could be helpful

Thank you for your understanding!

allan

If I've understood correctly, you want to check a primary key value to see if it exists in the current DataTable. If it does, update the corresponding row, if not, create a new row. Is that right?

If so, take a look at this thread and in particular the reply by Colin who was working on this for a client back then.

Allan

klymov.inekon

Partially yes. I want a window to pop up when importing where I can choose to ignore the repeated lines or change them. But I think this will be enough for me to understand how to find data in an existing table. Thanks. And sorry for my language)

allan

No worries. Its a complex topic. Hopefully that example will help. Let us know how you get on.

Allan

klymov.inekon

I already at end (thanks thread you have send). but I've met a new problem. first time I call multiset when fill field values for editing

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]);
    }
}

and then call multiset when fill field values for creating

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]);
    }
}

In this case field values not empty before filling values for creating and at backend I have an error that I try to add when it already exist. So my question is - How to clear field value (or multivalue)?

allan

You can clear the multi value by simply calling field().val() - e.g. field.val(''); in this case.

Allan

klymov.inekon

I have an error "Cannot read properties of undefined (reading 'INSERTED')" Inserted is one column in table

klymov.inekon

My mistake. I've found that parsing datetime was wrong. I fix that. But I've met another problem with Datetime. I've send 2 rows of data. When I use multiset - it worked. But when I send data to backend js going throw getFormatter twice with one value. It working only for datetime, other columns are fine.
Maybe some problem in field option?

{
  "getFormatter":    function (val) {
        if (val == "" || val === undefined) return;
        var result = moment(val, 'DD.MM.YYYY HH:mm:ss');
        if(result._isValid) return result.format('yyyy-MM-DDTHH:mm:ss');
        result = moment(val)
        if(result._isValid) return result.format('yyyy-MM-DDTHH:mm:ss');
    },
  "label":"INSERTED",
  "name":"INSERTED",
  "nullDefault":true,
  "setFormatter":    function (val) {
        if (val == "" || val === undefined) return;
        var result = moment(val, 'DD.MM.YYYY HH:mm:ss');
        if(result._isValid) return result.toDate();
        result = moment(val)
        if(result._isValid) return result.toDate();
    },
  "type":"datetime",
  "displayFormat":"DD.MM.YYYY HH:mm:ss",
  "opts":{"firstDay":1}
}

And watch in browser of datetime field property "s"

Maybe error because field.s.multiValue is false? But I've set values by multivalue

klymov.inekon

Finally I've got solution.

function selectColumns(baseEditorOptions, editor: Editor, csv: any[], header: string[], primaryKeysIn: string[]) {
    var selectEditor = new Editor(null);
    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 editorToUpdate = new Editor(baseEditorOptions);
    var table = $(baseEditorOptions.table).DataTable();
    var fieldData;
    var primaryKeys = primaryKeysIn;
    // Function to split the records in the import file into those to update and those to create
    function parseCSV(PKFields: { [key: string]: any }) { 
        //@ts-ignore
        var colData = table.column(0).data({
            order: 'index'
        }).toArray();
        var pos;

        for (var j = 0; j < csv.length; j++) {
            pos = colData.findIndex(col => {
                var keyNames = Object.keys(PKFields);
                return keyNames.every((element, index, array) => {
                    return (col[element] == csv[j][PKFields[element]])
                })
            })
            //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(editor: Editor, editorCreate: Editor) {
        if (toUpdate.length > 0) {
            editor.fields().forEach(s => editor.field(s).hide(false));
            var options = {
                title: 'Confirm update',
                buttons: [
                    {
                        text: 'Yes',
                        action: function (e, json, data, action) {
                            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]);
                                }
                            } 
                            editor.one("postSubmit", function (e, json, data, action, xhr) {
                                createRecords(editorCreate);
                            })
                            editor.submit(null,null,null,false);
                        }
                    },
                    {
                        text: 'No',
                        action: function (e, json, data, action) {
                            createRecords(editorCreate);
                        }
                    }
                ],
                message: `You have ${toUpdate.length} rows with same primary keys values. Do you want overwrite them?`
            } as IFormOptions;
            editor.edit(toUpdateIds, options = options);
        }
    }

    function createRecords(editor: Editor) {
        editor.fields().forEach(s => editor.field(s).hide(false));
        var opts = {
            title: 'Confirm import',
            buttons: 'Confirm',
            message: `Click the <i>Confirm</i> button to confirm the import of <i> ${toCreate.length}  rows of new data. `
        } as IFormOptions
        if (toCreate.length > 0) {
            //@ts-ignore
            editor.create(toCreate.length,opts);

            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({
            //@ts-ignore
            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.',
        onComplete: 'none'
    } as IFormOptions);

    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;
        var obj = {}
        for (const key of primaryKeys) {
            obj[key] = data[editor.field(key).name()];
        }
        parseCSV(obj);

        // If any records to update, do them first
        if (toUpdate.length > 0) {
            // and then queue the creation for after the updates have been submited
            updateRecords(editorToUpdate, editor);
        } else {
            // nothing to update, so just jump in and create the records
            createRecords(editor);
        }
    });
}

The problem with the Datetime type was that during normal editing I went through getFormatter, where I parsed the time from display format (“dd.MM.yyyy HH:mm:ss”) to iso8601. But with multiset the value first went through setFormatter from iso8601 to display, but the csv datetime format was display format, just like when exporting.

my datetime field configuration

{
  "getFormatter":    function (val) {
        if (val === undefined || val === null || val === '') return;
        var result = moment(val, 'DD.MM.YYYY HH:mm:ss');
        if(result._isValid) return result.format('yyyy-MM-DDTHH:mm:ss');
        result = moment(val)
        if(result._isValid) return result.format('yyyy-MM-DDTHH:mm:ss');
    },
  "label":"INSERTED",
  "name":"INSERTED",
  "nullDefault":true,
  "setFormatter":    function (val) {
        if (val === undefined || val === null || val === '') return;

// ===here I add parsing for csv, where data like display format ====
        var result = moment(val, 'DD.MM.YYYY HH:mm:ss');
        if(result._isValid) return result.format('yyyy-MM-DDTHH:mm:ss');
//=============================

        result = moment(val)
        if(result._isValid) return result.toDate();
    },
  "type":"datetime",
  "displayFormat":"DD.MM.YYYY HH:mm:ss",
  "opts":{"firstDay":1}
}

rf1234

Glad you got it sorted

allan

Yes, nice one getting it all working as you need

Allan