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

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
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
Photo of Michael Graham | Insight Global UK

Posted 2 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,004 Points 20k badge 2x thumb
Like this:

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

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,004 Points 20k badge 2x thumb
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?
(Edited)
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!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,004 Points 20k badge 2x thumb
>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);

(Edited)