CSV Import error

CSV Import error

Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

Hi

Question 1:

I am trying to import the data using CSV file upload.

I have uploaded the steps involved in process, at step-3 for some reasons it display value 2 (dont know why). Tried deleting and adding names/columns but no change.

Step -1:

step-2:

step-3:

**Question 2:

**

I have a compound key in one of my tables and on serverside the key looks like:

$editor = Editor::inst( $db, 'contract_return_firm',  array('product_code', 'member_name') )

and when I try to import data it shows this error :

When inserting into a compound key table, all fields that are part of the compound key must be submitted with a specific value
and not sure how can I fix this

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Well you need to submit "product_code" and "member_name" to the server. Looks like you don't do this. "member_name" may correspond with "First Name" or "Last Name", but nothing seems to correspond with "product_code" ...

    Looks like your database table "contract_return_firm" isn't suitable to import data like this from the client. So either you change your db table or you change the form to be imported.

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    @rf1234
    Thank you I tried to do same and error still remains there

    Step - 1: this is how my csv file looks like

    Step 2: mapping the fields after CSV import

    Step 3: this shows same error

    When inserting into a compound key table, all fields that are part of the compound key must be submitted with a specific value
    and not sure how can I fix this

    Also, tried to do import on a diffwerent table (without compound keys) and it shows the same error ast step -3, where 2 gets displaye din each box.

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    this might help:
    on my serverside my code looks like this:

    $editor = Editor::inst( $db, 'contract_return_firm',  array('product_code', 'member_name') )
     ->debug( true )
        ->fields(
    
    
    
            Field::inst( 'contract_return_firm.product_code' ),
            Field::inst( 'contract_return_firm.product_name' ),
            // Field::inst( 'contract_return_firm.pack_size' ),
            Field::inst( 'contract_return_firm.member_name'),
    
            Field::inst( 'contract_return_firm.opt_one' ),
            Field::inst( 'contract_return_firm.opt_two' ),
            Field::inst( 'contract_return_firm.opt_three' ),
            Field::inst( 'contract_return_firm.opt_four' ),
            Field::inst( 'contract_return_firm.opt_five' ),
            Field::inst( 'contract_return_firm.opt_six' ),
            Field::inst( 'contract_return_firm.comments' ),
    
            Field::inst( 'contract_return_firm.entry_time' )
    
    
    
    
        );
    
    
    if ( $_SESSION['username'] === 'crg') {
      $editor->fields(Field::inst( 'contract_return_firm.pack_size' )
    
    );
    }
    
    else if ( $_SESSION['username'] === 'birchall') {
      $editor->fields(Field::inst( 'contract_return_firm.pack_size' )
        ->getFormatter( function ( $val, $data, $opts ) { return null;}),
    
        );
    
    }
    
     $editor
    
    
     ->debug(true)
     ->tryCatch( false )
        ->process( $_POST )
        ->json();
      //}
    

    on client side:

        editor = new $.fn.dataTable.Editor( {
            "ajax": "../../controllers/contract_return_firm_fetch.php",
            "table": "#contractss",
            "fields": [
          {
            "label": "Product Code:",
            "name": "contract_return_firm.product_code"
          },
    
        {
            "label": "Product Name:",
            "name": "contract_return_firm.product_name"
          },
           {
            "label": "Member Name:",
            "name": "contract_return_firm.member_name"
          },
           {
            "label": "",
            "name": "contract_return_firm.opt_one"
          },
          {
            "label": "",
            "name": "contract_return_firm.opt_two"
          },
           {
            "label": "",
            "name": "contract_return_firm.opt_three"
          },
          {
            "label": "",
            "name": "contract_return_firm.opt_four"
          },
          {
            "label": "",
            "name": "contract_return_firm.opt_five"
          },
           {
            "label": "",
            "name": "contract_return_firm.opt_six"
          }, 
          {
            "label": "Comments:",
            "name": "contract_return_firm.comments"
          },
           {
            "label": "Entry Time:",
            "name": "contract_return_firm.entry_time"
          }
            ]
        } );
    

    The code for CSV Import looks same as used in example on website
    Thanks

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

    Are you able to link to your page so we can take a look?

    Colin

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0
    edited September 2020

    @colin I am not sure if I can link an example, as I am working on local host and retrieving data from database. However, I have created an simple example and posted all the code below :

    admin.php (client side code is this)

    <?php 
    require_once('login_details/initialize.php'); ?>
    
    
    <!DOCTYPE html>
    <html>
    <head>
    
    
    
            <table id="example" class="display" cellspacing="0" width="100%">
                  <thead>
    <tr>
            <th></th>
            <th>ID</th>
            <th>First</th>
            <th>Last</th>
            <th>Email</th>
            <th>Username</th>
            <th>Access Level</th>
    
            </thead>
        </table>
    
    
    
    <script>
    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();
    
        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.
            editor.create( csv.length, {
                title: 'Confirm import',
                buttons: 'Submit',
                message: 'Click the <i>Submit</i> button to confirm the import of '+csv.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 = data[ field.name() ];
    
                for ( var j=0 ; j<csv.length ; j++ ) {
                    field.multiSet( j, csv[j][mapped] );
                }
            }
        } );
    }
    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor( {
            ajax: "../../controllers/admins_fetch.php",
            table: "#example",
            fields: [  {
                    label: "First Name:",
                    name: "admins.first_name"
                }, {
                    label: "Last Name:",
                    name: "admins.last_name"
                }, {
                    label: "Email:",
                    name: "admins.email"
                }, 
                {
                    label: "User Name:",
                    name: "admins.username"
                }
            ]
        } );
    
     // 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 );
                            }
                        }
                    });
                }
            } ]
        } );
    
          $('#example').on('xhr.dt', function ( e, settings, json, xhr ) {
            var api = new $.fn.dataTable.Api( settings );
    
            // Replace with your actual condition
            var abc = "<?php echo $_SESSION['username']; ?>";
    
    
            if (abc === 'birch')
            {
                api.columns([2]).visible(false);
    
        }
         else if (abc === 'crg')
            {
                // api.columns([2]).visible(false);
    
        }
    
    
    
        });
    
    
        var table = $('#example').DataTable( {
            dom: 'Bfrtip',
            ajax: {
                url: '../../controllers/admins_fetch.php',
                type: 'POST'
            },
            serverSide: true,
            order: [[ 1, 'asc' ]],
    
            columns: [
                {
                    data: null,
                    defaultContent: '',
                    className: 'select-checkbox',
                    orderable: false,
                    searchable: false
                },
                { data: "admins.id" },
                { data: "admins.first_name" },
                { data: "admins.last_name" },
                { data: "admins.email" },
                { data: "admins.username" },
                { data: "admins.access_level" }
    
            ],
            select: true,
            buttons: []
        } );
    
    
        var sessionId = "<?php echo $_SESSION['username']; ?>";
        console.log(sessionId);
    
    
      if (sessionId === 'crd') {
        $('#example').on('click', 'tbody tr td', function() {
          editor.inline(this);
        }); 
    
        table.button().add(
          null, { extend: "create", editor: editor }     
        );
    
        table.button().add(
          null, { extend: "edit", editor: editor },
        );
    
        table.button().add(
          null, { extend: "remove", editor: editor }     
        );
    
        table.button().add(
          null, {
                    text: 'Import CSV',
                    action: function () {
                        uploadEditor.create( {
                            title: 'CSV file import'
                        } );
                    }}     
        )
    
      }
    
    
    } );
    </script>
    

    The code on serverside (admin_fetch.php) looks like this:

    <?php
    session_start();
    ....
    
    
    $editor = Editor::inst( $db, 'admins', 'id' )
        ->fields(
    
    
           Field::inst( 'admins.id' ),
             // Field::inst( 'admins.first_name' ),
            // ->get(false),
            Field::inst( 'admins.email' ),
            Field::inst( 'admins.last_name' ),
            Field::inst( 'admins.username' ),
            Field::inst( 'admins.access_level' ),
    
     Field::inst( 'admins.hashed_password' )
     ->validator( 'Validate::notEmpty' )
                  ->setFormatter( function ( $val, $data, $opts ) { return password_hash( $val , PASSWORD_DEFAULT);} )
                  ->getFormatter( function ( $val, $data, $opts ) { return null;}),
    
    
            Field::inst( 'created_by' )->set( Field::SET_CREATE ),
            Field::inst( 'last_updated_by' )->set( Field::SET_EDIT )
    
    );
    
    
    if ( $_SESSION['username'] === 'crd') {
      $editor->fields(Field::inst( 'admins.first_name' )
    
    );
    }
    
    else if ( $_SESSION['username'] === 'birc') {
      $editor->fields(Field::inst( 'admins.first_name' )
        ->getFormatter( function ( $val, $data, $opts ) { return null;}),
    
        );
    
    }
    
    
    $editor
     ->on( 'preCreate', function ( $editor, $values ) {
            $editor
                ->field( 'created_by' )
                ->setValue( $_SESSION['username'] );
    
             } )     
    
        ->on( 'preEdit', function ( $editor, $id, $values ) {
            $editor
                ->field( 'last_updated_by' )
                ->setValue(date("l jS \of F Y h:i:s A") );
        } )
    
    
    
        ->process( $_POST )
        ->json();
    

    And when I try to import the CSV file , it gets loaded but on
    1) My table looks like thhis , in begining

    2) data on csv file looks like thgis

    3) when uploading on screen it looks like

    4) the problem is in this step, the data look like this

    5) and this gets uploaded and final table looks like thgis:

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    @colin
    I managed to sort it out.
    Apparently , from the server side you need to change it

        FROM this : 
            Field::inst( 'contract_return_firm.product_code') 
            TO
            Field::inst( 'contract_return_firm.product_code', 'pcode' )
    

    and on client side use

           { data: "pcode" }
            rather than using 
            { data: "contract_return_firm.product_code" }  
    

    This sorted one of the issues but when inserting data in compound key table, this error is still there
    Exception: When inserting into a compound key table, all fields that are part of the compound key must be submitted with a specific value. in C:\wamp64\www\Editor-PHP-1.9.4\lib\Editor.php on line 2253

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    @colin
    Please ignore my all comments, I have sorted it out :)

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

    Excellent, glad all working, thanks for reporting back,

    Colin

This discussion has been closed.