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?
How to programmatically create a cmt.twm from txt files
-
- Posts: 41
- Joined: Sat Sep 14, 2013 3:55 pm
-
- Posts: 41
- Joined: Sat Sep 14, 2013 3:55 pm
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?
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?
-
- Posts: 41
- Joined: Sat Sep 14, 2013 3:55 pm
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
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:
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?
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
There is no searching ability though...how do I fix this? Why isn't book search working?
-
- Posts: 41
- Joined: Sat Sep 14, 2013 3:55 pm
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.
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,
Costas
Costas
-
- Posts: 41
- Joined: Sat Sep 14, 2013 3:55 pm
Re: How to programmatically create a cmt.twm from txt files
Of course, but that just takes the fun out...