Introduction

This Section will discuss what CensusExtract is


What Is CensusExtract

CensusExtract is a System to Capture, Format, and Store Census Data

  • The census data is captured by transcribing it into specially formatted Excel worksheets.
  • The CensusExtract program extracts the census data from the Excel worksheet and formats it so that it can be copied into the Windows clipboard.
  • The formatted census data is then pasted into the genealogy database of your choice.
  • The formatted data can be modified to suit the individual users needs by:
    1. Modifying the formatting rules in CensusExtract.
    2. Editing the data in the CensusExtract text boxes before copying and pasting into your genealogy database.
    3. Editing the data after it reaches your genealogy database.
  • CensusExtract is a Windows application written in C# and using an XML file to store formatting rules.


System Requirements

  • The CensusExtract program has been successfully run on:
    1. Windows XP.
    2. Windows Vista
    3. Windows 7.
  • Microsoft Excel 2003 or Microsoft Excel 2007.
  • Microsoft .NET Framework. Most computers already have the .NET framework installed. If yours does not, it will be installed before the program itself is installed.


Why Does CensusExtract Exist

  • I wanted to capture all of the juicy bits of data in the census without having to create individual tags or events for them. I also wanted to capture the different ways names, ages, birthplaces, etc. were presented in different census years.
  • I discovered CensusTools http://www.censustools.com/ and thought spreadsheets were a good way to capture the data. But then realized that I would have to format and retype everything to get into my genealogy database. Ugh!
  • Because programmers like myself are lazy (no retyping) and not too smart, I started around 2002 developing the tool as it exists today. What started out as a programmer grade hack tool to help me with my research has evolved into a tool I think is ready to share with a larger group.


Cost

What does CensusExtract cost?

  • CensusExtract is provided free of charge to use as you wish except you may not charge others to use it.
  • However, if you try the program and find that it is usesful I would appreciate an email to telling me so.
  • Please note that this is a request. You are under no obligation legal or moral to send me an email


Who Is Joe Flint

  • I work as a programmer at an insurance company coding in C#, ASP.Net, SQL Server, VB, and COBOL.
  • I’ve been a genealogist since my mother died in the fall of 2000.
  • I’m currently a TMG user and have been since 2000.


Install

This Section will discuss how to install CensusExtract and what is placed on your computer during the install


Install

How to install CensusExtract

  • Go to my web site www.joeflint.com
  • Click the Links button.
  • Scroll to the bottom of the page to the Downloads area.
  • Click the CensusExtract link.
  • Save Zip file to your hard drive.
  • Unzip the Zip file.
  • Double click the setup.exe.
  • Follow the simple directions.


What is intalled by CensusExtract

What is installed by CensusExtract setup?

  • The CensusExtract program
  • A CensusExtract program shortcut on your desktop.
  • A CensusExtract data folder in your Documents/My Documents folder containing:
    1. An Excel folder
    2. A GenDatabase Folder
    3. An XML Folder
  • A CensusExtract program folder in All Programs containing:
    1. A CensusExtract program shortcut.
    2. A shortcut to the CensusExtract folder in your Documents folder.
    3. A shortcut to the XML folder in the CensusExtract folder.
  • What is in the Excel Folder?
    1. An Excel worksheet, CensusExtract_Samples.xls, containing a number of filled out worksheets showing different issues of interest.
    2. A folder named US containing a worksheet, CensusExtract_US.xls, containing blank worksheets for each US census year from 1790 thru 1930 except 1890.
    3. Two folders Template_2003 and Template_2007 containing Excel templates for the US Census.
  • What is in the GenDatabase Folder?
    1. Genealogy Database modifications that I have developed for use with CensusExtract.
    2. RootsMagic
    a. Census_FactTypes.txt which describes the custom Fact Types I developed for CensusExtract
    3. TMG
    a. 01_ee_census_us_online.xst which the source template I developed for CensusExtract
    b. Census_Tags.txt which describes the custom tags I developed for CensusExtract
  • What is in the XML Folder?
    1. An XML file, Census_Extract.xml, containing the rules for formatting the Census data from the worksheet
    2. An XML schema file, Census_Extract.xsd, describing the structure of the XML file.


Install

