Kindle MyClippings.txt to Excel

 
Copyright 2012 by Robert W. Luedtke
Have you ever looked at your “My Clippings.txt” file? Ever wished there was a good way to search or organize all the highlights, notes, and bookmarks? There are a few scripts and programs on the web to do this, but I like using tools that give me more control, like Excel. I created a macro that will import the “My Clippings.txt” file into Excel, and split the data up in the following columns:
– Title
– Author
– Type (Note, Highlight, Bookmark)
– Pages
– Location
– Comment
– Date
This way you can sort or search on any of the columns. The code is not real efficient, but it is easier to follow. Copy the code, and open an Excel spreadsheet and go to the VBA Editor. (cick Alt+F11 or Developer tab, choose Visual Basic). Insert a new module and paste the code. You can put the titles one the first row of the spreadsheet. Save the spreadsheet, and make sure it is in the same folder where you copied in the “My Clippings.txt” file. When you are ready to import the file, put your cursor in cell A2 of the spreadsheet, click the Developer Tab, choose Macros from the ribbon, and run the ClipImp macro. You should now have the clippings in Excel! If you can’t get it to work drop me a line. Let me know how it works for you!
The code is located between the horizontal lines.


Private strFilePath As String
Private strFilePath As String
Public nfile As Integer
Public strLineIn As String
Public intStrLen As Integer
Public strTA As String
Public intLineLen As Integer
Public intAloc As Integer
Public strTitle As String
Public strAuthor As String
Public strType As String
Public strTypeD As String
Sub ClipImp()  'Import text file to worksheet.
strFilePath = Application.ActiveWorkbook.Path & "\My Clippings.txt"
nfile = FreeFile
Open strFilePath For Input As #nfile
While Not EOF(nfile)
    Line Input #nfile, strLineIn
    ' Enter first line - title and author
        intStrLen = Len(strLineIn)
        strTA = strLineIn
        intLineLen = Len(strTA)
        intAloc = InStrRev(strTA, "(", -1, vbTextCompare)
        strTitle = Mid(strTA, 1, intAloc - 2)
        strAuthor = Mid(strTA, intAloc + 1, intLineLen - intAloc - 1)
    ' Enter second line - type, location and date
        Line Input #nfile, strLineIn
        strType = Left(strLineIn, 3)
        Select Case strType
            Case "- N"
                strTypeD = "Note"
                If Left(strLineIn, 9) = "- Note on" Then
                    intStrLen = Len(strLineIn)
                    intDiv = Application.WorksheetFunction.Find("|", strLineIn)
                    intPgStrt = Application.WorksheetFunction.Find("Page", strLineIn) + 5
                    strPages = Mid(strLineIn, intPgStrt, intDiv - intPgStrt - 1)
                    intDateDiv = Application.WorksheetFunction.Find("  |", strLineIn)
                    strLocate = Mid(strLineIn, intDiv + 7, intDateDiv - intDiv - 7)
                    strDate = Right(strLineIn, intStrLen - intDateDiv - 12)
                    intDayM = Application.WorksheetFunction.Find(",", strDate)
                    intDayY = Application.WorksheetFunction.Find(",", strDate, intDayM + 1)
                    strDateE = Mid(strDate, intDayM + 2, intDayY - intDayM + 4)
                Else
                    intStrLen = Len(strLineIn)
                    intDateDiv = Application.WorksheetFunction.Find("  |", strLineIn)
                    strPages = ""
                    strLocate = Mid(strLineIn, 13, intDateDiv - 13)
                    strDate = Right(strLineIn, intStrLen - intDateDiv - 12)
                    intDayM = Application.WorksheetFunction.Find(",", strDate)
                    intDayY = Application.WorksheetFunction.Find(",", strDate, intDayM + 1)
                    strDateE = Mid(strDate, intDayM + 2, intDayY - intDayM + 4)
                End If
            Case "- H"
                strTypeD = "Highlight"
                If Left(strLineIn, 13) = "- Highlight o" Then
                    intStrLen = Len(strLineIn)
                    intDiv = Application.WorksheetFunction.Find("|", strLineIn)
                    intPgStrt = Application.WorksheetFunction.Find("Page", strLineIn) + 5
                    strPages = Mid(strLineIn, intPgStrt, intDiv - intPgStrt - 1)
                    intDateDiv = Application.WorksheetFunction.Find("  |", strLineIn)
                    strLocate = Mid(strLineIn, intDiv + 7, intDateDiv - intDiv - 7)
                    strDate = Right(strLineIn, intStrLen - intDateDiv - 12)
                    intDayM = Application.WorksheetFunction.Find(",", strDate)
                    intDayY = Application.WorksheetFunction.Find(",", strDate, intDayM + 1)
                    strDateE = Mid(strDate, intDayM + 2, intDayY - intDayM + 4)
                Else
                    intStrLen = Len(strLineIn)
                    intDateDiv = Application.WorksheetFunction.Find("  |", strLineIn)
                    strPages = ""
                    strLocate = Mid(strLineIn, 18, intDateDiv - 18)
                    strDate = Right(strLineIn, intStrLen - intDateDiv - 12)
                    intDayM = Application.WorksheetFunction.Find(",", strDate)
                    intDayY = Application.WorksheetFunction.Find(",", strDate, intDayM + 1)
                    strDateE = Mid(strDate, intDayM + 2, intDayY - intDayM + 4)
                End If
            Case "- B"
                strTypeD = "Bookmark"
                If Left(strLineIn, 12) = "- Bookmark o" Then
                    intStrLen = Len(strLineIn)
                    intDiv = Application.WorksheetFunction.Find("|", strLineIn)
                    intPgStrt = Application.WorksheetFunction.Find("Page", strLineIn) + 5
                    strPages = Mid(strLineIn, intPgStrt, intDiv - intPgStrt - 1)
                    intDateDiv = Application.WorksheetFunction.Find("  |", strLineIn)
                    strLocate = Mid(strLineIn, intDiv + 7, intDateDiv - intDiv - 7)
                    strDate = Right(strLineIn, intStrLen - intDateDiv - 12)
                    intDayM = Application.WorksheetFunction.Find(",", strDate)
                    intDayY = Application.WorksheetFunction.Find(",", strDate, intDayM + 1)
                    strDateE = Mid(strDate, intDayM + 2, intDayY - intDayM + 4)
                Else
                    intStrLen = Len(strLineIn)
                    intDateDiv = Application.WorksheetFunction.Find("  |", strLineIn)
                    strPages = ""
                    strLocate = Mid(strLineIn, 17, intDateDiv - 17)
                    strDate = Right(strLineIn, intStrLen - intDateDiv - 12)
                    intDayM = Application.WorksheetFunction.Find(",", strDate)
                    intDayY = Application.WorksheetFunction.Find(",", strDate, intDayM + 1)
                    strDateE = Mid(strDate, intDayM + 2, intDayY - intDayM + 4)
                End If
        End Select
    ' Enter third line - blank line
        Line Input #nfile, strLineIn
    ' Enter fourth line - note or highlighted text
        strComment = ""
        Do While strLineIn <> "=========="
            Line Input #nfile, strLineIn
            strComment = strComment + " " + strLineIn
            Line Input #nfile, strLineIn
        Loop
    ' Enter fifth line - end of entry
        ActiveCell.Offset(0, 0).Value = strTitle
        ActiveCell.Offset(0, 1).Value = strAuthor
        ActiveCell.Offset(0, 2).Value = strTypeD
        ActiveCell.Offset(0, 3).Value = strPages
        ActiveCell.Offset(0, 4).Value = strLocate
        ActiveCell.Offset(0, 5).Value = strComment
        ActiveCell.Offset(0, 6).Value = strDateE
        ActiveCell.Offset(1, 0).Select
Wend
Close #nfile
End Sub