Move Microsoft EDGE collections to raindrop / csv

README.md

Move Microsoft edge collections to services like Raindrop (that can accept csv)

Get the sqlite db file

1. You can find the db file in the following places

• Windows: %LocalAppData%\Microsoft\Edge\User Data\Default\Collections
• MacOS: ~/Library/Application Support/Microsoft Edge/Default/Collections
• Linux: TBD

2. Copy the files (esp. collectionsSQLite) to a seperate Folder.

Execute the script

1. Install python 3.10 and then setup pandas via pip pip install pandas
2. save collections_to_csv.py to the same folder with the db files from the previous step.
3. Exec the script python collections_to_csv.py

Then you can simply import the file normally to raindrop.

collections_to_csv.py

import json
import sqlite3
import pandas as pd

con = sqlite3.connect("./collectionsSQLite")
cur = con.cursor()

df = pd.read_sql_query(
  """
    SELECT i.title as title, c.title as folder, i.source as data, i.date_created as created
    FROM collections as c, items as i, collections_items_relationship as r
    WHERE r.item_id == i.id and r.parent_id == c.id
  """,
  con
)

def blob_to_json(col):
  my_json = col.decode('utf8')
  d = json.loads(my_json)
  return d["url"]

df["description"] = ""
df["tags"] = ""
df["created"] = df["created"].astype(int)
df["url"] = df["data"].apply(blob_to_json)

df.to_csv(
  "out.csv",
  index=False,
  columns=["url", "folder", "title", "description", "tags", "created"]
)

Shoot it doesn't seem to have exported everything for some reason. I'm not sure what happened exactly. If I upload my db files can someone take a look? I'm not that great with sql.

@Kamek437 I can do that for you! If possible, instead of sending the db file for privacy reasons, can you instead perhaps just send the table schema?

Assuming the script ran correctly with no errors (which means you have everything setup correctly)

import json
import sqlite3
import pandas as pd

con = sqlite3.connect("./collectionsSQLite")
cur = con.cursor()

for table_name in ['collections', 'items', 'collections_items_relationship']:
	df = pd.read_sql_query(
    f"PRAGMA table_info({table_name});",
    con
  )
  print(df, end="\n\n");

Thanks man! Sorry work stuff kept me. Here is the output:

    cid                    name         type  notnull dflt_value  pk
0     0                      id  LONGVARCHAR        0       None   1
1     1            date_created         REAL        1       None   0
2     2           date_modified         REAL        1       None   0
3     3                   title  LONGVARCHAR        1       None   0
4     4                position      INTEGER        1       None   0
5     5             is_syncable      INTEGER        0          1   0
6     6          suggestion_url  LONGVARCHAR        0       None   0
7     7    suggestion_dismissed      INTEGER        0       None   0
8     8         suggestion_type      INTEGER        0       None   0
9     9               thumbnail         BLOB        0       None   0
10   10     is_custom_thumbnail      INTEGER        1          0   0
11   11                     tag  LONGVARCHAR        0       None   0
12   12           thumbnail_url  LONGVARCHAR        0       None   0
13   13  is_marked_for_deletion      INTEGER        0       None   0

    cid                    name         type  notnull dflt_value  pk
0     0                      id  LONGVARCHAR        0       None   1
1     1            date_created         REAL        1       None   0
2     2           date_modified         REAL        1       None   0
3     3                   title  LONGVARCHAR        0       None   0
4     4                  source         BLOB        0       None   0
5     5             entity_blob         BLOB        0       None   0
6     6             favicon_url  LONGVARCHAR        0       None   0
7     7    canonical_image_data         BLOB        0       None   0
8     8     canonical_image_url  LONGVARCHAR        0       None   0
9     9            text_content  LONGVARCHAR        0       None   0
10   10            html_content  LONGVARCHAR        0       None   0
11   11                    type  LONGVARCHAR        0       None   0
12   12             progressing      INTEGER        0       None   0
13   13             is_syncable      INTEGER        0          1   0
14   14                   color  LONGVARCHAR        0       None   0
15   15        third_party_data         BLOB        0       None   0
16   16              remote_url  LONGVARCHAR        0       None   0
17   17                     tag  LONGVARCHAR        0       None   0
18   18  is_marked_for_deletion      INTEGER        0       None   0

   cid       name         type  notnull dflt_value  pk
0    0    item_id  LONGVARCHAR        1       None   0
1    1  parent_id  LONGVARCHAR        1       None   0
2    2   position      INTEGER        1       None   0

If you polished this up a bit and did a Medium article or something I bet you you'd get a ton of views man.

Jesus, THANK YOU.

Microsoft is so frustrating sometimes.

this is where you lost me.......save collections_to_csv.py to the same folder with the db files from the previous step.
Exec the script python collections_to_csv.py

This is not clear ..............some steps must be missing.........
Execute the script
Install python 3.10 and then setup pandas via pip pip install pandas
save collections_to_csv.py to the same folder with the db files from the previous step.
Exec the script python collections_to_csv.py
Then you can simply import the file normally to raindrop.

This is not clear ..............some steps must be missing.........

Ah think I could have worded that differently.

1. Create a file called collections_to_csv.py in the same folder as you found the db files (You can find the relevant folder from the step Get the sqlite db file
2. Install python You can find instructions here
3. Python comes with a package manager, pip on a command line run pip install pandas
4. Use the command line to execute collections_to_csv.py on the same folder by executing python collections_to_csv.py

I can help you better if you can tell me what platform/OS you are on.

Do note its been a solid minute since I wrote this, Microsoft may have updated their schema.

Hi, I am using Win 8.1 at this time.

…

__________________________________ Sent from eM Client | www.emclient.com <https://www.emclient.com/get>

------ Original Message ------ From "Blaine" ***@***.***> To "Blakeinstein" ***@***.***> Cc "Comment" ***@***.***> Date 6/24/2024 9:31:12 AM Subject Re: Blakeinstein/README.md

@Blakeinstein commented on this gist. -------------------------------------------------------------------------------- Do note its been a solid minute since I wrote this, Microsoft may have updated their schema. — Reply to this email directly, view it on GitHub <https://gist.github.com/Blakeinstein/0cdf4a17ad3dc2e440e8dd389f5f7aba#gistcomment-5099815> or unsubscribe <https://github.com/notifications/unsubscribe-auth/BJKLI6FA5RTADV6VFXJWQ4LZJBCVBBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVEYTEMBSGMZDSNJVU52HE2LHM5SXFJTDOJSWC5DF>. You are receiving this email because you commented on the thread. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

@Davecon11 Win 8.1? Holy cow man how do you not get viruses and crap. You know they don't do security patches for that anymore right? Upgrade like now if you value your data.

Thank you so much for sharing this!