Sage 300 CRE Bank Reconciliation with Cleared Checks Imported from PDF Statements

Sage 300 CRE does not import a PDF bank statement. Cash Management can import cleared checks from a text file with four fields, bank account, check number, amount and cleared date. Convert the PDF statement to CSV, keep the check lines, shape them into that layout, run Tools, Import Cleared Checks, then reconcile the month.

That is the short answer for a construction controller who has a stack of PDF statements and a Cash Management register full of open subcontractor checks. The rest of this article covers the file layout that imports on the first try, the statuses you will see afterward, and the parts of the statement the import does not touch.

Can Sage 300 CRE import a bank statement?

Partly. Sage 300 Construction and Real Estate, the product many offices still call Timberline, has no screen that reads a full bank statement and matches every line. What it has is Import Cleared Checks on the Tools menu of Cash Management. It reads a comma delimited or fixed length text file, finds each open check by number and amount, and marks it cleared with the date from the file. Deposits, transfers and bank fees are not part of that import, which users on the Sage Community Hub confirm in more than one thread.

For a general contractor that is still most of the work. A busy operating account can carry several hundred checks a month to subcontractors and suppliers, and clicking each one in the reconciliation screen is where the day goes. Deposits are usually a few dozen lines and clear quickly by hand.

Why the bank file usually does not work as is

The import was designed around a cleared check file sent by the bank. In practice offices run into three problems. Many banks only give the controller a PDF statement, or offer a cleared check export only with a treasury package nobody signed up for. When a bank file does exist, its column order rarely matches the Sage template, and the template has to be edited to match. And for any month older than what online banking keeps, the PDF is the only record left.

Sage Community Hub threads on this feature show the same pattern, files rejected because the template order did not match the bank's file, and partners who gave up after weeks. Building the file yourself from the statement removes that mismatch, because you choose the column order to fit the comma delimited template you already have.

What the Import Cleared Checks file needs

Only four fields, in the order of your import format. The Sage example for check 1550 for $79.00 cleared on June 26, 2003 at a bank with ID TERPBK is a single line: TERPBK,1550,79.00,06262003. The table shows where each value comes from when you start from a statement.

If you use the fixed length format instead, Sage asks you to copy the Cm_fixed.tpl template, rename it with a name that starts with CM_ and ends in .tpl, and adjust the field positions to your file. Most offices find the comma delimited format easier, and a file you build in Excel is comma delimited by nature.

How to import cleared checks into Sage 300 CRE from a PDF statement

Plan on twenty minutes for the first statement while you set up the Excel columns, and five minutes a month after that, because the same workbook can be reused with the new rows pasted in.

1. Upload the PDF statement to the statement converter on our Sage 300 bank reconciliation page (the same tool sits at the top of this article). Scanned and photographed statements go through OCR.
2. Tie the rows to the beginning and ending balances printed on the statement, then export CSV or Excel.
3. In Excel, filter to the check lines only. Build four columns: the bank account identifier repeated on every row, the check number, the amount as a positive number, and the date as =TEXT(A2,"mmddyyyy").
4. Copy the four columns as values into a new sheet with no header row and save it as CSV. Sage 300 CRE accepts any three character extension, so .csv or .txt both work.
5. In Cash Management open Tools, Import Cleared Checks. Enter the import file, a rejected records file, pick your comma delimited format and click Start.
6. Read the Import Cleared Checks journal. If anything was rejected, open the rejected records file, fix those rows and import only them.
7. Open Tasks, Reconcile for the bank account, enter the statement date and ending balance, and reconcile the cleared entries. Clear deposits, transfers and bank charges on screen.

Start with a one row test file. If that check flips to Cleared in Edit Register with the right date and amount, the format is right and the full month will import the same way. An error anywhere in the file stops the whole import, so a test row costs less than finding the problem in a 400 line file.

What changes in Cash Management after the import

