Bank File Conversion Overview

BAI and BTRS File Conversion – Overview

A division of Automotive Systems Training – A Microsoft Partner

  • Application: The Bank File Conversion App for Excel 2013 has been approved as of September 30, 2014. You can get your copy here. The application is available in English, French, German, Spanish and Japanese.
  • Purpose: This application converts BAI (Version 2) and BTRS (Version 3) files to both XML and CSV. This application supports the latest version of BAI file specifications which is called BTRS (Bank Transaction Reporting System). The application is designed to allow users to quickly view and analyze bank data in their Business Intelligence systems. The application not only sets the data in Excel tables but provides validated XML for inclusion in SQL Server tables as well.
  • Security: This application uses SSL encryption and since BAI (BTRS) files contain sensitive information, bank routing and account number information is obscured immediately. It is also recommended that you delete this information on any BAI files saved locally or in the cloud. If you open the BAI file in Notepad, the lines that start with ’02,’ have sensitive information in the next two fields and the lines that start with ’03,’ have the account number in the next field. All three of these fields are obscured by the application and you may also obscure them prior to as well. If you do, you may want to leave the first 4 digits of the account number intact, like the application does, because many files contain data on multiple accounts.
  • Overview: The applicationis designed toprovide your bank data in multiple formats to meet you Business Intelligence needs.
    • The application will set the BAI or BTRS data into Excel tables on the worksheets you choose based on your selection in the record selector drop down box. We recommend that you open a new sheet and then select a record type to have the application create the table in that sheet. You can then rename the sheet to reflect the records contained in the table.
    • When you check the Create XML checkbox, both the XML and XSD (schema) text will be available on their respective tabs in the task pane for saving to your computer or copying to the clipboard in the case of smaller files.
    • The XML file produced by the conversion is accompanied by a fully deterministic schema to allow XQuery analysis down to the text node level.
    • The CSV text blocks are created when you check the Create CSV checkbox. These text blocks are avaiable on the CSV tab for you to save or copy and paste wherever needed.
    • Follow the steps outlined on the Home tab of the application and, if you make a mistake, just skip to Step 7 and click (twice) the Convert Another File button. This will reset the application and you can try again.
  • Uses: This application is designed to be flexible, accurate and to allow you to analyze bank data using the functionality of Excel especially PowerPivot.
    • This application does not invoke the PowerPivot functionality directly however you can select which tables of the BAI file data (each record type has its own table) to create which then allows you to add the table to the PowerPivot data model. This function is called using the Add Table to Data Model selection on the PowerPivot tool bar. The benefits of PowerPivot for your analysis is that it supports creating table relationships and implements the very powerful DAX formula specification.
  • User Community: This site is our Help Desk and User Forum. Please join our community of users.


About John Donnelly

Businessman, Economist, Developer, Writer
This entry was posted in Apps for Excel, Bank File Conversion and tagged . Bookmark the permalink.

Leave a Reply