This Section will discuss how to capture the census data to an Excel worksheets


Prepare Census Document

Prepare Census Document And Excel

  • If the source census document is online.
    1. Resize your browser to occupy half of the screen either top or bottom.
    2. Use two monitors. (highly recommended)
  • If the source census document is printed, make sure print is readable.
  • Launch Excel if not already running
  • For the Census year of your document load a blank worksheet for that year by:
    1. Copy and Paste a blank worksheet for from the CensusExtract_US.xls workbook.
    2. Load a template from either the Template_2003 or Template_2007 folders
    3. I find templates easier to use than copying worksheets.
  • Fill in the blanks on the Excel worksheet with data from census document.
  • I’m attempting to make a transcription of census document so I generally fill in what I see.
  • Any Excel box not filled in will generally be ignored by the CensusExtract program
  • You may transcribe as much or as little of the census document as you like.


Capture Source Data

Capture Source Citation Data

  • The data entered in the top 6 rows will be used to generate source citation.
  • Ctrl +; will insert today’s date into the Extract Date box.
  • There are two areas for page number, Pg A and Pg B. It is my experience that there is often at least two page numbers on a given census sheet.
  • There are three values that can be put in the page Type boxes.
    1. W for written, favored by me
    2. P for penned, favored by Elizabeth Shown Mills
    3. S for stamped
  • Not all boxes in the first 6 rows are used for source citations. I added the Ancestry related fields for my own benefit as Ancestry has change their census organization scheme at least twice since I started this project. The bread crumbs help me find the image again if needed.


Capture Person Data

  • The data entered starting in row 14 will be used to generate information about individuals enumerated in the census
  • Blank, do, or “ surnames are OK. The most recent prior surname will be used.
  • When a check mark is used on the census record I enter X. However, any non blank character will have the same effect in the CensusExtract Program
  • When Yes is used on the census record enter Y or y
  • When No is used on the census record enter N or n
  • I transcribe state names as I find them and have the software recode them to a standard spelling
  • Blank, do, or “ state names are OK. The most recent prior state name will be used. However, there needs to be at least on non blank state name in the column
  • When entering a fractional age e.g. 6/12 precede the fraction with a single quote or Excel will think it is a number.
  • When a family of interest continues on to the next page of the census document:
    1. Add a new worksheet after the first one
    2. Enter the appropriate Source data. If I know I’m going to need an additional worksheet, I copy the first worksheet into the new worksheet after completing the source data and therefore only have to change that pages information.
    3. Continue transcribing the family on the new worksheet.
  • The Notes at the bottom of the worksheet is for any personal note you might what to make.


Application

This Section will discuss how to execute and use the CensusExtract application


How to Start CensusExtract

  • Make sure Excel is open and that a filled out CensusExtract worksheet is the active document.
  • For pre 1850 censuses select the individual you wish to extract.
  • On 1850 and later Census select the surname of the head of household.
  • Either double click the CensusExtract desktop shortcut or click the Refresh button if the program is already running


CensusExtract Main Screen

  • Title Bar shows the icon, CensusExtract, and the Genealogy Database software selected
  • File Menu
    1. Set Version, allows you to select which Genealogy Database software formatting to use. Defaults to TMG when application is run the first time.
    2. Set Path, allows you to change the path to the XML file. For advanced users only.
    3. Exit, quits the program
  • Edit Menu
    1. ReCodes, takes you to a screen to enter or modify state name recodes.
    2. Versions, takes you to a separate screen which allows you to enter or modify all the formatting rules. For advanced users only.
  • Help Menu
    1. Contents, accesses this help file.
    2. About, accesses the About screen.
  • Master Source Text Box, contains formatted data to either enter an entire source citation (Joe’s TMG) or just the Master Source
  • Source Detail Text Box, contains formatted data to enter Source detail or will contain any data over 255 characters when using Joe’s TMG.
  • Event Text Box, contains formatted data to enter the specific census event
  • People List, contains the people enumerated in the household.
  • Note: If an identical name appears in a household it will appear with a numeric suffix in the People List. The formatted data will not have the suffix.
  • Person Text Box, contains formatted data about the person selected in the People List.
  • The Copy buttons, will copy the entire contents of the associated text box to the Windows clipboard.


