papa parse - to dynamic table

montoyam

I'm thinking I can't be the first person to want to do this, but I can't find any posts regarding this...

I am using papa parse to bring text file contents into a sql table/dataTable. I love using this as there is no need to upload the actual text file to the server. However, my issue is that the text file structure is able to change as new columns are added. I have a "crosswalk" table linking column headers to another table (and eventually I unpivot the table in a sql stored procedure). However, whenever a new column is added to the text file I need to re-publish my dataTable with the new column to receive the data.

Question: Is there a way to use PapaParse to dynamically create the columns of a dataTable using the headers of the text file as column names and then populate the dataTables rows with the text file data? (then I can worry later about how to get that data to an unpivot process using my crosswalk table)

or, is there something better than papaparse to use for this need?

montoyam

I think I am on to something, but I am getting an error:

Cannot use 'in' operator to search for 'length' in First_Name

            // 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,
                            delimiter: ",",
                            complete: function (results) {
                                if (results.errors.length) {
                                    console.log(results);
                                    uploadEditor.field('csv').error('CSV parsing error: ' + results.errors[0].message);
                                }
                                else {
                                    var data = results.data;
                                    console.log("columns", results.meta.fields, "data",data);
                                    console.log("data0",data[0]);
                                    $('#testImport').dataTable({
                                        data: data
                                        , columns: results.meta.fields

                                    });
                                    //uploadEditor.close();
                                    //selectColumns(office365ImportEditor, results.data, results.meta.fields);
                                }
                            }
                        });
                    }
                }]
            });

In the console log for data[0] (line 22 above), returns something like:

{
    "First_Name": "John",
    "Last_Name": "Doe",
    "UserPrincipalName": "john@test.com",
    "Department": "Sales",
    "Licensed": "True",
    "Project_License": "",
    "Visio_License": "",
    "VPN": "",
    "SecureLink": "",
    "AccountSKUID": "G5",
    "ExtensionAttribute15": "Accounting",
    "Manager": "Fred Doe"
}

montoyam

ah, turns out it was hanging up on the columns, not the data. this seems to be working:

            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,
                            delimiter: ",",
                            complete: function (results) {
                                if (results.errors.length) {
                                    console.log(results);
                                    uploadEditor.field('csv').error('CSV parsing error: ' + results.errors[0].message);
                                }
                                else {
                                    var data = [];
                                    var columns = [];
                                    data = results.data;
                                    for (var i in results.meta.fields) {
                                        columns.push({
                                            data: results.meta.fields[i],
                                            title: results.meta.fields[i]
                                        });
                                    }
                                    $('#testImport').dataTable({
                                        data: data
                                        , columns: columns
                                    });
                                    uploadEditor.close();
                                    //selectColumns(office365ImportEditor, results.data, results.meta.fields);
                                }
                            }
                        });
                    }
                }]
            });

montoyam

now i just need to figure out how to get these dynamic columns over to my sql unpivot stored proc

montoyam

in case this helps anyone else, here is how I ended up doing the 'unpivot'

                                    //write the remaining/dynamic fields to new dbo.Outlook_Raw_Products
                                    var constantFields = ['First_Name', 'Last_Name', 'Department', 'AccountSKUID', 'ExtensionAttribute15', 'Licensed', 'Manager', 'UserPrincipalName'];
                                    var alterArray = function filteredArray(arr) {
                                        var results = [];
                                        for (var row = 0; row < arr.length; row++) {
                                            var i = arr[row];
                                            Object.keys(i).forEach(function (itm) {
                                                if (constantFields.indexOf(itm) == -1) {
                                                    if (i[itm] == 'Yes') {
                                                        results.push({
                                                            EmailAddress: i.UserPrincipalName,
                                                            Product: itm
                                                        });
                                                    }
                                                }
                                            });
                                        }
                                        return results
                                    }
                                    var unpivotedProducts = alterArray(results.data);
                                    OfficeLicenseProductsEditor.create(unpivotedProducts.length, false,{
                                        buttons: 'Submit'
                                    });
                                    var fields = OfficeLicenseProductsEditor.order();
                                    for (var i = 0; i < fields.length; i++) {
                                        var field = OfficeLicenseProductsEditor.field(fields[i]);
                                        for (var j = 0; j < unpivotedProducts.length; j++) {
                                            field.multiSet(j, unpivotedProducts[j][field.name()]);
                                        }
                                    }
                                    OfficeLicenseProductsEditor.submit();
                                    OfficeLicenseProductsEditor.close();

there may have been an easier way using classes to split the data out, but this was all I could figure out

colin

Nice, thanks for posting back,

Colin