Importing Microsoft Excel Data into Evernote without a Premium Account

After trying to work with Google Tasks for a year, I gave up.  Mostly due to the UI.  So I exported my Google Tasks to CSV files and then imported them to Excel.

Evernote Note Management

I have heard good things about Evernote and gave the free account a try.  It looks like the non-premium version should have all I need to work with.  Now it was time to import my CSV / Excel file.   I found that there is no clean import from a CSV file with the free account.

Excel Macro to Auto Build Evernote Special Formatted .ENEX files

After studying the import / export options, I realized that the Evernote .ENEX file format is a structured XML file.  So I exported one out of Evernote, studied its format, and then created my own Excel Macro to get my data into .ENEX files so they could be cleanly imported.

Click here to download the Excel file with sample data and the Excel Macro.

I wrote this macro with Excel 2003.  You will need to turn on Excel Macros (modify security settings) so you too can run it.  And you will need to tweak the path name and other program references to fit your data.  You should  see that the simple program is very readable.

Once you have your data exported as enumerated .enex files, use Evernote’s Import Folders feature to “suck them in” to the system.  I used tags to distinguish different types.  Once the notes are in Evernote,  it is easy to move them around.

Let me know if you find it useful.  If so, feel free to contribute to my Bitcoin address here:  1ByVdZAP13TFfeKaX2pAAdcWwAAAoKjpNb

 

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube

| Tags: , , , , | Category: Technical | 33 Comments

