dogsheep / evernote-to-sqlite Goto Github PK

Tools for converting Evernote content to SQLite

License: Apache License 2.0

Python 100.00%

evernote dogsheep datasette-io sqlite datasette-tool

evernote-to-sqlite's Introduction

evernote-to-sqlite

Tools for converting Evernote content to SQLite. See Building an Evernote to SQLite exporter for background on this project.

Installation

Install this tool using pip:

$ pip install evernote-to-sqlite

Usage

Currently the only available command is evernote-to-sqlite enex, which converts Evernote's ENEX export files into a SQLite database.

You can create an ENEX export in the Evernote desktop application by selecting some notes (or all of your notes) and using the File -> Export Notes... menu option.

This used to be able to export everything in one go, but it looks like more recent Evernote versions only allow exporting up to fifty notes at a time, or let you export an entire notebook by right-clicking on the notebook and selecting "Export notebook...".

You can convert that file to SQLite like so:

$ evernote-to-sqlite enex evernote.db MyNotes.enex

This will display a progress bar and create a SQLite database file called evernote.db.

Limitations

Unfortunately the ENEX export format does not include a unique identifier for each note. This means you cannot use this tool to re-import notes after they have been updated - you should consider this tool to be a one-time transformation of an ENEX file into an equivalent SQLite database.

ENEX exports also do not include details of which notebook a note belongs to.

Development

To contribute to this tool, first checkout the code. Then create a new virtual environment:

cd evernote-to-sqlite
python -mvenv venv
source venv/bin/activate

Or if you are using pipenv:

pipenv shell

Now install the dependencies and tests:

pip install -e '.[test]'

To run the tests:

pytest

evernote-to-sqlite's People

Contributors

Stargazers

Watchers

Forkers

mkorosec ktaranov riverzhou engdan77 d-rep msagar

evernote-to-sqlite's Issues

Convert dates to a better format

They currently look like this:

evernote-to-sqlite/tests/test_evernote_to_sqlite.py

Lines 35 to 36 in 9d8efd1

 "created": "20201011T212822Z", 

 "updated": "20201011T213026Z",

Figure out how to display images from <en-media> tags inline in Datasette

Relates to #1. Evernote XML looks like this:

<?xml version="1.0"?>
<en-note>
  <div>This note includes two images.</div>
  <div>
    <b>The Python logo</b>
  </div>
  <div>
    <en-media hash="61098c2c541de7f0a907c301dd6542da" type="image/svg+xml" width="125"/>
  </div>
  <div>
    <b>The Evernote logo</b>
  </div>
  <div>
    <en-media hash="91bd26175acac0b2ffdb6efac199f8ca" type="image/svg+xml" width="125"/>
  </div>
</en-note>

That hash is the md5 we use to store resources. It should be possible to turn these into embedded image tags, especially if done in conjunction with the https://github.com/simonw/datasette-media plugin.

xml.etree.ElementTree.Parse Error - mismatched tag

This is an error message I get upon parsing the enex file of my Inbox. Please find the full error message below. Any hints welcome.

