Importing Kindle myclippings.txt into a useful Table

In writing my previous blog entry, I sang the praises of the highlighting and clipping feature of the Kindle, and pointed out that it actually makes highlighting “useable” for the reader and academic.  Of course, there is only so much one can do with a text file, especially since the txt document stores the notes and highlights in order, based on when you entered it, not based on the book or document you were reading.

So, the usefulness is a bit limited.  Until now. 

I decided to search and see if anyone had written a script or application to convert the myclippings.txt file into a “sort-able” document.  In so doing, I found a blog where the author went through the steps necessary to import the file to a Word table.  So far, so good.  Unfortunately, there was room for “error” when certain characters were included in the text that was clipped.  I took a few minutes to figure out some of the problems, and reached back for some old S&R techniques I have used in the past (the ever-useful “replace a ^p with a character set you never use” technique, in this case &&&).

I turned it in to a VBA Macro, which pulls everything in, and formats it into a Table.  The only thing left for you to do is delete that pesky far right column, put borders on the table, and then sort it!

I do realize I could “optimize” the macro, and perhaps write a few other loops.  Maybe when I have more time…

I will include the source code here, and will email the VBA Macro to any that email me.

Enjoy!

===============

Sub my_Clippings()
‘
‘ my_Clippings Macro
‘
‘
Selection.Find.ClearFormatting
With Selection.Find
.Text = “^p^p”
.Replacement.Text = “”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Application.WindowState = wdWindowStateMinimize
Selection.Find.ClearFormatting
With Selection.Find
.Text = “^p^p”
.Replacement.Text = “”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Application.WindowState = wdWindowStateNormal
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = “^p^p”
.Replacement.Text = “&&&”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = “^p===”
.Replacement.Text = “&&&===”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = “^p-”
.Replacement.Text = “&&&-”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = “===^p”
.Replacement.Text = “===&&&”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = “^p”
.Replacement.Text = ” ”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = “Loc.”
.Replacement.Text = ” &&&Loc.”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = “| Added ”
.Replacement.Text = ” &&& Added ”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = “&&&”
.Replacement.Text = “^p”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.WholeStory
WordBasic.TextToTable ConvertFrom:=0, NumColumns:=6, NumRows:=475, _
InitialColWidth:=wdAutoPosition, Format:=0, Apply:=1184, AutoFit:=0, _
SetDefault:=0, Word8:=0, Style:=”Table Grid”
Application.WindowState = wdWindowStateMinimize
Selection.Find.ClearFormatting
With Selection.Find
.Text = “&&&”
.Replacement.Text = “^p”
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Application.WindowState = wdWindowStateNormal
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = “Added on”
.Replacement.Text = “”
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub