[Resolved] Need help parsing text file, building query

sledgeweb

Hello all.

I have a large text file I want to parse through and store the contents of it into a mysql table. Each passage is numbered, and I'd like to store each passage with it's number. The format of the text file is as follows:

Book1
1:1 Text here 1:2 More Text 2:1 More Text 3:1 More text

Book2
1:1 Text here

There is actually no BOOK1 or BOOK2, etc., but I can use the 1:1 to identify each new book. I want to store each passage using a number created by the BOOK#, CHAPTER#, and PASSAGE #. So, in the above example:

Book1 2:1 = 01-002-001 (Book-Chapter-Passage).

I've never done this kind of text parsing though. If I can isolate the passage's, I can format the data into an mysql query. I know to search for 1:1 to identify each new book, but how do I search for the chapter/passage numbers to isolate passages? I won't necessarily know how many chapters/passages are in each book. They will all be in the format of #:# however.

My idea is something like this: Have a variable to keep track of current book #, set to 0. Search for first occurence of 1:1 (not sure exactly how to find the first occurence of 1:1). Increment the current book variable (now 1). Read text until next passage number found 1:2 or 2:1. Write info to new file for SQL query using the current book, chapter and passage. If the next number is 1:1, increment the current book count. Otherwise, keep going, reading and writing info. So... I have a concept, just need some help on figuring out how to implement it.

Thanks,
Sledge

tekky

so its all just 1 long string? not a single line per item in the current file?

sledgeweb

There are line breaks before each book, 1:1. But other than that, there aren't any line breaks, so I need to search for 1-120:1-120 and read the text until the next 1-120:1-120.

sledgeweb

Anybody? Looks like maybe I can use fread to put everything into a string, and then split with split or preg_split? Any ideas?

tekky

this goes to 120:120 on EACH book?

I'm still working on ideas dont worry (I'm working too 😛)

sledgeweb

No... that's the maximum any book goes to, but they all vary. Some have 1, some have 120.

Pig

Can you please attach one of the files? I can't quite evision it.

sledgeweb

I don't have the text file with me (at work) right now. But, here's what it look likes.


Title of Book 1 (Can be ignored)
1:1 When I was a wee little lad, I sailed the ocean blue with my father. 1:2 Along the way, we were attacked by a band of thieving pirates and mermaids. 1:3 Fortunately, we were able to bribe them off with spagehttios and cheesy bread.
2:1 After 42 days on the sea, we ran into Davy Jones. 2:2 He was a kind man, though a bit thin, pasty and pale. 2:3 He offered to show us his locker, but we were short on time and politely told him we'd have to visit "some other day". 2:4 He replied with a strange smile, "Oh, yes you will. Indeed".
3:1 And everyone lived happily ever after.

Title of Book 2 (again, ignore)
1:1 Bobo is a curious man. 1:2 He's been master of ceremonies at the circus for 15 years now. 1:3 And for some strange reason, he loves to punch his clown. 1:4 All day long, he punches his clown. 1:5 Nobody knows why. 2:1 One day, while Bobo was punching his clown, his mother came to the circus. 2:2 She was horrified at what she saw. 2:3 "Bobo, why are you punching your clown!?", she exclaimed. "You know that's not how I raised you". 2:4 Embarassed, Bobo quickly put his clown away and tried to explain things away.

First let me say, the text above is just made up off the top of my head. As if you couldn't tell....

OK, so each book has a title line and there are hundreds of books in this file. Each passage in a book is marked as CHAPTER😛ASSAGE. Same kind of format used in a Bible. The Chapters can range from 1-120, and so can the Passages.

What I want to do is this:
Move through this text file searching for #:#, or [1-120]:[1-120]. If this number is 1:1, then I know it is a new book. So, I have a variable that keeps track of what book I'm on. Whatever the case, I want to search for the next #:# and store the passage inbetween in a database, referencing the Book-Chapter-Passage. For example, the example information above would be stores as:

001001001 When I was a wee little lad, I sailed the ocean blue with my father. 
001001002 Along the way, we were attacked by a band of thieving pirates and mermaids. 
001001003 Fortunately, we were able to bribe them off with spagehttios and cheesy bread.
001002001 After 42 days on the sea, we ran into Davy Jones. 
001002002 He was a kind man, though a bit thin, pasty and pale. 001002003 He offered to show us his locker, but we were short on time and politely told him we'd have to visit "some other day". 
001002004 He replied with a strange smile, "Oh, yes you will. Indeed".
001003001 And everyone lived happily ever after.
002001001 Bobo is a curious man. 
002001002 He's been master of ceremonies at the circus for 15 years now. 
002001003 And for some strange reason, he loves to punch his clown. 
002001004 All day long, he punches his clown. 
002001005 Nobody knows why. 
002002001 One day, while Bobo was punching his clown, his mother came to the circus. 
002002002 She was horrified at what she saw. 
002002003 "Bobo, why are you punching your clown!?", she exclaimed. "You know that's not how I raised you". 
002002004 Embarassed, Bobo quickly put his clown away and tried to explain things away.

Above, the first three numbers are the Book, second three are Chapter, last three are Passage.