State Name Recode Screen

  • Invoked from the main screen Edit -> ReCode
  • Title Bar shows the icon, Edit ReCodes, the Genealogy Database software currently selected, the path to the XML file
  • The Recode Types, All Types, option is for advanced users only.
  • To enter a new state name recode:
    1. Go to the bottom of the list.
    2. Click on the first column in the empty row.
    3. The value StateName will be automatically entered.
    4. In the second column enter the value you transcribed on the worksheet.
    5. In the third column enter the value you want to be displayed in the formatted data.


Database Transfer

This Section will discuss how to transfer formatted data to your genalogy database


Transfer TMG

This Section will show how I cut and paste formatted data into TMG

  • Make sure the File -> Set Version is set to TMG
  • Run the CensusExtract program against a filled out worksheet.


TMG Prepare

On Time Only TMG Preparation

  • Install the 01_ee_census_us_online.xst source template
    1. In TMG go to: Tools -> Source Types -> Import button.
    2. Navigate to CensusExtract/TMG Folder.
    3. Click on 01_ee_census_us_online.xst.
    4. Click Open button and follow directions.
  • Create two new Tags based on the patterns in the CensusExtract/TMG/Census_Tags.txt file.


TMG source

How to Transfer Source Data to TMG

  • Click the copy button associated with Master Source.
  • In TMG go to: Tools -> Text Macros.
  • In an empty slot, I use Shift+F2, paste the source data.
  • In the Master Source List add a new 01_EE_Census US Online
  • Place your cursor in the Abbreviation box.
  • Press Shift+F2 to run the source Text Macro.
  • Edit the Abbreviation if needed
  • Add an Ancestry.com Repository


TMG Event

How to Transfer Event Data to TMG

  • Click the copy button associated with Event.
  • In TMG Text Macro paste into another slot, I use Shift+F3
  • Add Census_US_Date tag for an 1850 or later census or a Census_US_Year tag to the head of house hold.
  • Place your cursor in Date box.
  • Press Shift+F3 to run the event Text Macro.


TMG Person

How to Transfer Person Data to TMG

  • In CensusExtract select the head of household on the People list.
  • Click the copy button associated with the Person.
  • In TMG paste into the Memo box of the census event tag.
  • For an 1850 or later census add household members as witnesses to the head of households census event tag.
  • Edit each witness and paste the person data for that person from CensusExtract into the witness Memo box.


Transfer Legacy

This Section will show how I cut and paste formatted data into Legacy

  • Make sure the File -> Set Version is set to Legacy
  • Run the CensusExtract program against a filled out worksheet.


Legacy Mstr Source

How to Enter the Master Source in Legacy

  • In Legacy go to: View -> Master Lists -> Source
  • Click the Add button.
  • Select Census records from the type of source list.
  • Fill in dropdowns for a US Census year
  • Click the Go to Step 2 button
  • Populate the boxes on the screen by copying tilde ~ delimitated text from the CensusExtract Master Source box.
  • Populate the Source List Name box with the first section of text.
  • Populate the Jurisdiction State box with the second section of text.
  • Populate the Jurisdiction County box with the third section of text.
  • Populate the Website Title box with the fourth section of text.
  • Populate the URL box with the fifth section of text.
  • Click the Save button.


Legacy Event

How to Transfer Event Data to Legacy

  • In Legacy on the Individual Information screen click the Add button
  • In the Event dropdown select Census
  • Populate the boxes on the Legacy Add Event screen by copying tilde ~ delimitated text from the CensusExtract Event box. Not all delimitated text sections contain data for all census years. Skip empty sections.
  • Populate the Description box with the first section of text.
  • Populate the Date box with the second section of text.
  • Populate the Place box with the third section of text.


Legacy Source Detail

