Unclaimed Property Systems
A division of Affiliated Computer Services, Inc.
2840 Wilderness Pl Suite F     Boulder, CO 80301        Phone: (303) 413-9450     Fax: (303) 413-0442
Holder Reporting System

Converting Excel Spreadsheet into a database
for import into the HRS-Pro

It is fairly easy to convert your Microsoft Excel spreadsheet into a database file for import into the Holder Reporting System (HRS):

Creating A HRS Spreadsheet
  1. The first step is to locate the template.dbf file, found in the HRS installation
    directory (default: C:\HRS).
  2. Now, copy and rename the template.dbf file to a more useful name and save this renamed file into a different folder.
  3. Then, start Excel and open the renamed template.dbf file.
  4. Once the renamed template.dbf file is open in Excel, open your spreadsheet with the data you want to copy in Excel as well.

Important notes: DO NOT change the titles on the first line of the (renamed) template.dbf file;
DO NOT change column widths; DO NOT change the format of the data cells in the (renamed) template.dbf file. If you want more information on the fields in the template file, refer to the users guide in appendix C, xbase compatible files.

Copying the Unclaimed Property Data
  1. Copy the information from your spreadsheet by highlighting the COLUMN of information, then pressing "Ctrl-C".
    Please note: You can only copy ONE column of information at a time!
  2. Once you have "copied" the information from your spreadsheet, you then switch to the (renamed) template.dbf file, highlight the appropriate column (including the SAME number of rows as data items) and press "Ctrl-V" to "Paste" that information.
    Note: DO NOT copydata over the first row column names!
  3. Continue copying information from your spreadsheet, one field at a time, by using the "Ctrl-C" keystroke command.
  4. Paste the information into the (renamed) template.dbf file using the "Ctrl-V" keystroke command, one field at a time, until all relevant information is copied. Again, DO NOT copy data into the column headings of the (renamed) template.dbf file.

Important notes: Not all spreadsheets come in the same format. You, the user must determine which columns contain relevant information; you may need to contact the person whom created the spreadsheet or the state you are reporting to, to confirm relevant data.

DO NOT change the column widths, column names, or column format types; DO NOT copy data over the first row column names.

Saving the Spreadsheet as a Database
  1. First, save your newly created file as a spreadsheet; click on the "File" menu, then "Save as"; under the "Save as Type", choose the "Microsoft Excel 4.0 Worksheet". This is done in case your data gets "lost" during this process. Note: It’s best saved in Excel 4.0 Worksheet format.
  2. Now, to save your newly created file as a .DBF, first choose the "Insert" menu, then "Name", then "Define".
  3. Once the Define Name box appears, you must choose "Database" in the "Names in Workbook" choice; then, under "Refers to", after the rightmost "$", replace the number 1 with the total number of rows (INCLUDING the first row field name information). So, if you have 10 property items, enter the number 11 after the rightmost "$" (10 property items + 1 column heading information = 11 rows).
    Click on "OK" to save.
  4. Now, to save, simply click on the "File" menu, then "Save as"; under the "Save as Type", choose the "DBF 4 (dBASE IV)" selection. Enter a file name that is relevant to the data contained. Now, click on "Save", then "Yes" to update this HRS data file. Close the data file; click on "No" if asked to "Save" (again). Reopen the file you just saved to confirm that all of the data is in the file. Then close Excel.

Important notes: Closing Excel confirms that all files are closed; DO NOT rename your newly created data file "template.dbf". You are now ready to use HRS to import this file!

Importing the database file into HRS

To import your newly created database file into HRS, create a new report or select an existing report. Then click Option A and click the xbase button and choose the directory where you have saved your database file.