Quick script to export data from a SQLite database, modify, then import back
Here's a quick script to export a table from a SQLite3 database, massage the content, then import back.
The specific application here is to change the bookmark sort order in the Midori web browser, which seems to automatically sort bookmarks by Title on startup. I happened to have a bunch of bookmarks and some of them start with a lower case letter while others with upper case or even a number, all of which breaks sorting, causing bookmarks whose title begins with a digit to sort first, followed by upper case letter, then lower case. I'd like all bookmarks to sort by the first letter. Commands below are a one way to do it.
- Set SQLite file variable
- Set csv file variable
- Export the bookmark database as a csv file
- Strip leading digits from every bookmark title, except ones containing string "Scuttle"
- Convert/transform the first letter of every bookmark title to upper case
- Sort bookmark file on title (second column)
- Delete existing bookmarks from the table
- Import the bookmarks from the csv file
1 2 3 4 5 6 7 8
d=~/.config/midori/bookmarks.db f=~/midori.bookmarks.csv sqlite3 $d "select * from bookmarks;" > $f sed -i -r '/Scuttle/!s/\|[0-9]+/|/' $f sed -i -r 's/\|[a-z]/\U&/' $f sort -t '|' -k2 $f | sponge $f sqlite3 $d "delete from bookmarks;" sqlite3 $d ".import $f bookmarks"
The above commands can simply be copied/pasted all at once into a Bash terminal or placed in a script.