How to Enter the Source Detail in Legacy.

  • In Legacy on the Add/Edit Event screen for the Census click the Add or Edit Source Citations icon (three books).
  • If you have already entered the master source above click the Cite a Master source button.
  • Select the master source you want to use.
  • Populate the boxes on the Legacy Add Source Detail screen by copying tilde ~ delimitated text from the CensusExtract Source Detail box. Not all delimitated text sections contain data for all census years. Skip empty sections.
  • Populate the Jurisdiction City box with the first section of text.
  • Populate the Roll box with the second section of text.
  • Populate the Enumeration District box with the third section of text.
  • Populate the Page box with the fourth section of text.
  • Populate the Line box with the fifth section of text.
  • Populate the Dwelling No box with the sixth section of text.
  • Populate the Family No box with the seventh section of text.
  • Populate the Id of Person box with the eighth section of text.
  • Populate the Date Accessed box with the ninth section of text.


Legacy Person

How to Transfer Person Data to Legacy

  • In CensusExtract in the People list select a person.
  • Click the copy button associated with the Person.
  • In Legacy paste the copied text in to the Notes box.


Transfer Legacy

This Section will show how I cut and paste formatted data into RootsMagic

  • Make sure the File -> Set Version is set to RootsMagic
  • Run the CensusExtract program against a filled out worksheet.


RootsMagic Prepare

On Time Only RootsMagic Preparation

  • Create two new FactTypes based on the patterns in CensusExtract/GenDatabase/RootsMagic/ Census_FactTypes.txt


RootsMagic Mstr Source

How to Enter the Master Source in RootsMagic

  • In RootsMagic Go to Lists -> Source Lists
  • Click the Add new source button.
  • On the Source Type screen select Census, U.S. Federal (Online images).
  • Populate the boxes on the RootsMagic Edit Master Source screen by copying tilde ~ delimitated text from the CensusExtract Source box. Not all delimitated text sections contain data for all census years. Skip empty sections.
  • Populate the Master Source box with the first section of text.
  • Populate the Year and type box with the second section of text.
  • Populate the Jurisdiction box with the third section of text.
  • Populate the Schedule box with the fourth section of text.
  • Populate the Website box with the fifth section of text.
  • Populate the URL box with the sixth section of text.
  • Populate the Credit line box with the seventh section of text


RootsMagic Event

How to Transfer Event Data to RootsMagic

  • On the RootsMagic Edit Person screen of the head of household click the Add a fact button.
  • On the Fact Types screen select Cen_US_Date for 1850 and latter census year or Cen_US_Year for pre 1850 census years.
  • Populate the boxes on the RootsMagic screen by copying tilde ~ delimitated text from the CensusExtract Event box.
  • Populate the Date box with the first section of text.
  • Populate the Place box with the second section of text.


RootsMagic Source Detail

How to Enter the Source Detail in RootsMagic.

  • Click the Sources button.
  • If you have already entered the master source click the Cite existing source button.
  • Select the master source.
  • Populate the boxes in the Source Details section on the RootsMagic Edit Source screen by copying tilde ~ delimitated text from the CensusExtract Source Detail box. Not all delimitated text sections contain data for all census years. Skip empty sections.
  • Populate the Civil Division box with the first section of text.
  • Populate the Enumeration District box with the second section of text.
  • Populate the Page ID box with the third section of text.
  • Populate the Household ID box with the forth section of text.
  • Populate the Person of interest box with the fifth section of text.
  • Populate the Access date box with the sixth section of text.


RootsMagic Person

How to Transfer Person Data to RootsMagic

  • In RootsMagic if the head of household census fact is not active make it so.
  • In CensusExtract click the copy button associated with the Person.
  • In RootsMagic paste the copied text in to the Description box.


Versions Screen

This Section will discuss How to Use the Edit Versions Screen

  • The Edit Versions screen is accessed from the main CensusExtract screen Edit -> Versions
  • This screen and associated sub screens allow copying, deleting and modifying of formatting rules stored in the XML file.
  • WARNING! Always make modifications to a copy of a version!
  • Question "Joe, I modified the only copy of my version, and now it does not work. What can I do?
  • Answer: Uninstall and reinstall the application!


Versions Top

The Edit Versions Title Bar and Menus

  • Title Bar shows the icon, Edit Versions, the current Genealogy Database software selected, and the current path to the XML file.
  • File Menu
    1. Set Path, allows you to change the path to the XML file
    2. Exit, quits the screen
  • Edit Menu
    1. Versions -> Copy Version, allows you to copy an entire Version.
    2. Versions -> Delete Version, allows you to delete an entire Version.
    3. Copy Census Types, allows you to copy a Census Type.
    4. Copy Census Year, allows you to copy a Census Year.
  • View Menu
    1. Recode, allows you to access all of the recode besides states
    2. Versions, allows you to return to the version screen.


