Copy from "added to library" to "access date"

[royalchris]

I have a large number of magazine records where I have not included the “access date” of an electronic copy. My solution is to bulk copy the date from the “added to library” field into the “access date” field. However, although I can see the “added to library” field in my records and can search on it, I cannot see it in the drop down menu in the Tools - change/move/copy fields menu. 

Am I missing something?

Help please

[crazygecko]

Unfortunately, the Endnote software developers made the “added to library” field inaccessible to users so users cannot copy the data to another field.

[hminney]

A lot of years later but I thought I’d use AI to solve this. 6,415 records later and it worked smoothly. The date I accessed and read these records is the date I added them to my EndNote - it is kind-of obvious!

This is “At Risk” and I’m going to explain the steps, not give the script, so you go through the steps. It’s probably different with different versions of EndNote:

Read this first — the non-negotiable safety bits

1. 1. An EndNote library is two things that must stay paired: the .enl file and the matching .Data folder. The actual database is a SQLite file inside the .Data folder (for me: <LibraryName>.Data\sdb\sdb.eni).
   2. Close EndNote completely before touching anything. The file must not be open.
   3. Get the library off any cloud-synced folder (OneDrive, Dropbox, etc.) before editing. A live SQLite database being synced mid-write is a classic source of a “database disk image is malformed” error. Copy the whole library to a local folder (e.g. C:\Temp\), pause sync, and work there.
   4. Back up. Make a full copy of the .enl file and the .Data folder before you start, kept somewhere separate. The script below also makes its own copy of the database file, but that’s a second line of defence, not the first.
   5. Work on the copy, open the copy in EndNote to verify, and only then move it back. Don’t edit your only library.
   6. If anything looks wrong at any point, stop and restore from your backup rather than letting EndNote try to “fix” a damaged file.

I went into the SQLlite database. However it’s got a number of ‘On Update’ triggers for sort-order so you have to trap each one and turn it off, then let EndNote turn them all back on again when it next starts. They aren’t important when you aren’t looking at the data in EndNote anyway, and trapping the triggers doesn’t affect the underlying database which goes back to triggering them when it’s next loaded into EndNote.

Step 1 — Read-only diagnostic (safe to run)

You’ll need Python installed (python.org; tick “Add Python to PATH” during install). Save the following as endnote_inspect.py, edit the path to point at your .Data\sdb\sdb.eni, and run it with python endnote_inspect.py. It only reads — it writes nothing.

python

import sqlite3

# EDIT THIS PATH to your own library's database file.
# Note: the .Data folder name must match your .enl name exactly.
src = r"C:\Temp\YourLibraryName.Data\sdb\sdb.eni"

conn = sqlite3.connect(src)

# EndNote uses custom collations; register harmless stand-ins so we can read.
for name in ("ENCI_Base", "ENCIN_Base"):
    conn.create_collation(name, lambda a, b: (a > b) - (a < b))

cur = conn.cursor()

print("=== columns in 'refs' (look for access_date and added_to_library) ===")
for col in cur.execute("PRAGMA table_info(refs)"):
    print(col)

print("\n=== a few rows: how is added_to_library stored, and access_date format? ===")
for row in cur.execute(
    "SELECT id, added_to_library, access_date FROM refs "
    "WHERE trash_state = 0 LIMIT 10"
):
    print(row)

print("\n=== triggers and tables touching the sort order / access_date ===")
for row in cur.execute(
    "SELECT type, name, sql FROM sqlite_master "
    "WHERE sql LIKE '%access_date%' OR sql LIKE '%SORT_KEY%' OR sql LIKE '%refs_ord%'"
):
    print(row)

print("\n=== how many references have a blank access_date? ===")
cur.execute("SELECT COUNT(*) FROM refs WHERE trash_state = 0 AND access_date = ''")
print("Blank access_date:", cur.fetchone()[0])

conn.close()

What to look at in the output

