Converting the Text File to a Spreadsheet

[OPRs] [ Home ] [ Searching and Saving the Indexes ] [ Cleaning Up the Files ] [ Converting to Database ] [ Converting to Spreadsheet ]

We have covered how to convert the downloaded OPR BIRTH text files into a "Clean" text file by removing all of the extraneous information and <CRs>. The file created is a fixed length text file capable of being imported into most database programs and imported into or opened by many spreadsheet programs. I open the file with MS Excel 97 and then save it as a Lotus 123 wk4 file to manipulate.

IMPORTING INTO MS EXCEL 97

While most database programs will import fixed length text files fairly easily, I prefer to first load the text files into a spreadsheet in order to manipulate the data more easily. I use MS Excel 97 to import the text file and Lotus 123 to manipulate the data. I assume that everything I do in Lotus 123 can also be done with MS Excel 97, but I've used Lotus 123 for over 10 years and am much more familiar with it than MS Excel 97. MS Excel 97 is much easier to use to actually create the file. To use MS Excel 97 to import the text file follow these steps.
This procedure uses a birth/christening text file but a marriage file can also be opened with minor adjustments.

Open MS Excel 97. From the menu bar select FILE - OPEN. In file type select Text Files. Select the file you wish to open. The "Text Import Wizard - Step 1 of 3" should appear. The "Original data type" should already have "Fixed width" selected.

Click on next.

The "Text Import Wizard - Step 2 of 3" should appear. This is where you set up the columns of your spreadsheet.

 

The first thing to do is remove all vertical lines that are shown. Double click on each line to remove it. Scroll to the right and double click on each line you see. When you've removed all the lines, scroll back to left to the beginning.

Just to the left of the name (position 9) click the left mouse button.
Just to the left of C: or B: (position 49) click the left mouse button.
Just to the left of the Date (position 52) click the left mouse button.
Just to the right of the Date (position 63) click the left mouse button.

Just to the left of Ba (position 111) click the left mouse button.
One space to the right of Ba (position 115) click the left mouse button.
Just to the left of Father (position 148) click the left mouse button.
One space to the right of Father (position 156) click the left mouse button.

Just to the left of the Place (position 189) click the left mouse button.

Just to the left of the FRxxxx (position 225) click the left mouse button.
Just to the left of So (position 248) click the left mouse button.
One space to the right of So (position 252) click the left mouse button.

Just to the left of Mother (position 285) click the left mouse button.
One space to the right of Mother (position 293) click the left mouse button.
Just to the left of Addl Info (position 326) click the left mouse button.

Just to the left of Pr (position 385) click the left mouse button.
One space to the right of Pr (position 389) click the left mouse button.

Click on next.

The "Text Import Wizard - Step 3 of 3" should appear. This is where you define the data type of each column. All columns should be formatted as text. Move the cursor to each column and click of Text in the "column data format".

Press "Finish". The Excel spreadsheet should open. I immediately save the file, first as an Excel file for future use and then save as a Lotus .wk4 file. I then close Excel and open the file in Lotus 123.

Cleaning Up the Lotus Spreadsheet

If everything went OK you should have a spreadsheet which resembles the pictures below. If so, all formula provided can be copied directly into the appropriate spreadsheet cell. The procedures that follow were tested on a spreadsheet containing 2,453 individual records. No ERRORs were found, though in a few isolated cases the formula did NOT produce the desired result. These cases are highlighted with a WARNING in red. Be very careful to follow the instructions in GREEN. These are instruction to convert formula to their resulting values. If you fail to this you'll find yourself in BIGGGGGGGGG trouble.

The example above is a good one because it contains just about all the problems you would encounter in downloading LDS-FHC OPRs. This includes long names with middle multiple middle names, alternate names, unwanted characters in names, and places that aren't complete without the additional information.

CREATING LABELS AND DELETING UN-NEEDED COLUMNS
The first thing I do is insert a row at the top of the spreadsheet, type in column labels and delete un-needed columns. In the first row of column A type Seq. Starting with row 2, highlight the entire column down to the last name and type /DF1 and press <ENTER> 3 times. This creates a column of original sequence numbers so that you can later resort the spreadsheet in its original order.

REMOVING EXTRANEOUS CHARACTERS
I then remove extraneous characters from the Name, Father, and Mother columns. Select the entire column B (Name) and do an EDIT - FIND AND REPLACE to replace all "." with nothing (make sure you click the REPLACE button). I also replace the "_"  and ")" with nothing. Select all of  column F (Father) and all of  column J (Mother) to replace all "_"  with nothing. Move to column C and do an EDIT - FIND AND REPLACE to replace all ":" with nothing.

 

SPLITTING THE SEX  FROM THE NAME
Insert a column between B and C and label it Sex. In cell C2 enter the formula @right(B2,1). Copy the formula down the entire column until you reach the last name. The cells in column B should contain either F or M.

