Census Upload

Tool

The Census Import Tool facilitates the entry of Census Data such as name, birth date and zip code into the Combined Enrollment (Census). This tool allows users to import data from a spreadsheet directly into Combined Enrollment (Census) using an intuitive mapping wizard instead of manual keying. This will yield quicker case entry with fewer data errors.

Before Subscriber Census data can be imported, the user must have created a case name and entered the required information into the Office Use only Tab...

The Census Upload icon is located on the “Subscriber List” Tab.

Launching the Census Upload tool opens the Census Import Wizard which guides the user through to select or enter the requested data. The Census Import Wizard contains the following screens:

Ü Census Select Input File Screen – This screen facilitates Excel file selection, entering spreadsheet location and boundaries.

Ü Census Import Mapping Screen – This step enables the user to map the spreadsheet fields to the Combined Enrollment (Census) fields.

Ü Medical Coverage Type Mapping – This is to be manually completed, to select the plans that are being enrolled.

Note: It is helpful to open the Excel file containing to be imported data information and be familiar with the following information:

Ü Excel Workbook Name

Ü Excel Worksheet Name – Unless renamed, imported sheet should always have the data in the first tab Sheet1.

Note: Employee census data must be complete and in the correct format (text is preferred)

Combined Enrollment (Census) will not import the data if any cells are blank or if the cells contain invalid data. (I.e. Acceptable dates are 20130501 (yyyymmdd) and 05/01/2013 (mm/yy/2013)). Processors have the ability to do a date conversion from one to the other before Enrollments.

Import Census

Input file

Selection Screen

The Import Census Input file Selection screen is the first step in the Import Wizard. This

Step enables the user to:

ü Enter a file name or browse for a saved file.

ü Define Excel filter has been predefined as the layout of the worksheet

The Import Census Configuration Selection fields are identified and defined below.

Ü Import File – Enables the user to either type the path and file name or use the Browse button to navigate to the file you want to import.

Ü Worksheet Name – Since an Excel workbook can contain up to 265 worksheets, the

Census Import Tool requires that the imported worksheet be identified. Type the name of the worksheet in this textbox or leave the default Sheet1, if applicable.

Note: The Worksheet name is limited to 30 letters or digits. Some Special

Characters cannot be used in worksheet names (i.e. /, \, *,). This field is case sensitive.

Ü Header Row – If the Excel Spreadsheet containing the Employee Census Data

Contains a Header Row, Do not enter the Row Number in this text box. Typically, the Header

Row is Row 1.

Note: A Header Row identifies the column labels for the spreadsheet. The header

Row is for identification and is not part of the Census Upload. The Header Row is

The row at the top of the spreadsheet and contains identifying information.

Ü First Row – Enter the row number of the first record to import. Typically, this is Row 2.

Ü Starting Column – Current Program design dictates that the first column must be

Column A.

Note: The worksheet must also contain at least Columns A through S. Importing a file with data in

Columns T through AJ may need to be reviewed and may cause an error.

Note: To ensure that your data loads without error, use the Employee Census

Upload Excel Template that has been provided. This is explained in more detail in About the Template.

Review and Map

Mapping the Data (formats): Mapping Import File Data to Combined Enrollment (Census) Employee Census Data is where the user maps the Employee HR Census Data to the Combined Enrollment (Census) data table and the formats of data requirements are important

This screen is broken into 16 distinct columns:

(Format values are displayed next to the field on the Import Mapping tool)

Case Number – Identifies the case assigned to company (6 characters Alpha/Numeric) not required.

Employee Social – this identifies the Employee (9 bytes Numeric) required.

Last name – 16 character max (Alpha) – Members Last name (family name) – required.

First Name: 12 characters max (Alpha) – Members first name, name given at birth – required.

Middle Initial – 1 character (Alpha) Middle name initial – Not required.

Gender – this is the sex of the member (alpha) - (Male or female) required.

Date of Birth – Date of birth (numeric) acceptable formats are (MM/DD/YYYY or YYYYMMDD) required.

Phone Number – phone number (10 bytes numeric) –do not include (dashes or parenthesis) not required

Street Address – Members Primary residence street address (24 characters) required.

City – Members city of residence (alpha) required.

State – Members residence state (2 characters (alpha)) required.

Zip Code – Members zip code (5 bytes (numeric)) required.

Address 2 –2nd address line, for additional information that did not fit in address 1 line - not required.

Date of Hire – Date member started working for company (numeric) acceptable formats are (MM/DD/YYYY or YYYYMMDD) required.

Relationship Code – This is the relationship code (Employees are SUB, Spouses are SPS and Children are CHD) 3 Characters (Alpha) required.

About the Template

The Employee Census Upload Excel Template has been provided for your convenience. You may choose to use the form to input your own data on to spreadsheet; however, your spreadsheet will need to contain the appropriate data types in order to upload successfully. An image of sample template is displayed below:

An Input file must be selected; this will be uploaded temporarily to the workbook.

Once above file is selected, the below data will be used to Map the data from one Excel to the other.

Enter the column Values in each box that corresponds to where the data is located on your spreadsheet

Once you click on the submit button all the data from your spreadsheet will be copied to the "Subscriber List-All but Dental" tab. And you will receive an edit telling you how many records were imported.