Each matched check shows a reconciliation status of Cleared in Edit Register, the cleared amount fills with the imported amount and the cleared date with the date from the file. The bank account's open checks total drops by the total imported. The status letters are worth knowing, because they are what you filter on when a balance looks wrong.

When you then run the reconciliation for the statement, choosing to reconcile cleared entries picks up every check with a cleared date on or before the statement date. If the Bank Reconciliation report looks wrong afterward, Sage's own advice is to filter the bank register in CM Inquiry by status C and O, fix any item in the wrong status, and run the Bank Account Balance Verification report.

Deposits, transfers and fees still need a pass

The import only clears checks. Deposits, wires, ACH payments to vendors, transfers between your own accounts and bank charges are cleared on screen during the reconciliation, the same way as before. The converted statement helps here too: sort the CSV by type and amount and you have the deposit list in statement order next to the screen, instead of flipping through a PDF. ACH vendor payments that were entered as checks in Accounts Payable with a non numeric reference will not match the import, so leave them for the screen as well.

Construction offices tend to pay subcontractors in large batches, and the same AP clerk who runs the check batch is often the one tracking subcontractor certificates of insurance before any payment is released. Taking the check clearing off that person's month end frees real hours in a team that is usually short of them.

Catching up months or a whole year

A new controller often inherits an operating account that has not been reconciled since the last one left. Work oldest month first. Import the cleared checks for one statement, reconcile it to that statement's ending balance, and only then move to the next. A check that cleared in March but sits in the April file will otherwise be marked cleared with the wrong date and throw both months off. Statements older than what online banking keeps are fine, because the converter reads scanned paper statements with OCR the same way as digital PDFs.

If the reconciliation work is part of a wider cleanup before an audit or a lender review, the bookkeeping cleanup workflow for PDF statements covers the other accounts and the card statements in the same pass.

What this costs

OFXStatement Starter is $49 a month, or $24.50 a month on the annual plan, for 2,500 pages from one shared pool, with statements up to 25 pages each. A contractor's operating account with heavy check volume often prints 30 to 60 pages a month, so a catch-up year on one account is roughly 400 to 700 pages, inside a single Starter month. Plus is $149 a month for 10,000 pages, removes the per statement page cap and adds bulk upload of up to 50 files at once. You can test 15 pages without a credit card.

Companies that also run Sage 300 ERP, the former Accpac and a different product from Sage 300 CRE, can import the same statements there as OFX files. Our Sage 300 bank reconciliation OFX import guide walks through Import OFX Statements and Reconcile E-Statements. For other systems that clear checks from a file, see the BS&A bank reconciliation import from PDF statements and the Dynamics GP import bank statement guide.

Sage 300 CRE bank reconciliation questions

Can Sage 300 CRE import a full bank statement?

No. The file import Cash Management documents for reconciliation is Import Cleared Checks, which clears checks from a text file with four fields. Deposits, transfers and other bank activity are cleared on screen during the reconciliation, so a full statement is never imported as one file.

Can Sage 300 CRE import deposits from the bank?

No. Import Cleared Checks is limited to checks, and users on the Sage Community Hub confirm that deposits, payments and transfers are not included. Clear deposits on screen, using the converted statement sorted by amount as your list.

Why does Import Cleared Checks reject my file?

Usually because the column order or the date format does not match the import format. Use the comma delimited format, four fields in the order bank account, check number, amount, cleared date, a date written as MMDDYYYY, and no header row. Test one row first and read the rejected records file.

What does reconciliation status C mean in Sage 300 CRE?

C means the item is cleared but not yet reconciled. Import Cleared Checks sets it between reconciliations. When you reconcile the statement and include cleared entries dated on or before the statement date, those items move to R, reconciled.

Can I reconcile Sage 300 CRE from a scanned bank statement?

Yes. Convert the scanned statement with OCR, tie the rows to the printed balances, build the four column cleared checks file in Excel and import it. Clear the deposits and fees on screen and reconcile to the statement ending balance.