papa parse - to dynamic table

papa parse - to dynamic table

montoyammontoyam Posts: 568Questions: 136Answers: 5
edited September 2021 in Extensions

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?

This question has an accepted answers - jump to answer

Answers

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited September 2021

    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"
    }
    
  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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);
                                    }
                                }
                            });
                        }
                    }]
                });
    
  • montoyammontoyam Posts: 568Questions: 136Answers: 5

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

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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 :smiley:

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    Answer ✓

    Nice, thanks for posting back,

    Colin

Sign In or Register to comment.