EN to Excel - Importing Note Body Text into Excel

[Robert C. 16]

I've been trying to find a way to import Evernote Note Body Text into Excel.  What is the Evernote Body Text?  It's the text that is typed into the main body of the note.  Just to be clear, I've attached a pic to show what I am trying to import. (And if there is a more accurate name for it, please let me know). Also, I just want the text, nothing else.  No paragraph breaks, graphics or attachments.  Actual text formatting (bold, underline, italics) would be fine, but I'm really most concerned about getting the note body text into an Excel cell.

 

I'm not a programmer, but I'm absolutely desperate to find a solution to this problem and will give anything a try.

 

Why am I desperate?  I have to convert over 44,000 notes into an Excel database. Getting all the other attributes of the Evernotes is a piece of cake.  Copy and paste from the list view in EN to Excel and you have all the dates, titles, tags, etc. in no time. What you don't get is the body text, and I need that (along with the other attributes).

 

Anyway, I've come across an Excel macro that seems to work... sometimes.  When it works, it gives me exactly what I need.  It imports the body text of notes in an .enex file, without any formatting.  But sometimes, it gives me an error (either 5 or 52), each time with respect to this routine:

 

    Open fdgOpen.SelectedItems(1) For Input As #fp
        WholeFileContent = Input$(LOF(fp), fp)
    Close #fp

 

Can someone take a look at this code and maybe give it a test? I've attached the code in a txt file.  Any help would be deeply appreciated!

 

Thanks!

 

(I did come to this forum several months ago and tried to get a solution, but it was never resolved.  The big difference now is that I have something that does work (when it works) and I'm trying to figure out how to resolve the run errors I'm having with it).

 

 

 

 

Evernote to Excel Macro.txt

[Robert C. 16]

I think I might have narrowed down the problem.  It seems to be a file size issue.  File types under 70KB seemed to work, but over that, I had problems (see attached pics).  Any way to compensate for that? Because I have HUGE .enex files that I need to import.

 

Please don't say, "break it up into smaller files." That would be very burdensome if I had to do it that way, especially since I don't think there is a way of knowing how big the enex file will be for a given set of notes until actually exported. However, if there is, let me know.

[Martin Packer 162]

Is this going into a single cell? Maybe there's a cell size limit. (Not familiar enough with Excel to know.)

[Robert C. 16]

Is this going into a single cell? Maybe there's a cell size limit. (Not familiar enough with Excel to know.)

 

One note per row, with the body text of each note in a single cell. 

 

I know that Excel is nowhere near stressed with the amount of text per cell.  As a test, I just pasted a paragraph with 5,000 words into a single Excel cell, and Excel didn't hesitate. I'd be surprised if a single note I'm trying to import exceeds 100 words. Most are less than 20 words.

 

The problem I'm encountering has to do with the size of the .enex file itself, I think. 

 

On the one hand, if I could find a way to export to an enex without the attachments, I think my problem would be solved because the size of the enex file would plummet. Not sure how I could do that. EN has an option to download all the attachments to selected notes without the notes, but not the other way around.

 

On the other hand, there may be some coding option that could fix whatever size limitation I'm hitting in Excel.  I just don't know enough about programming in Visual Basic to know what that is.

[Martin Packer 162]

Maybe an Excel string limit is 64KB (or 32KB but 64KB seems a closer match).

[Robert C. 16]

Maybe an Excel string limit is 64KB (or 32KB but 64KB seems a closer match).

 

I think you're probably right.  Now, how to get around it? 

 

I wish there was something built into EN that would help, but I've tried everything.  The closest thing I got was exporting to HTML and creating a macro to delete everything but what I need.  It kinda worked, but was inelegant, crashed a lot, and isn't nearly as quick and easy to use as the macro we're looking at now.  

 

The other thing is that when exporting a LOT of notes in EN, exporting to enex files is reliable.  Exporting lots of notes to HTML crashes EN regularly. So having a macro that works off of enex files is really the way to go. 

[Martin Packer 162]

Well, it might be a string LITERAL limit - rather than a string VARIABLE limit. If so there's maybe hope because a variable can be - in most languages - strung :-) together from multiple shorter ones. Not sure how you do concatenation in VBA (and it's 30 years since I last touched BASIC so not keen to find out now).

[srace 0]

I need to do exactly the same thing. The macro works for me except there's a problem with encoding. French characters with accents don't appear right. Anyone have an idea how to fix this?

Thanks!