Tinderbox User-to-User Forum (for formal tech support please email: info@eastgate.com)
http://www.eastgate.com/Tinderbox/forum//YaBB.cgi
Tinderbox Users >> Exporting from Tinderbox >> Exporting Tinderbox notes to a CSV file
http://www.eastgate.com/Tinderbox/forum//YaBB.cgi?num=1377795448

Message started by A. Cimino on Aug 29th, 2013, 12:57pm

Title: Exporting Tinderbox notes to a CSV file
Post by A. Cimino on Aug 29th, 2013, 12:57pm

I need to export my Tinderbox notes to one CSV file. I am utterly out of my depth.

Here's what I'm aiming to do: generate a file that can be imported into Excel such that a note's attributes can be used as individual columns. For example, the attributes "Name" "PubAuth" and "PubTitle" should each end up as their own column.

What is the best way to approach or start with this task?

Title: Re: Exporting Tinderbox notes to a CSV file
Post by Mark Anderson on Aug 29th, 2013, 1:50pm

Excel imports tab-delimited text which will do the same task and is much easier to set up in TB than CSV (as you don't get into gnarly issue to do with quotes in values**). I'll assume you've a agent that gathers all the notes to export.

** … which you do to guard against values with commas in then. Then you have possibly values with quotes and commas and down the rabbit hole you go. Tab-delim it much easier (as generally you don't have tabs in your values - and I'm assuming you don't).

Side note.  CSV/Tab-delim tend to go awry if you have records (lines) with no value in the last column. A simple trick is to added a last dummy data column and ensiure you provide data for it. You can then not import the column or delete when imported. I'll use this method here. If you don't want/need this method just leave out the last column from the templates below.

The export process uses 2 templates. If not already added, go to the file menu and import a built-in export template. we won't use it but it will import the prototypes and containers we do want. In Outline view (do all this in Outline!) go to new root-level container "Templates". Add two new template notes> We'll call then "tdWrapper" and "tdItem". If you use different template note names you'll need to edit the code below for tdWrapper accordingly.

As the forum code format doesn't show Tab characters, I'll use {tab} wherever you need to actually type a tab character. I'll also use [return] to indicate a line break typed into the code

tdWrapper:

Code:
Name{tab}PubAuth{tab}PubTitle{tab}Dummy[return]
^children("tdItem")^

tdItem:

Code:
^value($Name)^{tab}^value($PubAuth)^{tab}^value($PubTitle)^{tab}X[return]

Note how the 'Dummy' column ensures the value of each record exported has a value "X".

Now,:
  • select your agent and open HTML view (you should see this dialog)
  • in the 'File name' box, type 'data'
  • change the 'File extension' to '.txt'
  • from the Template pop-up list, select "tdWrapper"
  • click the 'Export folder' button and select where you want the data saved
  • click the 'Export' button
  • in Finder check a file 'data.txt' has been created
  • switch to Excel (I'm using Excel 2011)
  • use menu: Date -> Get External Data -> Import Text File…
  • navigate the chooser to the data.text file
  • in import wizard step 1 choose 'Delimited'; if you don't want to import the column heading (attribute names), select '2' for the data start line
  • in steps #2 and #3 use default (at least for your first try)
  • click 'finish' and the data will import.
  • delete the dummy data column (if used)
  • done!
Although it is for a different purpose, here's a tab-delim export demo file you can play with.

ProTip: if using dates you may need to use .format() with your date attributes and tell Excel which columns are dates at step #3 of the import wizard.

[edit]Oh dear, the forum 'code' mark-up didn't like my faux tab mark-up.  I've changed the the examples above to [Tab] and [return] which do render OK. Sorry.

Edit #2, forum eat the tabs again (they looked OK in preview!). This time I'll use {tab} which seems to work

Edit #3, I forgot the $ for some of the attributes in the tdItem template. Fixed.[/edit]

Title: Re: Exporting Tinderbox notes to a CSV file
Post by A. Cimino on Aug 29th, 2013, 3:44pm

Thank you so much for your work on this. The current problem I am having starts at the export step, wherein I simply get a "data.txt" file with nothing but tdWrapper. That is, it looks like this when I open it:

Name     PubAuth    PubTitle     Dummy

And then nothing under it. I have double checked that I have correctly named my export templates both of which are placed in the same folder. Any suggestions?

Title: Re: Exporting Tinderbox notes to a CSV file
Post by Mark Anderson on Aug 29th, 2013, 4:04pm

I just noticed the forum code mark-up scrambled my examples. Now fixed - in the original post above.  Update your templates and try again.

If still no joy…

Where is the data you are exporting in relationship you the note exporting via tdWrapper? I'd assumed an agent, but a container would do as well. Either way, for this method, the notes with data in them must be direct children of the note exporting data.txt. So for an agent:

agent
  alias1
  alias2
  …etc.


alias1 is an alias of the first note bearing data, and so on. Or, if you have a container:

container
  child 1
     descendant11
  child2

child1 and child2 will export their data but descendant11 will be ignored as it isn't a direct child.

If that doesn't help, can you post a small TBX showing the problem, or give a bit more information?

Title: Re: Exporting Tinderbox notes to a CSV file
Post by A. Cimino on Aug 29th, 2013, 4:37pm

Things are looking good. Thank you! The only remaining problem is that, in the imported Excel file, every other row is empty. I'm not sure how to prevent this from happening.

Title: Re: Exporting Tinderbox notes to a CSV file
Post by Mark Anderson on Aug 29th, 2013, 5:11pm

Look at the TXT file in TextEdit. Likely it has a blank line every other data line. If so, I suspect you've two line returns at the end of tdItem instead of just one.

Title: Re: Exporting Tinderbox notes to a CSV file
Post by A. Cimino on Aug 29th, 2013, 5:16pm

Good call, I thought I had corrected the double line break, but apparently not.

Tinderbox User-to-User Forum (for formal tech support please email: info@eastgate.com) » Powered by YaBB 2.2.1!
YaBB © 2000-2008. All Rights Reserved.