Scripting Evernote in Windows

Note: 

This post was updated in June 2017.  Please go to that post and ignore this one.

Evernote is one of those applications with a lot more power then is immediately evident. For example, Podcast #9 debuted yesterday, and they talk about Saved Searches. As part of that, they talk about their rich search vocabulary and how they have hidden that ability in the API documentation.

Another wonderful ability not obvious until you search around on their website, is Evernote's scripting capabilities. They have one for Windows they built called ENScript, and another for the Macintosh. Of course, they have their full fledged API as well, but for a hobbyist programmer like myself, the scripting was intriguing.

I've been playing around with the scripting and thought I'd share an example script showing the kind of power it gives an Evernote user with just a little bit of programming ability.

Before we all began using Evernote, we all had data in a variety of file formats. Text files, PDF's, and Delicious bookmarks are all easy to move into Evernote. Excel spreadsheets can be moved into Evernote if you have a Premium account. What if instead of moving an entire spreadsheet, you instead want to create a note from each line of data in a spreadsheet?


Setup and Running a Test:

While I could have done this project using Excel's built in scripting tool, Visual Basic for Applications, I decided instead to use a wonderful, and free, scripting tool called Autoit. A future project I'll blog about soon couldn't be done with VBA but could be with Autoit. And after doing this project in VBA, I re-did it in Autoit and found Autoit did it more elegantly. So I will standardize on Autoit for my Evernote scripting examples I'll show here starting with this post.

Autoit is very reminiscent of my favorite rendition of Visual Basic, their 3.0.

Autoits power can be extended by users. For this project I needed the ability to link to Excel. I found that through a User Defined Function by a user calling himself Locodarwin.

Note: Links to the various files you'll need for this project are listed together at the bottom of this post.

Step 1: Download and install Autoit3.

Step 2: Download Locodarwin's Excel.Zip file

Step 3: Unzip the file to its own folder

Step 4: Copy the excel.au3 to Autoit's Include folder. If you installed Autoit3 to its default loation, the include folder is at : C:\Program Files\AutoIt3\Include

Step 5: Download this script : Excel_to_Evernote.au3. It does not matter where you install it.

Step 6: For testing, download the spreadsheet, books.xls.

Step 7: You'll need to load the script into an editor (Notepad will do) and change the line :

$sFilePath = "C:\Documents and Settings\Mark\My Documents\Evernote\excel_to_en\books.xls"

to have the path to your books.xls file.
Note: You will need Excel to run this script. I used Excel 2003, but theoretically it should work on later versions too. If it does not, please note in the comments.

Also, to keep the script fairly clean and easy to read, I really don't have any error checking in it.

Warning: This script is provided AS-IS with no warranties. I strongly urge you to copy your Evernote Database file to another location in case anything in this script were to mess you up. It shouldn't but you never know.

Step 7: In Evernote, create a notebook called "books". You can make it unsynchronized to save on your 40 or 500 meg upload quota.

Step 8: Run Autoit. It will prompt for the script file to run. Point it at "Excel_to_Evernote.au3" script. Additional script running methods are available in the Autoit Help file.

When the script runs it will start Excel with the Books.xls file in it. It will take a minute or so to run, and then give you a message box indicating it is done. The script, and Excel will then end.

Evernote needs another minute to process in the incoming information. Then you'll see this in the Books Notebook :




Modifying the script to import you own data:

You can modify the script to bring in your own spreadsheets from Excel.

Bring up the script in your own editor (like Notepad). The pertinent section is the "Information about your spreadsheet" section.

Note: text in the script after a semi-colon in the code is a comment.

Change this to the path and name of your own spreadsheet :

$sFilePath = "C:\Documents and Settings\Mark\My Documents\Evernote\excel_to_en\books.xls"


Change this to list the columns you want to move to a note. The first column becomes the title and first line. The subsequent columns become the next lines of the note :


$columns = "A,C,E"


Show the first row in the spreadsheet to list. It must have data in it.


$row_start = 3


Indicate the row to stop on. The script will stop before this if it finds a blank cell in the first column chosen above.


$row_end = 15


You probably won't need to change this line if you installed Evernote in the default location:


$pathToENScript = "c:\program files\evernote\evernote3\"


The script takes the contents found in cells in the first column and makes them the title of the note. You can put in text to put before that data in the title:


$ENTitle = "James Michener Books:"


Notes will be placed in the Evernote notebook you specify here :


$ENNotebook = "books"

Each note will have the tags you list in this comma separated list :

$ENTags = "book to read,michener"


How does the script work?

Basically all this script does is walk through the spreadsheet gathering the data in the appropriate columns in a row.

Then it writes a temporary temp file with the data.

Then it invokes ENSCRIPT.exe, Evernotes script engine, to create a note from the text file, with the title and tags specified, and in the specified notebook.

Then it moves on to the next row until it reaches the first row where the first column is blank, or the last row is reached.



Concluding Notes

I realized after creating this script, and starting on the next one I'll share with you in a future post, I could have instead generated an XML file with the spreadsheet data and then imported that into Evernote. That method would require only one invocation of ENSCRIPT instead of one for each record. But it would have been less illustrative then this method.

Files you need to download:

AutoIt3 Install

Excel.zip

Excel_to_Evernote.au3 (broken link fixed - May 2017, new version added June 2017)

Books.xls (broken link fixed - June 2017)


Be sure to comment if you have questions or would like share something about Evernote and scripting.