• Column names. Mine were access_date (free text) and added_to_library (an integer timestamp). Confirm yours match — they may differ by version.
• The timestamp format. Mine was a plain Unix epoch (seconds since 1970), so a value like 1245576185 decodes to a sensible 2009 date. Yours might differ; older Mac-origin libraries can use a 1904 epoch. Check that whatever decoding you use produces believable dates before writing anything.
• The triggers. Look for an AFTER UPDATE trigger on refs (mine: refs__refs_ord_AU) and any AFTER INSERT partner (refs__refs_ord_AI). Note their exact names — those are what you’ll drop. Copy the full CREATE TRIGGER ... SQL from the output and keep it, so the definitions can be restored if ever needed (EndNote also recreates them on open).
• The blank count. Tells you how many records will change. Sanity-check it against what you expect.

A note on integrity_check: if you run one, you may see complaints about terms_term_index (the keyword index). That’s a regenerable search index, not your reference data, and EndNote repairs it on open. Don’t panic at those specifically.

---

Step 2 — Hand the final write step to your own AI assistant

Rather than copy a write-script from a stranger, paste the description below into your AI tool along with your diagnostic output, and let it generate code matched to your library. Read what it produces and make sure it matches the safety notes before running.

Prompt to give your AI assistant:

"I want to edit an EndNote SQLite database (the sdb.eni file inside the .Data folder) to set the access_date field equal to the date each reference was added to the library, but only for references where access_date is currently blank (I want to preserve ones I’ve set manually). EndNote stores the created date in the added_to_library column. Here is the output of a diagnostic script showing my columns, a sample of rows, and my triggers: [PASTE YOUR DIAGNOSTIC OUTPUT HERE].

Please write a Python script using the built-in sqlite3 module that:

1. First makes a copy of the database file as a backup.
2. Opens the database and registers harmless stand-in collations for any custom EndNote collations shown in my output (e.g. ENCI_Base, ENCIN_Base), using simple string comparison.
3. Drops the EndNote sort-order triggers on the refs table shown in my output (the AFTER UPDATE and AFTER INSERT ones that call EN_MAKE_SORT_KEY), so the update doesn’t try to call EndNote’s custom sort function. Explain that EndNote rebuilds the sort order when it next opens the library.
4. Updates access_date to the added_to_library date formatted as YYYY-MM-DD (this text format sorts correctly in EndNote), converting the timestamp using the correct epoch based on my sample data — confirm with me whether my timestamps look like Unix epoch (1970) before finalising.
5. Only updates rows where trash_state = 0 and access_date = ''.
6. Prints how many rows it changed.
7. Commits and closes.

Remind me to close EndNote, work on a local copy off any cloud sync, and open the copy in EndNote afterwards to let it rebuild the sort order before I trust it."

A couple of things worth telling the AI or watching for yourself:

• Date format YYYY-MM-DD matters. EndNote sorts the Access Date column as plain text, so 2009-06-15 sorts correctly whereas 15 June 2009 or 15/06/2009 won’t.
• Backslashes in Windows paths trip up Python — use a raw string (r"C:\...") or forward slashes.
• Verify the epoch. Get the script to print a couple of decoded dates and check they’re believable before doing the bulk write.

---

Step 3 — After running

1. Open the local copy in EndNote. Let it settle — it’ll rebuild the sort order, and may offer to recover/repair the keyword index. Let it.
2. Sort by Access Date and spot-check that dates match the import dates, and that any access dates you set by hand are untouched.
3. Only once you’re happy, move the library back to its normal location (sync still paused), then resume sync. Keep your backup for a few days.

---

That’s it. It’s not pretty, and it’s emphatically not supported by Clarivate, but it works and it fills a gap the supported tools don’t. Done carefully — backups, local copy, verify-before-trust — the risk is manageable. Done carelessly it’s a great way to lose a library, so please don’t skip the safety steps.

Happy to answer questions on the approach, though I’d gently steer everyone toward running the diagnostic on their own library and letting their own AI generate the final step against their actual schema, rather than me writing the write-script for individual libraries.