Generic Database Import (Excel Spreadsheet)

Generic Database Import (Excel Spreadsheet)

Importing Cardholder Data from an XLS or XLSX File

  • This is not a licensed feature so it can be used without any additional charge.

Setup Procedure

  1. Create a folder on the C: drive and copy your XLS or XLSX file into it.
  2. Go to the local drive on the PC, click Keri >> DoorsNET then ensure the files and folders are sorted by type.
  3. Scroll down the list and locate the executable file named: CardholderImportGeneric.

    Excel Importing - Image 2

  4. Execute the file.
  5. Login with the default username and password (both admin) then click Connect.

    Excel Importing - Image 3

  6. Right-click in the Connect String text field and select Excel if you are importing an XLS file.

    XLS File

  7. Select Excel 2007 if you will be importing data from an XLSX file.
  8. The following text will be added (if using XLS/Excel) -Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
  9. If using an XLSX/Excel 2007 file, the default text will be: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
  10. Change Source=c:\myFolder\myExcel2007file.xls(x) to the specific name of the folder you created and the exact name of your Excel file.
  11. For example: Source=c:\Cardholders\20users.xls(x).

    Excel Importing - Image 5

  12. In the Query text field right-click and you will see the names of the DoorsNET cardholder data fields. It is easiest if these exactly match the headers in the Excel spreadsheet.

    Excel Importing

  13. Once your spreadsheet headers match those that display when you right click enter the following query (or similar):
  14. Select Imprint, Last_Name, First_Name, Middle_Name, Cardnumber, ActivationDate, ExpirationDate FROM [Sheet1$] - Sheet 1 is the default name of the first sheet of an XLS or XLSX spreadsheet.
  15. If the headers in the spreadsheet do not match, you will need to type Select [HeaderName 1] AS - then right-click and select the Doors.NET cardholder field to map it to.

    For example FNAME AS First_Name, LNAME AS Last_Name, MNAME AS Middle_Name, etc FROM [SHEET1$]

  16. Your import wizard should resemble what is displayed below:

    Excel Importing - Image 7

  17. Next, assign a valid access group to the imported cardholders or set them to Unassigned.
  18. Set the card status to either active or inactive.

    Excel Importing - Image 8

  19. Click the Import button. You will see the following window.

    Excel Importing - Image 9

  20. You will then see a review window similar to that shown below.

    Excel Importing - Image 10

  21. Click Yes and importing of the data will begin.
  22. At the bottom of the wizard you will see an indicator each time a record is saved.
  23. Once complete a notification will appear confirming the importing has finished. Click OK to this message.

    Excel Importing - Image 11

  24. On the import wizard you now have to check the option "Check Multiple Access Groups". Then click the Import button again. 
    This will upgrade each of the cardholder records so that in the database they will support the assignment of multiple access groups.

    Excel Importing - Image 12

  25. Click Import again, each of the imported records will be scanned, then the following message will appear.

    Excel Importing - Image 13

  26. OK this message and close the import wizard.
  27. You will now be able to log into the Doors.NET software and view the imported cardholder records.

    • Related Articles

    • LDAP Import for Active Directory - Overview

      The LDAP Import feature has two parts: a stand-alone LDAP Import utility and an LDAP Synchronization Task that runs continuously to keep the Doors.NET Cardholder database synchronized with the Active Directory Users database. The LDAP Import Utility ...
    • Doors.NET to Borealis Database Conversion Procedure

      1.0 Introduction The database conversion tool allows you to convert a Doors.NET system to a Borealis system. It will copy across cardholders, credentials, controllers and door information, plus time schedules and holidays. The easy-to-use wizard ...
    • Local Linkage - Import and Export

      The Linkage Export/Import feature allows you to save Local Linkage actions which can then be imported and used on other controllers (or completely different Doors.NET systems), potentially saving a lot of system programming time.   Note: ALL the ...
    • Detach/Attach a Doors.NET Database

      1.0 Introduction This document explains how to detach a Doors.NET database - (so it can be copied to a backup location and used for migrating a system to a different host PC/Server), It then explains how to attach a Doors.NET database. Typical ...
    • Doors32 to Doors.NET Database Conversion Agreement

      Doors32 to Doors.NET Database Conversion Agreement - standalone document