Formula                                                  Value

VERY IMPORTANT: Highlight all of column C and type the following: /RV and press <ENTER> twice. This will convert the formula do the value they returned, If you fail to do this and proceed to the next step, these values will change.

Go back to column B and do an EDIT - FIND AND REPLACE to replace all " (M" with nothing and all " (F" with nothing (make sure you click the REPLACE button).

 

COMBINING "PLACE" AND "ADDL INFO" FIELDS
Insert a new column between column H (place) and I (Frame). In the second row of the new column, now column I type the following formula: @IF(@ISERR(+H2&" "&M2),H2,+H2&" "&M2). Copy the formula down the column until you get to the last entry in column H.

Formula
(Columns B thru G and J thru L have been hidden)

Values
(Columns B thru G, J thru L and N have been hidden)

Highlight the new values column I and type the following: /RV and press <ENTER> twice.
Copy the Title "Place" in Column H to Column I.  You can now delete columns M (Addl Info) and H (the old Place). Highlight column H and do an EDIT - FIND AND REPLACE to replace all ", Scotland" with nothing (make sure you click the REPLACE button).

(Columns C thru G and I thru I thru M have been hidden)

TRIMMING THE "BATCH" FIELD
Insert a new column between column E (Date) and column F (Batch). The new column will become column F. In cell F2 type the formula: @trim(G2). Copy the formula down column F until you reach the last entry in column G.

Highlight the new values in column F and type the following: /RV and press <ENTER> twice. Copy the column label in column G (Batch) to F1. Delete column G.

The spreadsheet should now look something like below:

SPLITTING THE "NAME", "FATHER" & "MOTHER" FIELDS
To split the Names into fist, middle, and last takes quite a bit of manipulation. As you can see from this example there is a wide variety of possibilities in the Name, Father, and Mother fields. If you have only a few names you can go through and manually split them up. Since I normally work with a 1,000 or more names at a time, here's how I do it.

Highlight column B (Name) and do an EDIT - FIND AND REPLACE to replace all " Or " with "%or%"
Highlight column G (Father) and do an EDIT - FIND AND REPLACE to replace all " Or " with "%or%"
Highlight column K (Mother) and do an EDIT - FIND AND REPLACE to replace all " Or " with "%or%"

Insert 9 new columns between column B and C. These new columns will become C through K.

In cell I1 type Last
In cell J1 type First
In cell K1 type Middle
Type in or copy the following formula
In cell C2: @IF(@ISERR(@FIND(" ",B2,0)),0,@FIND(" ",B2,0))
In cell D2: @IF(@ISERR(@FIND(" ",B2,C2+1)),C2,@FIND(" ",B2,C2+1))
In cell E2: @IF(@ISERR(@FIND(" ",B2,D2+1)),D2,@FIND(" ",B2,D2+1))
In cell F2: @IF(@ISERR(@FIND(" ",B2,E2+1)),E2,@FIND(" ",B2,E2+1))
In cell G2: @MIN(F2..C2)
In cell H2: @MAX(F2..C2)
In cell I2: @IF(@ISERR(@RIGHT(B2,@LENGTH(B2)-H2)),"",@TRIM(@RIGHT(B2,@LENGTH(B2)-H2)))
In cell J2: @IF(@ISERR(@LEFT(B2,G2)),"",@TRIM(@LEFT(B2,G2)))
In cell K2: @IF(@ISERR(@MID(B2,G2,H2-G2)),"",@TRIM(@MID(B2,G2,H2-G2)))

Copy the formula in cells C2 through K2 down the page until you reach the row with a name in the name field.

Check the entries in Last, First and Middle against the entries in Name.

Insert 9 new columns between columns P and Q. These new columns will become Q through Y.

In cell W1 type F-Last
In cell X1 type F-First
In cell Y1 type F-Middle

Move the cursor to C2. Holding the <SHIFT> key, press the <RIGHT ARROW> until the cursor is highlighting cells C2 through K2. Still holding the <SHIFT> key press <END><DOWN ARROW>. From the the menu select Edit - Copy. Move the cursor to Q2 and from the menu bar select Edit - Paste.

Check the entries in F-Last, F-First and F-Middle against the entries in Father.

Insert 9 new columns between column AC and AD. These new columns will become AD through AL.

In cell AJ1 type M-Last
In cell AK1 type M-First
In cell AL1 type M-Middle

Move the cursor to C2. Holding the <SHIFT> key, press the <RIGHT ARROW> until the cursor is highlighting cells C2 through K2. Still holding the <SHIFT> key press <END><DOWN ARROW>. From the the menu select Edit - Copy. Move the cursor to AD2 and from the menu bar select Edit - Paste.

Check the entries in M-Last, M-First and M-Middle against the entries in Mother.

