Census imports will populate the system with Individual or Dependent records. This way, administrators can avoid having to key everything in manually. It is much faster and much less prone to error than manual data entry.
Prerequisites
To import individual or dependent information, you will need a census file with all of your Individual or dependent information. Here are some guidelines for your import files:
- Import files must be in Excel (*.xls, .xlsx) or Comma Separated Values (.csv) format.
- Individual and dependent data must be in separate files.
- Import files must contain only one row per individual/dependent record.
- Import files must meet the minimum import requirements. For more information about census requirements, refer to Common Census’ [Census Template]
- If importing dependent information, employees MUST be imported into your company first.
The Import Census Screen
The Import Census tool can be accessed once you are logged in to Common Benefits as an administrator, and have selected a company. The Import Census tool is located under:
Tools>Import Census.
From here, you can configure/execute a new import, or view, execute, edit and delete saved import configurations. The Employees and Dependents sections list all saved import configurations.
Click Imports History beside any saved import to display all prior imports using the selected configuration. On the Imports History screen, View Details will open the log file from when the import occurred.
Execute imports a new census file using a previously saved import configuration. Note that to work, this requires that the census file includes the same column mappings and formatting that the configuration was designed for. After clicking Execute, you may select a census file, upload the file to the server, and import the file. This is particularly useful for recurring new hires imports where the census file is always formatted the same way.
Edit allows you to view and modify existing import configurations, where you have similar formatting/mapping, but columns or data have been changed.
Delete removes an import configuration from your database. Deleting an import configuration also removes the history of imports and log files created when using that configuration.
Configure/Execute New Import:
Click Configure/Execute New Import to start the import configuration wizard. The wizard is split into tabs which walk through configuring your import.
The “File” Tab:
File to import: Click Choose Import File to select the census file to be imported, then select a valid import file from your computer.
Import Type: Select a radio button option to specify the import type Employees or Dependents.
Employee(s) Key: The employee key is the unique identifier which the import will use to determine whether each employee exists in the database or not while processing the import file. Every employee must have a unique identifier. Employees may be identified based on their SSN or Employee ID. If multiple records on the file have the same key, each record will be imported into the same key.
Dependent(s) Key: When importing dependents, the dependent key is used to determine whether the dependent exists in the database or not while processing the import file. Dependents may be identified based on their First Name and Date of Birth or SSN. If multiple records on the file have the same key, each record will be imported into the same key.
Options: These options control how the records on the file are handled during the import:
Excel Worksheet Name: If you are importing from a spreadsheet with multiple tabs, you must select the specific tab to be imported.
First row is a header row: Determines whether the first row of your census includes column headers. These headers will be used during the column mapping.
Allow Inserts: If this option is checked, new records may be created during your import. Using the key values, if a key on the census is not in the database, then a new record is created.
Allow Updates: If this option is checked, existing records will be updated during your import. Updates will occur when the census file includes an employee key which already exists in the database.
The “Map Fields” Tab
On the “Map Fields” tab, data columns on the import file are matched to the database fields. When this tab is first opened, you will be prompted to Auto Map Fields. It is recommended to click Auto Map Fields so the import tool will automatically match your census columns to the import fields based on the column headers. If you click “Not Now”, then no fields will be mapped, and you must assign each field manually.
Mapping Options: When mapping fields, there are two mapping options available. You may Map Incoming Fields (the default option) where all columns of the import file are listed, and you select a destination field for each column. Alternatively, you may Map System Fields where all available import fields are listed, and census columns may be selected for each field.
Destination: The “Destination” table outlines all of the source fields and database fields. Depending on your selection under Mapping Options, this section may display differently.
When mapping system fields, the left hand column displays all available database fields and the census columns may be selected from the drop down lists in the next column. When mapping incoming fields, the left hand column will list all of the census columns on the import file, and the matching database fields may be selected in the next column.
Some database fields allow only certain values to be entered. For example Gender allows Male and Female. When importing fields like this, a colored indicator will appear beside the mapped field; this can be Red or Green. Red indicates that the census import values have not yet been reviewed. Click the indicator to review and match the values. Once all values are mapped, click Submit and the indicator will turn green, letting you know that the values have been reviewed.
The Missing Record Action column indicates what the system will do when the census file does not contain a value for a given field. The list of actions depends on whether the field is required or not. Examples:
Ignore Field: Available only if the field is not required. The database value will not be changed if there is no value provided on the census.
Reject Record: The record will be rejected if no value is provided on the census.
Use Company Default: Available if a default can be set at the company level (see Company Setup>Employee Defaults for more information).
Use This Value >>: This option refers to the Default Value in the right-hand column. If no value is provided on the census, then the value in the Default Value column will be used.
Import as “”: This option is available only for updates. This option will overwrite the database value with an empty string.
Default Value: This column can only be assigned when Missing Value Action Use This Value >> is set. If the field allows only certain values, such as “Gender, a drop down list of available import values will be displayed. Otherwise, the field will allow any text value.
Map Field: This section appears only when Map Incoming Fields is selected. This is used when one census column should be imported into more than one system field. After a column is mapped in the above Destination section, the column may be selected again, and another destination field can be selected. Clicking Add will add the additional mapping to the Destination fields.
Source Fields Not Mapped: This section appears when Map System Fields is selected. This lists all census columns which have not been mapped to a field. It is not required that all fields on the import file are mapped, however, minimum requirements must be met to proceed with the import.
Required (CC) Field Not Mapped: This section appears when the “Map Incoming Fields” mapping option is selected. If any required system fields have not been mapped to import fields, they will be listed under this section. All required fields must be mapped before you can proceed with the import.
The “Options” Tab
Before the census is imported, the system needs to know more about how to handle certain situations during the import.
Use Home Address As Mailing Address: This option will duplicate information imported into the Home address fields into the mailing address fields as well. If this option is selected, then existing mailing addresses will be overwritten with any imported addresses.
Restore and update records vs. Delete and create new records: These options refer to archived employees. If a record is imported with the same Key as an archived record, this option determines how to handle that record.
If …restored and updated is selected, the archived record will be activated, and the record will be updated to match the import file.
If **…deleted and new records will be created” is selected, the archived record will be deleted along with all dependents and enrollments saved on the archived record. Then the import file will be used to create a new employee record.
Replace current work status record with new status as of import date (default is inserting as of import date): This option will replace the existing work status on the employee record with a new status as of the current date.
Terminate Enrollments for Terminated Employees (this cannot be undone): This option will terminate enrollments for any record on the file that has an employment termination date. When selected, the system will default the termination date to the end of the month in which the employee was terminated, however a custom termination date can be selected which applies to all terminations on the file.
Update status and capacity records for employees with updated hire dates (this cannot be undone and will delete any earlier statuses/capacities if moved ahead of them): This option will apply updated work status and capacity for all records with an updated hire date.
The “Execute” Tab
Here we will choose how to import the file and save the import configuration.
Execute Import: This option will run the import as configured, but will not save the configuration for later review and use.
Save Import Configuration: Saving the import configuration will record all of the settings for later review and use, however your census will not be imported. When saving the configuration, you will be required to enter a name for your import in Configuration Name.
Save Import Configuration & Execute: This will save the configuration so that you may use it later, and will also execute the import. When saving the configuration, you will be required to enter a name for your import in Configuration Name.
Configuration Name: The configuration name set here will be the name that appears on the Import Census screen. You can view and edit saved import configurations at a later date.
Processing the Import File
While your file imports, the page will display a status and numbers to indicate how the import is progressing. The number of records processed represent the rows of your import file.
# of Records Processed: This is the total number of records on your import file which have been processed.
# of Records Inserted: This is the number of records which have been inserted into the database.
# of Records Updated: This is the number of records which have been updated within the database.
# of Records Rejected: This is the number of records which failed to import.
Click here to view the import log: When the import completes, this link will appear. Clicking this will open the import log and show additional details regarding the results of the import.
The Import Log
The import log lists the date and time that the import was performed and additional details about all of the records which were processed during the import.
Rejected Records: Any records on the import file which failed to import will be listed here along with a reason for the failure. Records are identified by their “Record Index” which is the number of the record on the import file. Note that if your import file included a header row, the record index will be one less than the import file row; i.e. Record 1 is on row 2 of your import file.
Inserted / Updated Records: This section contains all records which were successfully imported from the census file. Records are identified by their “Record Index” which is the number of the record on the import file. Note that if your import file included a header row, the record index will be one less than the import file row; i.e. Record 1 is on row 2 of your import file. To the right of the record, the “Message” column shows whether the record was inserted, or updated.