String Manipulation of a string to read data into a database

[Vast]

Hi

I need some help. I`m looking for a routine or code that will help me in reading a text file into a database. This is how the text file look.

Gen 1:1 IN THE beginning God (prepared, formed, fashioned, and) created the heavens and the earth.
Gen 1:2 The earth was without form and an empty waste, and darkness was upon the face of the very great deep. The Spirit of God was moving (hovering, brooding) over the face of the waters.
Gen 1:3 And God said, Let there be light; and there was light.
Gen 1:4 And God saw that the light was good (suitable, pleasant) {and} He approved it; and God separated the light from the darkness.

What needs to be done is to read in the book name the chapter the verse and the string, each into a different table from which searches can be done on the content.

Can Anyone help me with this or point me in the right direction where I can read up on this. Keep in mind that I am not a programmer, I do this for a hobby.I don`t worry about the code to read it into the database, that I can do myself as I have worked a lot with databases, but the manipulation of the string is a problem for me.

Thank you for any help you can provide to me.

[debasisdas]

From the string in the text file how you know which is
1. the book name
2. the chapter
3. the vers

what is the database that you are using ?

[Vast]

This is the different sections

Gen 1:1

Gen is the book

1 is the chapter
:1 is the verse number

The database is a access database or mysql

[debasisdas]

Will the length of the book, the chapter and that of the verse number will remain same ?

[Vast]

No it change quite often, the books as well as the verse numbers

Some more examples:

Matt 21:39 And they took him and threw him out of the vineyard and killed him.
Matt 21:40 Now when the owner of the vineyard comes back, what will he do to those tenants?

1 Cor 16:21 I, Paul, [add this final] greeting with my own hand.
2 Cor 1:6 But if we are troubled (afflicted and distressed), it is for your comfort (consolation and encouragement) and [for your] salvation; and if we are comforted (consoled and encouraged), it is for your comfort (consolation and encouragement), which works [in you] when you patiently endure the same evils (misfortunes and calamities) that we also suffer {and} undergo.

As you can see some off the books also has a number before them as it is a part of a series

[samsylvestertty]

The data needs to be manipulated(for any reason) needs to have a uniform format to have the job done. Im sure the length of the chapter wont be the same with bookname like I Judges or I Chronicles etc.
Ill give you a format, kindly check your text and tell whether all the book match.

BOOK_NAME<space>CHAPTER:VERSE<space>VERSE_IN_TEXT

Dim sReadLine As String
Dim sBookName As String
Dim nChapter As String
Dim nVerse As String
Dim sVerse As String
Dim sChapterVerse as String
Dim sQuery as String
Open <filename> For Input As #1
    While(!EOF(1))
        Input #1, sReadLine
        sBookName = Split(sReadLine, " ")(0) 'Book Name
        nChapter = CInt(Split(Split(sReadLine, " ")(1), ":")(0)) 'Chapter Number
        nVerse = CInt(Split(Split(sReadLine, " ")(1), ":")(1)) 'Verse Number
        ' Holds both Chapter and verse (GEN 1:1 ) 
        sChapterVerse = Split(sReadLine, " ")(0) & " " & _
                           Split(sReadLine, " ")(1) & " " 
        sVerse = Right(sReadLine, Len(sReadLine) - Len(sChapterVerse))
        'Use the variables to insert them into the database
        'The code listed below are just an example
        'Tables and column names may change
        'sQuery = "INSERT INTO BIBLE(BOOK_NAME, CHAPTER, VERSE, VERSE_IN_TEXT)" & _
        '        "VALUES('" & sBookName & '," & nChapter & "," & nVerse & "," & _
        '        "'" & sVerse & "')"
        'g_Connection.Execute sQuery
    Loop
Close #1

The above routine reads text from the text file and fills the variables that you need to be stored in the database

replace <filename> to the filename that contains the verse list.

Insertion into database needs the connection to opened. Thats why i commented it. if you can you can use it too.

The above code works well as long as the text is in the specified format.

I have the not tested the code. Pls do post if you have any trouble executing it.

Make sure you have no space in the book name field (i.e. IJUDGES not I JUDGES)

All the best

[WaltP]

Use the function instr() to find the COLON. Store that position in variable C1

Starting at C1 test each character backwards (from C1 to 1) looking for the SPACE. Store that location in S1

