Card Import for Sapphire Pro and Topaz
Contents
1. interlogix Card Import for Sapphire Pro and Topaz Reference Manual J P N 460921001C ISS 27FEB13 Copyright Trademarks and patents Manufacturer Version Certification FCC compliance 2013 UTC Fire amp Security Americas Corporation Inc Interlogix is part of UTC Climate Controls amp Security a unit of United Technologies Corporation All rights reserved The Topaz product and logos are trademarks of United Technologies Other trade names used in this document may be trademarks or registered trademarks of the manufacturers or vendors of the respective products UTC Fire amp Security Americas Corporation Inc 791 Park of Commerce Blvd Suite 100 Boca Raton FL 33487 3630 USA Authorized EU manufacturing representative UTC Fire amp Security B V Kelvinstraat 7 6003 DH Weert Netherlands This document applies to Topaz version 1 5 1 This device complies with part 15 of the FCC Rules Operation is subject to the following two conditions 1 This device may not cause harmful interference and 2 this device must accept any interference received including interference that may cause undesired operation Class A 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 reasonable protection against harmful interference when the equipment is operated in a commercial environment This2. Template Trace Time TRACE TZ Schedule 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 User Defined Field 3 User Defined Field 4 User Defined Field 5 User Defined Field 6 User Defined Field 7 User Defined Field 8 User Defined Field 9 USERFLD2 USERFLD3 USERFLD4 Alphanumeric Alphanumeric 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 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 Up to 24 characters User defined fields can be set up by the operator for use in storing additional referen
3. and may be used at the option of the system owner Vehicle 1 VEH1STAT Alphanumeric Up to 2 characters Registration This is a reference field for the state in which a cardholder s primary State 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 Importing Security 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 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 A ge oe o Make certain that the numerical Access Groups ID is used for each Access Group not the ASCII Name 11 Label columns according to file CARD must be the first column format requirements Change Access Groups to numeric values Make certain these columns are in the correct order CARD LNAME FNAME SSN TI
4. 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 Example CARD LNAME FNAME MNAME SSN 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 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 M
5. 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 A AK AL AN AN SA SAL J SAL 4 SATT SA TL Convert Text to Columns Wizard Step 2 of 3 a E 3 AE This screen lets you set the delimiters your data contains You can see how your text is affected in the preview below B Aer E Treat consecutive delimiters as one I Tab F Semicolon l Comma Space Other I Text qualifier F onference onference onference arking B arking B 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 AJ AK AL Atl AM SA 2 SAS SA 4 SA 1 77 SA TZ Convert Text to Columns Wizard Step 3 of 3 a x This screen lets vou select each column and set oluran data Format the Gata Format General General converts numeric values to numbers date Text Date MOY ig Do not import column skip Values to dates and all remaining values to text Advanced P Destination AI 1 Pa
6. the is greater than condition and the 127465 variable the upload only contains those records that have an user ID number after number 127465 lf 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 ee x Specihy how you want your data sorted IF you don t want to sort the data click Nert Ascending Descending Then by Ascending Descending Then by Ascending Descending 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 nest Retum Data to Microsoft Excel Save Query View data or edit query in Microsoft Query Create an OLAF Cube from this query 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 re x Save Ir E Queries a i EE File name Quem from Hansole lnc Employees Save as type Query Files dqy
7. 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 USERFLDA Alphanumeric Up to 12 characters Field A 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 Tenth user defined field of an employee card record User Defined USERFLDB Alphanumeric Up to 12 characters Field B 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 VEH 1 ID Alphanumeric Up to 10 characters License This is a reference field for a cardholder s primary vehicle license Number number
8. 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 E A 2 x Where do you want to put the data ox Existing worksheet Cancel New worksheet Popei PiyvokTable report E lf 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 1 USERID CCFCCARD ISSUELYL STATUS SERISLNO CARDE 0001000000000011 OOO TOOUOUOUOE 1 0 Ak IE 0 0001 00000000004101 00010000004101 0 ACTIVE 004101 0001000000000040 0001000000000041 0 0 ACTIVE 004102 OOO TOOOOOOOO0041OS 00010000000040 0 ACTIVE 004103 6 012300450000000033 012300450000000033 0 AL IE a3 012300450000000100 0124350045S00000001 00 0 Ak IE 1111 8 012300450000000101 012300450000000101 0 ACTIVE 26 10 057 9 012300450000000102 012300450000000102 0 ACTIVE J52 012300450000000 103 012300450000000103 0 AL TIVE J33 012300450000000104 0123004500000001 04 0 ACTIVE 12 012300450000000105 012300450000000105 0 ACTIVE 2 O12S00450000000106 012300450000000106 0 ACTIVE 2d 14 INA Tannen AS Ad nA Annan 4 T mn Ae Th fe JE The information can now be modified to conform to the Field titles that are used by the Host Upload Program Manipula
9. NAME 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 Y Custom Fld YFacility Code Variable Crd Reader Evnt Alarm Opt Access Groups Access Group Name Accounting Finance K l K3 PaE i Use this number to Engineering add Access Groups Everywhere Full Access gt i Receive from Card Import 1A Access Group ID Status Control A Available Security Areas a gt Time Schedule et W SavelAdd amp 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 configured 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 Other 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 numbe
10. NUM FIELD NAME NOT DEFINED 14 46 37 HOST UPLOAD EVENT AOD 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 not 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 Si
11. TLE USERFLD1 CCH FC 124 BAKER EVERETT L 123456769 SAFETY SPECIALIST K C FARMLAND 3 CC RC 54 BENKIN LARRY 234567890 FARMLAND 4 CCH Ait 75 BREWER BRUCE 345678901 VIRONMENTAL ENG K C FARMLAND 3 SR Change card format to Facility Code 4 digits l Company Code 4 digits SS ii Card Number up to 10 digits 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 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 pi Area Loading Guard Tour Redundnt Sys Elevator t Panel EtL z Ce Alarm Database Repair i Cardholder Database Repair Cardholder Database Diagnostic with APB Repair pereen eae hia errors wih aS Reader Alarm Point Relay Access Trace Reset APB Bk Up Rest J g k 7 a a ad Cardholder Database Diagnostic without APB Repair Synchronize Entire Database Transmit Cards Modified in Last 12 Card Import for Sapphire Pro and Topaz Reference Manual lf the exact path to the import file is Known type the pat
12. anada European Union directives Contact information Customer support Notice This is a Class A product In a domestic environment this product may cause radio interference in which case the user may be required to take adequate measures This Class A digital apparatus complies with Canadian ICES 003 Cet appareil num rique de la classe A est conforme la norme NMB 0330 du Canada 12004 108 EC EMC directive Hereby UTC Fire amp Security declares that this device is in compliance with the essential requirements and other relevant provisions of Directive 2004 108 EC 2002 96 EC WEEE directive Products marked with this symbol cannot be disposed of as unsorted municipal waste in the European Union For wees PLOper 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 2006 66 EC battery directive This product contains a battery that cannot be disposed of as unsorted municipal waste in the European Union See the product documentation for specific battery information The battery is marked with this symbol which may include lettering to indicate cadmium Cd lead Pb or mercury Hg For proper recycling return the battery to your supplier or to a designated collection point For more information see www recyclethis info www interlogix com www interlogix com customer supp
13. ata 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 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
14. ce 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 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 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 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 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 Up to
15. e 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 LVAME 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 header 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
16. e 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 a a x Server Hansole OK Jw Use Trusted Connectior Cancel Login ID ser Help Password E Uptions gt gt Md lf 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 be the data source for this query Password E amp Options gt Options Database Main ad Language English Application Name Microsoft Query Work Station ID Station 12 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 Defa
17. 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 Operation of this equipment in a residential area is likely to cause harmful interference in which case the user will be required to correct the interference at his own expense Class B This equipment has been tested and found to comply with the limits for a Class B digital device pursuant to part 15 of the FCC Rules These limits are designed to provide reasonable protection against harmful interference in a residential installation This equipment generates uses and can radiate radio frequency energy and if not installed and used in accordance with the instructions may cause harmful interference to radio communications There is no guarantee that interference will not occur in a particular installation If this equipment does cause harmful interference to radio or television reception which can be determined by turning the equipment off and on the user is encouraged to try to correct the interference by one or more of the following measures e Reorient or relocate the receiving antenna e Increase the separation between the equipment and receiver e Connect the equipment into an outlet on a circuit different from that to which the receiver is connected e Consult the dealer or an experienced radio TV technician for help ACMA compliance C
18. event should be the Cardholder Upload as shown below Figure 3 Sample Events Queue MM up SS i gu mem Click o on 1 Event for More Detail pua I 14 26 31 JARED AR e PEA MONITOR TIME ZONE 02 14 26 31 SECURED AREA MONITOR REPORT SECURITY AREA CONFERENCE S UNSECUPE AREA MONITOR TIME ZONE 02 14 26 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 MASE 14 28 31 ALARM MASKING REPORT COMMAND MASE 14 97 41 Al ABM MASEINIG REPORT COMMAND IMKAASEK lf there are any problems with any of the cards those errors are also shown in this box Click on Event for More Detail FIELD MAME NOT OE FIELD NAME NOT DEFINED Ab FIELD NAME NOT DEFINED 14 46 37 HOST UPLOAD EVENT ADD CARD 1230 0500 at MO O00 g FIELD CARDNUM FIELD MAME MOT aije 14 46 37 HOST UPLOAD EVENT SOD CARD 1230 0500 000000U008 FIELD NAME MO LDERHHTE 14 46 37 HOST UPLOAD EVENT SDD CARD 1230 0500 000000001 TAME NWOT DEFINED 14 46 37 HOST UPLOAD EVENT S00 CARD 1230 0500 0000000016 FIELD CARDNUM FIELD WAME 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 CARDO 1000 0450 000000001 4 FIELD CARDONUM FIELD NAME NOT DEFINED 14 46 37 HOST UPLOAD EVENT ADD CARD 1000 0450 000000001 3 FIELD CARD
19. h 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 e All fields are verified by the host system If a field name is not found or contains invalid data the message is rejected e After data is saved to disk it is the data is queued to be background downloaded to access control panels AC Us e The upload from the external system to the host 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 e 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 e 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 successtul click Events in the upper left corner of the screen This opens the Events Queue The most recent
20. he 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 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 d
21. 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 Note Ifthe 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 my 2 Filter the data to specify which rows to tnclide in your query Fou don t want to filter the data click Next Column to filter Only include rows where USERID eee lis greater than 127465 Nahe FIRST And C Or NAMEMID SERIALNO AFFIL r r KEYPADID Ane pi ISSUELYL o STATUS GRAPE C Aand C Or Back Cancel This window allows you to 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
22. ly add 4 if cardholder access should be traced add 8 ONLAFLAG Numeric e For Topaz always 1 employee e For Sapphire Pro 1 for employee 9 for visitor Tenant ID OPGRPID ID of operator group Photo ID PHOTOID Numeric This is the name of the photo of the Cardholder The photo file itself must be in the BADGER folder on the Server The photo must have a special indexed numerical name See Section 2 for details Number RELATION Alphanumeric Up to 16 characters This is a reference field that may be used at the option of the system owner Header is Up to 12 characters ASCII To add a Security Area to a record use the ASCII name of the Security Record Last Updated Cardholder flags Affiliation flags Emergency Contact Relationship 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 ID of first authorized Security Security area SA1 TZ Numeric ID of time schedule when access is authorized to corresponding security area SA 2 ID of second authorized Security Security areat SA 2 TZ Numeric ID of time schedule when access is authorized to corresponding security area SA 3 ID of third authorized Security Security area SA 3 TZ Numeric ID of time schedule when access is authorized to corresponding security area SA 4 ID of fourth authorized Securit
23. ort Contents OVEIVION saeco ae eeu e eae ee See ea ee ae ear ea oe eee ee eee ee en 1 inpoi SOUrce FIE issrrssar reds rrt Iras teecitsan Hee eee eee eet bees teeeeee 1 Information Needed to Format the Comma Delimited File 0 0 0 cc eee 1 Formatting the Comma Delimited File 2 0 0 0 0 eee eee nee 2 Field Names and Data ValueS 0 0 ccc teens 2 Format Review 2 244 622524 e00 e een Soebbaeehedesendadh ag 40 Eb howe Re oh eet Ree eeet 10 Importing the Database Manually 00 ccc eee 11 Verify the Card Import 0 0 ee ee eee eee teens 12 Setting up the Host for Automatic Import 0 00 eee 13 Configuring the Site SettingS 0 0 ee eee ee ee ees 13 Automatic Import Processing 0 ccc ee eee eee eee ees 14 Conversion Hints and Tools 21 22 2 cce ecdeseaes wees serie due ee oud eee westes sd acne 14 MOUCHO 4 4 225244652645 885 5 5065 cee nee Bee et aes ec bse ceed ees ee eee ee 14 Using Microsoft Query to Import the Database to Excel 0 0 0 ccc eee 14 Cheate oO QUELY e Seo a e Benes Se eee eae h eee eee eee oye ba ee eee ee 15 Manipulating the Data in Microsoft Excel 0 c eee ee 21 Moving the CARD Field to Column One 0 0 eee ees 21 Renaming Column Headers 0 eee ee eee eens 21 Renaming User Defined fieldS 0 0 ccc eee eee tenes 22 Distributing Data across Multiple Columns 0 0000 eee eens 22 Veny Field Dala FOMA
24. 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 ISSUEDAT J 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 1 to 6 digits 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 Keypad Number Numeric 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 ONLFLAGS Numeric 0 255 For import add the following values together if authorized for guard tour Sapphire Pro only add 1 if authorized to bypass anti passback GUI field Executive APB override add 2 if a visitor card Sapphire Pro on
25. ported 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 _ View Insert Format loos Uata window Help BASRY BeA n A E amp 4 g aa B B R GRP SA 1 SA SA 3 SA 4 SA 1 Admin Break ArealCorr Break ArealCor Break ArealCarr Now click the Data menu and select Text to Columns to display the following Wizard dialog box A AK AL Alvi Al AD SA J SA d4 SAITf SATA S5A3TZ Convert Text to Columns Wizard Step 1 of 3 F B E 2 x The Text Wizard has determined that your data is Delimited i IF this is correct choose Next or choose the data type that best describes your data erel riginal data type Choose the file type that best describes your data i Delimited Characters such as commas or tabs separate each Field Fixed width Fields are aligned in columns with spaces between each field arking Break Areal Conterence Break Areal Conference Break Areal Conference 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
26. r 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 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 1 Access Group ACCGRP 2 Numeric 2 Access Group ACCGRP 3 Numeric 3 Access Group ACCGRP 4 Numeric 4 Access Group ACCGRP 5 Numeric 5 Access Group ACCGRP 6 Numeric 6 Access Group ACCGRP 7 Numeric 7 Access Group ACCGRP 8 Numeric 8 Activation ACT DATE Numeric Date 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 number The card format i
27. rking A Conference i 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 from the Save as Type drop down menu Click Save 26 Card Import for Sapphire Pro and Topaz Reference Manual
28. s 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 Numeric Up to 3 characters This field is the numeric ID number assigned to an Access Group the Access Group ID is imported Up to 3 characters This field is the numeric ID number assigned to an Access Group the Access Group ID is imported Up to 3 characters This field is the numeric ID number assigned to an Access Group the Access Group ID is imported Up to 3 characters This field is the numeric ID number assigned to an Access Group the Access Group ID is imported Up to 3 characters This field is the numeric ID number assigned to an Access Group the Access Group ID is imported Up to 3 characters This field is the numeric ID number assigned to an Access Group the Access Group ID is imported Up to 3 characters This field is the numeric ID number assigned to an Access Group the Access Group ID is imported Up to 3 characters This field is the numeric ID number assigned to an Access Group Only the Access Group ID is imported Format for this numerical field is Year Month Day separated by slashes and two digits each 6 Card Import for Sapphire Pro and Topaz Cardholder Name Up to 32 characters When the database being imported uses more than one field for the Cardholders Name this data must be combined in
29. seres sett ngaeecund Ee aS deb RE ewe PEER a iee a a E 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 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 Information Needed to Format the Comma Delimi
30. t is to import the data into the security system Note Ifthe automatic Import function is enabled these fields should be exact matches Otherwise the cvs file must be formatted each time there is an import 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 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
31. te Settings screen Figure 4 Sample Site Settings screen i Anti Parmnack STETE TE Dian Aes on Duress ae Grant Ace ess i on n AP B Violation invalid PIN mpre y BL fore Alann z Area L vading Two Ma am i amirol Yal lnvalid PIN Atlempts i eee epee i _ Before Card Deac tivation X i J Event Re opart i card Name with fione ee a i Month Day Year Ti 7 T r A i MEDIE Lard Format Byitone Day Month Year i EErEE TE rT EEE eer eEaiai Sere ee ee e A a ama a Ta a a E _ ii e E Auto C ard Deactivation Period Days Blaz Cameras pinin Sanie _ Maximum Presets Per Camera _ Bar Moniiars Uui Ot vate SACO a il r ere ne Enable Automatic ac linger 4 C a man camara ieee baaa dea haini doled abl nd and ek a a eaa aaa a amkan tee baa dr datnan ms CETE Directory For Video Cins T i r 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 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 The processing of each record consists of 1 Update t
32. ted 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 f 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 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 i
33. the corresponding USERFLD Replace Imported Header with Sapphire Pro or Topaz Header 2 Je 2 MOE Ba anil Ls LM Ngee E 0 EMPHUM KEYPAD AFFIL GUARDTOUR ENMPNUM KEYPAD USERFLD amp GUARDTOUR 125456 Executive OFF 12345A N Fyerutive 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 File Edit wiew Insert Format Tools Data Window Help This cell has both Break Area and 4 AE A gt IC te SJE Lav B ho u 24 Conference They are separated AL CCFOCARD 1 by a vertical line ee een cere ee em e E at E 4 ACCGRPNUM AREANAME AREANUM THAME Admin 5 34 Break ArealConterence Als 4 4 Break ArealConterence AIS 514 Break ArealConference AIS 6 4 Parking AlParking GlBreak ArealConference 10111145 EF Caontaranrcal Parkina A Ali 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 im
34. this is a new file select Cell A1 From the Data Menu of Excel select Get External Data and then select New Database Query ools Data Window Help Acrobat Et Sort fe Al Z bn 43 Filter Validation E Text to Columns C Get External Daka F ne Run Saved Query d Refresh Data fet New Web Query New Database Query a Import Text File it hol Edit Query Data Range Properties This opens the Choose Data Source window which allows you to select the location of your database Choose Data Source a 2 x Databases Queries OLAF Cubes New Data Source dB ase Files Word dBASE Files DelutelD ECO CMusic Excel Files FoxPro Files Word LocalS erer Man Database BJ M Use the Quer Wizard to createredit 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 Al E 3 x What name do you want to give your data source 1 JHansole Inc Employees Select a driver for the type of database you want to access SOL Server o 0000 Click Connect and enter any information requested by the driver J Connect Select a default table for your data source optionall 4 Save my user lD and password in the data source definition ok Cancel Type in th
35. ting 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 E File Edit View Insert Format Tools Data window Help Dee 4k BB o TAEA GB ara 10 CAL es CCFCARD A B D _1 CCFCCARD USERID ISSUELVL STATUS 2 000100000000000611 WOU TOUOUUOOOOO06T 1 D ACTIVE Renaming Column Headers Sometimes the Column Headers line one of the comma delimited file have 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 lf 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 mak
36. to the one cell For Example Last Name Boulder First Name John must be combined into CARDNAME Boulder John Reference Manual 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 CARDNAME_ Alphanumeric 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 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
37. ult settings If the database is ina 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 ae i eid What name do vou want to give your data source 1 JHansole 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 J Connect Main Select a default table for your data source optional 1 ee T Save my user D and password in the data source definition Cancel lf 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 17 18 Card Import for Sapphire Pro and Topaz Reference Manual Query Wizard Choose Columns ae x What columns of data do you want to inclide in your quen Available tables and columns Columns in your Query RE mol UELL al a i _ lt GRAPE ha LARDEFFDATE cael _ lt caRpexpoare CANESCORT PHOTO ESCORTREQ BADGETEMPLATE TRACF f ARERI f Preview of data in selected column iene B Preview Now Options Back Cancel Using the
38. y 8 Card Import for Sapphire Pro and Topaz Reference Manual Numeric ID of time schedule when access is authorized to corresponding security area Numeric ID of fifth authorized Security Numeric ID of time schedule when access is authorized to corresponding security area Numeric ID of sixth authorized Security Numeric ID of time schedule when access is authorized to corresponding security area Numeric ID of seventh authorized Security Numeric ID of time schedule when access is authorized to corresponding security area SA 8 Numeric ID of eighth authorized Security Security area SA 8 TZ Numeric ID of time schedule when access is authorized to corresponding security area Social SSN Numeric 11 characters Security Data for this reference field is transmitted with contiguous numbers For example if the Social Security Number is 555 12 3456 data is transmitted as 555123456 Number Card Status STATUS Numeric 1 Digit Enter one of the following values for each card VALUE DESCRIPTION OK This is the default Lost Access will be denied Stolen Access will be denied Inactive Access will be denied Terminated Access will be denied Numeric Telephone number extension ASCII Up to 12 characters File name of the Badge Template Numeric If this employee is to be traced value is a number of the time schedule the trace is active 1 never 2 always Phone TELEPHON Extension Badge TEMPLATE
Download Pdf Manuals
Related Search