If you’re actual end use is in Excel, I’d suggest exporting to a tab-delimited text file. We’ll do this using the envelope/letter method. I’ll make some assumptions, listed here:
- We can’t use inline code in $Text (leave note text alone)
- Exporting containers may also hold exportable data themselves, regardless of descendants.
- The export should work whether exporting containers only have children or have children and descendants.
- All containers export the same 2 attributes: $Name, $BloomDays.
- All template names are unique a note names within the document (this lets us call them by $Name not $Path). To assist this we’ll use a ‘t_’ prefix to our template names.
- The data will be exported manually, per container rather than as a full file export. The latter can be done but is more set-up and I don’t want to make this post over-long.
Firstly, if you’ve not added any export templates, please add the build-in ‘HTML Template’ via the File menu → Built-in Templates sub-menu. This adds the necessary containers and prototypes used for hassle-free export set-up.
Go to Window menu, ‘Show Text Pane Selector’ so you can see the export sub-tabs in the text pane for checking output.
To the /Templates container (root-level) add two new notes, which will be automatically assigned the correct prototype by the container’s $OnAdd: t_table_wrapper and t_table_item. Make these as sibling - i.e. direct children of the Templates folder so the $OnAdd fires. If you want to then nest the latter template within the former go ahead now the correct prototype has been assigned. The prototype is important in configuring the note to suppress all sorts of RTF ‘cleverness’ auto-adding corrections that will otherwise wreck your export code.
In t_table_wrapper add the following code - replace the ‘[TAB]’ items with an actual Tab character (a limitation of forum mark-up!). The layout/line breaks in the code are important for the output layout especially line breaks:
Name[tab]BloomDays
^if($BloomDays)^^value($Name)^[tab]^value($BloomDays)^
^endIf^^children("t_table_item")^
Same procedure as above, but for t_table_item:
^value($Name)^[tab]^value($BloomDays)^
^children("t_table_item")^
Open the HTML Inspector’s Export tab. Select all the containers due to export their contents. On the inspector open the ‘Template’ pop-up and select the t_table_wrapper item and in the ‘Extension’ box replace the existing values with ‘.txt.’ (as we’ll be exporting text files not HTML)
Now, if you select any of those containers and click the ‘HTML’ tab in the text pane you’ll see your file’s data:
Don’t use the ‘Preview’ tab as this isn’t HTML, just a tab-delimited table in plain text. For the latter the ‘HTML’ tab is more use (though don’t expect tabbed columns to align). It doesn’t above, but if ‘Project data’ had a value for $BloomDays it too would be in the output (I tested - it’s the conditional bit of code in the wrapper template). You can see descendants are allowed for as ‘Project A1’ exports its data.
Now, to export simply select the container to export and use File menu → Export Selected Note. Choose a name for the exported file and save. You should now be able to open these files in Excel.
Footnote: Excel is a pretty old app and isn’t too kind to more exotic unicode - if you’ve note names with lots of accents or the like they should work but may need correction (due to Excel not TB!).
