[sword-devel] Help converting text to imp

[Mike Hart]

If you look at the The excel file I sent you on Friday(?) (RVR2011-VPL-heading.xlsx) ... 

A shortened example for another Bible version, converted into open office calc format is viewable at 

http://www.archive.org/details/ImportFilterWorksheetsForSwordExamples

Open office is MUCH less efficient than Excel when building text via this method.  It does work, just somewhat slowly (be prepared to wait minutes per step on anything but a vault sized/screaming fast computer using Open office for these steps.) 


Collumn H in (VPL...xlsx) can be modified to include the import key.  

Add what I put inside brackets here ["$$$"&] to the first of cell H2 (H3 in the example) just after the "=" and before the LOOKUP, then copy/paste that cell for all of the rows. collumn (CTRL-c, CTRL-SHIFT-DOWN, CRTL-V). 

Also, To prevent trouble finding an search target to add the newlines between the key and the text, add a jedit search target like "<>" between the end of the verse number and the start of the verse.  Be sure to pick a target that is not characters already appearing in the file. 

Then copy the whole collumn, and paste it into jedit, textifying it. This step is the longest Timewise, and I've had jedit burp sometimes taking in that much data, especially if your system is low on REAL memory. Once you get this text into jedit,  You can then use regexp search replace inside jedit to move the verse text to another row. 

I only work in plain text at the moment, but this method should be osis and ThML compatible too. (but requiring significant modification to Collumn H to make the proper syntax)

More notes on these worksheets: 

Collumn A is the verse number, That text becomes part of the key when present or a 0 if nothing is there

Collumn B is the verse text. When collumn A is not empty, this field is the text that appears after the key

Collumn C contains Chapter text, When this collumn is not empty, the chapter is incremented one, and this text becomes the text that appears after the key

Collumn D is the Book Text.  When this collumn is not empty the bookname is changed to the next row in the booklist and this text becomes the text after the key. 

Collumns EFG are the math to keep up with the chapter and book numbers (EF), and a check against invalid rows, or missing verses (G).  Non blank values here should end up being a list of verses that come immediately after any excluded verses in the text (won't find excluded verses coinciding with the end of a chapter.)

Take your latest text file in row formatting for book/chapter, and make these modifications:

Insert 3 tab characters before bookname rows
Insert 2 tab characters before chapter/psalm title rows
Insert 1 tab character between the verse number in your text and the verse data (and fix the verse number to be outside brackets, the very first itam on the row if you haven't already) 

That is

Genesis
Chapter 1
1 In the beginning

Should look like: 

			Genesis
		Chapter 1
1	In the Beginning

This tab formatted text then can be pasted in this vpl-sorted excel file to update to your text. Or it can be imported by the Open File (all) dialog.