Insert unique code to a field while import csv file

Insert unique code to a field while import csv file

SarbastSarbast Posts: 85Questions: 0Answers: 0

Hi,
I have two php functions which generate a unique code, so I use these function with the datatables editor and it work fine, but when import data as csv file the field of unique code get the same code and when add manually the code would be unique.

Note: I added the two functions at the begining of the server site file and use "setValue( generateKey() )" to call function for unique code and save into db.

Thanks in advance

Replies

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

    Could you post your code, please, so we can take a look.

    Colin

  • SarbastSarbast Posts: 85Questions: 0Answers: 0
    edited January 2021

    Hi colin,

    Here my full code:

    client side:

    <script type="text/javascript" language="javascript" class="init">
        
    
    
    var editor; // use a global for the submit and return data rendering in the examples
    
    // 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 = $('#students').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(0).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('student_ku').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();
            }
        });
    }
    
    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor( {
            "ajax": "../controllers/students.php",
            "table": "#students",
            "fields": [ 
                 {
                    "label": "Student (Ku.):",
                    "name": "student_ku"
                 },{
                    "label": "Student (Ar.):",
                    "name": "student_ar"
                 },{
                    "label": "Student (En.):",
                    "name": "student_en"
                 }
            ]
        } );
    
    // Buttons array definition to create previous, save and next buttons in
        // an Editor form
        var backNext = [
            {
                text: "&lt;",
                action: function (e) {
                    // On submit, find the currently selected row and select the previous one
                    this.submit( function () {
                        var indexes = table.rows( {search: 'applied'} ).indexes();
                        var currentIndex = table.row( {selected: true} ).index();
                        var currentPosition = indexes.indexOf( currentIndex );
    
                        if ( currentPosition > 0 ) {
                            table.row( currentIndex ).deselect();
                            table.row( indexes[ currentPosition-1 ] ).select();
                        }
    
                        // Trigger editing through the button
                        table.button( 1 ).trigger();
                    }, null, null, false );
                }
            },
            'Save',
            {
                text: "&gt;",
                action: function (e) {
                    // On submit, find the currently selected row and select the next one
                    this.submit( function () {
                        var indexes = table.rows( {search: 'applied'} ).indexes();
                        var currentIndex = table.row( {selected: true} ).index();
                        var currentPosition = indexes.indexOf( currentIndex );
    
                        if ( currentPosition < indexes.length-1 ) {
                            table.row( currentIndex ).deselect();
                            table.row( indexes[ currentPosition+1 ] ).select();
                        }
    
                        // Trigger editing through the button
                        table.button( 1 ).trigger();
                    }, null, null, false );
                }
            }
        ];
    
    
    // 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 ) {
                                console.log( results );
                                uploadEditor.field('csv').error( 'CSV parsing error: '+ results.errors[0].message );
                            }
                            else {
                                uploadEditor.close();
                                selectColumns( editor, results.data, results.meta.fields );
                            }
                        }
                    });
                }
            } ]
        } );
    
        var table = $('#students').DataTable( {
             mark: true,
            "bLengthChange": true,
            "fixedHeader": true,
            "responsive": true,
             colReorder: true,
            "ordering": true,
            "stateSave": true,
            dom: "Bfrtip",
            ajax: "../controllers/students.php",
            columns: [
                { data: "student_ku" },
                { data: "student_ar" },
                { data: "student_en" }
            ],
            select: true,
            buttons: [
            { extend: "create", editor: editor },
            {
                    extend: 'selected',
                    text:   'Edit',
                    action: function () {
                        var indexes = table.rows( {selected: true} ).indexes();
    
                        editor.edit( indexes, {
                            title: 'Edit',
                            buttons: indexes.length === 1 ?
                                backNext :
                                'Save'
                                
                        } );
                    }
            },
            { extend: "remove", editor: editor }
            ]
        } );
    } );
    
    </script>
    

    Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

  • SarbastSarbast Posts: 85Questions: 0Answers: 0
    edited January 2021

    server side:

    <?php session_start(); ?>
    <?php
    require ('../../../dbc/ConnectwDB.php');
    $DB = new ConnectwDB; 
    $connwdb = $DB->connectDB();
    
    function checkKey($connwdb, $randStr) {
    $keyExists = "";
    $sql = $connwdb->prepare("SELECT * FROM students"); 
    $sql->execute();
    
    while($row = $sql->fetch(PDO::FETCH_ASSOC)){
    if($row['student_code'] == $randStr){
    $keyExists = true;
    break;
    } else{
    $keyExists = false;
    }
    }
    return $keyExists;
    }
    
    function generateKey($connwdb){
    
    $keyLength = 8;
    $str = "1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $randStr = substr(str_shuffle($str), 0, $keyLength);
    
    $checkKey = checkKey($connwdb, $randStr);
    while($checkKey == true){
    $randStr = substr(str_shuffle($str), 0, $keyLength);
    $checkKey = checkKey($connwdb, $randStr);
    }
    return $randStr;
    }
    
    
    /*
     * Example PHP implementation used for the index.html example
     */
    
    // DataTables PHP library
    include( "../../plugins/datatables-editor/lib/DataTables.php" );
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'students' )
        ->fields(
            Field::inst( 'student_ku' ),
            Field::inst( 'student_ar' ),
            Field::inst( 'student_en' ),
            Field::inst( 'student_code' )
                 ->setValue( generateKey($connwdb) ),
            Field::inst( 'stage' )
                 ->setValue( $_SESSION['stage'] ),
            Field::inst( 'dept_id' )
                ->setValue( $_SESSION['dept_id'] )
        )
            ->where( 'dept_id', $_SESSION['dept_id'])
            ->where( 'stage', $_SESSION['stage'])
            ->on( 'preEdit', function ( $e, $id, $values ) {
                 $e->field( 'student_code' )->set( false );
               })
        ->process( $_POST )
        ->json();
    

    Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    but when import data as csv file the field of unique code get the same code and when add manually the code would be unique.

    I’m sorry, it is probably too early in the morning here, but I’m not understanding that. Could you explain a bit more please?

    Allan

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Hi allan,
    I meant when insert several rows into table via "import csv" the field "student_code" take the same code and it must be defferent at each row.
    Example:

    First row: student_name= Mark, student_code= RR12TYUI
    Second row: student_name= Jane, student_code= RR12TYUI
    And so on
    As you see the "student_code" take the same code.

    thanks

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Right - I think I understand what is going on now - thanks for the clarification. The way the CSV import example works is that it uses Editor's multi-row editing ability. That means it will submit all of the imported rows in a single request.

    You are currently only calling generateKey() once and setting the value to be the same for all rows. Hence why it is the same for all rows from an import.

    What I think you will need to do here is use a preCreate event listener in the PHP code to modify the value for each row being submitted - e.g.:

    ->on( 'preCreate', function ( $editor, &$values ) {
      $values['student_code'] = generateKey($connwdb);
    } )
    

    This will work as preCreate is called once per row submitted.

    Allan

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    thanks allan

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Hi allan
    it worked perfectly after adding "use ($connwdb)" because this variable is out of function's scope.

    thanks again

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Hi allan,

    I have another problem with "import csv" I link the "students" table with "gender" table when import csv to "students" table all text and number fields such as name, email, mobile number become number, so my question here does import csv work with linked tables or not?
    if yes, could you please help me to solve this problem.

    Thanks

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I always forget about the variable scoping in PHP! Good to hear that (almost) worked).

    The example import to CSV doesn’t take into account linked tables. If your CSV had the linked primary key in it, then it would just work as is. But if you need to lookup the linked primary key, that is something that would need to be added into the import processing code at the server-side before Editor sees it to insert into the database - in preCreate for example.

    Allan

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Thanks alot
    allan

This discussion has been closed.