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.

  1. Set SQLite file variable
  2. Set csv file variable
  3. Export the bookmark database as a csv file
  4. Strip leading digits from every bookmark title, except ones containing string "Scuttle"
  5. Convert/transform the first letter of every bookmark title to upper case
  6. Sort bookmark file on title (second column)
  7. Delete existing bookmarks from the table
  8. Import the bookmarks from the csv file
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.

1 Comment

  • 1. Alain Kelder is a Giant D&hellip replies at 18th July 2012, 11:45 am :

    […] 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 […]

Leave a comment

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