sobrique | How to report on your (Chrome) web history

For reasons not entirely related to 'just curiosity'. Here's how to do a report on your web history in Chrome.
Chrome is really nice, in that it uses a 'database format' for the various file. SQLite can be used to query it.

So here's how to do it (mostly for my reference):

1: Download SQLite shell from here: http://www.sqlite.org/download.html

2: Run a command prompt

3: Change to your 'chrome' history directory:
On XP – C:\Documents and Settings\

name\Local Settings\Application Data\Google\Chrome\User Data
On Vista - C:\Users\

name\AppData\Local\Google\Chrome\User Data

4: (Assuming you put the SQLite shell in D:\Tools - amend accordingly)
D:\Tools\sqlite3.exe History "select datetime(last_visit_time/1000000-11644473600,'unixepoch'),url from urls order by last_visit_time desc" > History.txt

This will extract the complete history - at least, assuming you haven't cleared it ever.

5: Open Excel, and import 'History.txt'. Use 'text to columns' (location varies depending on Excel version) and use delimiters of 'space' or '|' (other).

6: Add a couple of columns to your spreadsheet (I used C and D).
Title C as 'Find/' and D as 'Site'.
Populate C2 with: =FIND("/",E2,9)
Populate D2 with: =LEFT(E2,C2-1)

Paste the formula down the full list.

7: Add another column, at the end (Column F?) and put in it '=A1' and paste down. (Yes, that does mean Column F is a copy of Column A. There is method in my madness. )

8: Make a pivot table of your results. Set 'row' to 'Site', and 'values' to 'Count of URLs'.

9: Add a vlookup to your pivot table - if you've done the above, and happen to be on exactly the same versions of everything as me, Column C should be empty. Fill it in with: =VLOOKUP(A5,Sheet1!D:G,3,0) (The 'third column in the range D:G will be 'Column F' so this _should_ populate Column C with the last accessed date of that site)

10: Scroll down the list, and be shocked and awed at how many times you've visited Facebook.Com.