Interlogix Topaz Reference Manual
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. UTC Fire & Security
Trademarks and patents Topaz product and logo are trademarks of UTC Fire & Security.
assumes no responsibility for inaccuracies or omissions and specifically disclaims any
liabilities, losses, or risks, personal or otherwise, incurred as a consequence, directly or
indirectly, of the use or application of any of the contents of this document. For the latest
documentation, contact your local supplier or visit us online at www.utcfireandsecurity.com.
This publication may contain examples of screen captures and reports used in daily operations. Examples may include fictitious names of individuals and companies. Any similarity
to names and addresses of actual businesses or persons is entirely coincidental.
GE and the GE monogram are trademarks of the General Electric Company and are under
license to UTC Fire & Security, 9 Farm Springs Road, Farmington, CT 06034-4065, USA.
Other trade names used in this document may be trademarks or registered trademarks of
the manufacturers or vendors of the respective products.
Intended use Use this product only for the purpose it was designed for; refer to the data sheet and user
Software license
agreement
FCC compliance This equipment has been tested and found to comply with the limits for a Class A digital
Certification and
compliance
Manufacturer UTC Fire & Security Americas Corporation, Inc.
Contact information For contact information see our Web site: www.utcfireandsecurity.com
documentation for details. For the latest product information, contact your local supplier or
visit us online at www.utcfireandsecurity.com.
The EULA is included on the product DVD.
device, pursuant to part 15 of the FCC Rules. These limits are designed to provide reasonable protection against harmful interference when the equipment is operated in a commercial environment. This equipment generates, uses, and can radiate radio frequency energy
and, if not installed and used in accordance with the instruction manual, may cause harmful
interference to radio communications.
You are cautioned that any changes or modifications not expressly approved by the party
responsible for compliance could void the user's authority to operate the equipment.
2002/96/EC (WEEE directive): Products marked with this symbol cannot be disposed of as
unsorted municipal waste in the European Union. For proper recycling, return this product
to your local supplier upon the purchase of equivalent new equipment, or dispose of it at
designated collection points. For more information see: www.recyclethis.info.
791 Park of Commerce Blvd, Suite 100, Boca Raton, FL 33487 3630, USA
Contents
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Import Source File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Information Needed to Format the Comma Delimited File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Formatting the Comma Delimited File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Field Names and Data Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Format Review. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Importing the Database Manually . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Verify the Card Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Setting up the Host for Automatic Import. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Configuring the Site Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Automatic Import Processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Conversion Hints and Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Using Microsoft Query to Import the Database to Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Create a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Manipulating the Data in Microsoft Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Moving the CARD# Field to Column One . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Renaming Column Headers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Renaming User Defined fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Distributing Data across Multiple Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Verify Field Data Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
i
Card Import for Sapphire Pro and Topaz
ii
Reference Manual
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. For
example, human resources has a separate system and uses a stand alone employee database.
Most databases can export some or all data into a comma delimited file. The file should be placed in
a common location where both systems can access it. It should contain only information to be used
in the host (Sapphire Pro or Topaz) system. Other fields cause the file to be rejected. If automatic
rather than manual import is going to be used, the field names used in the export/comma delimited
file must match the field names in the employee descriptor file. It must also follow the formatting
parameters described below. Some of the formatting may require manual manipulation of the file
(such as keypad masking permissions) if an automatic import method is used, it may be better for
these changes to be added manually once the record is in the host system.
1
Information Needed to Format the Comma Delimited File
Depending on how comprehensive the information in the import file is to be, one or more of the
following documents may not be required.
To prepare the comma delimited file for importing into the host system, the operator needs several
reference documents:
• The list of Field Titles that are used in the host system. These are found in the second column
of the “Employee Table Field Descriptions” on page 5
• If Access Groups are a part of a card record, the operator needs the names of Access
Groups as they are used in the source database along with the corresponding Access
Group ID Numbers that are used in the host database (Sapphire Pro or Topaz).
• If the Card Status is to be included in the card record, the operator needs the Sapphire Pro or
Topaz numerical ID numbers, used to identify Card Status, that are equivalent to the ones
used in the source database.
• If Security Areas are to be included in the card record, the operator needs the Sapphire Pro
or Topaz numerical ID numbers, used to identify Time Schedules, that are equivalent to the
ones used in the database that is being imported.
• If Custom Fields are to be included in the card record, the operator needs a reference guide
that lists these fields and the USERFLD numbers that are used in Sapphire Pro or Topaz to
hold this information.
The more concurrence there is between the fields in the source database and the employee field
descriptors in the host database, the easier it is to import the data into the security system.
Note: If the automatic Import function is enabled, these fields should be exact matches. Otherwise, the.cvs file must be
formatted each time there is an import.
Card Import for Sapphire Pro and Topaz
2
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. The card number must correspond to the card being used by the employee.
During the import, if the record is found on file, the record is updated; if it is not found, the record is
added.
Note: Card numbers of 0 are not allowed.
Field Names and Data Values
The first line of the comma delimited text file which is to be imported must contain the names of the
fields to be imported. These fields must exactly match the field names found in the employee
descriptor file. The following lines of the import file contain a record for each card. Each record
contains the field data, comma delimited with or without double quotes terminated by a carriage
return/line feed. Fields with embedded commas must use surrounding quotes, e.g., “Fox, Suzy”. The
last field in a record may not be followed by a comma. See the illustration below.
The input file is defined as follows:
Field Name 1, Field Name 2, Field Name 3, Field Name 4, Field Name 5,...
Value 1, Value 2, Value 3, Value 4, Value 5,....
Example:
CARD# LNAME, FNAME, MNAME, SSN,
5555123456789 Bardot, Brigitte, M 560233343,
A typical card 'add' or 'modify' upload contains the following fields:
• Card Number
• Personal Identification Number
• Employee Name
• Security Areas and Time Zones, or Access Groups
• Card Issue Code or Status
• Card activate date
• Card Deactivate Date.
CARD# Field
The CARD# consists of the Company Code, the Facility Code, and a unique number
encoded on the card. This must be the first field in the comma delimited file. The order of
fields after this is not important with the exception of the CARDNAME Field.
CARDNAME Field
Use this number to
add Access Groups.
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. They must be in the order LNAME,FNAME,MNAME. The
host system inserts a comma between the LNAME and FNAME fields and a blank
between the FNAME and MNAME fields. If the import file contains the
LNAME,FNAME,MNAME field, do NOT have CARDNAME as a field in the file.
Adding Access Groups
All Access Groups to be imported as part of any record in the import file, must be
configured in the host system before the import.
Figure 1. Sample Sapphire Pro Access Group page
3
Create each Access group as described in the host system User Manual making certain
to place a check mark in the Receive from Card Import check box. This allows
cardholders that are imported to be added into these groups. Make a note of the Access
Group ID so that it can be used as the Field Value in the comma delimited file. Any
Access Group that is not configured is not added and cardholders who have that Access
Group may not have the expected access.
Card Import for Sapphire Pro and Topaz
4
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. The field name LOBBY will be included in the first line of the import field.
The number 8 will be in the corresponding place on the cardholder record line of the
import file.
Other Field Formatting Parameters
The file to be imported can use either comma delimited fields with quotes on fields when
embedded commas are used (EXCEL format) or comma delimited fields with quotes on
all fields (ACCESS format).
Dates can be YY/MM/DD or YYMMDD.
Empty values. i.e., , "", or ,, are permissible for field types DATE,BCD,BINARY and ASCII
fields. DATE, BCD and BINARY fields receive zero fill. ASCII fields receive blank fill.
Employee Table Field Descriptions
5
Functional
Field Name
Access Group #1ACCGRP 1 Numeric Up to 3 characters
Access Group #2ACCGRP 2 Numeric Up to 3 characters
Access Group #3ACCGRP 3 Numeric Up to 3 characters
Access Group #4ACCGRP 4 Numeric Up to 3 characters
Access Group #5ACCGRP 5 Numeric Up to 3 characters
Access Group #6ACCGRP 6 Numeric Up to 3 characters
Protocol
Field Name Type of Value Description
This field is the numeric ID number assigned to an Access Group. Only
the Access Group ID is imported.
This field is the numeric ID number assigned to an Access Group. Only
the Access Group ID is imported.
This field is the numeric ID number assigned to an Access Group. Only
the Access Group ID is imported.
This field is the numeric ID number assigned to an Access Group. Only
the Access Group ID is imported.
This field is the numeric ID number assigned to an Access Group. Only
the Access Group ID is imported.
This field is the numeric ID number assigned to an Access Group. Only
the Access Group ID is imported.
Access Group #7ACCGRP 7 Numeric Up to 3 characters
This field is the numeric ID number assigned to an Access Group. Only
the Access Group ID is imported.
Access Group #8ACCGRP 8 Numeric Up to 3 characters
This field is the numeric ID number assigned to an Access Group. Only
the Access Group ID is imported.
Activation
Date
Anti-passback
index
Card Number CARD# Numeric A unique decimal number.
ACT DATE Numeric Format for this numerical field is Year, Month, Day separated by slashes
and two digits each:
YY/MM/DD
APB INDX Internally assigned value, should not be used for import.
This is a significant field, and is the actual number encoded on the
access card.
Each cardholder must
The card format is 1234-1234-1234567890 where the first four digits
are the Facility code, the next four digits are the Company code and the
final is the unique number encoded on the card.
be assigned a unique card number.
Loading...