Move the cursor to A2 and press <SHIFT><END><HOME> to highlight all the values in the spreadsheet and type the following: /RV and press <ENTER> twice.

You can now delete columns AC through AI, columns P through V and columns   B through H. Delete these columns in the order shown or else the column letters may change.

With your cursor in cell A2, hold the <SHIFT> key and press <END><HOME>. This will highlight the entire spreadsheet, except the column labels. Do an EDIT - FIND AND REPLACE to replace all "%or%" with " or ".

Warning: The formula above used to separate names will give wrong results if the last name has a space in it, such as Mac Donald. The Mac will be included as part of the Middle Name.

Your spreadsheet should now look something like this:

There are still 2 things left to do. Split the Place field and convert the date field.

SPLITTING THE "PLACE" FIELD
I prefer to split the Place field into Church, Parish, and County. To do this insert 5 new columns between L (Place) and M (Frame). The new columns will be columns M through Q.

In cell O1 type Church
In cell P1 type Parish
In cell Q1 type County
In cell M2 type the formula: @FIND(",",L2,0)
In cell N2 type the formula: @IF(@ISERR(@FIND(",",L2,M2+1)),M2,@FIND(",",L2,M2+1))
In cell O2 type the formula: @IF($M2=$N2,"",@LEFT($L2,$M2))
In cell P2 type the formula: @TRIM(@IF($M2=$N2,@LEFT($L2,$M2),@MID(L2,M2+2,N2-M2-2)))
In cell Q2 type the formula: @RIGHT(L2,@LENGTH(L2)-N2-2)

Copy the formula in cells M2 through O2 down the page until you reach the last row with a name in the Place field.

Check the entries in Church, Parish and County against the entries in Place.

Highlight the new values in columns O, P & Q and type the following: /RV and press <ENTER> twice.
Delete columns L through N. Columns P through Q will now be Columns L through N. Your spreadsheet should now look something like this:

CONVERTING THE "DATE" FIELD
The date field, as it comes into the spreadsheet, cannot be searched or sorted. To really use this field you must convert it to 3 separate fields - Day, Month, and Year. As you browse through this field you will that there are 3 possible formats:
day month year (27 Apr 1876), month year (Apr 1876) and year only (1876). This complicates the conversion.

The first thing you need to do is insert 5 new columns between column F (Event) and G (Date). The new columns will be columns G through K.

In cell I1 type Day
In cell J1 type Mon-Text
In cell K1 type Year
Type in or copy the following formula
In cell G2: @IF(@ISERR(@FIND(" ",L2,0)),0,(@FIND(" ",L2,0)))
In cell H2: @IF(@ISERR(@FIND(" ",L2,G2+1)),0,@FIND(" ",L2,G2+1))
In cell I2: @IF(H2-G2<=0,0,@VALUE(@LEFT(L2,G2))) (Note: This formula will result in day = 0 if the date does not have a  day in it. If you prefer to have the day show as a blank, replace <=0,0, with <=0,"",)
In cell J2: @IF(@LENGTH(L2)>=9,@MID(L2,G2+1,3),@IF(@LENGTH(L2)>4,@LEFT(L2,3)," "))
In cell K2: @VALUE(@RIGHT(L2,4))

Move the cursor back to I2. Hold the <Shift> key and press <End><Down Arrow><RIGHT><RIGHT>. This will highlight the Day, Mon-Text, and Year fields. Type the following: /RV and press <ENTER> twice.

Move your cursor to G1 and delete columns G and H.

Scan through your spreadsheet to compare the Day, Mon-Text, and Year with the dates in the Date field. If everything is OK you can delete the Date column.

CONVERTING THE "MON-TEXT" FIELD
The only thing left to do is convert Mon-Text field to its corresponding Month number. There are several ways to do this. The first thing you need to do is insert a new column between column G (Day) and H (Mon-Text). The new column will be column H. In cell H1 type Month.

If you only have a few records you can manually go down the spreadsheet and type in column H the number of the month in column I.

I prefer to use a lookup table. If you are using Lotus v4, v5, or 97 insert a new sheet, otherwise press <End><Home><Down><Down><Right><Right>. Input the following data into the spreadsheet:

Highlight the all the cells and name the range "Months" (/rncMonths<ENTER>) or Select RANGE from the menu bar, then select NAME from the drop down menu. In the box than appears type "Months" and press OK.

Go back to main spreadsheet and Column H (Months). In cell H2 type or copy the following formula: @vlookup(I2,$Months,1). Copy the formula down the entire column.

Formula

Highlight the new values in columns H and type the following: /RV and press <ENTER> twice.
Scan through your spreadsheet to compare the Month numbers with the text in the Mon-Text column. (Note: Dates without a month will have a month number 0. If you prefer to have the month number blank, change the 0 in the second column of your lookup table to <SPACE>).  If everything is OK you can delete column I. The spreadsheet is now complete and should look like below.

Congratulation, you've done it