Twitter live feed  
View unanswered posts | View active topics It is currently Tue Feb 20, 2018 3:41 am

Reply to topic  [ 5 posts ] 
 How to programmatically create a cmt.twm from txt files 
Author Message

Joined: Sat Sep 14, 2013 2:55 pm
Posts: 15
Post How to programmatically create a cmt.twm from txt files
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?

Sat Nov 26, 2016 11:12 pm

Joined: Sat Sep 14, 2013 2:55 pm
Posts: 15
Post Re: How to programmatically create a cmt.twm from txt files
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?

Sun Nov 27, 2016 7:40 am

Joined: Sat Sep 14, 2013 2:55 pm
Posts: 15
Post Re: How to programmatically create a cmt.twm from txt files
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

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:

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
    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("topic_id") = i
        records("data") = c
        records("data2") = ""
    Next i
    ' Commit all the work: cleanup + new tasks
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?

Sun Nov 27, 2016 9:31 am

Joined: Sat Sep 14, 2013 2:55 pm
Posts: 15
Post Re: How to programmatically create a cmt.twm from txt files
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.

Sun Nov 27, 2016 10:07 am
Site Admin

Joined: Tue Aug 29, 2006 2:09 pm
Posts: 8570
Location: Corfu, Greece
Post Re: How to programmatically create a cmt.twm from txt files
Have you checked the importer tool? It does exactly this, you don't need to code it yourself,

Mon Dec 19, 2016 12:24 pm
Profile WWW
Display posts from previous:  Sort by  
Reply to topic   [ 5 posts ] 

Who is online

Users browsing this forum: No registered users and 1 guest

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.
Designed by STSoftware for PTF.