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
- The first step is to locate the template.dbf file, found in the HRS installation
directory (default: C:\HRS).
- Now, copy and rename the template.dbf file to a more useful name and save this renamed
file into a different folder.
- Then, start Excel and open the renamed template.dbf file.
- 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
- 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!
- 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!
- Continue copying information from your spreadsheet, one field at a time, by using the
"Ctrl-C" keystroke command.
- 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
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
- 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: Its best saved in Excel
4.0 Worksheet format.
- Now, to save your newly created file as a .DBF, first choose the "Insert" menu,
then "Name", then "Define".
- 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.
- 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