Extracting a list of all tags using SQL or some other means

[IncrediMetaBeta 22]

Is there a workaround to getting a tag list?  Is using SQL on a copy of the .exb  file possible?  I've noticed a number of posts on the forum about obtaining a tag list.  They seem to dwell on

1) you cannot export or print a tag list in EN and/or

2) it would be a nice feature to have and will you please make it immediately or yesterday.

It appears that none of these posts has yielded a definitive answer about a workaround.

I have about 3,000 tags.  That number is rapidly growing.  I access EN two ways: EN for Windows and online in Chrome.  

I curate the tags in a hierarchy.  I am aware that the prevailing consensus seems to be that tags are a superior way to organize one's EN content than notebooks, at least beyond some threshold volume of notes.  But it has become tough to organize tags.  My tags and their hierarchy are in continual flux as I add notes.  I have about 35,000 notes.  I continue to add tags and to delete and rearrange them.   I am experimenting with the use of "special characters," ie., punctuation characters, to establish various categories.

By "curation" I mean inspecting how the tags are organized (naming conventions and hierarchical arrangement) to be sure there is internal consistency.  Part of the difficulty in doing so is that: 

a) there are so many;

I can only see a small subset of them at a time;

c) lacking an overview, I tend to have several curation and re-organization efforts going on concurrently and/or only partially implemented.

d) these curation and re-organization efforts can be in conflict.

The whole effort has become analogous to examining an entire landscape sequentially by examining parts of it through the narrow aperture of a drinking straw.   But examining things sequentially can never replace simultaneous examination.

I want to see all the tags.  I need an overview, and to see their hierarchical organization at a glance.  I am aware that there is no feature in EN to either print or export tags.  

Can I extract the tags using SQL?  Doing so would not change the actual working file.  I could use a copy of my .exb file (Windows platform), perhaps renaming it to a .db, and obtain from that renamed file a list of all of my tags.  I recognize it is not good to go into the original backend database file with SQL tools.  I would never touch that.

It would be helpful to get even just a tag list, regardless of the hierarchical level of each tag.  Getting the tags in their hierarchy would be good, though it's not strictly necessary.  

If you believe that it is infeasible to access the tags in a read-only manner via SQL, please let me know if you are aware of some other option.  For example, alternately, is there some third-party software,

perhaps analogous to VB for Office, or

some macro-like utility of some kind,

that would enable me to copy the tags successively, whether from within EN for Windows or EN in my browser, and paste them into MS Word?

I am confident that it will be infinitely easier to manage them, once I have them in a word-processing document.  I will be able to see the tags and their hierarchical position all at once rapidly, to grok relationships among them, disparities; similar, duplicative labels, etc.  Changes to them in a Word doc can then be easily made in the original EN file.

I would welcome any suggestions.  

That said, I do not imagine it would be helpful to question, why do you have so many tags?  If you are thinking in those terms, please realize that you may be using EN for entirely different purposes and/or not have as much content.  I have about 35,000 notes.  Consider that the interrelationships among notes increase enormously as you add new topical areas.  I want to track these with the tagging system.  

Nor do I imagine it would be helpful to ask, how can you use so many notes?  Consider that, for the value that I wish to create, I do not need to use them all.  Nor is it possible at the moment that I clip them to determine either exactly how I might use them or the likelihood that I will do so.  Please realize that the process of capture with EN is often done intuitively, rather than with extensive planning aforethought.  In that respect, EN is (or should be) not only a way of managing information.  It is also (or should be) a way of managing how to allocate one's limited attention.  Likewise, scrolling through a very lengthy tag list in EN for Windows does not support human cognitive strengths and inherent limitations.

 

[jefito 5,589]

2 minutes ago, IncrediMetaBeta said:

Can I extract the tags using SQL?  Doing so would not change the actual working file.  I could use a copy of my .exb file (Windows platform), perhaps renaming it to a .db, and obtain from that renamed file a list of all of my tags.  I recognize it is not good to go into the original backend database file with SQL tools.  I would never touch that.

The Evernote .exb file is a SQlite database. You can spelunk it with a tool like DB Browser for SQlite. Just open the .exb file; the tag table is named 'tag_attr'. From there you can export it as a CSV file, and open it in Excel or a text editor. All of this without using SQL. Use at your own risk, preferably on a copy of your note database.

[IncrediMetaBeta 22]

Thanks for your speedy reply to my question.  This is every exciting to hear that there is a way to do this.  I copied and renamed my .exb file to .db.  (Is that OK?)

