I got a scenario where I have to read an excel file from the external storage of client on daily basis. That excel file contains at-least one million transactions. I have to create invoices and payments against those transactions on daily basis.

My Plan:

My plan of execution is that I’ll read the file and convert it into a panda data-frame. Make changes in the name of columns so that they can match the technical names of the fields in odoo. After that I’ll convert the data-frame in one list of dictionaries. And create records in my own model in odoo by postgresql create querry to hold all transactions as raw data.

In next step I’ll run a crone job that will convert all those transaction into payments and invoices. i.e Use SQL create query to create invoices and payments.

Questions:

1). When I am reading data from excel should I set some limit. i.e that only 10,000 line should be executed in one go. Like create dataframe of 10,000 excel lines at first and then create records and move to next 10,000 line. I need a suggestion that should I go for a specific number of lines of read the whole file at once.

2). When I am creating the records in odoo. I am planing to use database create query and give list of dictionary as data stream to create records. Is that a right approach? The best way to create bulk records in odoo is through create query or any other way. Pls keep note of a fact that I have millions of transactions in excel file.

3). The approach of creating records as raw data first and then convert them to invoices and payments is a correct approach or should we go for direct creation of invoices and payments. If yes then how? It is depended on transaction type that we need to create a payment or invoice.

4). In case when I need to create invoice then surly I have to do that with create query of postgresql, How I can create and invoice with a query that will create invoice and its lines at the same time and quickly?

5). What would be a quick and reliable method to post bulk invoices and payments?

6). The odoo is hosted on odoo.sh and the version is 16. I am planning to add some workers for this process and will do this process in midnight when the system is not in use. Is there a facility provided by odoo which will allow me to assign a number of workers to a specific piece of code?

That might be possible that I haven’t provided with exact information, if something is missing then pls ask me questions. i’ll replay asap.