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
Brief and quick thoughts about Kindle and higher education.
Like you I see the Kindle as a ‘killer device’ in higher education.
Just as I saw the mobile phone/smartphone in 2002. Colleges & Universities were motivated by finances to devise a way to recapture the income lost to dorm phones on traditional landlines. 6 years later and few schools have been able to construct a means to generate revenue from mobile student based mobile technology. A segment of the faculty don’t want mobile devices in their class. Some faculty adopt the technology to aid learning (interactive, polling, twitter, etc.). The administration can’t figure them out.
They got sidetracked with the cell phone as an emergency notification device.
Where is the $$$$?
So, how to motivate higher ed leadership to adopt the Kindle as an instructional tool integral to learning? The answer may be in the money stream.
Your suggestion of self-publishing or co-publishing with commercial publishers has merit.
The landscape is strewn with disjointed efforts to self-publish. Use of learning management systems, faculty blogs, twitter, course/faculty facebook accounts, iTunes University, faculty generated web pages, etc. etc.
The challenge here is to provide a platform to aggregate ‘published’ works across diverse platforms.
How to get all these self-published works loaded onto the Kindle?
There is the green consideration. As more faculty select e-texts for their courses students inevitably will print pages/chapter(s)/books at college provided printers or printers in their dorms. Defeats the inherent value of e-texts: lower unit cost, ease of transport, bookstore floor space & inventory management, etc.
Interesting side note. I use an e-text for my digital photography course. The primary rationale is the text is updated more frequently than publishers can print new version. The main argument from students is they are restricted to read the text while at a computer. Can’t time-shift reading assignments, read on the train/bus/standing in line. Annotate, highlight, dog ear pages, etc. And there are the inherent challenges of laptop technology.
There is the possibility of providing all incoming freshmen with a Kindle: included in the tuition. This is no different than colleges that require the purchase of a laptop. In fact it is better, IMHO. A joint effort with Amazon would seal the deal. As would a partner relationship between Amazon and publishers.
In any case it seems Amazon has given this some measure of consideration. Where do they go from here?
How quickly can those of us who have a shared vision for e-readers like the Kindle advance this technology along the Rodgers innovation adoption curve?