33 thoughts on “Importing Microsoft Excel Data into Evernote without a Premium Account

  1. Neil says:

    Thanks Marty. The macro works great and is just what I was looking for to import my Excel Notes into Evernote.

    One thing I found though was where the note contains an “&” sign like in your last sample note in the file (“I have also bought PH decreaser & calcium”) you need to replace the “&” with the code “&” or the note text itself doesn’t appear in the note imported to Evernote.

    I did this by adding the line “CBr = Replace(CBr, “&”, “&”)” in your Function CBr so it reads

    Function CBr(val) As String
    CBr = Replace(val, Chr(13), “”)
    CBr = Replace(CBr, “&”, “&”)
    End Function

    Thanks again. You saved me a lot of time and gave me the confidence to look at how the enex file is structured.

  2. Neil says:

    I’ll try again! – the code is “”&”” – It got converted to an “&” sign when I posted it

  3. Neil says:

    Once more! The code is “&” followed by “amp;”

  4. Marty Zigman says:

    Thank you Neil for enhancing this. I did notice that my notes were not showing up on the web client but were on the PC client. You probably solved it. Do you mind clarifying how the final Function should look?

    Marty

  5. Neil says:

    The function is as below except remove the + sign near the end of the second line. This is the only way I could think of to stop the code being converted to an & sign when I post it.

    Function CBr(val) As String
    CBr = Replace(val, Chr(13), “”)
    CBr = Replace(CBr, “&”, “&+amp;”)
    End Function

  6. Nige says:

    Can you tell me how to use this please.
    I have a file of data in csv format that I’d like to get into evernote.
    I’ve imported your xls file, not sure how I get it into enex format to save ready for Evernote.
    Thanks
    Nige

  7. Marty Zigman says:

    Hi Nigel,

    Did you study the sample data format to get your data in a similar structure? Did you active the Excel Macro Security setting so that Macros will run? Once you run the Excel Macro, you will get a ENEX ready file that you can use to import.

    Marty

  8. Nige says:

    Hi Marty
    Just delving into the wonders of macros now 🙂
    I’m trying to jiggle it about a bit.
    The macro as it stands, creates one enex file for each note I’ve got.
    When I ran it against my own csv it created 250 output files hee hee.
    Trying to frig it so it just creates the one file.
    A very useful little macro thanks 🙂
    Nige

  9. Marty Zigman says:

    Hi Nige,

    It should create one file. I suspect something is up with the way your data is structured.

    Marty

  10. Marty Zigman says:

    For those having some trouble with that function, see this via image:
    Evernote VB Code Function

  11. Michael says:

    This is great! Just what I was looking for, thanks Marty. However, I am getting 4 different files. I have tried both the original code and the amended codes.

  12. JD says:

    I was wondering if anyone had come up with a way to move data in the opposite direction. I used Evernote to do some inventory with pictures. Now, I want that data in a spreadsheet or database program, but I can’t figure out how to make that happen.

  13. Robert Pate says:

    Marty,

    Great macro! I have changed it a little and I’m posting those changes here:

    * Outputs all notes into one import file
    * Lets you set the create date for each note
    * Removes the column for tags (they are easy to add post-import)
    * Includes Neil’s update to the CBr function.

    You can copy my source code from here:
    https://gist.github.com/2992931/

    Or download a zip of the excel file here:
    https://dl.dropbox.com/u/6237857/Evernote%20Import%20Macro.zip

    Thanks,
    Robert

  14. Liz Decker says:

    I have the same problem as JD. I want to export tags and notes from Evernote to excel.I have a premium account.It gives the option but I can’t figure it out. Anyone have a solution? Thanks for your help.

  15. Marty Zigman says:

    Robert,

    Thanks for updating the macro and including the enhancements. I am sure many here will appreciate it.

    Marty

  16. Gonzalo Orti says:

    Hello,
    It’s great, I succeded with you macro but had to change the writing a little as my language is Spanish and I had a error of not UTF-8 formatting. So I found how to save each .ENEX file to UTF-8 format.

    I copy my code in case it is useful for other people:

    [marty here] Here is the code for download as the information below may not show up clean.

    Sub OutputNotesXML()

    Dim iRow As Long

    With ActiveSheet
    ‘For iRow = 2 To 2
    For iRow = 2 To .Cells(.Rows.Count, “A”).End(xlUp).Row
    Dim DOC As Object
    Set DOC = CreateObject(“ADODB.Stream”)
    DOC.Type = 2
    DOC.Charset = “utf-8”
    DOC.Open
    DOC.WriteText “”
    DOC.WriteText “”
    DOC.WriteText “”
    DOC.WriteText “”

    DOC.WriteText .Cells(iRow, “A”).Value ‘Title

    DOC.WriteText “<![CDATA[”
    DOC.WriteText “”
    DOC.WriteText “”

    DOC.WriteText CBr(.Cells(iRow, “C”).Value) ‘Note

    DOC.WriteText “]]>20120202T042955Z20120202T070232Z”

    DOC.WriteText .Cells(iRow, “B”).Value ‘Tag

    DOC.WriteText “34.03670000000000-118.4060000000000135.59999847412109mobile.android”
    DOC.SaveTofile “C:\Users\G\Downloads\Evernote\” & iRow & “.enex”
    Next iRow
    End With

    End Sub

  17. Marty Zigman says:

    Thank you Gonzalo for the updates. This should help folks that have international concerns.

    Maryt

  18. Gonzalo Orti says:

    When I pasted here the code, many parts dissapeared, I don’t know why (everything after DOC.WriteText) so I cannot show the exact code I used.

    You can recover my code only remarking that where there was written in the original macro “Print #1,” I changed it by “DOC.WriteText”. The rest at the right of this expression is the same of the original macro.

  19. Marty Zigman says:

    Send me the script in a file and we will recraft the message with a link to a downloadable file. marty.zigman@prolecto.com

    Marty

  20. Gonzalo Orti says:

    By the way, I have a problem with the imported notes. They are put into a folder called Imported Notes tha is not syncronised. When I put them into a syncronised folder and I sincronised it takes some time and after 5% their is an syncronisation error. All the notes but two (they are 168) are put in a folder called “Unsynced Notes”, and I cannot syncronise them any more.

    Can it be a problem with the format? Anybody had the same problem?

  21. Kipe says:

    Hi there! Based on this work, I was able to create another Excel macro, which is capable of reading Evernote files (*.enex) into Excel worksheet.
    This is a really basic implementation but it’s enought for me: https://gist.github.com/md2z34/4748871

  22. Marty Zigman says:

    Thanks Kipe.

    Here is the link again:

    https://gist.github.com/md2z34/4748871

    I have reviewed the code but have not tried it. If anyone has thinking on this new code, let us know.

    Marty

  23. Bling says:

    Thank you all! Extremely helpful.
    On here:
    CBr = Replace(val, Chr(13), “”)

    It should be as Marty published:, with the BR:
    CBr = Replace(val, Chr(13), “”)

    .. however, the br should be in lowercase. This is important, otherwise evernote will not sync. It took me hours and hours to figure out this was the issue.

    I think you may also need to enclose the notes in .

  24. Bling says:

    It appears the comment posting takes way the “br”. I was trying to type:

    CBr = Replace(val, Chr(13), “[**insert “” here**]”)

  25. Bling says:

    eh, no avail – still can’t type it out. anyhow, it is the tag with br forward slash for html

  26. Mark Lewis says:

    Hi, Thank you for that very useful macro

    I’m wondering if it possible to import many Enex files at once.

  27. Hi Marty,

    This article and the comments are a great resource! I did up an ad hoc random Evernote generator and now, I’m ready to dig deeper. Your examples and code will save me a lot of time, as I’m not very conversant with XML. If and when I come up with anything interesting, I’ll be sure to share with this community!

    Cheers,

    Mitch

  28. Peter Buyze says:

    I don’t understand why you went through these complicated contorsions. By just going through tools/import folders you can import xls(x) files. So rather than creating csv files, you could have just imported the excel files.

  29. Robyn says:

    I’m interested in editing Excel documents on my iPad and computer using Evernote. I tried to import a file, but it just looks like a link to a file and I don’t see how to edit using Evernote. Would a macro help? I know nothing about using macros, so I’d like easy to follow instructions. Thank you!

  30. Joe Ramsay says:

    Thanks for this macro Marty.

    I tried using the macro file to import some old data from a MS Access datafile (exported first to an .xls file of course).
    Problem #1 The macro created a separate .enex file for every record (I have 2700 records to import as notes).
    Problem #2 In order to import en mass I created an import folder in my Windows Desktop EN app. Not all of the .enex files would import, giving me errors on about 25% of them.
    Problem #3 I tried instead to import the 2700 files on my MacBook. However, with the Mac EN app, the tags would not import, and only note titles would show – the body of the notes was not showing up even through the note sizes were sometimes large (>10k).

    Any suggestions?

  31. Mark Lewis says:

    Hello

    I can’t seem to import notes that contain french accents. What can I do ? Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *