Answers

• montoyam Posts: 568Questions: 136Answers: 5

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

  September 2021

  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 Posts: 568Questions: 136Answers: 5

  September 2021

  now i just need to figure out how to get these dynamic columns over to my sql unpivot stored proc
• montoyam Posts: 568Questions: 136Answers: 5

  September 2021

  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 Posts: 15,177Questions: 1Answers: 2,590

  September 2021 Answer ✓

  Nice, thanks for posting back,

  Colin