Data Import Utility

Data Import Tool 

 

New Features

  • Auto Scheduling Imports
  • Importing of ODBC
  • Allows for cancelling a Data Import that is in progress.
  • Delete existing Personnel Record if Duplicate Card Number
  • Update existing Personnel Record based on an index field
  • Import from Lenel, Andover, and PointGuard.
  • Check for duplicate cards.
  • New function to 'move data from one field to another'.  For example, moving the data in Card_Number2 to one of the Custom fields and then doing a personnel download.
  • There is no need to close IS2000 and make sure that they don't attempt to click on any of the other functions and/or attempt to do an import.
  • Will import BLOBs to jpg files
  • When an import is performed and a 'subscript out of range error' is displayed, make sure the most current import utility is being used.

Note:  We do not recommend that anyone uses the above functions unless they have been trained.

 

FAQs

Location:

The Import Utility is located under the Windows Start Menu, All Programs, Application Name, Import Utility.

  • When the file has been received, copy the Data_Import file into the remote path on the server.
  • Execute the data import utility, ensure the import utility is executed with elevated permissions by performing a right mouse click on executable and use the “Run As” option.

Running as a Service:
From the Command Prompt enter the following:
     Data_Import -install

Uninstall the Service:
From the Command Prompt enter the following:
    Data_Import -uninstall

If the module is run as a service I cannot find a way to access the set up screen.
Correct, it is recommended to first configure the Data Import Utility when not configured as a service and then once you've tested the settings to work, then configure it to run as a service.

If I stop it running as a service and open it as an application it searches for a CSV finds it and then starts an import (this is if you have previously set an import time) so you have to be quick to stop it using the cancel button.
The new version of the Data Import utility adds a 10 second delay to give time to cancel an Import.