Importing from ENEX  [##################------------------]   50%  00:00:50
Traceback (most recent call last):
  File "/Users/utopist/.virtualenvs/evernote-to-sqlite-Og2PIW3Y/bin/evernote-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/Users/utopist/.virtualenvs/evernote-to-sqlite-Og2PIW3Y/lib/python3.9/site-packages/click/core.py", line 1137, in __call__
    return self.main(*args, **kwargs)
  File "/Users/utopist/.virtualenvs/evernote-to-sqlite-Og2PIW3Y/lib/python3.9/site-packages/click/core.py", line 1062, in main
    rv = self.invoke(ctx)
  File "/Users/utopist/.virtualenvs/evernote-to-sqlite-Og2PIW3Y/lib/python3.9/site-packages/click/core.py", line 1668, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/utopist/.virtualenvs/evernote-to-sqlite-Og2PIW3Y/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/utopist/.virtualenvs/evernote-to-sqlite-Og2PIW3Y/lib/python3.9/site-packages/click/core.py", line 763, in invoke
    return __callback(*args, **kwargs)
  File "/Users/utopist/.virtualenvs/evernote-to-sqlite-Og2PIW3Y/lib/python3.9/site-packages/evernote_to_sqlite/cli.py", line 30, in enex
    for tag, note in find_all_tags(fp, ["note"], progress_callback=bar.update):
  File "/Users/utopist/.virtualenvs/evernote-to-sqlite-Og2PIW3Y/lib/python3.9/site-packages/evernote_to_sqlite/utils.py", line 17, in find_all_tags
    for event, el in parser.read_events():
  File "/usr/local/Cellar/[email protected]/3.9.6/Frameworks/Python.framework/Versions/3.9/lib/python3.9/xml/etree/ElementTree.py", line 1329, in read_events
    raise event
  File "/usr/local/Cellar/[email protected]/3.9.6/Frameworks/Python.framework/Versions/3.9/lib/python3.9/xml/etree/ElementTree.py", line 1301, in feed
    self._parser.feed(data)
xml.etree.ElementTree.ParseError: mismatched tag: line 6837961, column 2

Use a content hash for the note IDs

Without a GUID note IDs are pretty ineffective, but using a hash of the contents will at least avoid creating identical duplicates in the future.

https://sqlite-utils.readthedocs.io/en/stable/python-api.html#setting-an-id-based-on-the-hash-of-the-row-contents

evernote-to-sqlite on windows 10 give this error: TypeError: insert() got an unexpected keyword argument 'replace'

running evernote-to-sqlite 0.2 on windows 10. Command:

evernote-to-sqlite enex evernote.db MyNotes.enex

I get the followinng error:

File "C:\Users\marti\AppData\Roaming\Python\Python38\site-packages\evernote_to_sqlite\utils.py", line 46, in save_note
note_id = db["notes"].insert(row, hash_id="id", replace=True, alter=True).last_pk
TypeError: insert() got an unexpected keyword argument 'replace'

Removing replace=True,

Leads to below error:

note_id = db["notes"].insert(row, hash_id="id", alter=True).last_pk
File "C:\Users\marti\AppData\Roaming\Python\Python38\site-packages\sqlite_utils\db.py", line 924, in insert
return self.insert_all(
File "C:\Users\marti\AppData\Roaming\Python\Python38\site-packages\sqlite_utils\db.py", line 1046, in insert_all
result = self.db.conn.execute(sql, values)
sqlite3.IntegrityError: UNIQUE constraint failed: notes.id

Configure FTS + add an index on the date columns

Sort by date descending is likely the most common way of sorting, so that column should be indexed.

Also add FTS configuration for both notes and the OCR column on resources.

time data '2014-11-21T11:44:12.000Z' does not match format '%Y%m%dT%H%M%SZ'

evernote-to-sqlite enex evernote.db ./我的笔记.enex
Importing from ENEX  [#####-------------------------------]   14%
Traceback (most recent call last):
  File "/usr/local/bin/evernote-to-sqlite", line 8, in <module>
    sys.exit(cli())
             ^^^^^
  File "/usr/local/lib/python3.11/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/evernote_to_sqlite/cli.py", line 31, in enex
    save_note(db, note)
  File "/usr/local/lib/python3.11/site-packages/evernote_to_sqlite/utils.py", line 46, in save_note
    "created": convert_datetime(created),
               ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/evernote_to_sqlite/utils.py", line 111, in convert_datetime
    return datetime.datetime.strptime(s, "%Y%m%dT%H%M%SZ").isoformat()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/Cellar/[email protected]/3.11.5/Frameworks/Python.framework/Versions/3.11/lib/python3.11/_strptime.py", line 568, in _strptime_datetime
    tt, fraction, gmtoff_fraction = _strptime(data_string, format)
                                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/Cellar/[email protected]/3.11.5/Frameworks/Python.framework/Versions/3.11/lib/python3.11/_strptime.py", line 349, in _strptime
    raise ValueError("time data %r does not match format %r" %
ValueError: time data '2014-11-21T11:44:12.000Z' does not match format '%Y%m%dT%H%M%SZ'

enex is exported by evernote mac client

Better handling of OCR data

I haven't done the FTS on OCR yet. I'm going to move that to another ticket because it requires more thought.

Originally posted by @simonw in #4 (comment)

ParseError: undefined entity &scaron;

I encountered a parse error if the enex file contained š or

Run command:
evernote-to-sqlite enex evernote.db evernote.enex

Traceback (most recent call last):
...
  File "evernote_to_sqlite/cli.py", line 31, in enex
    save_note(db, note)
  File "evernote_to_sqlite/utils.py", line 35, in save_note
    content = ET.tostring(ET.fromstring(content_xml)).decode("utf-8")
  File "/usr/lib/python3.8/xml/etree/ElementTree.py", line 1320, in XML
    parser.feed(text)
xml.etree.ElementTree.ParseError: undefined entity &scaron;: line 3, column 35

Workaround:

sed -i 's/&scaron;//g' evernote.enex
sed -i 's/&nbsp;//g' evernote.enex

Documentation on how to use this with Datasette

In particular how to use datasette-render-images to see the images.

XML parse error

I am on Windows 10 using Windows Subsystem for Linux, Python 3.8. I installed evernote-to-sqlite via pipx (in a venv). I tried using enex files from the latest version of Evernote for Windows (10.6.9 which only lets you export 50 notes at a time) and from Legacy Evernote (6.25.2.9198 which lets you export all your notes at once). The enex file from latest evernote gives this error:

File "/usr/lib/python3.8/xml/etree/ElementTree.py", line 1320, in XML parser.feed(text)
xml.etree.ElementTree.ParseError: XML or text declaration not at start of entity: line 2, column 6

The enex file from Legacy Evernote gives this error:

File "/home/david/.local/pipx/venvs/evernote-to-sqlite/lib/python3.8/site-packages/evernote_to_sqlite/utils.py", line 28, in save_note
updated = note.find("updated").text
AttributeError: 'NoneType' object has no attribute 'text'

xml.etree.ElementTree.ParseError: not well-formed (invalid token)

Got this error today:

(evernote-to-sqlite) /tmp % evernote-to-sqlite enex evernote.db simonwillison\'s\ notebook.enex 
Importing from ENEX  [######------------------------------]   17%
Traceback (most recent call last):
  File "/Users/simon/.local/bin/evernote-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/Users/simon/.local/pipx/venvs/evernote-to-sqlite/lib/python3.9/site-packages/click/core.py", line 1137, in __call__
    return self.main(*args, **kwargs)
  File "/Users/simon/.local/pipx/venvs/evernote-to-sqlite/lib/python3.9/site-packages/click/core.py", line 1062, in main
    rv = self.invoke(ctx)
  File "/Users/simon/.local/pipx/venvs/evernote-to-sqlite/lib/python3.9/site-packages/click/core.py", line 1668, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/simon/.local/pipx/venvs/evernote-to-sqlite/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/simon/.local/pipx/venvs/evernote-to-sqlite/lib/python3.9/site-packages/click/core.py", line 763, in invoke
    return __callback(*args, **kwargs)
  File "/Users/simon/.local/pipx/venvs/evernote-to-sqlite/lib/python3.9/site-packages/evernote_to_sqlite/cli.py", line 31, in enex
    save_note(db, note)
  File "/Users/simon/.local/pipx/venvs/evernote-to-sqlite/lib/python3.9/site-packages/evernote_to_sqlite/utils.py", line 36, in save_note
    content = ET.tostring(ET.fromstring(content_xml)).decode("utf-8")
  File "/usr/local/Cellar/[email protected]/3.9.6/Frameworks/Python.framework/Versions/3.9/lib/python3.9/xml/etree/ElementTree.py", line 1347, in XML
    parser.feed(text)
xml.etree.ElementTree.ParseError: not well-formed (invalid token): line 2, column 132

	"created": "20201011T212822Z",
	"updated": "20201011T213026Z",