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?
[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.
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.
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
|Asked: 2/6/15, 8:52 PM|
|Seen: 2700 times|
|Last updated: 4/9/15, 5:33 PM|