Importing a spreadsheet into TBX


(Alex Strick van Linschoten) #1

I introduced someone to tinderbox and they asked me a question for which I have no answer. Potentially useful to others on this forum. So:

If I have a regularly ordered XLS (or, more likely, CSV) file, with headings. For the sake of simplicity, let’s say:

Name, Age, Country
Alex, 23, UK
Jim, 42, USA

What I’d like to do is import these into tinderbox so that I have individual notes. A sample note would be named “Alex”, and would have age and country attributes that get auto-populated.

Is there any way to do this so that the attributes are auto-populated?


(Paul Walters) #2

Yes. It is very simple to do. And very powerful.

Select the range, including column names, in Excel or Numbers:

Open your Tinderbox document, select a Map or Outline, and paste – ⌘V

et voilà

In your Excel spreadsheet, you should have a column with the title Name, and you can optionally have a column with name Text. If your user attributes should be something other than string than I recommend you first create those attributes and give them their type before doing the import from Excel. E.g., in the above example when Age is imported $Age is a string attribute. You can change a string attribute to a number attribute in the Attributes Inspector, but setting this typing up in advance is easy and avoids errors. The data that Excel or Numbers put on the clipboard are not typed – the clipboard doesn’t know what a number is, or a string, or list, etc.

If your Text is going to have multiple lines or punctuation then I recommend Numbers rather than Excel. Numbers is better at formatting what goes on the clipboard than Excel. Also, if you plan to export from the spreadsheet to CSV and then import to Tinderbox, use Numbers – it is more successful for that sort of thing.

See also Mark Anderson’s notes on this here and here.

If you use the Excel or Numbers sheet to performance calculations, the result of the calculation is copied - not the formula. So, if your spreadsheet had a column NameAge with the calculation =CONCATENATE(A2,", ",B2), your import would have an attribute $NameAge with values

Alex, 23
Jim, 45

That’s a silly example – merely meant to illustrate that you can quickly build a partnership between your spreadsheet program and Tinderbox using the strengths of each. Getting data from Tinderbox to Excel can be accomplished with a couple of simple export templates:

CSVheader
Name,Age,Country
^children(/Templates/CSVdetail)^

CSVdetail
^value($Name)^,^value($Age)^,^value($Country)^


(Alex Strick van Linschoten) #3

Thank you so much! This is exactly what I was looking for.


(Mark Anderson) #4

Hi, Alex!

This and this article should cover it. If there are still questions arising, just ask.

Sorry, the earlier reply didn’t show when i posted and this only repeats whats already been said.


(Lew Friedland) #5

Am trying to implement instructions here. Am importing a spreadsheet with a series of entries named “event.” Created a prototype for import also named “Event”, added that prototype column, and have successfully imported the spreadsheet, with each row properly inheriting its attributes.

Now, another really simple question. I want each of these notes to have the title of the event field. So if, for example, the event field says “Walker Recall,” that should be the name of the note. What is the simplest way to rename the rows on import so that each new note has the title of the event? Would it be an OnAdd code, e.g. $Name=“Event”? or a rule? Thanks.


(Mark Anderson) #6

The easiest way would be to copy the ‘Event’ column in the source spreadsheet as ‘Name’. But, I assume for some reason that’s not possible. Otherwise I’d suggest an OnAdd of:

$Name=$Event;

(Lew Friedland) #7

Thanks Mark, that worked. Should have thought of it. Can you direct me to current best instructions on how to set up a timeline? I’m reading the section in Getting Started and it refers to the “Info button at the left edge of the timeline view’s time scale, at the bottom of the window.” But don’t see such a bottom bottom left or elsewhere.


(Mark Anderson) #8

Timeline now uses a properties pop-up accessed via an ‘i’ button on the window tab’s icon. Over time most view styles have moved to tab-based properties so some older references may pre-edate that.

More on Timeline view.


(Lew Friedland) #9

Thank you as always.