Can I Export a Group of Notes to Excel?

[Kraz 1]

I want to export a group of notes to Excel so that the Note Title and its related tags populate each line across separate columns.

My goal is to use Evernote as a simple sales forecast tool.  This allow me to avoid having a cell that lists all pre-sales activities for each opp, information no one cares about.

i.e.

title                                              tag                                            tag                                        tag

ABC Company                           Open                                         $15,000.00                          3/31/2019

[jefito 5,524]

Evernote doesn't support export to Excel format in any of the Evernote clients (Windows, Mac, etc.) that I am aware of.

[DTLow 5,000]

On 1/8/2019 at 7:56 AM, Kraz said:

I want to export a group of notes to Excel so that the Note Title and its related tags populate each line across separate columns.

Excel export is not supported by Evernote; you could look at third party products (Zapier)

I use scripting (Applescript) on a Mac
The sample below is for a Numbers spreadsheet; Excel would be similar, or output to a csv file

tell application "Evernote"
     ...
     tell application id "com.apple.iWork.Numbers"
          ...
          repeat with theNote in theNoteList
              add row below last row
              tell last row
                     set value of cell 1 to theTag
                     set value of cell 2 to title of theNote
                end repeat
....

 

[CalS 4,681]

On 1/8/2019 at 8:56 AM, Kraz said:

I want to export a group of notes to Excel so that the Note Title and its related tags populate each line across separate columns.

My goal is to use Evernote as a simple sales forecast tool.  This allow me to avoid having a cell that lists all pre-sales activities for each opp, information no one cares about.

i.e.

title                                              tag                                            tag                                        tag

ABC Company                           Open                                         $15,000.00                          3/31/2019

If in Side or Top List view, you can click in the list, Ctrl+A. Ctrl+C, and paste the result into Excel.  Use Text to Columns to expand the tags and you should be in the neighborhood.  Requires a bit of Excel skill and be sure the columns displayed in the List view are the ones you want in the model.

[jefito 5,524]

9 minutes ago, CalS said:

If in Side or Top List view, you can click in the list, Ctrl+A. Ctrl+C, and paste the result into Excel.  Use Text to Columns to expand the tags and you should be in the neighborhood.  Requires a bit of Excel skill and be sure the columns displayed in the List view are the ones you want in the model.

Hey, that's a nice tip. +1 for list views. You can also just select the rows you want, doesn't need to be all of them. Stores it on the clipboard as both text and an HTML table. Excel uses the HTML clip, I'd be pretty sure.

[CalS 4,681]

44 minutes ago, jefito said:

Hey, that's a nice tip. +1 for list views. You can also just select the rows you want, doesn't need to be all of them. Stores it on the clipboard as both text and an HTML table. Excel uses the HTML clip, I'd be pretty sure.

Yeah, I use the Ctrl+A since most of the time the list is a search result   Long lists require a bit of patience,   All Notes for 40k notes takes a few minutes.  Ctrl+A, wait for the CPU to drop, Ctrl+C, wait for the CPU to drop, Paste into Excel, wait for the lines to appear.  Not too bad considering the results.  Process comes in handy every once in a while.

[Kraz 1]

Thanks CalS, I use these shortcuts all the time, never thought to use them here, this gets me mostly there, I think I can do a little more formatting to get exactly what I need, thanks again for the help

[CalS 4,681]

33 minutes ago, Kraz said:

Thanks CalS, I use these shortcuts all the time, never thought to use them here, this gets me mostly there, I think I can do a little more formatting to get exactly what I need, thanks again for the help

You are welcome. 

[n.a.k. 0]

On 1/10/2019 at 10:53 AM, DTLow said:

tell application "Evernote"
     ...
     tell application id "com.apple.iWork.Numbers"
          ...
          repeat with theNote in theNoteList
              add row below last row
              tell last row
                     set value of cell 1 to theTag
                     set value of cell 2 to title of theNote
                end repeat
....

7

 

Thanks @DTLow for the helpful code snippet!  I'm having trouble with this part of it:

                            set value of cell 4 to modification date of theNote
                            set value of cell 3 to creation date of theNote
                            set value of cell 2 to title of theNote
                            set value of cell 1 to tags of theNote

                            (full code below)

 

It doesn't pick up the notes' properties.

All works fine otherwise - it loops through each note, it would write "hello" in the right cell if that was the only thing I ask it to write, etc. I searched for hours with no solution.

Many thanks from a first-time applescripter,

N

 

tell application "Evernote"
	
	set theNotes to find notes "notebook:Reference"
	
	tell application id "com.apple.iWork.Numbers"
	
		activate
		
		try
			if not (exists document 1) then error number 1000
			
			tell document 1
				
				try
					tell active sheet
						set the selectedTable to ¬
							(the first table whose class of selection range is range)
					end tell
				on error
					error number 1001
				end try

				
				tell selectedTable
					
					repeat with theNote in theNotes
						
						add row below last row
						
						tell last row
							
							set value of cell 5 to "hi"
							set value of cell 4 to modification date of theNote
							set value of cell 3 to creation date of theNote
							set value of cell 2 to title of theNote
							set value of cell 1 to tags of theNote
						
						end tell
						
					end repeat
					
				end tell
				
			end tell
			
		end try
		
	end tell
	
end tell

 

 

[DTLow 5,000]

4 hours ago, n.a.k. said:

 set value of cell 4 to modification date of theNote
                            set value of cell 3 to creation date of theNote
                            set value of cell 2 to title of theNote
                            set value of cell 1 to tags of theNote

                            (full code below)

It doesn't pick up the notes' properties.

Without extensive debugging, I suspect the tell code for Evernote and Numbers is getting confused.
You can simply separate the Evernote and Numbers processing
So the complete Evernote code is

tell application "Evernote"
     set theNotes to find notes "notebook:Reference"
     set theNotesTitles to {}
     set theNotesModDates to {}
    set theNotesCreateDates to {}
     set theNotesTags to {}
     repeat with theNote in theNotes
          set end of theNotesTitles to title of theNote
          set end of theNotesModDates to modification date of theNote
          set end of theNotesCreateDates to creation date of theNote
          set end of theNotesTags to tags of theNote                     <<<<< THIS WILL REQUIRE MORE WORK
     end repeat
end tell

And the Numbers code is

tell application id "com.apple.iWork.Numbers"
          ...

tell selectedTable
     set theIndx to 0
     repeat with theNote in theNotes
          set theIndx to theIndx + 1
          add row below last row
          tell last row
               set value of cell 5 to "hi"
               set value of cell 4 to item theIndx of theNotesModDates
               set value of cell 3 to item theIndx of theNotesCreateDates
               set value of cell 2 to item theIndx of theNotesTitles
              set value of cell 1 to item theIndx of theNotesTags
          end tell
     end repeat
end tell

A clue about the Tags.  "Tags of theNote" returns a list of tags
You need to include a repeat loop, and I'm guessing you want the tag names separated by commas.

[n.a.k. 0]

That's is fantastic, thank you so much for helping with this @DTLow!