Card Import for Sapphire Pro and Topaz Reference Manual P/N 460921001B • ISS 04MAR11
Copyright Copyright © 2011, UTC Fire & Security. All rights reserved. This document may not be copied in whole or in part or otherwise reproduced without prior written consent from UTC Fire & Security, except where specifically permitted under US and international copyright law. Disclaimer The information in this document is subject to change without notice.
i Contents Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Import Source File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Information Needed to Format the Comma Delimited File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Formatting the Comma Delimited File . . . . . . . . . . . . . . . . . .
ii Card Import for Sapphire Pro and Topaz Reference Manual
1 Overview The information contained in this manual provides instruction on uploading and converting an external cardholder database to a Sapphire Pro or Topaz host. Note: The illustrations included in this document are based on the Sapphire Pro user interface; however the field references are the same in Sapphire Pro and in Topaz. Import Source File This file can be generated as an export file from a database on another computer or network.
2 Card Import for Sapphire Pro and Topaz Reference Manual Formatting the Comma Delimited File The comma delimited file must have at least the card number and the cardholder name. We also recommend that either the card status or the deactivation date for the card be included. This allows employees who have been terminated to have their cards automatically deactivated in the system. The card number is used to uniquely identify the record. This can be, but is not necessarily the employee number.
3 CARDNAME Field The CARDNAME field is an exception to field names matching the descriptor file. Often the data for this field are contained in three fields for the last name, first name, and middle initial of the cardholder. Rather than having to manually combine these three fields to allow the file to be imported, the host system creates the single field out of the three fields. To do this, the field names must be: LNAME, FNAME, MNAME corresponding to the last, first and middle names.
4 Card Import for Sapphire Pro and Topaz Reference Manual Importing Security Areas If Security Areas are to be assigned to a cardholder in the import process, the area name is the Field Name in the first line of the comma delimited file and the Time Schedule ID number is the value in the card record. For example, the cardholder is to be assigned to the Lobby during Business Hours. The Lobby Security Area is LOBBY and the Business Hours ID is 8.
5 Employee Table Field Descriptions Functional Protocol Field Name Field Name Type of Value Description Access Group #1 ACCGRP 1 Numeric Access Group #2 ACCGRP 2 Access Group #3 ACCGRP 3 Access Group #4 ACCGRP 4 Access Group #5 ACCGRP 5 Access Group #6 ACCGRP 6 Access Group #7 ACCGRP 7 Access Group #8 ACCGRP 8 Activation Date ACT DATE Up to 3 characters This field is the numeric ID number assigned to an Access Group. Only the Access Group ID is imported.
6 Card Import for Sapphire Pro and Topaz Reference Manual Functional Protocol Field Name Field Name Type of Value Description Cardholder Name CARDNAME Alphanumeric Up to 32 characters. When the database being imported uses more than one field for the Cardholders Name, this data must be combined into the one cell. For Example: Last Name: Boulder First Name John must be combined into CARDNAME: Boulder John. List the last name first and the first name second. Separate the names with a space.
7 Functional Protocol Field Name Field Name Type of Value Description Keypad Number KEYPAD Numeric Record Last Updated LASTUPDT Numeric (Date) This is the computer generated date for the last time the record was updated. This field must be left blank so that the computer generates the information based on the date the file is uploaded into the host system.
8 Card Import for Sapphire Pro and Topaz Reference Manual Functional Protocol Field Name Field Name Type of Value Description Security area1 SA 4 TZ Numeric ID of time schedule when access is authorized to corresponding security area. Security area1 SA 5 Numeric ID of fifth authorized Security Security area1 SA 5 TZ Numeric ID of time schedule when access is authorized to corresponding security area.
9 Functional Protocol Field Name Field Name Type of Value Description User Defined Field #2 USERFLD2 Alphanumeric User Defined Field #3 USERFLD3 User Defined Field #4 USERFLD4 User Defined Field #5 USERFLD5 User Defined Field #6 USERFLD6 User Defined Field #7 USERFLD7 User Defined Field #8 USERFLD8 User Defined Field #9 USERFLD9 Up to 24 characters. User defined fields can be set up by the operator for use in storing additional reference information on cardholders.
10 Card Import for Sapphire Pro and Topaz Reference Manual Functional Protocol Field Name Field Name Type of Value Description User Defined Field #A USERFLDA Alphanumeric Up to 12 characters. User defined fields can be set up by the operator for use in storing additional reference information on cardholders. This is usually a type of information that is unique to the site or facility. For more information on defining User Fields, see “Renaming User Defined fields” on page 22.
11 CARD# must be the first column Label columns according to file format requirements Make certain these columns are in the correct order Change card# format to Facility Code (4 digits) Company Code (4 digits) Card Number (up to 10 digits) Change Access Groups to numeric values SSN contains no dashes Importing the Database Manually Once the comma delimited file has been created, all Field names are correct, and data has been verified as in the correct format, the file can be uploaded into the host sy
12 Card Import for Sapphire Pro and Topaz Reference Manual If the exact path to the import file is known, type the path into the field beside Import from File. Otherwise, click the browse button and locate the file that is to be imported. Next, click Card Import. After a few minutes (depending on the number and complexity of cards to be imported) the Operation Complete message displays. • • • • • All fields are verified by the host system.
13 In the illustration above, the error is listed as FIELD CARDNUM, FIELD NAME NOT DEFINED. This means that one of the column headers in the Excel spreadsheet is incorrect. Checking the field names in the appendix, it is learned that the correct field name is CARDSN for Card Serial Number. Correct this header in Excel and save the file. When the file is imported again, this problem should be corrected.
14 Card Import for Sapphire Pro and Topaz Reference Manual Place a check mark in the Enable Automatic Import checkbox. Note: This check box is also part of the Multi-Server Option and in that program facilitates the sharing of information between the servers. If this option is not available on the system, this setting allows only the Host Upload program to function. Automatic Import Processing The Sapphire Pro host system processes all files found in the folder each time a file is found.
15 There are several drivers that are already available in the program that can be used to retrieve data from the following data sources: Microsoft Access 2000, dBASE, Microsoft FoxPro, Oracle, Paradox, SQL Server and Text file databases. You can also use ODBC drivers or data source drivers from other manufacturers. An ODBC driver is a dynamic-link library (DLL) file that Microsoft Query and Microsoft Excel can use to connect to a particular database.
16 Card Import for Sapphire Pro and Topaz Reference Manual Double click on to locate the database file to be imported. The following dialog box displays. Type in the name that you want to call this query. For our example, we will use Hansol, Inc. Employees. The Information is contained on the Company’s Network Server in a Microsoft SQL Server. Once the name is typed, a drop-down list of available drivers displays.
17 Select the Database from the drop-down list of all databases on the data server selected above. If there is only one database on the server, you can accept the Default settings. If the database is in a Language other than English, it can be selected from the Language drop-down list. Click OK to close the dialog box. If you know the name of the specific data table used to retrieve the employee information, select it from the drop-down menu of tables available in the selected database.
18 Card Import for Sapphire Pro and Topaz Reference Manual Using the list of Field Name Definitions in the Appendix of this supplement, select the fields from the database table by clicking on the desired field, then clicking the right arrow button to move it from the Available window to the Columns in your query window. It is recommended that columns are selected in the same order as the Field Definitions table to facilitate editing work in the Excel file.
19 our example, we have already uploaded all the employee records before user number 127465. By selecting the USERID column and the is greater than condition and the 127465 variable, the upload only contains those records that have an user ID number after number 127465. If this is the first time an upload has been performed, do not select any filters so that all employee records are imported. Now click the Next button.
20 Card Import for Sapphire Pro and Topaz Reference Manual If uploads are performed routinely using the selections that have been made in this query, click the Save Query... button at this time. Either accept the default name Query from... or type in the name you want give the file. Then click Save to save the file and return to the Finish window. Click the Finish button. If the cell where this upload is to be started was correctly selected in the first step of this process, it shows in this field.
21 The information can now be modified to conform to the Field titles that are used by the Host Upload Program. Manipulating the Data in Microsoft Excel Moving the CARD# Field to Column One Often, when a database is data exported from another database, the first field is not the CARD# data. It may be the employee name or the Human Resources Employee Number. If this is the case, the following procedure easily moves the data to the first column.
22 Card Import for Sapphire Pro and Topaz Reference Manual When Sapphire Pro sees these columns, it combines them into the one field CARDNAME and discards the other fields. While renaming the columns, delete any columns of information that will not be used. Renaming User Defined fields Sapphire Pro and Topaz provide 11 fields for User defined additional personnel information. This could include items like employee Blood Type, Home Address, or Birth Date. Anything that Security would find helpful.
23 Now separate the multiple data that is contained in some of the columns from the imported database. In our sample, all Security Areas in the imported database are contained in the single column AREANAME. While we were renaming the columns, we inserted a column for each additional piece of data in that one column. Click on the gray bar above the cell that contains the data that needs to be redistributed to the cells to the right. This highlights the entire column .
24 Card Import for Sapphire Pro and Topaz Reference Manual . The next Wizard dialog box allows the operator to set the format for the data in each column. The default column format is General. For most jobs, it is OK to accept this format. However, if the data that is being separated requires a special format, for example a date or time format, this dialog box allows the format to be selected.
25 Click Finish. A message asks if the data being separated can “over write” the data in the columns to the right. Since these columns are empty, click OK to separate the data into the various columns. Verify Field Data Formats Verify that the data in each column is in the correct format for Sapphire Pro. If the format is incorrect, the upload either does not work or it is incomplete or incorrect. For example, The DEPT field can only contain eight (8) characters.
26 Card Import for Sapphire Pro and Topaz Reference Manual
