Below is a re-post of an entry from August 1, 2009. My first ever re-post, but in my defense, still relevant.
And I've updated it slightly.
I am working on another project using Autoit and Evernote's Windows scripting engine, ENSCRIPT so have been looking at this script again, and fixing some broken links.
So, onto the old post ...
Evernote is one of those applications with a lot more power then is immediately evident. For example, Podcast #9 (June 2017 note - no longer available) 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 (June 2017 note: link updated) , and another for the Macintosh (June 2017 note: link updated). Of course, they have their full fledged API (Link added June 2017) 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.
Update June 2017 : I did a post about why I chose Autoit for this and future projects.
A future project I'll blog about soon couldn't be done with VBA but could be with Autoit. (June 2017 note -- never did it, and don't even remember now what it was) 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 location, 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"
(Updated June 2017)
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.
(In June 2017, I am running it in Office 365's Excel with no issues)
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 your40 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 :
(June 2017 : almost instant. Computers are faster since 2009)
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"
$sFilePath = "C:\Users\Mark\Documents\books.xls"
(Updated June 2017)
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\"
(Updated June 2017)
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.
(June 2017 : never tried that. Turns out it would not be an XML file, but a ENEX file. Slightly different beast).
Files you need to download:
AutoIt3 Install
Excel.zip
Excel_to_Evernote.au3
Books.xls
Be sure to comment if you have questions or would like share something about Evernote and scripting.
And I've updated it slightly.
I am working on another project using Autoit and Evernote's Windows scripting engine, ENSCRIPT so have been looking at this script again, and fixing some broken links.
So, onto the old post ...
Evernote is one of those applications with a lot more power then is immediately evident. For example, Podcast #9 (June 2017 note - no longer available) 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 (June 2017 note: link updated) , and another for the Macintosh (June 2017 note: link updated). Of course, they have their full fledged API (Link added June 2017) 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.
Update June 2017 : I did a post about why I chose Autoit for this and future projects.
A future project I'll blog about soon couldn't be done with VBA but could be with Autoit. (June 2017 note -- never did it, and don't even remember now what it was) 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 location, 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 :
(Updated June 2017)
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.
(In June 2017, I am running it in Office 365's Excel with no issues)
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
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 :
(June 2017 : almost instant. Computers are faster since 2009)
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:\Users\Mark\Documents\books.xls"
(Updated June 2017)
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:
(Updated June 2017)
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, a
(June 2017 : never tried that. Turns out it would not be an XML file, but a ENEX file. Slightly different beast).
Files you need to download:
AutoIt3 Install
Excel.zip
Excel_to_Evernote.au3
Books.xls
Be sure to comment if you have questions or would like share something about Evernote and scripting.
Comments
Post a Comment