Discussions

Expand all | Collapse all

Excel advice on how to prepare excel to import into Multi Select field in QuickBase

  • 1.  Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Posted 04-06-2017 09:39
    I currently have an excel sheet with recordID and Features columns
    In the record ID I have the same number multiple times and in Features I have the different options.

    To Import this into QuickBase, I need to join the Features rows with a ; in between

    Red
    Blue
    Green

    Red;Blue;Green in a single field so that I can import it into QuickBase

    I have hundreds of records, each with 6 or 7 features.

    Does anyone know of any tricks and tips to be able to speed up preparing the excel sheet?

    Thanks in advance

    Mick


  • 2.  RE: Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Top
    Contributor
    Posted 04-06-2017 10:13
    Like this:

    =A1 & ";" & B1 & ";" & C1



  • 3.  RE: Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Posted 04-06-2017 12:30
    It looks like I would have to type in that for each record.   Ive got 2000 records.
    I could just as well export it them to a text file and manually add the ; between the text :(


  • 4.  RE: Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Posted 04-06-2017 12:31
    man o man.  Just copy the formula down 2000 rows.  Edit Copy. Highlight the 1,999 cells below. Edit Paste.


  • 5.  RE: Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Top
    Contributor
    Posted 04-06-2017 12:35
    Just pull down on the handle for 2000 rows:



  • 6.  RE: Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Posted 04-06-2017 12:37
    Record ID   Feature
    1 Red
    1 Blue
    1 Green
    1 Yellow
    1 Pink
    1 Gray
    2 Yellow
    2 Brown 
    2 Red

    1  Red;Blue;Green;Yello;Pink;Gray
    2  Yellow;Brown;Red

    Thats what I need to achieve.  I cant see how copying the formula down is going to acheive that??


  • 7.  RE: Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Posted 04-06-2017 13:02
    I sounds like this data is already in QuickBase since you refer to Record ID#. 
    Make a summary report where the rows are the Record ID# and the columns are the feature.


    Then export that to excel.

    Then make a formula to concatenate the data.  You could do the same with a pivot table in Excel.

    If you need help contact me via the information in my profile at quickbasecoach.com and I can help you off line.


  • 8.  RE: Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Top
    Contributor
    Posted 04-06-2017 13:17
    I think we both assumed your data looked like this:
    1 | Red | Blue | Green | Yellow | Pink | Gray
    2 | Yellow | Brown  | Red
    So this is basically a grouping problem.

    What I would do is write a custom uploader that transformed the data before uploading to QuickBase. This will obviate the need to screw around in Excel.

    You create a little form with a file control and when the file is loaded, parse the data with D3's CSV method. Now you have an array of arrays of the data that looks like this:

    var data = [
      [1, "Red"],
      [1, "Blue"],
      [1, "Green"],
      [1, "Yellow"],
      [1, "Pink"],
      [1, "Gray"],
      [2, "Yellow"],
      [2, "Brown"],
      [2, "Red"]
    ];

    This code will group the data by the first column value and create a csv string in preparation for being uploaded by API_ImportFromCSV method:

    var csv = _.chain(data)  .groupBy(function(item) {
        return item[0];
      })
      .reduce(function(memo, item, key) {
        return memo + key + "," + _.pluck(item, 1).join(";") + "\n";
      }, "")
      .value();
    console.log(csv);
    //outout:
    //1,Red;Blue;Green;Yellow;Pink;Gray
    //2,Yellow;Brown;Red
    Problem solved! What could be easier?


  • 9.  RE: Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Posted 04-06-2017 13:36
    Well, the data comes in excel as I have explained above.  To get it into QuickBase originally, I imported the features into their own table and linked them with their Parent Record IDs.

    If its easy enough to then get them from their table into a Multi Select Field in the Parent Table then all well and good because importing them from the spreadsheet was easy. I could then repeat this with  other files as and when.

    Dan's solution confuses me LOL!


  • 10.  RE: Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Posted 04-06-2017 13:41
    I think that its easiest to do the data manipulation in QuickBase.
    You can export that summary report to a new table and it should create the fields for the unique choices.  The use the List ( ";", field 1, field 2 etc) formula to get them in a format to import into the multi select field.


  • 11.  RE: Excel advice on how to prepare excel to import into Multi Select field in QuickBase

    Top
    Contributor
    Posted 04-06-2017 13:53
    >Dan's solution confuses me LOL!

    It is very simple.

    For a long time the only thing you could do with a file is upload it directly to the server unchanged. Improvements in HTML5 now allow you to read a file selected through a standard <input type=file> element using an API called the FileReader.

    So you create a little form that offers the user to select the CSV file and when the file is loaded you create a FileReader object. The FileReader object is used in conjunction with D3's CSV parser (D3 is an excellent open source visualization library) to read the data in the CSV file and return it as an array of arrays like this:
    var data = [   [1, "Red"],
      [1, "Blue"],
      [1, "Green"],
      [1, "Yellow"],
      [1, "Pink"],
      [1, "Gray"],
      [2, "Yellow"],
      [2, "Brown"],
      [2, "Red"]
    ];
    Then the script I posted then runs and converts the data into a CSV blob of data that is in the proper format for importing with API_ImportFromCSV.

    The only challenging part of this process is the manipulation done with Underscore. But you can paste the code into the console from any QuickBase page and you will see that the script works as advertised.

    var data = [   [1, "Red"],
      [1, "Blue"],
      [1, "Green"],
      [1, "Yellow"],
      [1, "Pink"],
      [1, "Gray"],
      [2, "Yellow"],
      [2, "Brown"],
      [2, "Red"]
    ];
    var csv = _.chain(data)  .groupBy(function(item) {
        return item[0];
      })
      .reduce(function(memo, item, key) {
        return memo + key + "," + _.pluck(item, 1).join(";") + "\n";
      }, "")
      .value();
    console.log(csv);