FreeREG - Using Microsoft Excel to Create a CSV file

Creating a CSV file using Microsoft Excel

Creating a CSV file from a spreadsheet program such as MS Excel is very easy indeed. (Most other spreadsheet programs will also behave in the same way). In preparing these notes we have assumed that you are somewhat familiar with the basics of using Excel.

There are a number of steps involved. Step 1: Setting up the data fields. Step 2: Entering the data. Step 3: Saving the CSV file. Step 4: Correcting the CSV file. Step 5: Adding the file Headers.

Step 1:Setting up the data fields.

Starting from scratch.
Open Excel in the normal manner and open a new file. Then to assist entry of data into row 1 enter the field headings. This will help you to remember what data goes where. (You will delete this row before submitting the data to FreeREG). As you likely now remember baptisms, marriages, and burials all have different layouts.
Using our preloaded data/column headers.
To assist we have created three simple CSV files that have the data/column headers in place and an example set of data in row 2.

Baptisms Marriages Burials

bapt.csv mar.csv bur.csv

To get started using these files, simply right click on the one you wish to use, save it into a directory of your choice and then open the saved file with your default spreadsheet program and you are ready to add your data. It would be a good idea to immediately save the file under the name of your project so that you can reuse the pro-forma file again in the future. Don't forget to delete the column headers and example data before you try to load the file on FreeREG.
Using whatever method you are now ready to enter data.

Step 2:Entering the data.
With the column headers in place you are ready to enter your data. As might be expected there are a number of rules on the values that can be entered into each of the columns. These are explained in the enter data web page. You might what to keep this page open when you are entering data or alternatively print out a copy and keep it to hand. One of the most important things to remember is that all of the fields are TEXT fields, even the date fields. Don't let Excel try to compute the date for you in a date field. If you do then we will get some strange entries, as Excel assumes that the world started in 1900. Our advice would be to select all of the columns that will contain data and format them as text before you get started. (Hold down the left button on your mouse; move over all of the active columns at the A,B,C etc level; right click on your mouse; select the format cells option; highlight the text option and click OK.)
After you have entered a few records its time to save your file; how many is up to you but you want to avoid losing all of your entries if your computer crashes or you go off and do something else. Its very frustrating to have to re-enter data because of a glitch!!!!! I know I've done it too many times. To do so follow the procedure in Step 3 below. (If you had used our preformated file then the file type is most likely already set to CSV.) When you have entered all of your data there are two final steps to be done.
FIRSTLY, as we noted above delete the column header row and any example rows that were loaded.
SECONDLY, we have to work around an apparent bug in Excel. It has been found that Excel does NOT ALWAYS put out the correct number of empty fields if the last field (Always the notes field) and some preceding fields are blank. There is a relatively simple work around. If the NOTES field is blank enter the characters zzz; easily done by copying a line cell that contains the zzz and then pasting it to all NOTE fields that are empty. Then save your file as normal and go to Step 4.

Step 3:Saving the CSV file.
With Excel running, and the table containing the data being viewed:
On the menu, Click on File, then click on "Save as..."
On the save screen you will see a place where you can "Save as type:"

Click on the little "down" button at the end of that line, and select
"CSV (Comma delimited) (*.csv)" from the list of file types, as shown in the example above. (Depending upon the version of Excel you are running you may actually see other forms of csv files with slightly different names in the drop down menu. eg cvs(MS-DOS) and cvs (Macintosh). Just ignore them and use the first one noted earlier.
Give the file to be saved a meaningful name. While you may use any file name at this point we would encourage you to start using the file naming convention required by FreeREG. The file name must consist of: [three letter Chapman code for the county][a three letter placename code assigned by you][BA|BU|MA][optional part number].CSV. e.g. NFKASLBA1.csv Make sure that you retain the .csv filename extension that was put in there for you.
Click on the "Save" button.
That's it. Done.

Step 4:Correcting the CSV file.
As noted above it is necessary to correct the CSV file for an error caused by Excel. Also after you have loaded the file into FreeREG you may have to correct entries. The best way of proceeding is to open the CSV file with Notepad or some other simple text editor. DO NOT USE WORD OR WORD PERFECT or any other word processing software. Having opened the file, select the Replace option in the Edit menu, enter zzz in the find what field, leave the Replace with field blank, select Replace All, entries disappear, when the Find and Replace is finished close it of and save the file. You are now ready for Step 5 Adding the file Headers.

Step 5:Adding the File Headers.
You are now at the penultimate stage. Before submitting the data to FreeREG it is necessary to tell FreeREG something about the data that you have entered. This is described in detail on the headers web page. As noted the headers are added using Notepad or something similar. It CANNOT be done with Excel!