I wonder what I'm doing wrong.  I find that the resulting CSV export of 'tag_attr' shows only column headers when I open it in Excel. 

Can you please point me in the right direction?

 

[DTLow 5,736]

53 minutes ago, IncrediMetaBeta said:

I wonder what I'm doing wrong.  I find that the resulting CSV export of 'tag_attr' shows only column headers when I open it in Excel. 

Can you please point me in the right direction?

 

Can you open the file in a text editor and look at it
You should see a simple file with values separated by commas

If you can post the file, we can give you some feedback
This is the tag table from my Mac  ZENTAG.csv  (approx 300 tags)

[IncrediMetaBeta 22]

34 minutes ago, DTLow said:

Can you open the file in a text editor and look at it
You should see a simple file with values separated by commas

If you can post the file, we can give you some feedback
This is the tag table from my Mac  ZENTAG.csv

 

 

 

 

Thanks for your quick reply.  Per your suggestion, I looked at the exported file in Notepad and discovered that all the data seems to be there, but nothing demarcates each end-of-line tag record.  Thus Excel is attempting to import the entire CSV as a single row but balks because it runs out of columns.

So, I've apparently got all the data, but I need to transform it to a 13 column by N rows format.  (I believe there are 13 fields in each record.  In Excel I see that Jefito's ZENTAG.csv file contains 13 columns.)

I suppose there are at least 2 ways to go:

1. Is there some way to configure the DB Browser for SQlite, so that it exports the data with end-of-record breaks after each 13 fields?  
2. Alternatively, I wonder whether DB Browser for SQlite by default has been stripping out existing end-of-record control characters.  If that's the case, then is there a way to prevent it from removing them?  

Below is a portion of the file from Notepad.  (I'd prefer not to post the entire file publicly, if possible.)  I've also attached the text file showing this portion of the database.  Hope this helps; thanks!)

uid,parent_uid,name,date_created,date_updated,item_color,item_style,local_flags,subtag_count,note_count,is_linked,business_id557,,humanize - storify - dramatize,736222.007222222,,,,,,1,,558,,Mystery,736222.007222222,,,,,,2,,559,,Screen Recorder,736222.007222222,,,,,,1,,560,691,Attention science,736222.007222222,,14185682,0,,,1,,561,562,Applications,736222.007222222,,,,,,2,,562,,Tek-What?,736222.007222222,,,,2,15,,,563,564,.Thesis element?,736222.007222222,,,,,,2,,564,889,.Writing Topics,736222.007222222,,,,2,7,1,,565,391760,.StartWriting Here -- header,736222.007222222,736315.976990741,5751551,0,2,11,2,,566,567,Leverage?,736222.007222222,,14185682,0,,,2,,567,,? Attention,736222.007222222,,14185682,0,2,31,4,,568,569,! Music,736222.007222222,,,,2,1,,,569,,!Concepts -- header,736222.007222222,736276.061921296,5751551,0,2,11,,,570,571,Evernote and sharing notes,736222.007222222,,,,,,1,,571,583,Ever,736222.007222222,,,,2,17,1,,572,573,.When,736222.007222222,,,,,,,,573,889,.Writing Actions,736222.007222222,,,,2,4,,,574,,

 

 

 

part of tag_attr.txt

[DTLow 5,736]

2 hours ago, IncrediMetaBeta said:

• s there some way to configure the DB Browser for SQlite, so that it exports the data with end-of-record breaks after each 13 fields?  
• Alternatively, I wonder whether DB Browser for SQlite by default has been stripping out existing end-of-record control characters.  If that's the case, then is there a way to prevent it from removing them?

This is the export panel from DB Browser on my Mac
It allows me to set the delimiters  for end of fiield/record

[gazumped 11,652]

...and see https://discussion.evernote.com/topic/101756-export-list-of-tags/

 

[jefito 5,589]

3 hours ago, DTLow said:

This is the export panel from DB Browser on my Mac
It allows me to set the delimiters  for end of fiield/record

Yup, that's it. It's similar on Windows: I chose the Windows end-of-line: CR+LF (\r\n), and that worked fine.

The nice thing about this method is that it gives you (apparently) the note count for each tag (column 'note-count'). But be careful, I've found duplicate tags in the list; I believe the ones with an empty 'parent_uid' field are tags shared to you; the note counts here don't always seem to be valid (they don't match what we see in the UI). I really haven't looked at this too closely, otherwise, but some fun stuff to play around with.

[IncrediMetaBeta 22]

Thank you for all the help!