*How to (Automatic) Export/ Import
- Brittany Cornfield
- Rob Donker
General
With iProtect™ you have the ability to do an Automatic Export and Import of Employee data. Within this document it will be described step by step on how to do this.
Change system parameters
In order to do the automatic Export and Import the following parameter in iProtect™ is to be changed.
Enable “HTA enable:” (Tab “Other”)
Location: Installation| Settings| System parameters
Files
Required files export/ import Employee data
To do the (automatic) export and import a couple of files (Ex.a. and Ex.b.) will be required to download and generate.
Export
HTA file (importexport.hta) - Download
Arguments file - Download
.bat File for setup of automatic export.
Import
HTA file (importexport.hta) - Download
Arguments file - Download
Import file (.csv format, comma separated or .xml) - Path to the file location where the updated file is saved is required to be filled in the argumentsfile as well.
.bat file for setup of automatic import.
Download files
Downloading the HTA file can be done via two locations within iProtect™.
On the first location you will be able to generate the HTA file only, while on the second location you will be able to generate both the HTA file as well as the Arguments file for Employee data. Eventually both files are required in order to do the extraction of your export file of the Employee data. It is up to your preferences which location you use. In the follow-up chapters there are more detailes described on both locations and how to download.
Location: Installation| Settings| Local tools
Location: Installation| Settings| Client tools| Import/export
Local tools
Import/ Export file
Select “import & export” from the dropdown menu “Tool:” and press the download button. Now the HTA file will be downloaded and automatically saved into your downloads folder on your computer.
Client tools
As mentioned earlier, within the client tools menu you will be able to generate both the HTA file as well as the arguments file belonging to an export or import. In order to do so you must go to the known location and select “Employee” from the dropdown menu “Data group:” first before you will be able to continue with the next steps.
Export of Employee data
With an export file of the Employee data you will be able to read and or edit this file. If for example you have added in this file some person information e.g. “First name”, you will have the ability to use this file and its location for importing the updated data within iProtect™. How to import employee data will be discussed from chapter “Import of Employee data” of this document. The steps on how to do an export of the employee data will be discussed in the next chapters.
Export and Arguments file
Once the data group have been selected more fields will appear to fill with data. These fields are required in order to download the Arguments file. Below you will find a description of each field. Once all fields have been filled in properly you have to press the Create button to download the Arguments file and press the Download button to download the importexport.hta file.
Field: | Type of Field: | Data or Data option: | Description: |
|---|---|---|---|
Data group: | Dropdown |
| Export can/ will only be done of persons that are also a Employee |
Data transfer type: | Dropdown |
| Choose if you want to generate export or import files |
Login name: | Text | Free text | The iProtect™ login name of the system user to extract the data |
Password: | Text | Free text | The iProtect™ password of the system user to extract the data |
Function name: | Text | Auto-filled text | Statement that it regards Employee data for extraction |
File type: | Dropdown |
| Choose the type of extension you prefer to use |
File: | Text | Free text | Provide a full path where you would like to see the extraction of the employee data. This path has to end with a name for your file including the extension type |
Column names length: | Dropdown |
| These are the columns within iprotect. E.g: Short (does not include table name) NAME or FIRSTNAME The option "Short" is deprecated and will no longer be maintained. The option "Long" is its replacement. Nevertheless, the option "Short" is still available, but due to the above the main focus on the "how to" in this document will be about the new option "Long". Long (includes table name) PERSON_NAME or PERSON_FIRSTNAME |
User defined fields as: | Dropdown |
| When user defined fields have been created in the person data, the data will be exported either in number or text format |
Data selection: | Dropdown |
| If “All” is selected all data of all of the employees will be generated. When “Changes” is selected only the employee data where changes have been made are shown |
File name: | Text | Free text | Name of the arguments file belonging to an export, including the type of extention (e.g. .xml, .csv) |
Below is an example Ex.a. of the arguments file without any data entered:
Below is an example Ex.b. of the arguments file with data entered.
Create .bat file
Open Notepad on your Windows machine and enter the path as per below example (Ex.a.).
Go to “file”, press “Save as” and save the file on a preffered location, with the filename ending with the extention .bat and select at “Save as type:” All files.
Generate Export file with Employee data - Manually
If the above went well you will be able to generate the employee data manually.
Double click on the .bat file and the file with the employee data have been automatically generated and saved on the location mentioned within the arguments file. If you have chosen xml you can open the file with the browser by double clicking, click on the right mouse button and choose edit or choose to open it with a different program such as “Notepad”. When .csv was choosen it is best to open the file with “Excel” in order to read or edit.
Generate Export file with Employee data - Automatically
When the export file is generated as per above, you can setup a task on your Windows machine to automatically export the employee data from iProtect™ as per prefered set day(s) and or time(s). The export of the employee data will consists of the current data available in iProtect™ at the moment the automatic export takes place. Next scheduled export will consist of the by than current available data.
Below describes which steps to follow in order to set the automatic export.
Setup Task Scheduler for automatic export
On your Windows machine you have a program called “Task Scheduler” (Ex.a.). You can either find it by searching for the program name in the search bar of your Windows machine or go to the location where the program is saved.
After you opened the program you have to create a new task. (Ex.b.). When you have pressed on this a new popup will appear where you can further create your task.
Fill in data in the tab “General”. Yellow marked as a minimum, all other data is as per your preferences/ needs. (Ex.c.)
Following up on the tab “General” you can setup a time schedule underneath the tab “Triggers”. Click on “New” to set it up as per your company preferences. (Ex.d.)
Following up on the above the next tab “Action” gives you the ability to insert the .bat file which you created previously. This is required in order for the task scheduler to know from which location it is to do the export from. Once you have gone to the tab “Action” you must click on “New” and add the .bat file and press OK to save. (Ex.e.) other fields in this tab are optional and as per company preferences.
All other tab’s left are to be filled in as per company preferences. Once this is done you can press OK and the task have been created. From that moment onwards the automatic export takes place as per schedule.
If you want to Delete a task you can look it up in the scheduler library, select the task and press on the delete button on the right side of the panel. For updating the task you can simply click on the task once to edit in the bottom or double click on the task to open for edit.
Import of Employee data
The import function can be used to add new employee data or to change existing employee data.
Import file
An import file can be created in .csv, comma separated format or .xml format. Below table describes the data which is mandatory as a minimum and the optional fields including a description of the columns.
Further down below you will also find two real examples (Ex.a. and Ex.b.) in both formats.
COLUMN NAME LONG | DESCRIPTION | MANDATORY YES/ NO |
|---|---|---|
OPERATION | Indicates the effect that the import has on the iProtect database.
| YES |
EMPLOYEE_SALARYNR | Is a unique identified for that person. This can be a combination of numbers and characters. | YES |
EMPLOYEE_HIREDATE | Start of the employment. | NO |
EMPLOYEE_TERMINATIONDATE | End of the employment. | NO |
EMPLOYEE_BIRTHDATE | Date of birth. | NO |
EMPLOYEE_LANGUAGE | Language for report for this employee.
| NO |
PERSON_NAME | Is the family name of the person. | YES |
PERSON_FIRSTNAME | Is the surname of this person. | NO |
PERSON_PREFIX | Prefix. | NO |
PERSON_HOMEADDRESS | Home address. | NO |
PERSON_HOMECITY | Home city. | NO |
PERSON_HOMEZIP | Home zipcode. | NO |
DEPARTMENT_NAME | Is the name of the department, if the name does not exist in the database a new department will be created. | NO |
ACCESSKEY_RCN | Is the card number. | NO |
ACCESSKEY_CARDCLASSID | Is the ID of the accesskey type. iProtect can couple many accesskeys of different type to a single person. So only the card number is not enough to uniquely define the card. | NO |
CARDCLASS_CARDCLASSNAME | Refers to the name of the used cardclass, not recommended for import. | NO |
CARDCLASS_CODE | Unique reference to the cardclass, recommended for import. Cardclasscode must be defined in the iProtect user interface. | NO |
ACCESSKEY_ENDDATE | End date of the validity period. | NO |
ACCESSKEY_STARTDATE | Start date of the validity period. | NO |
ACCESSKEY_NOABP | Boolean to indicate whether anti pass-back should be activated for this card.
| NO |
ACCESSKEY_NOTIMEABP | Boolean to indicate whether time anti pass-back should be activated for this card.
| NO |
ACCESSKEY_VALID | Boolean to indicate whether the accesskey is valid. An accesskey is only valid between the start and end date AND when this value is 1.
| NO |
ACCESSKEY_UNLIMITED | If this Boolean is true the start and end date are ignored.
| NO |
ACCESSKEY_TRACE | Boolean to indicate whether the trace function should be activated for this card.
| NO |
ACCESSKEY_COMMENT | Free text for explanation the reason setting trace function. | NO |
DEFAULTKEYGROUPCODE | This tag refers to the code of a keygroup that should be defined in the iProtect database. When the accesskey is imported it is placed in this keygroup and gets the access rights accordingly. In this way all imported card can get default access rights. | NO |
FREETEXT1_NOHEADING (to 14) | Free text 1 to 14 are the custom text fields from the person form. | NO |
FREESELECT1_NOHEADING (to 14) | Free select 1 to 14 are the custom dropdown boxes from the person form. | NO |
FREEUNIQUETEXT1_NOHEADING (to 2) | Free text 1 to 2 are the custom text fields from the person form. | NO |
FREEUNIQUESELECT1_NOHEADING (to 2) | Free unique select 1 to 1 are the custom dropdown boxes from the person form. | NO |
FREEDATE1_NOHEADING (to 2) | Free date 1 to 2 are the custom date fields from the person form. | NO |
SELECTID1_NOHEADING (to 8) | Selectid 1 to 8 are the custom dropdown boxes from the employee form. | NO |
PHONE_NAME_0 (to 4) | The logic Name of the phonenumber (example: mobile number) | NO |
PHONE_PHONE_0 (to 4) | The real phonenumber (example: +316 12345678) | NO |
EMAIL_NAME_0 (to 4) | The logic name of the mailadres (example: keyprocessor mail) | NO |
EMAIL_ADDRESS_0 (to 4) | The real mail address (example: info@keyprocessor.com) | NO |
.xml format
.csv format
Import and Arguments file
To get the ability to import employee data almost the same steps as with the export have to be followed. Starting with download the HTA file from the prefered location “Local tools” or “Client tools” and continue with the steps further down in this document.
When creating the arguments file on the known location you will have simular fields as with export when selecting import however they are not the same. Below you’ll find a description of all available fields appearing for import.
Field: | Type of Field: | Data or Data option: | Description: |
|---|---|---|---|
Data group: | Dropdown |
| Export can/ will only be done of persons that are also a Employee |
Data transfer type: | Dropdown |
| Choose if you want to generate export or import files |
Login name: | Text | Free text | The iProtect™ login name of the system user to extract the data |
Password: | Text | Free text | The iProtect™ password of the system user to extract the data |
Function name: | Text | Auto-filled text | Statement that it regards Employee data for extraction |
File type: | Dropdown |
| Choose the type of extension you prefer to use |
Encoding: | Dropdown |
| Choose the type of encoding you prefer to use |
File: | Text | Free text | Provide a full path to the updated export file which is to be imported. Including its extention type. |
Error file: | Text | Free text | Provide name including extention type . When an error occurs there will automatically be generated a file with this name and extention type. |
Column names length: | Dropdown |
| These are the columns within iprotect. E.g: Short (does not include table name) NAME or FIRSTNAME Long (includes table name) PERSON_NAME or PERSON_FIRSTNAME |
User defined fields as: | Dropdown |
| When user defined fields have been created in the person data, the data will be imported either in number or text format |
File name: | Text | Free text | Name of the arguments file belonging to an import, including the type of extention (e.g. .xml, .csv) |
Below is an example Ex.a. of the arguments file without any data entered:
Below is an example Ex.b. of the arguments file with data entered.
Create .bat file
Open Notepad on your Windows machine and enter the path as per below example (Ex.a.).
Go to “file”, press “Save as” and save the file on a preffered location, with the filename ending with the extention .bat and select at “Save as type:” All files.
Import updated Export file with Employee data - Manually
If the above went well you will be able to import the updated employee data manually.
Double click on the .bat file and the file with the updated employee data have been imported in iProtect™. All changes within the updated employee data file have been corrected in iProtect™.
Import updated Export file with Employee data - Automatically
The setup for doing an automatic import will be exactly the same as the setup for an automatic export. The only differences will be the name of the task and the scheduled day(s) and or time(s). Make sure that the file which is to be imported is available at the location mentioned within your import arguments file.
In short:
Create the .bat file and save an your prefered location
Create an import task with prefered settings within the task scheduler.