Welcome, Guest. Please Login
Tinderbox
  News:
IMPORTANT MESSAGE! This forum has now been replaced by a new forum at http://forum.eastgate.com and no further posting or member registration is allowed. The forum is still accessible via read-only access for reference purposes. If you wish to discuss content here, please use the new forum. N.B. - posting in the new forum requires a fresh registration in the new forum (sorry - member data can't be ported).
  HomeHelpSearchLogin  
 
Pages: 1
Send Topic Print
Exporting Tinderbox notes to a CSV file (Read 5589 times)
A. Cimino
Full Member
*
Offline



Posts: 15

Exporting Tinderbox notes to a CSV file
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?
Back to top
 
 
  IP Logged
Mark Anderson
YaBB Administrator
*
Offline

User - not staff!

Posts: 5689
Southsea, UK
Re: Exporting Tinderbox notes to a CSV file
Reply #1 - 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.

Edited:
Oh dear, the forum 'code' mark-up didn't like my faux tab mark-up.  I've changed the the examples above to       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.
Back to top
 
« Last Edit: Jul 12th, 2014, 5:01pm by Mark Anderson »  

--
Mark Anderson
TB user and Wiki Gardener
aTbRef v6
(TB consulting - email me)
WWW shoantel   IP Logged
A. Cimino
Full Member
*
Offline



Posts: 15

Re: Exporting Tinderbox notes to a CSV file
Reply #2 - 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?
Back to top
 
 
  IP Logged
Mark Anderson
YaBB Administrator
*
Offline

User - not staff!

Posts: 5689
Southsea, UK
Re: Exporting Tinderbox notes to a CSV file
Reply #3 - 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?
Back to top
 
 

--
Mark Anderson
TB user and Wiki Gardener
aTbRef v6
(TB consulting - email me)
WWW shoantel   IP Logged
A. Cimino
Full Member
*
Offline



Posts: 15

Re: Exporting Tinderbox notes to a CSV file
Reply #4 - 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.
Back to top
 
 
  IP Logged
Mark Anderson
YaBB Administrator
*
Offline

User - not staff!

Posts: 5689
Southsea, UK
Re: Exporting Tinderbox notes to a CSV file
Reply #5 - 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.
Back to top
 
« Last Edit: Aug 29th, 2013, 5:11pm by Mark Anderson »  

--
Mark Anderson
TB user and Wiki Gardener
aTbRef v6
(TB consulting - email me)
WWW shoantel   IP Logged
A. Cimino
Full Member
*
Offline



Posts: 15

Re: Exporting Tinderbox notes to a CSV file
Reply #6 - Aug 29th, 2013, 5:16pm
 
Good call, I thought I had corrected the double line break, but apparently not.
Back to top
 
 
  IP Logged
Pages: 1
Send Topic Print