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

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

man o man.  Just copy the formula down 2000 rows.  Edit Copy. Highlight the 1,999 cells below. Edit Paste.

Just pull down on the handle for 2000 rows:

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??

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.

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?

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!

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.

>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);