Odoo Help

Welcome!

This community is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

0

How do I import a bank statement with dates like '1/20/2015'

By
Troy Benjegerdes
on 2/6/15, 8:52 PM 2,722 views

I am trying to replace quickbooks, and I'd like to either use the same .qbo export file from my bank(s), or use CSV. But with CSV, dates like '1/20/2015' fail to validate.  So how do I change the data format(s), or make it so I can automate import of bank account data?

what I would really like are pointers to the import python code in https://github.com/odoo/odoo, and an estimate on what it would cost to have configurable data format import filters in the SaaS version, and then I can decide if I should just pay for the development or write my own importer. I'm trying to get away from external programs (particularly spreadsheets) that require lots of (error-prone) gui clicking to massage the data before import, and automate the whole thing so I can click one button and pull in *all* of my latest bank statements with some simple programmatic automation with something like Selenium.

Troy Benjegerdes
on 4/9/15, 5:33 PM
2

Fabrice Henrion (fhe)

--Fabrice Henrion (fhe)--
5823
| 7 7 8
San Francisco, United States
--Fabrice Henrion (fhe)--

Director Odoo USA

Fabrice Henrion (fhe)
On 2/7/15, 9:25 PM

[Click on pictures to enlarge]

The correct date format that Odoo accepts is YYYY-MM-DD. The way I know this is because I exported an existing bank statement in "Import-compatible export" in CSV and looked at how it was made. You export in the list view after clicking the checkbox of the records you want to export. Here I exported 1 bank statement that contains 4 lines. The export window looks like below and I exported the bank statement date and the date of each line:

If I open the CSV in Excel, I see this:

Be careful with spreadsheet editors, they tend to display dates differently than how they are stored. This format looks weird, so I decided to open the csv file in a text editor:

You see that the date format is YYYY-MM-DD.

Now, I can reuse this template to re-import a new bank statement in Odoo. It helps to have the correct column headers. Note that I only did this test with a few fields; more fields can be added. I changed the data as follows:

Back in the list view of bank statements of Odoo, I clicked on the Import button and selected my file. All the fields are recognized automatically.

When I validate, a green message indicates everything is correct, and I then click on Import.
My new bank statement appears then:

Note: you don't necessarily have to use a text editor to make a change in the CSV or reformat a date. I did it here to warn about the fact that certain spreadsheet editors display something different than the encoded data. It is still possible to use a spreadsheet editor like LibreOffice, Excel, or Google spreadsheet. You'll have to play with the "Format Cells" feature.

0
Troy Benjegerdes
On 4/9/15, 5:32 PM

I can't comment yet because I don't have enough points, but what I would really like are pointers to the import python code in https://github.com/odoo/odoo, and an estimate on what it would cost to have configurable data format import filters in the SaaS version, and then I can decide if I should just pay for the development or write my own importer.

I'm trying to get away from external programs (particularly spreadsheets) that require lots of (error-prone) gui clicking to massage the data before import, and automate the whole thing so I can click one button and pull in *all* of my latest bank statements with some simple programmatic automation with something like Selenium.

And apparently I can't delete my own 'answer' which should probably be a comment either...

Troy Benjegerdes
on 4/9/15, 5:34 PM

Your Answer

Please try to give a substantial answer. If you wanted to comment on the question or answer, just use the commenting tool. Please remember that you can always revise your answers - no need to answer the same question twice. Also, please don't forget to vote - it really helps to select the best questions and answers!

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines

Question tools

1 follower(s)

Stats

Asked: 2/6/15, 8:52 PM
Seen: 2722 times
Last updated: 4/9/15, 5:33 PM