Card Import for Sapphire Pro and Topaz Reference Manual
Contents
1. 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 Use this product only for the purpose it was designed for refer to the data sheet and user 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 This equipment has been tested and found to comply with the limits for a Class A digital device pursuant to part 15 of the FCC Rules These limits are designed to provide reason able protection against harmful interference when the equipment is operated in a commer cial 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 CE 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 UTC2. be the data source for this query 17 Password Options gt gt Options Database Min s Language Engih o x Application Name Microsoft Quey WorkStation ID Stain 120 C lt lt C 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 Create New Data Source m7 x What name do you want to give your data source T Hansole Inc Employees Select a driver for the type of database you want to access 2 soL Server Click Connect and enter any information requested by the driver 3 Connect Main Select a default table for your data source optional A Il Save my user ID and password in the data source definition Cancel 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 The name of the Database appears to the right of the Connect button in number 3 of the dialog box Click OK to save the Data Source to the Databases Menu of the Choose Data Source dialog box Click OK to open the Query Wizard 18 Card Import for Sapphire Pro and Topaz Reference Manual Query Wizard
3. limit the query to certain records that match selected criteria This is helpful if this table has been imported before and only the additional records are to be retrieved In 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 Query Wizard Sort Order E x Specify how you want your data sorted If you don t want to sort the data click Next Sort by al ee Ascending ae eee ae Descending Then by Se Ascending iy Descending Then by SE cc amp scen d in g C Descend ng ef lt Back Cancel If the data should be sorted my a specific column such as Last Name or Employee Number the column s can be selected on this page Up to three columns can be selected to control the order in which the records are displayed in Excel This is a personal choice on the part of the operator Now click Next to choose the destination for the report Query Wizard Finish i x What would you like to do next Betum Data to Microsoft Excel Save Quem View data or edit query in Microsoft Query Create an OLAP Cube fr
4. number 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 6 Card Import for Sapphire Pro and Topaz Reference Manual Cardholder CARDNAME Alphanumeric Up to 32 characters Name 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 If there is a Middle Name the Initial letter of that name can be included after the First name separated by a space Card Serial CARDSN Numeric 1 to 8 digits Number This is a reference field and does not affect access control operation The number represents a card s serial number and is typically printed on the card This is not to be confused with the actual encoded card number described above For security reasons these are often different numbers Deactivation DACTDATE Numeric Format for this numerical field is Year Month Day separated by slashes Date and two digits each YY MM DD Department DEPT Alphanumeric Up to 8 characters Name This is the Department in which the Employee works Emergency EMERNAME Alphanumeric Up to 32 characters Contact Name
5. site or facility For more information on defining User Fields see Renaming User Defined fields on page 22 User Defined Field 7 USERFLD7 Alphanumeric Up to 16 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 User Defined Field 8 USERFLD8 Alphanumeric Up to 16 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 User Defined Field 9 USERFLD9 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 10 Card Import for Sapphire Pro and Topaz Reference Manual User Defined Field A USERFLDA Alphanumeric Up to 12 characters User defined fields can be set up by the operator for use in storing add
6. system can be made faster by temporarily disconnecting serial lines for access control devices ACUs especially if initializing a new system When the upload process is complete reconnection of ACUs begins a download process for all records added or updated at the host Upload transactions are logged to disk The card number is queued to a downloader thread in order to background download card data to access control devices Downloading card data can take from several seconds up to several hours if thousands of cards have been uploaded A card may not be immediately usable if a large number of cards are uploaded to the host at one time This is only typical during the initial commissioning phase of some installations Verify the Card Import To verify that the card upload was successful click Events in the upper left corner of the screen This opens the Events Queue The most recent event should be the Cardholder Upload as shown below Figure 3 Sample Events Queue rT Ne 4 Click on Event for More De Detail AREA MONITOR TIME ZONE 02 14 28 31 SECURED AREA MONITOR REPORT SECURITY AREA CONFERENCE IS UNSECURE AREA MONITOR TIME ZONE 02 14 28 31 REMOTE CONTROL REQUESTOR TIMER ONLINE TASK 14 28 31 SECURED AREA MONITOR REPORT SECURITY AREA ACCOUNTING IS UNSECURE AREA MONITOR TIME ZONE 02 14 28 31 ALARM MASKING REPORT COMMAND MASK 14 28 31 ALARM MASKING REPORT COMMAND MASK 1420 31 Al ARM MASKING REPOR
7. that can be used in formatting the comma delimited file for import into the host system These tools are available with the Microsoft Office Suite Using Microsoft Query to Import the Database to Excel Microsoft Query is a feature that is available with Microsoft Office 2000 One way to import information from a database into Excel is to use Microsoft Query This program translates the data directly from the database into Excel without the intermediate step of having to export it from the database In order for this to work however the database must use a driver that can be used by Query 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 An ODBC driver is a data source driver that conforms to the Open Database Connectivity standard Each database program requires a different driver ODBC drivers make Microsoft Query flexible so that you can connect to new databases as they become available by installing new drivers For information about installing a data source driver from a database that is not listed above consult the documentation that came
8. 300450000000102 0 ACTIVE 332 012300450000000103 012300450000000103 0 ACTIVE 333 012300450000000104 012300450000000104 0 ACTIVE 12 012300450000000105 012300450000000105 0 ACTIVE 23 012300450000000106 012300450000000106 0 ACTIVE 24 nasannaeannnnnnninsy nadvannaenannnnnniny n AOTN E g 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 To move this column click on the grey bar at the top of the column so that the entire column is highlighted Right click and select Cut from the pop up window Now click on the grey bar at the top of Column A Again right click and select Insert Cut Cells The CARD column disappears from its former location the other columns move to the right and the CARD column with all its data reappears in column A File Edit view Insert Format Tools Data Window Help JOSH AIS6RY BREN ezaug Al id CCFCCARD Arial 10 CCFCCARD USERID ISSUELVL STATUS 00010000000000061 1 000100000000000611 0 ACTIVE Renaming Column Headers Sometimes the Column Headers line one of the comma delimited file ha
9. Card Import for Sapphire Pro and Topaz P N 460921001B ISS 04MAR11 Copyright Disclaimer Trademarks and patents Intended use Software license agreement FCC compliance Certification and compliance Z Manufacturer Contact information Copyright 2011 UTC Fire amp 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 amp Security except where specifically permitted under US and international copyright law The information in this document is subject to change without notice UTC Fire amp 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 opera tions Examples may include fictitious names of individuals and companies Any similarity to names and addresses of actual businesses or persons is entirely coincidental Topaz product and logo are trademarks of UTC Fire amp Security GE and the GE monogram are trademarks of the General Electric Company and are under license to UTC Fire amp Security 9 Farm Springs Road
10. Choose Columns a xi What columns of data do you want to include in your query Available tables and columns Columns in your query ISSUELVL aj ADAR STATUS priina _ lt GRaPB i CARDEFFDATE EAA sh L carpexpoare CANESCORT PHOTO ESCORTREQ BADGETEMPLATE TRACF zl AREANAME z Preview of data in selected column e Options 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 Preview Mow Note If the database being imported has the employee name in more than one column select the columns in Last Name First Name Middle Initial order These are combined into the single field CARDNAME when the data is imported into the host system Once you have selected all the columns to be converted click Next to open the Filter dialog window Query Wizard Filter Data k x Filter the data to specify which rows to include in your query If you don t want to filter the data click Next Column to filter Only include rows where USERID fis greater than 127465 And Or C And Or C And Or coca This window allows you to
11. Fire amp Security Americas Corporation Inc 791 Park of Commerce Blvd Suite 100 Boca Raton FL 33487 3630 USA For contact information see our Web site www utcfireandsecurity com Contents OVEN VIOW i oes arsrip eect lacie aie E Gite Mob Sienna Hh ewan a lend ae a od Gate Naas dives 1 Import So rce Fil sic eis ee Ree wee eee eee ee een Gnd ew dae hee ee ee ee eee 1 Information Needed to Format the Comma Delimited File 1 0 0 0 0 00 c eee eee eee 1 Formatting the Comma Delimited File 0 0 0 0 cette 2 Field Names and Data Valu s 22 0 2 crpg ee ore ee oye ede de ee eee ee ee ee ee 2 Format REVIEW lt a cigis apra sade Soars poe he See ee nae hee Zeon a Euler a Siete hinge ere 10 Importing the Database Manually 00 02 2 cee ee 11 Verify the Card Import 1 0 0 eee eee 12 Setting up the Host for Automatic Import 00 0c ee 13 Configuring the Site Settings cscs ce04 ee eek ee eee eee eee ede ee eee kee 13 Automatic Import Processing o gcce noes bho eee we Reb ehhh eae eda eh aay 14 Conversion Hints and Tools 2 4602 e ces eae eee eee eae ee ee eee eee wee 14 WMTROGUICTON sig ese his Sete Sige Sega nE Gok cap arii a amp Puavane Eaa E mide easels cia ie AE 14 Using Microsoft Query to Import the Database to Excel 0 2 ce eee 14 Create a QUEN ren nade ene h eee ee een eh eee ee eh eke eae he eRe hee 15 Manipulating the Data in Microsoft Excel 0 0 00 c eee ee
12. T COMMAND I INKMASK If there are any problems with any of the cards those errors are also shown in this box i Click on Event for More Detai 14 46 37 HOST UPLOAD EVENT ADD CARD 1230 0500 000000001 7 FIELD CARDNU A OT DEFINED 14 46 37 HOST UPLOAD EVENT ADD CARD 1230 0500 0000000016 FIELD CARDNUM FIELD NAME NOT DEFINED 14 46 37 HOST UPLOAD EVENT ADD CARD 1000 0450 0000000015 FIELD CARDNUM FIELD NAME NOT DEFINED 14 46 37 HOST UPLOAD EVENT ADD CARD 1000 0450 000000001 4 FIELD CARDNUM FIELD NAME NOT DEFINED 14 46 37 HOST UPLOAD EVENT ADD CARD 1000 0450 0000000013 FIELD CARDNUM FIELD NAME NOT DEFINED 14 46 37 HOST UPLOAD EVENT ADD CARD 1000 0450 0000000012 FIELD CARDNUM FIELD NAME NOT DEFINED 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 Using the Event window to verify each host upload and to trouble shoot whatever caused an error assures both accuracy and efficiency in the upload process Once the cards have been imported they should be available for immediate use or modification Setting up the Host for Automatic Import Note This feature is n
13. This field is the numeric ID number assigned to an Access Group Only the Access Group ID is imported Access Group ACCGRP 3 Numeric Up to 3 characters 3 This field is the numeric ID number assigned to an Access Group Only the Access Group ID is imported Access Group ACCGRP 4 Numeric Up to 3 characters 4 This field is the numeric ID number assigned to an Access Group Only the Access Group ID is imported Access Group ACCGRP 5 Numeric Up to 3 characters 5 This field is the numeric ID number assigned to an Access Group Only the Access Group ID is imported Access Group ACCGRP 6 Numeric Up to 3 characters 6 This field is the numeric ID number assigned to an Access Group Only the Access Group ID is imported Access Group ACCGRP 7 Numeric Up to 3 characters 7 This field is the numeric ID number assigned to an Access Group Only the Access Group ID is imported Access Group ACCGRP 8 Numeric Up to 3 characters 8 This field is the numeric ID number assigned to an Access Group Only the Access Group ID is imported Activation ACT DATE Numeric Format for this numerical field is Year Month Day separated by slashes Date and two digits each YY MM DD Anti passback APB INDX Internally assigned value should not be used for import index Card Number CARD Numeric A unique decimal number This is a significant field and is the actual number encoded on the access card Each cardholder must be assigned a unique card
14. This is a reference field for the name of a cardholder s emergency contact and may be used at the option of the system owner Emergency EMERTELE Numeric Up to 12 characters Contact s Data for this reference field is transmitted with formatted numbers The Telephone Area Code Prefix and Number may be separated with a space a dash Number or a period Parenthesis may NOT be used For example 703 555 1212 is a correctly formatted phone number Cardholder EMP NO or Numeric Up to 12 characters Number CRDHLDR This is the unique employee user number This number can be different from either CARD or CRDHLDR For example a number assigned by Human Resources etc Hire Date ISSUEDAT Format is YYMMDD or YY MM DD Issue Number ISSUENUM Numeric 1 digit The default is 0 An Issue Number permits the same card number to be re issued in the event that a card is lost or stolen The new card is issued with the same card number but the issue number increments by one The missing card will not work in the system since the issue numbers do not match Issue number is applicable to magnetic stripe cards which are encoded accordingly Keypad KEYPAD Numeric 1 to 6 digits Number This is the number entered by an employee at a reader Also known as the PIN personal Identification Number A keypad number must be assigned if the employee is to use a keypad reader Record Last LASTUPDT Numeric T
15. authorized Security Security area SA1 TZ Numeric ID of time schedule when access is authorized to corresponding security area Security area SA 2 Numeric ID of second authorized Security Security area SA 2 TZ Numeric ID of time schedule when access is authorized to corresponding security area Security area SA 3 Numeric ID of third authorized Security Security areat SA3 TZ Numeric ID of time schedule when access is authorized to corresponding security area Security area SA 4 Numeric ID of fourth authorized Security 8 Card Import for Sapphire Pro and Topaz Reference Manual Security area SA 4 TZ Numeric ID of time schedule when access is authorized to corresponding security area Security area SA 5 Numeric ID of fifth authorized Security Security area SA 5 TZ Numeric ID of time schedule when access is authorized to corresponding security area Security area SA6 Numeric ID of sixth authorized Security Security area SA 6 TZ Numeric ID of time schedule when access is authorized to corresponding security area Security area SA7 Numeric ID of seventh authorized Security Security area SA 7 TZ Numeric ID of time schedule when access is authorized to corresponding security area Security area SA8 Numeric ID of eighth authorized Security Security area SA 8 TZ Numeric ID of time schedule when access is authorized to corr
16. curity Areas on page 4 Format Review As a final precaution review the comma delimited file to verify that the information is correct and in the correct format This can easily be done by using a spread sheet program like Excel Use the following list to verify the data file for the host Import program Pe E ASCII Name Make certain that the column containing the Card Number is the first column of the file Make certain that the column headers to match the Field Names in Sapphire Pro or Topaz Make certain that dates and other numbers are in the correct format Make certain that Time Schedules and Card Status are numerical NOT the ASCII Names Make certain that the numerical Access Groups ID is used for each Access Group not the 11 Label columns according to file CARD must be the first column format requirements Make certain these columns Change Access Groups to are in the correct order numeric values A c E F l G 1 CARD LNAME FNAME MNAME SSN TITLE USERFLD1 2 CCH FC 124 BAKER EVERETT L 123456789 SAFETY SPECIALIST K C FARMLAND 3 3 CC RC 54 BENKIN LARRY 234567890 FARMLAND 4 4 CC 78 BREWER BRUCE 345678901 VIRONMENTAL ENG K C FARMLAND 3 Change card format to Facility Code 4 digits Company Code 4 digits Card Number up to 10 digits SSN contains no dashes Importing the Database Manually Once the comma d
17. e eee 21 Moving the CARD Field to Column One 0 00 21 Renaming Column Headers 00 000 cee tenes 21 Renaming User Defined fields 00 00000 ccc tenes 22 Distributing Data across Multiple Columns 000 ccc eee 22 Verity Field Data Formmats escea hivaciiva widens E ig eadelea eee a Pie e ae edge 25 ii Card Import for Sapphire Pro and Topaz 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 belo
18. eader name to the corresponding USERFLD For example In our sample database a column with the header AFFIL refers to the Function of the employee i e Executive Engineer etc The operator determines that this is USERFLD8 in Sapphire Pro or Topaz The operator changes the header of that field from AFFIL to USERFLD8 Using the Custom Field reference rename the headers in the database that is being imported to the corresponding USERFLD Replace Imported Header with Sapphire Pro or Topaz Header S Zi ow Leaner l G i M N 0 M N 0 EMPNUM KEYPAD AFFIL GUARDTOUR EMPNUM KEYPAD USERFLD8 GUARDTOUR 123456 D Executive OFF 123456 O Executive OFF Continue to rename the column headers until all the headers match the field names in Sapphire Pro or Topaz Distributing Data across Multiple Columns There may be some columns that contain more than one piece of data For example in our spread sheet all of the Security Areas are contained in the one column named AREANAME i File Edit view Insert Format Tools Data Window Help z A aby pa wae A This cell has both Break Area and Deis Slav Br 2 i Conference They are separated Al 7 CCFCCARD by a vertical line 1 JACCGRPNUM AREANAME Al AREANUM TZNAME Admin 3 Break ArealConference 45 Break ArealConference 4 5 Break ArealConference 4 5 Parking AlParking B Break Area Conference 101111415 C
19. elimited 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 system This is done from the Services Database screen To navigate to the Services Database screen click Control on the left side of the screen Then click on the Blue Eye button beside Service Functions When this page opens click on the Database tab at the top of the screen and a page similar to the following displays Figure 2 Sample Sapphire Pro Services Database screen Alarm Database Repair Cardholder Database Repair Cardholder Database Diagnostic with APB Repair Cardholder Database Diagnostic without APB Repair Import from file A Synchronize Entire Database Transmit Cards Modified in Last 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 If a field name is not found or contains invalid data the message is rejected After data is saved to disk it is the data is queued to be background downloaded to access control panels ACUs The upload from the external system to the host
20. erator 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 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 T
21. esponding security area Social SSN Numeric 11 characters Security Data for this reference field is transmitted with contiguous numbers For Number example if the Social Security Number is 555 12 3456 data is transmitted as 555123456 Card Status STATUS Numeric 1 Digit Enter one of the following values for each card VALUE DESCRIPTION 0 OK This is the default 1 Lost Access will be denied 2 Stolen Access will be denied 3 Inactive Access will be denied 4 Terminated Access will be denied Phone TELEPHON Numeric Telephone number extension Extension Badge TEMPLATE ASCII Up to 12 characters Template File name of the Badge Template Trace Time TRACE TZ Numeric If this employee is to be traced value is a number of the time schedule Schedule the trace is active 1 never 2 always Trace TR DIST Numeric Workstation number reporting workstation User Defined USERFLD1 Alphanumeric Up to 24 characters Field 1 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 Section 2 of this supplement User Defined Field 2 USERFLD2 Alphanumeric Up to 24 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 infor
22. figured is not added and cardholders who have that Access Group may not have the expected access 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 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 Access Group ACCGRP 1 Numeric Up to 3 characters 1 This field is the numeric ID number assigned to an Access Group Only the Access Group ID is imported Access Group ACCGRP 2 Numeric Up to 3 characters 2
23. from the Save as Type drop down menu Click Save 26 Card Import for Sapphire Pro and Topaz Reference Manual
24. he 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 e Card Number e Personal Identification Number e Employee Name e Security Areas and Time Zones or Access Groups e Card Issue Code or Status e Card activate date e 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 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
25. his is the computer generated date for the last time the record was Updated Date 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 Cardholder ONLFLAGS Numeric 0 255 flags For import add the following values together e if authorized for guard tour Sapphire Pro only add 1 e if authorized to bypass anti passback GUI field Executive APB override add 2 e if a visitor card Sapphire Pro only add 4 if cardholder access should be traced add 8 Affiliation ONLAFLAG Numeric 0 9 flags For Topaz always 1 employee e For Sapphire Pro 1 for employee 9 for visitor Tenant ID OPGRPID Numeric ID of operator group Photo ID PHOTOID Numeric This is the name of the photo of the Cardholder The photo file itself Number must be in the BADGER folder on the Server The photo must have a special indexed numerical name See Section 2 for details Emergency RELATION Alphanumeric Up to 16 characters Contact This is a reference field that may be used at the option of the system Relationship owner Security Area Header is Up to 12 characters ASCII To add a Security Area to a record use the ASCII name of the Security Value is Area as the Field Header Then enter the Time Schedule ID Number as Numeric the Field Value If the record is NOT to have the security area enter a Field Value of zero 0 Security area SA Numeric ID of first
26. iew of selected data Park The text data in the field is separated delimited by a vertical line Be certain that the radio dot is in the Delimited option and click Next The next Wizard dialog box allows you to select the type of delimiter the text uses In our sample the delimiter is a vertical line so the check mark is placed in the Other option and a vertical line is typed into the window beside the option When the correct delimiter is selected the Data preview window displays the data in columns as shown below 24 Card Import for Sapphire Pro and Topaz Reference Manual AJ AK AL AM _ AN SA2 SA3 SA4 SA1TZ SA2TZ Convert Text to Columns Wizard Step 2 of 3 3 ixi This screen lets you set the delimiters your data contains You can see how your text is affected in the preview below elimiters tae pane Treat consecutive delimiters as one I Semicolon T Comma Space M Other fl Text qualifier I z al el Break Area Conference bd H 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 AJ AK AL AM AN SA2 SA3 SA4 SA1TZ SA2TZ 3 Convert Text to Columns Wizard Step 3 of 3 E 2x Th
27. is screen lets you select each column and set olumn data format the Data Format General General converts numeric values to numbers date C Text values to dates and all remaining values to text C Date MDY C Do not import column skip Advanced yo F Destination AI 1 Data preview 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 If the data being imported has more characters than this the import will fail See Employee Table Field Descriptions on page 5 for details on how the data in each column must be formatted Saving the Modified Spreadsheet Save the spread sheet as a comma delimited text file so that it can be imported into Sapphire Pro In Excel 2000 this file has an extension of csv This file type can be imported into Sapphire Pro When all data is correct in the spread sheet click on the File menu and select Save As Select the destination folder where the file is to be saved Type a file name that is easy to remember then select CSV Comma delimited csv
28. itional 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 Data that is transmitted for USERFLDB appears in the Tenth user defined field of an employee card record User Defined Field B USERFLDB 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 Data that is transmitted for USERFLDB appears in the eleventh user defined field of an employee card record Vehicle 1 License Number VEH 1 ID Alphanumeric Up to 10 characters This is a reference field for a cardholder s primary vehicle license number and may be used at the option of the system owner Vehicle 1 Registration State VEH1STAT Alphanumeric Up to 2 characters This is a reference field for the state in which a cardholder s primary vehicle is registered and may be used at the option of the system owner Use the standard two characters abbreviations i e California is entered as CA 1 Although these security area fields are available it is easier to use procedure shown in mporting Se
29. mation that is unique to the site or facility For more information on defining User Fields see Section 2 of this supplement User Defined Field 3 USERFLD3 Alphanumeric Up to 24 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 Section 2 of this supplement User Defined Field 4 USERFLD4 Alphanumeric Up to 24 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 Section 2 of this supplement User Defined Field 5 USERFLD5S Alphanumeric Up to 16 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 User Defined Field 6 USERFLD6 Alphanumeric Up to 16 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
30. om this query lt Back Cancel 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 ax Save in a Queries gt FA File hame Query from Hansole Inc Employees Save as type Query Files dqy 7 Cancel 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 Returning External Data to Microsoft Excel IES Where do you want to put the data Existing worksheet Cancel C New worksheet Properties PivotTable report Parameters 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 Verify that the cell in the window is correct Click OK to import and display the data in Excel 21 A E USERID CCFCCARD ISSUELVL STATUS SERIALNO CARDE 000100000000000611 000100000000000611 D ACTIVE 0 000100000000004101 000100000000004101 0 ACTIVE 004101 000100000000004102 000100000000004102 0 ACTIVE 004102 000100000000004103 000100000000004103 0 ACTIVE 004103 012300450000000033 012300450000000033 D ACTIVE 33 012300450000000100 012300450000000100 0 ACTIVE 1111 012300450000000101 012300450000000101 0 ACTIVE 26 1005 2 012300450000000102 012
31. onferencealParkina A l141 Sapphire Pro and Topaz use separate columns for each area so the operator must insert a column to the right of this column for each area Row 6 requires three additional columns Rename the columns to match the Sapphire Pro or Topaz fields 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 cat wew Insert Format loos Data window Help BASRY SE o Bx 4 M Arial SA1 co Now click the Data menu and select Text to Columns to display the following Wizard dialog box AJ AK AL AM AN AO SA3 SA4 SA1TZ SA2TZ SA3TZ S 4 Convert Text to Columns Wizard Step 1 of 3 a 2 x The Text Wizard has determined that your data is Delimited If this is correct choose Next or choose the data type that best describes your data Original data type Choose the file type that best describes your data i Characters such as commas or tabs separate each field CiFixed width Fields are aligned in columns with spaces between each field oferel arking Prev
32. ot available in Topaz Sapphire Pro can be configured to import new employee information automatically This is done by assigning a network folder where files to be imported can be saved until they are imported into the system If the Automatic Import feature is enabled the host system checks the network folder for any files with the extension csv If a file is found the host system opens the file processes each record and then deletes the file Configuring the Site Settings The Site Settings screen must be set to accept uploads from the host computer This is done on the Site Settings screen Figure 4 Sample Site Settings screen Anti Passback 4 Disable Grant Access on Duress P pense Grant Access on APB Violation Invalid PIN Attempts Before Alarm Area Loading M Two Man Control of Invalid PIN sd parae Before Card Deactiv Sara a None rd Name With Month Day Year a Day Month Year i ii m Auto Card De ivation Period i fo Days 0 to disable featu wt reree ewe ewseee ae 4 m y Max Cameras Into Switcher Skip Command a Priority 0 is Highest Priority Alarm m a Max Monitors tnable Automatic Import D r 7 mn Import Directory Directory For Video Clips Clear 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 M
33. t name do you want to give your data source ib Hansole Inc Employees Select a driver for the type of database you want to access 2 See Click Connect and enter any information requested by the driver 3 Connect Select 4 default table for your data source optional 4 gt F Save my user ID and password in the data source definition OK Cancel 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 Select the correct Driver from the drop down list of available drivers as shown above Once you have selected the correct driver click the Connect button to locate and connect with the database SQL Server Login x IV Use Trusted Connectiori Cancel Login ID fu ser 1 Help Password E n Options gt gt If the Sapphire Pro or Topaz server that the operator is working from is connected to the Host network the server computer should appear in the drop down list in the Server field Select the name of the SQL server from the list The default is to Use Trusted Connection This uses the default Login ID and password that normally connects this computer with the server If this does not work consult the Network Administrator for Login and password assistance Click Options to select the database to
34. 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 Time Ctrl Security Area Site Settings ors r Evnt Alarm Opt i Access Groups i Access Group Name i a ee Acenunting Finance Use this number to Engineering add Access Groups Everywhere i 7 a Full Access z Receive from Card Import E i Access Group ID a te a Available Security Areas a N A a Selected Security Areas ie i Time Schedule 4 ee ae ee EOT Delete Clear 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 con
35. ulti 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 The processing of each record consists of 1 Update the record in the employee data file 2 Queue the record for background download to field panels 3 Generate a card record import event When a Record Fails When a record fails for some reason an error is reported as a card record upload event in the Event queue and the program skips the record No alarm is created for this event If you know when an import has been performed for example HR always generates a new employee report at the end of each business day the operator can check the Event Queue and verify that the import was successful See Verify the Card Import on page 12 Source File Parameters The source computer for example the Human Resources database server is responsible for giving each import file a unique name This could be an increasing integer file name such as 1 csv 2 csv etc e Maximum size per record is about 3500 characters e Maximum fields per record is 200 e File update rate is approximately 1000 records per minute Conversion Hints and Tools Introduction This provides some tools and short cut hints
36. ve titles that do not match the ones in the host system Using the Employee Table Field Descriptions on page 5 verify that each of the column headers is correct In our example CCFCCARD becomes CARD USERID becomes CRDHLDR ISSUELVL becomes ISSUENUM etc If the source file has the employee name in three columns instead of one Sapphire Pro and Topaz use the field CARDNAME to hold the employee name make certain that the Last Name has the column header LNAME the First Name column header is FNAME and the Middle Name is only the initial and the column header is MNAME They must also be in order LNAME FNAME MNAME 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 These user field names can be modified in Sapphire Pro or Topaz and once modified the new labels appear on the Employee Custom field screen However Sapphire Pro and Topaz still see this information as being in field USERFLD1 USERFLD2 etc The operator must ascertain what information is contained in a column and change the h
37. w 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 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 e 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 e 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 e Ifthe 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 e 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 e If Custom Fields are to be included in the card record the op
38. with the database Create a Query Open Microsoft Excel on your computer If you are importing this data into an existing Excel spread sheet open that file Select the cell to be used as the starting point for the data that is to be imported If you are using an existing file be certain that there is no data below or to the right of the selected cell as this data will be overwritten If this is a new file select Cell A1 From the Data Menu of Excel select Get External Data and then select New Database Query Is Data Window Help Acrobat ool amp 02 ant Brea mM Bl me Filter gt Validation E Text to Columns Ea Get External Data gt ee Run Saved Query H Refresh Data ic New Web Query a New Database Query KE Import Text File y Edit Query Ek Data Range Properties This opens the Choose Data Source window which allows you to select the location of your database Databases Queries OLAP Cubes dBase Files Word elete lt New Data Source gt Cancel dBASE Files DeluxeCD Browse ECDCMusic Excel Files Options FoxPro Files Word Del LocalServer Main Database IMCs JV Use the Query Wizard to create edit queries 16 Card Import for Sapphire Pro and Topaz Reference Manual Double click on lt New Data Source gt to locate the database file to be imported The following dialog box displays Create New Data Source Wha
Download Pdf Manuals
Related Search