How to import data from a TXT file to mysql with conditions?

[ivytony]

I am trying to create a database of Bible that allows people to query by chapter and/or verses.

 

I have the entire bible in a txt file, formatted in this way:

 

22:19  And if any man shall take away from the words of the book of this prophecy, God shall take away his part out of the book of life, and out of the holy city, and from the things which are written in this book.

 

22:20  He which testifieth these things saith, Surely I come quickly. Amen. Even so, come, Lord Jesus.

 

22:21  The grace of our Lord Jesus Christ be with you all. Amen.

 

I would like to import the file to mysql and save the chapter #, verse # and the verse in three different columns. I wonder how I could possibly do this?

 

Thanks

[xtopolis]

Well, you may need to change your formatting for the file, but the function that you would probably use is here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

 

It has support for fields terminated by, lines terminated by, etc... but you may have to replace the separators to match.  If your data looks like that, you could make it something like:

22|19|And if any man....

  It depends if any of those verses have a | character in them for content, which is unlikely.

 

 

 

Alternatively, depending on filesize or other things, you could use PHP to parse your file and input it, or chunk it into parsed, smaller files, and then insert those [if you want to leave your original file intact, but it is large].