There is no menu option for initiating tabular data import. For tabular data two formats are supported, tab-delimited (TSV) or comma-separated (CSV):
- Tab-delimited: supported for file drag drop and copy/paste of table data. For files use '.txt' or '.tsv' file extensions.
- CSV: supported for file drag drop (CSV copy/paste is not supported). For files use the '.csv' file extension.
Tab-delimited tables are imported intelligently when dragged or pasted into Tinderbox, involving some mapping of table data into existing or new attributes. Mapping is based on a header row which is expected as row 1 of the data.
For example, selecting a table in a spreadsheet and pasting it into Tinderbox will create a useful set of notes:
- The first row is treated as a set of headings, which map to attributes. New user attributes will be created for attributes that do not already exist.
- Any column with the heading 'Name' will map to $Name (early documentation requiring 'Name' to be the first datacolumn can be ignored).
- A new container will be created to hold the table's rows. Unlike early versions of this feature source data is never placed in the import container's $Text. If such data is needed for review or re-import, it should be retained as an external file (or link to it via a File-type attribute).
- Each row of the table becomes a note. The table's fields are Displayed Attributes, and these attributes are populated from the table.
- For date-based data: see date formatting for import.
- If there is a 'Prototype' column in the input data and appropriately (case-sensitively) named prototypes pre-exist in the document, the record(s) will have the relevant prototype set for them (i.e. the app sets each new note's $Prototype). Note the necessary prototypes must exist in the receiving Tinderbox document before import occurs: Tinderbox will not create named prototypes found only in the data.
More on attribute creation and data types: mapping to existing attributes, detecting data types, line breaks in cell values, empty cells, etc.
The tabular feature works beyond formal spreadsheets, any tab-delimited text sample should import. This allows other material such as vcf address card data to be dragged into Tinderbox. Tinderbox will attempt auto-detection of dates, non-ASCII characters and to handle clipboard data from Numbers '09.
Also, Tinderbox will generally deal with characters in column headers that are not normally supported in attribute names. For instance, an underscore is often used as a substitute character in field names. Whilst a user cannot deliberately make an attribute named "Some_Field" via the User Attribute Inspector, because it contains an underscore, data import may do so in order to retain fidelity with source.
File Extensions. For Tinderbox to correctly handle tabular data added use and appropriate (case-insensitive) file extensions. For tab-delimited data use '.txt' or '.tsv'. For comma-separated data use '.csv'. Only tab-delimited data can be pasted directly from the clipboard and still be parsed into new notes.
Displayed Attributes. The import process sets the $DisplayedAttributes locally for all notes it creates. Thus if you apply a prototype using Displayed Attributes to the newly created notes, the Displayed Attributes will not show in the inheriting notes as the local values unless/until you reset the $DisplayedAttributes for these notes.
Prototypes. Data from a 'Prototypes' column will be used to set $Prototype for the newly imported notes, but only if the prototypes are defined in the document before the import occurs. Otherwise even if there is data, $Prototype will remain empty.
Existing notes vs New notes. The tab-delimited import method only creates new notes; there is no automated data merge for existing notes. Thus, tabular import cannot be used to set attribute values in pre-existing notes. This means a new note with a title ($Name) of an existing note will get created on import as a duplicate, rather than changing attributes of the existing note. However, this does offer scope for updating existing notes. Action code can search in a given location, for notes whose $Name matches a note elsewhere. Having identified the link, further action code could then copy across the necessary attribute values to the older pre-existing note. The newly imported imported note could then be moved to a new container outside the scope of the original query (i.e. so the update action only occurs once).
CSV content detection
From v9.2.0, CSV import of dropped/pasted data into the view pane is abandoned if more than two fields are missing. The challenge for auto-detecting CSV is that if the first paragraph/line of the received test has commas these might either be CSV markers or normal punctuation.
The revised CSV-detection logic is:
- If there is only one record (i.e. input row or line), this is not CSV data.
- Does the first record have at least 2 columns? If not, this is not CSV data.
- Note that successive commas, with or without intervening spaces, are detected as empty (valid) columns
- Is this the first record? If so, 'EXPECTED' is set as the expected number of columns in other rows.
- If this is not the first record, and this line has more columns than EXPECTED, this is not CSV data.
- If this is not the first record, and this line has 2 or more fewer columns than EXPECTED, this is not CSV data.
- Repeat for each record.
- If no record has failed CSV detection, the data is CSV.
If CSV is not detected, normal paste/drop-to-view behaviour applies. The filename (or first sentence, if not a file) is used as the new note $Name and the entire content is placed in $Text.