Problem with Datatable CSV import

Problem with Datatable CSV import

jalapejalape Posts: 117Questions: 2Answers: 1

Good night

I have a problem using the Datatable CSV import example:
https://editor.datatables.net/examples/extensions/import.html
To select the data to be imported, the Papa Prase library is used.
The problem is that to confirm the import, this library requires that the name of the fields only contain the name of the field without the name of the table to which they belong.
Example:
name: "description",

The problem is that I am using table joins leftJoin related, so I have to forcefully use the table name:
Example:
name: "table.description",

Is there an alternative for this case?

Thanks

Replies

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

    Hi,

    Could you prefix the table name here: field.multiSet( j, csv[j][mapped] ); - e.g.:

    field.multiSet( j, csv['myTable.' + j][mapped] );
    

    ?

    Allan

  • jalapejalape Posts: 117Questions: 2Answers: 1

    Thank you very much Allan for answering,
    I am replacing:
    field.multiSet( j, csv[j][mapped] );
    to
    field.multiSet( j, csv['sml_markers.' + j][mapped] );
    and it still does not work, I do not know if I am doing something wrong although in debugging I do not appreciate errors.

    datatable_marker.php

    <script>
    
    window.onload = function(){
    
        var editor;
    
        // Mostrar un formulario de editor, 
        //permite al usuario elegir los datos CSV para aplicar a cada columna
        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: 'Asignar campos CSV',
                buttons: 'Importar '+csv.length+' registros',
                message: 'Seleccione la columna CSV de la que desea utilizar los datos para cada campo.'
            });
    
            selectEditor.on('submitComplete', function (e, json, data, action) {
                // Utilice la instancia del editor de host para mostrar un formulario 
                //de creación de varias filas que permite al usuario enviar los datos. 
                editor.create( csv.length, {
                    title: 'Confirmar importación',
                    buttons: 'Enviar',
                    message: 'Haga clic en el botón <i>Enviar</i> para confirmar la importación de '+csv.length+' filas de datos. Opcionalmente, anule el valor de un campo para establecer un valor común haciendo clic en el campo a continuación.'
                } );
    
                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] );
                       ** field.multiSet( j, csv['sml_markers.' + j][mapped] );**
                    }
                }
            } );
        }
    
        // Editor de Datatable
        $(document).ready(function() {
            
            editor = new $.fn.dataTable.Editor( {
                // get_datos_marker es el nombre de ruta de: public/datatable/datos_marker.php
                ajax: "datos_marker.php",
                table: "#markers_table",
                //con esto controlamos el diseño del cuadro editor (sin contar los botones)
                fields: [
                    {
                        label: "Mapa:",
                        name:  "sml_markers.id_map",
                        type: "select",
                        opts: {
                            "placeholder": "Seleccionar un mapa",
                            "allowClear": true
                        }
                    },
                    {
                        label: "Categoría:",
                        name:  "sml_markers.id_category",
                        type: "select",
                        opts: {
                            "placeholder": "Seleccionar una categría",
                            "allowClear": true
                        }
                    },                          
                    {
                        label: "name:",
                        name:  "sml_markers.name"
                    },
                    {
                        id: "latitude_edit",
                        label: "Latitud:",
                        name:  "sml_markers.lat"
                    },
                    {
                        id: "longitude_edit",
                        label: "Longitud:",
                        name:  "sml_markers.lon"
                    },                        
                    {
                        name:  "sml_markers.description",
                        type: "textarea"
    
                    }
                ]
            } );
    
            // Upload Editor - se activa desde el botón de importación. Se usa solo para cargar un archivo en el navegador
            var uploadEditor = new $.fn.dataTable.Editor( {
                fields: [ {
                    label: 'Archivo CSV:',
                    name: 'csv',
                    type: 'upload',
                    ajax: function ( files, done ) {
                        // Anulación de la carga Ajax, para que podamos manejar el archivo localmente. 
                        // Aquí usamos la librería PapaParse para analizar el 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 );
                                }
    
                                // Comunicar al editor que la carga está completa: la matriz es una lista de archivos
                                // El valor id no importa en este caso.
                                done([0]);
                            }
                        });
                    }
                } ]
            } );
    
            $('#markers_table').DataTable( {
    
                "lengthMenu":       [[1, 5, 10, 25, 50, -1], [1, 5, 10, 25, 50, "Todos"]],
                "iDisplayLength":   5,
    
                responsive: true,
                select: true,
                // dom: "Bfrtip",
                dom: "Bfrtlpi",
                ajax: "datos_marker.php",
                columns: [
                    { data: "sml_maps.name" },
                    { data: "sml_categories.name" },
                    { data: "sml_markers.lat" },
                    { data: "sml_markers.lon" },
                    { data: "sml_markers.name" },
                    { data: "sml_markers.description" }
                ],
    
                buttons: [               
                    {
                        extend:     'create',
                        className: 'espacio_botones', 
                        editor: editor,
                        text:       '<img class="ico-datatable" src="../../plugin/_icono/datatable/nuevo-48.svg">',
                        titleAttr:  'Nuevo'
                    },
                    {
                        extend:     'edit', 
                        editor: editor,
                        text:       '<img class="ico-datatable" src="../../plugin/_icono/datatable/editar-48.svg">',
                        titleAttr:  'Editar'
                    },
                    {
                        extend:     'remove', 
                        editor: editor,
                        text:       '<img class="ico-datatable" src="../../plugin/_icono/datatable/eliminar-48.svg">',
                        titleAttr:  'Borrar'
                    },
                    {
                        extend: 'csv',
                        text: 'Export CSV',
                        charset: 'utf-8',
                        fieldSeparator: ',',
                        className: 'btn-space',
                        exportOptions: {
                            orthogonal: null
                        },
                        bom: true
                    },
                    {
                        text: 'Import CSV',
                        action: function () {
                            uploadEditor.create( {
                                title: 'Importación de archivos CSV'
                            } );
                        }
                    },
                    {
                        extend: 'selectAll',
                        className: 'btn-space'
                    },
                    'selectNone',            
                ]
            } );
        } );
    
    }
    </script>
    

    datos_marker.php

    Editor::inst( $db, 'sml_markers' )
    ->field( 
    
        Field::inst( 'sml_markers.id_map' )
        ->validator( Validate::notEmpty( ValidateOptions::inst()
            ->message( 'El campo mapa es necesario' )   
        ) ) 
        ->options( Options::inst()
            ->table( 'sml_maps' )
            ->value( 'id' )
            ->label( 'name' )
        ),
        Field::inst( 'sml_maps.name' ),
    
        Field::inst( 'sml_markers.id_category' )
        ->options( Options::inst()
            ->table( 'sml_categories' )
            ->value( 'id' )
            ->label( 'name' )
        ),
        Field::inst( 'sml_categories.name' ),
    
        Field::inst( 'sml_markers.lat' ),
        Field::inst( 'sml_markers.lon' ),
        Field::inst( 'sml_markers.name' ),
        Field::inst( 'sml_markers.description' )
    )
    
    ->leftJoin( 'sml_maps', 'sml_maps.id', '=', 'sml_markers.id_map' )
    ->leftJoin( 'sml_categories', 'sml_categories.id', '=', 'sml_markers.id_category' )
    
    ->process($_POST)
    ->json();
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you give me a link to your page so I can trace it through with a debugger please?

    Allan

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

    Thanks! Apologies for having not looking into this today - it is on my list :).

    Allan

  • jalapejalape Posts: 117Questions: 2Answers: 1

    Thanks Allan for answering,
    It seems that it is more difficult than he thought, hopefully it will be possible in future versions of Datatable Editor.

This discussion has been closed.