xTuple.com xTupleU Blog & News Customer Support

Import Bank Statement - CSV

I'm trying to set up the import for bank statements via CSV. I am familiar with the CSVImport, but I want to make sure I map/import to the correct table and none of the names are jumping out at me as the one.

What is the name of the table to map/import bank statements to please? 

Import bank statements:  Setup, Accounting, global, check the box for Enable Bank Reconciliation Import

You then have an import button on the bank rec screen

Yeah - It's telling me I don't have a map/atlas thing set up.

If it's the same as when I imported with CSVImport, I'm pretty confident I can get it to work. I just don't know the name of the table to map....

Am I missing something simple? Is there any documentation anywhere on this? The "Reference Manual" that we paid for says simply "You will then follow the instructions in the Import Data

screen to complete the import.".......But there are no instructions for which view/table to map. Not exactly helpful. And definitely not something that a customer should have to guess at. 

I have attached the "standard" csv map for bank reconciliation import.  You will probably have to change based on the format of your file.  Note that there are pre and post sql procedures.

Thanks! I never would have guessed that post sql line.

So the bankrecimport_id I can leave to Default even tho it says it's required?

And was I correct about the _reference and _decscrip columns?

Our bank download doesn't seem to include a 'Memo' column - I assume that is what would go in the _comment column?

Some implementation guidelines:

1) Use Accounting->Setup, Global tab to enable Bank Reconciliation Import and define Debit and Credit Adjustment Types

2) Use System->Setup->Import/Export to setup the import utility

3) Start a new Bank Reconciliation period and click on the Import button and select the import file

4) The importBankrecCleared will attempt to clear bank transactions.  For deposits, will use bankrecimport_reference to match with cashrcpt_docnumber and clear the amount bankrecitem_debit_amount.  For payments, will use bankrecimport_reference to match with checkhead_number and clear the amount bankrecitem_credit_amount.  For both of the clearing transactions, bankrecimport_effdate is the date of the transaction.  Columns bankrecimport_descrip and bankrecimport_comment are not used.  Column bankrecimport is not used, but could be used to import currency exchange rates.  If a matching deposit/payment document is not found then a bank reconciliation adjustment is created based on configuration.

I think I've found the table/view I should map....bankrecimport ?

Question about what a few of the fields are tho - 
bankrecimport_id = Bank Account Number? Or the closing date in integer form? or?

bankrecimport_reference = Check Number ?

bankrecimport_descrip = Payer/Payee name? If so, do I need to manually edit out the "Debit Card Pament to" etc stuff on each line?

bankrecimport_curr_rate = I've no idea what this is...


We are having an issue as well with the bank import function.

After several attempts, we have been unable to upload a bank statement to our practice database as an admin user.

We have documented the upload error and related information.
We have followed the solution specified above plus variations without success yet.

So the attachment mentioned in this comment appear to not have survived the transition of the forums to Discourse. Does anyone know the pre/post SQL steps mentioned in this thread? I can’t find those documented anywhere.

I was able to figure out that the post-sql was at least:

SELECT importBankRecCleared(-1);

which is basically:

SELECT importBankRecCleared(fetchmetricvalue('ImportBankRecId')::INTEGER);

The Bank Reconciliation form will set the metric value “ImportBankRecId” when you press the “Import” button.

As to what the “pre” sql was or if there were other needs in “post” was unclear.