Employee and Certificate Import Process

This Employee Certificate Import  program will import the contents of a .csv file to update certificate information.  This Import Process can also be used to import new demographic information as well as certificate information from Apply To Education, using a .csv file that has been generated from Apply To Education.

The import program will search to see whether the employee exists before creating new employee records.  It does this by first using the Employee Identification Number (EIN) from the import file. If the EIN is not blank and exists in Atrieve HR, and the first and last name from the import file matches the first and last name in Atrieve HR, the program will tell you: Found Matching Employee (Same Emp Number and First/Last Names), Will Only Add Cert/Educ Info If Applicable.  In other words, because it found a matching employee already in Atrieve HR, the import will not import that employee's information into Atrieve, but will only try to import the certification and education information if there is any. 

If the employee number is blank, the system will still search Atrieve HR for an employee by the same name.  If found, the import of the employee information will not occur, and a slightly different message will be displayed: Found Matching Employee (Same First/Last Names), Will Add Cert/Educ Info If Applicable 

This employee searching logic will warn and prevent you from importing duplicate employees should you accidentally run the import twice. 

The population of the employee demographic data in Atrieve HR is straightforward.  All fields populate into the fields you would expect.  

Certification and Education Information 

Both Certification and Education data are stored in the Atrieve HR Certificate Table.  They can be kept separate from each other by directing them into different certificate codes, classes, and types. 

The certification fields populate as follows: 

As per the above table, the Atrieve HR certificate code is loaded from the Certificate_Type field in the import file.  The value that’s in the import file is a description and not a code.  The Atrieve HR system stores that data as a ‘certificate code’ which has an associated certificate code description.  In order to convert the Certificate_Type description in the import file to a valid certificate code in Atrieve HR, it does the following: 

1. Uses the Certificate Code Translation table.  You can enter values in this table via HR Setup & Maintenance > Setup Tables > Translation Certificate Code.  Here users enter the Certificate Code followed by the New Code – which is the Certificate_Type description field from the import file.  The New Code field has a maximum size of 50 characters so enter as much of the Certificate_Type description as you can.  As long as you don’t have two (or more) Certificate_Type descriptions where the first 50 characters are the same, this won’t be a problem. 

2. If the program is unable to find a matching Certificate_Type value in the Certificate Code Translation table, the program will search through all of the certificate code descriptions in Atrieve HR looking for an exact match.  If the Atrieve HR long description is populated, it will use that, otherwise it will use the normal description. 

3. If after the above, it can’t find a valid certificate code, the report will contain the following message: Invalid Certificate Code.  The creation of the Atrieve HR record will be skipped until users can create a translation entry for the certificate code, modify the Atrieve HR certificate code description to match, or modify the Certificate_Type description in the import file. 

There is also duplicate checking in place for both certification and education records.  If the identical information is found in Atrieve HR, records will not be duplicated.  You will see the following exception on the report: Found Duplicate Certification Certificate Record, will not ADD To HR. 

The education fields populate as follows: 

The calculation of the Certificate Code for education information is the same as for certification information, except the Education_Degrees field from the import file is used instead of the Certificate_Type field.   

Whether the education institution, province and country are loaded into the Atrieve HR certificate comment field or the Certificate Institution Code depends on how you answer the Field To Import Institution To.  If you enter C, the comment will be updated.  If users enter I, the institution code will be calculated and updated. 

The import calculates the institution code in the same way as the certificate date, looking first for a matching translation value in the Institution Translation table, and failing that, trying to find a institution in Atrieve HR that has the same description from the import file – again using the long institution name if it exists. 

Because there are hundreds if not thousands of certificate/degree granting institutions in North America, districts may decide not to enter a new institution code for every possible imported institution.  There is an option of just storing the institution description in the certificate comment field. However, if you do want to create institution codes for every imported institution, you can choose enter Y in the Create Missing Institution Codes field.  Using this feature will first search to see if there is matching institution already in Atrieve HR as per above, and failing that, it will actually create both the institution code record and the corresponding institution translation code for you, using a sequential number starting from 1 (field is filled with leading zeros).  This will save you the work of having the create new institution codes and corresponding translation records for every imported institution.  This option is definitely something to consider, if you don’t go with the comment option. 

The major and minor codes are calculated in the same way as the certificate and institution codes, i.e. matching the imported description first to the major and minor translation table, and then matching on existing Atrieve HR major and minor code descriptions. 

The Certificate Import can be run from the Atrieve Web menu from HR Setup and Maintenance > Imports >Employee Certificate Import 

Name of Import File 

Enter the name of the import file (including the directory).  This is the name/dir of the file on the atrieve Server.  We recommend that you use the download icon option which will let you browse for the import file on your system and then copy it over to the atrieve server. 

Import Teaching Certificate Information 

Enter Y to import the certification information. Enter N to not import it. 

Hire Date

Enter in Hire Date 

Preliminary/Final 

Enter P to run in preliminary mode, F to run in final.  Preliminary mode will present you with a report without actually importing anything into Atrieve HR.  A Final run will generate a report and also import the data into Atrieve HR. 

Note: You can customize and save the defaults that are used in this import.  You can do this by creating a REPORT SELECTION CRITERIA PROFILE with a REPORT of ‘ATE IMPORT’ and REPORT CODE of ‘ATE IMPORT’. 

Note: you can also create field skip/force settings for this program using a UNIQUE ID of ‘ATEIM’ and EMPLOYEE TYPE of *DEF. 

Note: All records that are created will be fully audited. 

When users run the import, they will see a report that beings with a statistics page.  

After the statistics page, users will see an exception summary as per below.

It is very possible that users may see exceptions further down in the report.. Some exceptions are just warnings, others may require users  to do make changes to the .csv file and re-run the import process again.

After the summary, users will see the details of all exceptions. 

 After the exceptions, users will see the regular report which will show you what was in the import file, and what was imported.  The imported data will show up first, following by data to be imported into Atrieve HR.