It performed the first import ,however I modified the source CSV saving it to the same location and file name, Set it to run as a service but it did not alter the Access control database (SQL DB, Build .41) (I had deleted a number of users.
If you deleted a number of user from the source file then it will not delete those records from the access control database. This is a Data Import Utility as opposed to a synchronization tool. It will only import the records from the source and only delete records from the existing personnel database if it finds an existing card number.

Notes:

  • The utility will delete duplicate card numbers found in the Card_Number1 and/or Card_Number2 field
  • The utility has an option to 'Delete Existing Personnel Record if Duplicate Card Number is Found'. This is located in the first menu called Options
  • The utility has an option to import based on an Indexed field.  If Card_Number is chosen, then duplicate card numbers will NOT be created and the existing record will get updated.  This is located in the first menu called Options.
  • The utility can be setup to run automatically as well without having to shutdown IS2000. Once you've confirmed that the manual process works then you can set it to run automatically at periodic intervals

 

Options

Delete Existing Personnel Record if Duplicate Card Number Found - This option checks the information to be imported against the existing data, if duplicate records exist between the two (2) databases, the new record will overwrite the existing record.  

  • To select this option, click on the Options Menu and click on the “Delete Existing Personnel Record if Duplicate Card Number Found” menu item.  Once this item has been selected, a check mark will appear to the left of the menu item as an indication of the selection.  This item will remain selected until deselected by the operator and will not be removed in the event of the application closing.
  • Card Number is in Hexadecimal - This option is used in the event the card number is in Hexadecimal format.


Import From Menu - The Import from Menu item is used to import data from existing Access Control Products in the event of a system take-over.  These options should not be used without proper training.  Each database being taken over will have different information and values and should be evaluated on a case by case basis, please contact Technical Support for assistance to perform this function.

Functions Menu

  • Identify Personnel Records with Duplicate Card Numbers - This option can be used to query the existing database and provide a report of all those records in the existing that have duplicate records.
  • Move Data from One Field to Another- Use this feature to move existing data from one field in the database to another field.  To use this feature do the following:
  1. Select the location where the data currently resides from the “Move Data From this Field” pick list.
  2. Select the location where the data will be moved to from the “to this Field” pick list.
  3. Click Move Data to complete the process.

 

Import Configuration

  • File Type - Select the appropriate file type by clicking on the drop down arrow to the right of the File Type tag.  The import can accept file types of Tab delimited, CSV delimited, IS100 data, or ODBC connections.  If utilizing an ODBC connection, this connection must be created prior to configuring the Data Import Utility.  To create an ODBC connection please see the section in this document labeled Configuring ODBC Connections.
  • Filename - Type the full UNC path or browse to the import file location.  To browse for the import file, click on the folder icon and navigate to the location of the import file. Once the UNC path has been established, if the file contains file headings click on the Ignore First Row check box. If an ODBC connection will be utilized to import data directly from a third party database do the following:
    • File Type - Set the File Type to a value of ODBC by clicking on the drop down arrow to the right of the File Type tag and click on the desired file type from the list.
    • DSN - Select the DSN name from the drop down list, the DSN list is populated with the Data Sources previously created in Data Sources (ODBC).
    • Table -  From the drop down list located to the right of Table, select the table the information will be imported from; for example the personnel table. Click on the icon to the right of the Table tag to display the data from the selected table. 
  • Data Mapping- Once the data has been displayed from the file / database, the fields from the file / database must be mapped to fields within the Access Control Security Application.  To map the data field(s) from the file / database, locate the field number(s) in the previewed data box, associate the correct Preview data field number with the Personnel field mapping field.  To select the correct “Preview the Data File” number in the Personnel field mapping column click to the right of the desired field and select the Preview data file field number from the drop down list.

 

Scheduling

  • It is often desired to have the import occur on a scheduled basis, the import utility allows for the file / database to be imported on a timed basis.  To schedule an import to run click on the drop down arrow to the left of Begin Import.  Select the desired import intervals, the import will automatically adjust based on the time selected.  If the import is to take place one time, use the Manual Import option.
    • Click on Begin Import to begin the import.

 

Running as a Service

To Run the Import utility as a Windows service do the following:

  1. From the Window Start Menu click on Run.
  2. Type in cmd and click on the Ok button.  Note:  if the Windows Operating system is Vista or 2003 the command prompt application will need to be started using elevated permissions.
  3. From the command prompt change direction until you have reached the saved location for the Data_Import.  For example cd C:\Program Files\Application name. The following prompt will be shown: C:\Program Files\Application Name> at the prompt type Data_Import –install.
  4. To Uninstall the Import Utility Service do the following:
  5. From the Window Start Menu click on Run.
  6. Type in cmd and click on the Ok button.  Note:  if the Windows Operating system is Vista or 2003 the command prompt application will need to be started using elevated permissions.
  7. From the command prompt change direction until you have reached the saved location for the Data_Import.  For example cd C:\Program Files\Application Name. The following prompt will be shown: C:\Program Files\Application Name> at the prompt type Data_Import –uninstall.


NOTE:  If an error of “Service not installed” is received during the installation of the service, the operating system account permissions are not elevated enough to add a Windows Service.   To correct this error execute the command prompt using the “Run As Administrator” option.

 

ODBC

Configuring ODBC Connections

  • From the Windows Start Menu, click on Control Panel.
  • Click on Administrative Tools.
  • Select Data Sources (ODBC)
  • Click on the first tab, User DSN.
  • Click Add
  • Select SQL Server and click Finish.
  • Enter the Name of the Data Source.
  • Enter the Description to describe the Data Source
  • Enter the SQL Server name and instance, for example a default instance of SQL Express on a machine name Access_Control would be entered as the following:  Access_Control\SQLEXPRESS.  This provides both the SQL Server name and the instance name.  For networked SQL Server machines it is not necessary to precede the Server name with back slashes and may cause an invalid connection if entered.
  • Click Next.
  • Click the second Radio button for “With SQL Server authentication using a login ID and password entered by the user”.
  • Check the box for “Connect to SQL Server to obtain default settings for the additional configuration options.
  • Enter the Login ID, this user must have permissions to attach and write to a database.
  • Enter the Password for the above mentioned Login ID.
  • Click Next>
  • Check the box “Change default database to:” Select the desired database to replicate to by clicking on the drop down arrow and clicking on the desired database. This database should be created in SQL Server Management studio prior to configuring the ODBC Data Source.
  • All other settings on this page can be left as default values.
  • Click Next>
  • Click Finish to complete the configuration of the ODBC Data Source.