problems with csv import

crush123 · October 2023

i am trying csv import for the first time, and am using the example page as a start point.
I believe that i am loading all the files in the right order, and the page seems to work for everything except the import, where after choosing my csv file, the modal with the field options doesn't appear.
I dont get any console errors that i can see.

i uploaded a demo page to display the problem

https://therushes.co.uk/vanillacsv.php

grateful for any insight as to what i'm doing wrong

kthorngren · October 2023

You are using Bootstrap 3 as the styling framework for the page. But you aren't using the Datatables and Editor Bootstrap 3 style integration files. See the Style docs for details. See this Editor with BS 3 example. Click on the CSS tab. This might be causing the form to not show as you aren't getting any errors in the browser's console.

You can get the proper files using the Download Builder.

As a test you can temporarily comment out lines 19 and 43 that load bootstrap.css and bootstrap.js to see if the upload process works.

Kevin

crush123 · October 2023

Many thanks for your reply - will check that out.

in the meantime, i have commented out the bootstrap lines, but the problem persists

kthorngren · October 2023

Inspect the form when the Choose File input is displayed. Choose a file and the form disappears but the form is still displayed but is behind the table. Hover over the form's div and form elements, in the inspector, and you will see where it is on the page. Try adding this CSS to bring it forward:

div.DTED_Lightbox_Wrapper {
    z-index: 9999;
}

If this doesn't work then @allan will need to take a look.

Kevin

crush123 · October 2023

Thanks for trying, on choosing a file, the form disappears, but the 'select fields' form does not load, it is the 'choose file input' form which is still open and behind the table.

i remember reading somewhere in the forums that there was an issue with an earlier version of editor (1.9.1 ?) when using bootstrap, but I m using 1.9.7.

Never mind, thanks for your input

allan · October 2023

It looks like the opacity is being set to 0 for the lightbox, which is why we can't see it.

I wonder if the import for in the current example has been updated a little since the 1.x series' example.

This is the code that shipped for that example with 1.9.7:


// Use a global for the submit and return data rendering in the examples.
// Don't do this outside of the Editor examples!
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() {
    // Regular editor for the table
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/staff.php",
        table: "#example",
        fields: [ {
                label: "First name:",
                name: "first_name"
            }, {
                label: "Last name:",
                name: "last_name"
            }, {
                label: "Position:",
                name: "position"
            }, {
                label: "Office:",
                name: "office"
            }, {
                label: "Start date:",
                name: "start_date",
                type: "datetime"
            }, {
                label: "Salary:",
                name: "salary"
            }
        ]
    } );

    // 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').DataTable( {
        dom: 'Bfrtip',
        ajax: "../php/staff.php",
        columns: [
            { data: 'first_name' },
            { data: 'last_name' },
            { data: "position" },
            { data: "office" },
            { data: "start_date" },
            { data: "salary", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) }
        ],
        select: true,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor },
            {
                extend: 'csv',
                text: 'Export CSV',
                className: 'btn-space',
                exportOptions: {
                    orthogonal: null
                }
            },
            {
                text: 'Import CSV',
                action: function () {
                    uploadEditor.create( {
                        title: 'CSV file import'
                    } );
                }
            },
            {
                extend: 'selectAll',
                className: 'btn-space'
            },
            'selectNone',
        ]
    } );
} );

It is very similar, but there is a slight difference in the complete function - specifically the call to uploadEditor.close();. It might be worth trying that before we look at this further (1.x isn't really supported any more).

Allan

crush123 · October 2023

Thanks Allan,

that code snippet certainly helped the modal window issue.

I can now choose the fields, but the result is a bit odd, in that it populates each of fields with the imported number of rows (minus one).

I guess this could be down to the libraries, as all of the ones I used are via links rather than on the local server.
I will update these all to the ones shipped with 1.9.7 and see what i get.

thanks!

crush123 · October 2023

nope, it wasn't that ;-(

crush123 · October 2023

upgraded to 2.2.2 - bit of fiddling about, but I can now get the vanilla sample to work

;-)

crush123 · October 2023

I now have the example working with editor 2.2.2
Having applied the code to one of my pages, i exported some rows as a csv and then tried to re-import them.
Some fields are fine, others display an integer in the field, which is always the number of rows-1.
can anyone shed any light on why this might be please ?

test page https://therushes.co.uk/markers.php

csv file is in this format...

Map Name: Address: Postcode: Title: Description: Icon Colour: Marker Colour: Icon:
Map 3 Stoke on Trent ST4 1HP Stoke on Trent None #3caac3 #ffff00 fa-socks
Map 3 Salford M5 5AG Salford None #3caac3 #3caac3 fa-space
Map 3 Tameside SK16 4LA Tameside None #3caac3 #3caac3 fa-space
Map 3 Norfolk NR1 2DH Norfolk None #ffffff #95c022 fa-1
Map 3 Liverpool L15 4LP Liverpool None #ffffff #95c022 fa-8

allan · October 2023

Hi,

Thanks for the updated link (and for updating to 2.x ). I believe I see what the issue is here - it is related to the use of the table name as part of the import Editor field names, which the import code from the example doesn't cope with.

Fortunately the fix is quite straight forward with the DataTable.util.get() method. Replace:

var mapped = data[field.name()];

with:

var mapped = DataTable.util.get(field.name())(data);

and that should allow it to resolve correct and populate your data.

I'll update the example with this for the next release.

Allan

crush123 · October 2023

Wonderful !

problems with csv import

problems with csv import

Answers

Howdy, Stranger!

Categories

DataTables

problems with csv import

problems with csv import

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables