Patron Data Tab-Delimited Validation Tool User

Guide (revised 5/15/2015)

Purpose OCLC provides the Patron Data Validation Tool to libraries choosing to send patron data in a tab-delimited file.

After extracting your patron data, you should use the Patron Data Validation Tool to validate that your file is

well formed and valid. This validation step ensures that the patron data being sent to OCLC is either free of or

limited in errors.

By using the validation tool prior to sending the file to OCLC, you’ll:

• Reduce the time to load patron data

• Avoid delays during the migration with WorldShare Management Services

• Increase your understanding of the patron data format when sending ongoing patron data updates to

OCLC.

Patron Data Validation Tool The Patron Data Validation Tool for tab-delimited files is a macro-enabled Excel spreadsheet. The tool allows

you to import patron data records into an Excel sheet, click a button to run a set of validation rules against your

data to either confirm the validity of the file or provide error reports so that you can fix the file.

The Patron Data Validation Tool for tab-delimited files requires Microsoft Office and has been certified to work

in Excel Office 2007, 2010 and 2013 for Windows and Excel for Mac 2011.

Patron Data Validation Tab-Delimited Tool Overview The Patron Validation Data Tool is an Excel Spreadsheet that contains three tabs/worksheets.

1. Validation Sheet The Validation Sheet provides a summary of the tool. It allows you to execute the validation of the patron

data as well as view a summary report; download a detailed error report and generate a Patron Data .txt

file.

The Validation Sheet provides step by step instructions to use the tool. It also contains the buttons for

executing the validation, viewing the validation report, and generating error reports and the final formatted

file ready to deliver to OCLC.

2

2. Patron Data Sheet The Patron Data Sheet is where you’ll import patron records for validation. You also have the option to edit

the data in this sheet based on the results in the validation report. This edit option allows you to avoid

having to extract data from your current system repeatedly until all errors are resolved.

3. Validation Report The Validation Report tab provides a list of errors the number of occurrences and examples of the errors.

The Validation Report will be blank if no errors are uncovered.

Validate Patron Data Button

View Patron Data Validation

Report

Download the Validation Report to

file directory

Download corrected tab - delimited file

directory

Clears records

from the Patron Data Sheet

Navigates user back to the Patron

Validation Tool Homepage

Navigates user to the Patron

Validation Report that lists errors

3

Import Patron Data The first step to validating your patron files is to import your patron records into the Patron Data Sheet. Do not

copy and paste patron data into the validation tool. If you copy and paste patron data into the validation tool, it

will not function as designed.

1. Download the Patron Validation Tool from the User Support Center

2. Open Patron Validation Tool Excel file.

3. Ensure that your machine has macros enabled. If macros are not enabled, your computer will prompt

you to enable them.

a) Microsoft Excel running on the Microsoft Operating System will display a security warning

message at the top of the Excel sheet if macros are not enabled.

i. Click Enable Content to allow the Patron Validation Tool to run.

ii. You may also receive a text box, Select the Enable this Content radio button to run the

validation tool

iii. Click the Ok button

List of errors

Navigates user back to the Patron

Validation Tool Homepage

4

b) If Microsoft Excel is running on the Mac Operating system, the tool may display a security

warning message if macros are not enabled.

i. Click Enable Macro button to allow the Patron Validation Tool to run.

7. To import your patron records in Excel, Go to the Patron Data Sheet

a) Select Data from the Menu

b) Select From Text Icon to open the Text Import Wizard

5

Note: Excel for Mac go to Data->Get External Data->Import Text File to open the Text Import Wizard c)

Text Import Wizard Step 1

d) Text Import Wizard Step 2

Confirm that your data is

delimited

Confirm that the import

stars at row 1

Preview your data

Select Next to move to next wizard

6

e) Text Import Wizard Step 3

Note: To ensure that each column is selected utilize the scroll bar in the Data preview section of the Text Import

Wizard.

Confirm that your data is

Tab delimited

Review the text in the Data preview box to verify that the

tex t will be seperated into columns on the workshet as

specified by OCLC

Select Next to move to next wizard

Select Column data format

of Text

Select finish to complete

import

Select each column and verify that the

column is formatted as Text.

Scroll Bar

7

f) Confirm that the data will be inserted in cell A2.

Validate Patron Data

1. Go to the ’Validation Sheet’ of the Patron Data Validation Tool

2. Click the Validate Patron Data Button

Note: The validation processing time will increase with the number of records being validated. The following message

will appear when the validation has complete.

3. Once complete, the Validation Report Summary will populate.

a) If no errors exist, the Validation Report Summary will confirm the number of records validated

and state that no errors found.

.

b) If errors exist, the Validation Report Summary will confirm the number of records checked and

the number of errors found. You can view the list of errors on the Validation Report Tab of the

Workbook.

8

Correct Patron Data You have two options for correcting errors in your patron data files. You can either correct the data in the

Patron Data Sheet or download the validation report and correct the data in your source system. The former

allows you to make all updates within the Patron Data Validation Tool.

To view the Validation Report in the Patron Validation Tool:

1. Go to the ’ValidationReport’ sheet

4. Fix the errors indicated in the validation report

5. Repeat steps until the Validation Report Summary on the ‘Validation Sheet’ indicates no errors found.

Delete patron records

1. Go to the ‘PatronDataSheet’ sheet

2. Click the Clear PatronData Sheet button

2. View the Validation Report

3. Go to the ‘PatronDataSheet’ sheet

The Sample Error Data column will link to the first occurrence of an error

9

Note: By selecting the Clear PatronDataSheet button your data will be deleted from the worksheet. Undo functionality will

not reverse this action.

Download the Validation Report

1. Go to the Validation Sheet

2. Click Save Validation Report as an Excel File to generate an error report for download

3. Select a folder to save the Validation Report to your machine

Note: The validation report will not create if there is either no data or no errors generated 4.

The File Creation Status will update with the name of the file created:

5. View the Validation Report in the file path you indicated.

Export patron data into .TXT File You have the option to export your corrected Patron Data files into a txt file for delivery to OCLC.

1. Go to ValidationSheet

2. Click Save Validation Report as an Excel File to generate an error report for download

3. Select a folder to save the Validation Report to your machine

4. The File Creation Status will update with the name and of the file created

10

Note: The Patron Data Text file will not be created if there are no records present