Starting at C1 test each character forwards (from C1 to end) looking for the SPACE. Store that location in S2

Book is in 1 to S1-1
Chap is in S1+1 to C1-1
Vers is in C1+1 to S2-1
Text is in S2+1 to end

Use the mid() function to test each character and to extract the parts.

[debasisdas]

Can you have the data in a delimited format (ex. csv) rather than text format.

[samsylvestertty]

Dim sReadLine As String
Dim sBookName As String
Dim nChapter As String
Dim nVerse As String
Dim sVerse As String
Dim sChapterVerse as String
Dim sQuery as String
Open <filename> For Input As #1
    While(!EOF(1))
        'Read line from text file
        Input #1, sReadLine
        If IsNumeric(Split(sReadLine, " ")(0)) Then 'Check whether the first word is numeric
                sBookName = Split(sReadLine, " ")(0) & " " 'Assign the Book number to the bookname               
               	'I wrote this code only because I don want to disturb other lines as I have little time to   
               	'access the internet. 
	'Remove the book number from the read line 	
                sReadLine = Right(sReadLine, Len(sReadLine) - Len(sBookName) 
	'Now the read line comes to the default format which can be processed as earlier
        End If
       'prepend the Book number to the book name
        sBookName = sReadLine & Split(sReadLine, " ")(0) 'Book Name
        nChapter = CInt(Split(Split(sReadLine, " ")(1), ":")(0)) 'Chapter Number
        nVerse = CInt(Split(Split(sReadLine, " ")(1), ":")(1)) 'Verse Number
        ' Holds both Chapter and verse (GEN 1:1 ) 
        sChapterVerse = Split(sReadLine, " ")(0) & " " & _
                           Split(sReadLine, " ")(1) & " " 
        sVerse = Right(sReadLine, Len(sReadLine) - Len(sChapterVerse))
        'Use the variables to insert them into the database
        'The code listed below are just an example
        'Tables and column names may change
        'sQuery = "INSERT INTO BIBLE(BOOK_NAME, CHAPTER, VERSE, VERSE_IN_TEXT)" & _
        '        "VALUES('" & sBookName & '," & nChapter & "," & nVerse & "," & _
        '        "'" & sVerse & "')"
        'g_Connection.Execute sQuery
    Loop
Close #1

[samsylvestertty]

Dim sReadLine As String
Dim sBookName As String
Dim nChapter As String
Dim nVerse As String
Dim sVerse As String
Dim sChapterVerse As String
Dim sQuery As String
    Open <FILENAME> For Input As #1
    Do While (Not EOF(1))
        'Read line from text file
        Input #1, sReadLine
        sReadLine = Replace(sReadLine, "<COMMA>", ",") 'Replace <COMMA> to ","
        If IsNumeric(Split(sReadLine, " ")(0)) Then 'Check whether the first word is numeric
                sBookName = Split(sReadLine, " ")(0) & " " 'Assign the Book number to the bookname
                'I wrote this code only because I don want to disturb other lines as I have little time to
                'access the internet.
                'Remove the book number from the read line
                sReadLine = Right(sReadLine, Len(sReadLine) - Len(sBookName))
                'Now the read line comes to the default format which can be processed as earlier
        End If
       'prepend the Book number to the book name
        sBookName = sBookName & Split(sReadLine, " ")(0) 'Book Name
        nChapter = CInt(Split(Split(sReadLine, " ")(1), ":")(0)) 'Chapter Number
        nVerse = CInt(Split(Split(sReadLine, " ")(1), ":")(1)) 'Verse Number
        ' Holds both Chapter and verse (GEN 1:1 )
        sChapterVerse = Split(sReadLine, " ")(0) & " " & _
                           Split(sReadLine, " ")(1) & " "
        sVerse = Right(sReadLine, Len(sReadLine) - Len(sChapterVerse))
        'Use the variables to insert them into the database
        'The code listed below are just an example
        'Tables and column names may change
        'sQuery = "INSERT INTO BIBLE(BOOK_NAME, CHAPTER, VERSE, VERSE_IN_TEXT)" & _
        '        "VALUES('" & sBookName & '," & nChapter & "," & nVerse & "," & _
        '        "'" & sVerse & "')"
        'g_Connection.Execute sQuery
    Loop
Close #1