How to programmatically create a cmt.twm from txt files

Discussion on theWord modules and other resources
jaredaseltzer
Posts: 41
Joined: Sat Sep 14, 2013 3:55 pm

How to programmatically create a cmt.twm from txt files

Post by jaredaseltzer »

I have 23,145 txt files corresponding to each verse of the Old Testament.
Each txt file contains commentary in rtf format ready to be loaded into the 'content' table of a cmt.twm database.

So how do I do this?
What is the best way for me to take the content of these files and put them into a database, thereby creating a commentary module?

I understand that the db format has to be sqlite, but in all my vb and vba programming, I've never figured out how to use vba to programmatically put data into a database. In the past I've put the data into an Excel worksheet, saved it as a CSV and then used an sqlite database utility to import the CSV into the db as a new table called 'content'. But this trick is not possible in this case because some of the TXT files are too large (the biggest one is 189kB, or 191000+ characters long). Does someone have code to accomplish this, or does someone have good advise for me?
jaredaseltzer
Posts: 41
Joined: Sat Sep 14, 2013 3:55 pm

Re: How to programmatically create a cmt.twm from txt files

Post by jaredaseltzer »

I've been researching how to add data to a sqlite database with vba, and it appears to be possible, but it appears complex.
Programatically, I know how to load the text of each of my text files and store them into a String variable, but could someone please give me a simplified answer as to how I can then programmatically load that String variable into a .db database as the next data entry, from within the UI of vba for Excel?
jaredaseltzer
Posts: 41
Joined: Sat Sep 14, 2013 3:55 pm

Re: How to programmatically create a cmt.twm from txt files

Post by jaredaseltzer »

I've managed to use vba in excel to write all of my content to the database.
First I had to obtain the necessary driver that allows vba to communicate with sql.
I downloaded so many things trying to get it right, but I think what worked for me was
http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe.

Then I modified some vba sample code that I found to load my txt files (1.txt, 2.txt, ... , 23145.txt) into the 'content' table of my module. Here is the vba code I used:

Code: Select all

Option Explicit

Private Function OpenConnection() As ADODB.connection
    ' Read type and location of the database, user login and password
    Dim location As String
    location = "C:\Temp\MikraotGedolot.cmt.twm.db3"
    
    ' Build the connection string depending on the source
    Dim connectionString As String
    connectionString = "Driver={SQLite3 ODBC Driver};Database=" & location
    
    ' Create and open a new connection to the selected source
    Set OpenConnection = New ADODB.connection
    Call OpenConnection.Open(connectionString)
End Function


Public Sub AddData()
    Dim connection As connection
    
    Set connection = OpenConnection()

    ' Create a record-set that holds all the tasks
    Dim records As ADODB.Recordset
    Set records = New ADODB.Recordset
    Call records.Open("SELECT topic_id,data,data2 FROM content", connection, , adLockOptimistic)
    
    ' Begin a transaction to avoid corrupting the database in case of error
    connection.BeginTrans
    
    Dim i As Integer
    Dim p As String, c As String
    For i = 1 To 23145
        
        p = "C:\Users\Acer\Desktop\Biblical Hebrew\TorahEmet\MikraotGedolot Project\MGContent\TXT\" & Trim(Str(i)) & ".txt"
        c = ImportTextFile(p)
        ' Add a new task
        records.AddNew
        records("topic_id") = i
        records("data") = c
        records("data2") = ""
    Next i
    
    records.Save
    records.Close
    
    ' Commit all the work: cleanup + new tasks
    connection.CommitTrans
    
    connection.Close
End Sub

Function ImportTextFile(strFileName As String) As String

    Open strFileName For Input As #1
    ImportTextFile = Input$(LOF(1), 1)
    Close #1

End Function

So I placed the finished module into the 'Program Data' location, started up TheWord, and lo, and behold, it worked! is looks great.

There is no searching ability though...how do I fix this? Why isn't book search working?
jaredaseltzer
Posts: 41
Joined: Sat Sep 14, 2013 3:55 pm

Re: How to programmatically create a cmt.twm from txt files

Post by jaredaseltzer »

I figured out how to solve the no-searching problem. I opened my commentary in 'DB Browser for SQLite' and deleted the 'content-search' table, then opened it back up in TheWord and ran a search. TheWord automatically created the search table, and now the search function works just fine.
csterg
Site Admin
Posts: 8627
Joined: Tue Aug 29, 2006 3:09 pm
Location: Corfu, Greece
Contact:

Re: How to programmatically create a cmt.twm from txt files

Post by csterg »

Have you checked the importer tool? It does exactly this, you don't need to code it yourself,
Costas
jaredaseltzer
Posts: 41
Joined: Sat Sep 14, 2013 3:55 pm

Re: How to programmatically create a cmt.twm from txt files

Post by jaredaseltzer »

Of course, but that just takes the fun out...
Post Reply