Creating the Query and Export

Create the query

• Go to Query > New Query
  
• Select the query type 'Constituent' and click 'OK'
  
• Select the records to be cleaned. As an example you may choose the criteria as below:
  
• Save your query as 'SmartZIP: Bulk bank clean' and then close it
  

• Go to Export and open the 'SmartZIP: Bank bulk export' export
  
• Go to File > Export
  
• Select the filename to use and 'Save' (note the file location as they then need to open the file in Excel)
  

Cleaning the data

1. Open the file in Excel
   
2. To perform the bank data clean, ensure you have set a PCA/Loqate key in the Excel add-in
   
3. Load your exported bank file which should have headers:
   
   
4. Click the 'Clean Accounts' button
   
5. When the process completes review the results 
   
6. You must then save the file in 'txt' format. Select File > Save As and then select 'Save as type' > 'Text (Tab delimited) (*.txt) before saving the file
   
   

Creating the Import 

• Go to Admin > Import
  
• Select 'Constituent Bank' and click 'New'
  
• On the General tab:
  
• Select 'Update existing records'
  
• Select your .txt file which will be in the same folder as the exported XLS file from the above export process usually
  
• Note: You may have to change the file types when selecting the file to show all otherwise it may not appear
  
  
• The rest should be left at default values but for reference it should look as below:
  
• Click 'Next' and change the Field Separators to 'tab', leave the other options at their defaults:
  
• Fields should be mapped as below:
  
  
• On the final tab we suggest you select to create an Exception file but the options are really down to yourselves.
  
• Then save the Export as 'SmartBANK Bulk Import'
  
• Finally click 'Update Now' to import the updated information back into Raiser's Edge
  

Notes

• You may wish to review the file prior to import to correct any issues that might need manual intervention but you can also do this after the data is back in Raiser's Edge through use of the 'Bank Account notes' field which has the overall status.
  
• You can use an attribute instead of Bank Account Notes to record the validation result.
  
• In this case your export would have the fields:
  
• Constituent Import ID
  
• Bank Import Id
  
• Bank Sort Code
  
• Bank Account Number
  
• [Attribute] Description (Attribute must have type text)
  
• [Attribute] Date
  
• [Attribute] Import ID
  
• You would also need to change your import to use these details