Overview
Importing transactions from Excel into Bank Rec Online is easy once your fields are properly mapped. Mapping tells the system which columns contain transaction dates, amounts, descriptions, check numbers, and other reconciliation data.
This guide explains common mapping practices, required and optional fields, and tips to ensure a successful import. Whether you're importing bank activity, credit card transactions, lockbox activity, or general ledger transactions, proper mapping helps streamline your bank reconciliation process and reduce manual entry.
Table of Contents
- Common Mapping Practices and Tips
- Required Fields for Bank Reconciliation Imports
- Optional Fields
- Importing Amounts Correctly
- Frequently Asked Questions
1. Common Mapping Practices and Tips
Not every column needs to be mapped
Your Excel file may contain information that is useful internally but not needed for reconciliation.
Examples include:
- Customer IDs
- Department Codes
- Internal Notes
- Reference Numbers
- Approval Information
These columns can be skipped during the import process.
Column order does not matter
Bank Rec Online identifies data based on your field mapping selections, not the order of the columns in your spreadsheet.
Header rows are supported
If your Excel file contains column headers, they can remain in the file. During import, you'll simply map each column to the appropriate Bank Rec Online field.
Preserve leading zeros
If your data includes transaction IDs, reference numbers, or account numbers that begin with zero, format those columns as Text in Excel before saving.
This prevents Excel from automatically removing leading zeros.
Preview before importing
Always review the mapping preview screen before completing the import. This allows you to verify that dates, amounts, and descriptions appear correctly.
2. Required Fields for Bank Reconciliation Imports
The required fields may vary depending on your reconciliation process, but most imports should include the following:
Amount
The transaction amount is required for reconciliation.
The system uses the amount to match imported transactions against existing bank activity and ledger entries.
Examples:
| Date | Description | Amount |
|---|---|---|
| 10/15/2026 | Deposit | 1500.00 |
| 10/16/2026 | Check 1054 | -425.00 |
Description
A transaction description helps identify the item during reconciliation.
Examples:
- ACH Payroll
- Deposit
- Wire Transfer
- Check Payment
- Merchant Deposit
Descriptions improve transaction visibility and help users quickly locate unmatched items.
Transaction Date
Transaction dates are strongly recommended and are typically included in most import files.
Examples:
- Posting Date
- Effective Date
- Transaction Date
- Bank Date
Accurate dates help narrow reconciliation periods and simplify matching.
3. Optional Fields
Depending on your import file, you may also map additional information.
Check Number
If your imported transactions include check numbers, mapping this field can improve matching accuracy.
Examples:
| Check Number |
|---|
| 1054 |
| 1055 |
| 1056 |
Check numbers are particularly useful when reconciling check activity against bank statement transactions.
Reference Number
Many banks provide reference IDs, confirmation numbers, or trace numbers.
Examples:
- ACH Trace Number
- Wire Reference Number
- Bank Reference ID
- Transaction ID
Mapping these fields provides additional transaction detail for research and reporting.
Debit/Credit Indicator
Some financial institutions provide separate indicators rather than signed amounts.
Examples:
| Type |
|---|
| Debit |
| Credit |
When available, this information can help identify the direction of a transaction.
Notes or Memo Fields
Memo information can provide additional transaction details that assist during research and reconciliation.
Examples:
- Vendor Name
- Invoice Number
- Customer Name
- Payment Reference
4. Importing Amounts Correctly
Proper amount mapping is critical for successful reconciliation.
Signed Amounts
Many bank exports use positive and negative values.
Example:
| Amount |
|---|
| 500.00 |
| -150.00 |
In this example:
- Positive values typically represent deposits or credits.
- Negative values typically represent withdrawals or debits.
Separate Credit and Debit Columns
Some bank exports provide separate columns for deposits and withdrawals.
Example:
| Debit | Credit |
|---|---|
| 100.00 | |
| 500.00 |
When importing these files, map the appropriate columns based on your import configuration.
Verify Bank Formatting
Different banks export transaction data differently.
Before importing:
- Confirm date formats are correct.
- Verify decimal placement.
- Review signs on transaction amounts.
- Check for blank rows or totals rows.
Taking a few moments to verify the file can prevent reconciliation issues later.
Frequently Asked Questions
1. Do I need to map every column in my Excel file?
No. Only map fields needed for reconciliation. Any unnecessary columns can be skipped.
2. Can I import Excel and CSV files?
Yes. Bank Rec Online supports common spreadsheet formats used by banks and accounting systems.
3. What if my file contains additional columns?
Additional columns can simply be ignored during the mapping process.
4. Why are my transaction dates not importing correctly?
Review the date format in your spreadsheet and verify that the correct column is mapped to the Transaction Date field.
5. Why are transactions importing with incorrect amounts?
Check whether your bank export uses:
- Positive and negative values
- Separate credit and debit columns
- Parentheses to indicate negative values
Incorrect formatting can affect import results.
6. Can I re-use the same mapping configuration?
Yes. If you regularly import files from the same bank or accounting system, using a consistent file layout can significantly speed up the import process.
Need Help?
Still have questions? Contact Treasury Software Support or visit www.treasurysoftware.com for personalized assistance.
