This article details how to bulk import jpg photos for use in RapIDCard. This process is performed outside of the RapIDCard application using our RapIDutilities data import/export utility.



What does RapIDCard require to display a jpg photo?


  • The photo must be jpg format only (jpeg not allowed)
  • The photo must reside in the PHOTO folder (Find the path by navigating to Utilities > Setup > Paths). This requires you to copy the photos to the correct folder otherwise RapIDCard won't be able to display the image.
  • The database record must contain a reference to the photo filename. The photo column name can vary depending upon the database schema but the photo column is typically named PHOTO in Pro/IDMS/Elite databases and PHOTOPATH in RTK12v3 databases.


Introduction


To guide you through the process of bulk importing your own set of photos, in this example we will update an example Microsoft SQL Server database.


We start with a database table that has 8 records without photos. Records 10010-80080 have a NULL PHOTO and PHOTO_DATE column. The presence of NULL indicates the records do not have an associated photo. In addition the RapIDCard application will show a gray photo background for these individuals.




Photo Import Requirements


1) Photos (jpg)

2) A CSV/txt file containing IDNumber and the Photo filename.


If you do not have a CSV/txt file, contact the resource that provided the photos. For advanced users see instructions for the CreatePhotoCSV.ps1 script at the bottom of this article, 


Here we see a screenshot of our temp folder containing the photos we want to import, and a data file containing two columns: IDNumber,Photo




Next, we will use RapIDutilities to import the data file into the SQL database.


Create a new RapIDutilities Import Map


  1. Open RapIDutilities
  2. Navigate to the Import tab, click NEW
  3. Complete the following:
    1. Import Name  = Use any name
    2. DSN = Select the DSN where the database table is located
    3. Username / password = The SQL login credential
    4. Table = This table holds the RapIDCard records
    5. Primary key = This is the unique key, typically ID_NUM or IDNumber
    6. Import File
    7. Enable UPDATE ONLY
    8. Add the ID_NUM, PHOTO, and PHOTO_DATE columns to the mapping

       
  4. Next, select the Import File Column for Mappings 1 and 2
  5. For the photo date: In the best-case scenario your import file should include a column for photodate, but if that is not available we can use the %GETDATE() function to apply the current timestamp. Or, enter your own MM/DD/YYYY value. 

  6. Save the map
  7. Click Run


After executing the import map the SQL database table now reflects the photo filename and photo date for records 10010-80080:




Copy photos to the PHOTO folder


Now that the database has been updated, the last step in the process is to copy the new photos to the RapIDcard PHOTOS path.


To find the PHOTO folder, logon to RapIDcard, then navigate to Utilities > Setup > Paths:



Using Windows Explorer, copy/paste the photos to the designated PHOTO folder.




After the photo copy is complete verify the process was successful by opening the RapIDcard database (DBL) and then Quick Find to one or more records.



Here we confirm record 10010 now contains a photo with a photo date.




Optionally, left-click the photo reveal the photo location:





This concludes the bulk photo import instructions.




Tips


  • To aid in the CSV file creation we have authored the attached CreatePhotoCSV.ps1 PowerShell script found at the bottom of this page. It will scan the designated folder for jpgs and generates a file named PhotoImportCSV.csv containing 3 columns: "IDNumber","Name","CreationTime".

  • To import jpg as BLOB in the database contact Vision Database Support