Versions DDL

The Edit Versions Screen Dropdown Lists

  • Version, select the software version you want to see or modify.
  • Census Type, select the census type you want to see or modify. Add a new Census Type in the blank row. Currently there is only one Census Type, U.S. Census, but there could be others. E.g. English census.
  • Census Type Tables, Allows you to see or modify a Census Type Table. See the Census Type Tables section in this help file
  • Census Year, when the Census Year table is selected, allows you to view a specific set of Census Year tables.
  • Census Year Tables, Allows you to see or modify a Census Year Table. See the Census Year Tables section in this help file


Census Type Year

Census Type Year Table

  • Allows you to see, modify or add a Census year to a Census Type.


Census Type SourceMap

Census Type SourceMap Table

  • Defines which elements and in what order those elements will be displayed in the Master Source and Source Detail boxes.
  • Where specific elements are to be found on a specific census year is defined on the Source, Census Year Table for the Census year. Not all elements are defined for all years.
  • Column Definitions
    1. Sequence: The high level order of the source elements. Usually correspond to an entry field in the genealogy database program.
    2. SubSequence: Used to order elements in a source element composed of more than one element.
    3. Element: The element name.
    4. Literal: Not used at this time.
    5. Prefix: Text to be added before the Element.
    6. Suffix: Text to be added after the Element.
    7. SuffixRequired: If Y then the text in the Suffix column will be out put even if the element name is not found in the Source, Census Year Table for a specific census year.
    8. MasterDetail: If M the text generated will be placed in the Master Source box. If D the text generated will be placed in the Source Detail box.
  • Warning! Be careful about deleting Elements on this table associated with a Source, Census Year Table. An error will occur if the Source, Census Year Table cannot find an element in this table.


Census Type EventMap

Census Type EventMap Table

  • Defines which elements and in what order those elements will be displayed in the Event box.
  • Where specific elements are to be found on a specific census year is defined on the Event, Census Year Table for the Census year. Not all elements are defined for all years.
  • Column Definitions
    1. Sequence: The high level order of the event elements. Usually correspond to an entry field in the genealogy database program.
    2. SubSequence: Used to order elements in a event element composed of more than one element.
    3. Element: The element name.
    4. Literal: Not used at this time.
    5. Prefix: Text to be added before the Element.
    6. Suffix: Text to be added after the Element.
    7. SuffixRequired: If Y then the text in the Suffix column will be out put even if the element name is not found in the Event, Census Year Table for a specific census year.
  • Warning! Be careful about deleting Elements on this table associated with a Event, Census Year Table. An error will occur if the Event, Census Year Table cannot find an element in this table.


Cen Year Person

The Census Year Person Table

  • Defines which elements and in what order those elements will be displayed in the Person box.
  • Defines in what column on the CensusExtract Excel worksheet the element can be found.
  • Column Definitions.
    1. Sequence: The order of the person elements.
    2. Name: Not used by the program but it sure helps mere humans keep track of what is going on.
    3. Column: The worksheet column the element can be found in.
    4. Literal: Override commands on what to do with the element.
    a. ReCode: Look the recode name up in the ReCode table and use that value e.g. State name recodes.
    b. SuppressData: do not use the data but use the Prefix and or Suffix instead. Usually used with check mark entries
    5. Prefix: Text to be added before the Element.
    6. Suffix: Text to be added after the Element.


Cen Year Source

The Census Year Source Table

  • Defines where on the worksheet specific Source elements can be found
  • Column Definitions.
    1. Name: The Name of the Source element as defined on the SourceMap, Census Type Table.
    2. Row: The worksheet row the element can be found in.
    3. Column: The worksheet column the element can be found in.


Cen Year Event

The Census Year Event Table

  • Defines where on the worksheet specific Event elements can be found
  • Column Definitions.
    1. Name: The Name of the Event element as defined on the EventMap, Census Type Table.
    2. Row: The worksheet row the element can be found in.
    3. Column: The worksheet column the element can be found in.