If I can go through the file and isolate the passages and extract the numbers I need, I can write a new file that adds the mysql code needed to insert the data in the table.

Pig

I don't have to time to code it for you, but here are the steps I would take:

-open file and read it into a variable.

-do a preg_replace on the var, replacing the first character before "int:int" with ### or something similarly unique.

-explode the new variable into an array based on \r\n, so that each line becomes an element of the array. Actually, I think there might be a function that does that, but I don't recall for sure.

-do a foreach() on the array.
--use preg_match and is_int() to see if the first part of the line is a number.
---If it isn't, then move on
---If it is, then explode the current line into a sub array based on then ### that you inserted ealier.

Then you can do a preg_replace to get rid of the ###1:3 stuff.

You now have a muliti dimensional array with all of your data. do a print_r() on the new array to see what it looks like, and you should be able discern a simple foreach()/SQL scheme to process the array.

sledgeweb

Hmm... but if I use preg replace to insert the ### where the int:int occurs, it will replace the int:int. Right?

sledgeweb

Ahhh... backreferences! I never used them before, but just read up on them in the manual. OK, I now have done a preg replace inserting the ### after the int:int and using backreference to keep the int:int.

Will continue to work on the rest. Thanks for the nudge.

Pig

You're welcome. Good luck with the regex, I always shudder doing those.

sledgeweb

Mwahahah... I've done it. Well, I have to actually re write the code a little to work for the real thing, but I've gotten the data how I need it. This code isn't optimized as I've just had the breakthrough I need, but it gives you an idea for how I'm doing it. I didn't follow your method exactly pig, but you definitely got me on the right track.

//Open File
$fp = fopen("1.txt", "r");
//Read contents into variable
$data = fread($fp, filesize("1.txt"));
//Add unique characters before each passage
$dataMarked = preg_replace("/[0-9]{1,3}:[0-9]{1,3}/", "###\\0", $data);
//Explode into an array
$passagesArray = explode("###", $dataMarked);
//Set a variable to keep track of current book
$bookCount = 0;
//cycle through array
foreach($passagesArray as $value)
	{
                //Get the int:int into matches array
	$bookTest = preg_match("/([0-9]{1,3})(:)([0-9]{1,3})/", $value, $matches);
	//if the match is 1:1, increment the current book count
	if (($matches[1] == 1) && ($matches[3] == 1))
		{
		$bookCount++;
		}
                //format numbers and concatenate
	$book = sprintf("%03d", $bookCount);
	$chapter = sprintf("%03d", $matches[1]);
	$passage = sprintf("%03d", $matches[3]);
	$refNumber = $book . $chapter . $passage;
                //Remove chapter:passage numbers from passage
	$passage = addslashes(preg_replace("/[0-9]{1,3}:[0-9]{1,3}/", "", $value));
                //create query text
	$queryText = "INSERT INTO table1 VALUES ('$refNumber', '$passage')<BR>";
	print $queryText;
	}

Pig

Behold the power of PHP!

sledgeweb

If anyone is interested, here is the final code I used. The only problem I had was with the script timing out due to how big of a file it is processing. Since this is a one time conversion deal, I upped my timeout and changed it back when it was complete.

//Open file, read contents into variable, close file
$fp = fopen("textfile.txt", "r");
$data = fread($fp, filesize("textfile.txt"));
fclose($fp);

//Add marker ### before each passage
$data = preg_replace("/[0-9]{1,3}:[0-9]{1,3}/", "###\\0", $data);

//Explode passages into arrays
$passagesArray = explode("###", $data);

//Set counter for current book
$bookCount = 0;

//Cycle through array
foreach($passagesArray as $value)
	{
                //store chapter and passage numbers into array matches
	$bookTest = preg_match("/([0-9]{1,3})(:)([0-9]{1,3})/", $value, $matches);

            //if chapter is 1, and passage is 1, then increment book counter
if (($matches[1] == 1) && ($matches[3] == 1))
	{
	$bookCount++;
	}

             //Open output file. Will end up with one file for each book.
$filename = $bookCount . "-mysql.txt";
$fp = fopen("$filename", "a");

            //Format Book, Chapter and Passage numbers into a reference number
$bookNo = sprintf("%02d", $bookCount);
$chapterNo = sprintf("%03d", $matches[1]);
$passageNo = sprintf("%03d", $matches[3]);
$refNo = $bookNo . $chapterNo . $passageNo;

            //Remove chapter:passage marking, trim, and add slashes to passage
$passage = addslashes(trim(preg_replace("/[0-9]{1,3}:[0-9]{1,3}/", "", $value)));

            //Don't store book titles, create mysql query text for passages, write to file
if (($chapterNo != 0) && ($passageNo != 0))
	{
	$queryText = "INSERT INTO verses VALUES ('$refNo', '$passage ');\r\n";
	fwrite($fp, $queryText);
	}

            //Close file
fclose($fp);
}

Worked like a charm, but did take some time to process. Mostly due to the multiple file opens and closes (for each line of the array).

Thanks again for the help.

tekky

for future references (instead of changing your php settings...)

set_time_limit()