SQLite3 csv export import of Midori web browser bookmarks to perform regex string replacements

Had a bunch of bookmarks in the Midori web browser with undesirable ampersands in the URL. The bookmark file is a SQLite3 database. Here's how I got rid of them.

First, installed sqlite3.

ak@loon:~$ sudo aptitude install sqlite3

UPDATE: See Quick script to export data from a SQLite database, modify, then import back for a more efficient/faster approach..

Then exported the bookmarks table as a csv file.

ak@loon:~$ sqlite3 .config/midori/bookmarks.db
sqlite> .mode csv
sqlite> .output midori.bookmarks.csv
sqlite> select * from bookmarks;
sqlite> .quit

I then had the bookmarks in a comma delimited file. Next up a non-greedy regex replacement of anything between an '&' (ampersand) and the first ',' (comma). Also sort the bookmarks and remove the quotes (for some reason they presented a problem during import).

ak@loon:~$ sed -r -i 's/\&[^,]*,/,/' midori.bookmarks.csv 
ak@loon:~$ sort midori.bookmarks.csv | sponge midori.bookmarks.csv
ak@loon:~$ sed -i 's/"//g' midori.bookmarks.csv

Then imported back.

ak@loon:~$ sqlite3 .config/midori/bookmarks.db
sqlite> delete from bookmarks;
sqlite> .separator ,
sqlite> .import midori.bookmarks.csv bookmarks
sqlite> .quit

Restarted Midori and proceeded to have a fantastic day.

Leave a comment

NOTE: Enclose quotes in <blockquote></blockquote>. Enclose code in <pre lang="LANG"></pre> (where LANG is one of these).