CSV Import error

Khalid Teli

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

rf1234

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 Teli

@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 Teli

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

colin

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

Colin

Khalid Teli

@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 Teli

@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 Teli

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

colin

Excellent, glad all working, thanks for reporting back,

Colin