This blog is going to share a simple way to import an XLSX file to Odoo using Python script or code. You might know how to import XLSX files using the default import button in Odoo. However, you might not be sure how the option works then this blog is for you. On the contrary, if you do not know how to import data on Odoo, check out the following blog:
How to import data in OdooLet's discuss XLSX data import via a Python script. For that initially, we first need an XLSX file. Here I am going to import journal entry data to Odoo therefore, let us take an example XLSX file.
journal_entry.xlsx
The columns are defined as follows:
Acc# - Account code.
Communication – Label for journal item line.
Partner – Name of the partner.
Currency – Column for currency id.
Dr – Debit value.
Cr – Credit value.
As of now, we have the XLSX file and now let's see how to import it to Odoo. We need a button or some option to perform a Python function. Here I select a button function therefore, I have created a wizard as shown below
Additionally, I created some fields that are required for the journal entry that is not in the excel file, such as Journal, Reference, and Date. You can also view a field for entering the file name, which is actually used to specify the path of the file that we saved on the system.
The python function is executed when the ‘Import Journal Entry’ button is clicked. The button function ( import_journal_entry ) will be as follows,
import xlrd
xlrd module is used to read data from the excel sheet therefore you must import xlrd in your python file.
def import_journal_entry(self):
try:
book = xlrd.open_workbook(filename=self.file_name)
except FileNotFoundError:
raise UserError('No such file or directory found. \n%s.' % self.file_name)
except xlrd.biffh.XLRDError:
raise UserError('Only excel files are supported.')
for sheet in book.sheets():
try:
line_vals = []
if sheet.name == 'Sheet1':
for row in range(sheet.nrows):
if row >= 1:
row_values = sheet.row_values(row)
vals = self._create_journal_entry(row_values)
line_vals.append((0, 0, vals))
if line_vals:
date = self.date
ref = self.jv_ref
self.env['account.move'].create({
'date': date,
'ref': ref,
'journal_id': self.jv_journal_id.id,
'line_ids': line_vals
})
except IndexError:
pass
book = xlrd.open_workbook(filename=self.file_name) – This opens our excel sheet from your file path for action. Just as we open a book before reading it.
book.sheets() - Started reading the excel sheet.
if sheet.name == 'Sheet1': – It checks the name of the sheet we want to read.
for row in range(sheet.nrows): - Loops up to the number of rows of content.
row_values = sheet.row_values(row) –Reads the values of each row.
The row values are then passed to the _create_journal_entry function, from where we create a dictionary of records to generate journal entries.
def _create_journal_entry(self, record):
code = int(record[0])
account_id = self.env['account.account'].search([('code', '=', code)], limit=1)
if not account_id:
raise UserError(_("There is no account with code %s.") % code)
partner_id = self.env['res.partner'].search([('name', '=', record[2])], limit=1)
if not partner_id:
partner_id = self.env['res.partner'].create({
'name': record[1],
'customer': True,
})
line_ids = {
'account_id': account_id.id,
'partner_id': partner_id.id,
'name': record[1],
'debit': record[4],
'credit': record[5],
}
return line_ids
Here we read row values by using index values. i.e, record[0] is the first column of the excel sheet.
Further we search for the account code in the ‘account.account’ model, if the account does not exist, then raise an error. In the ‘res.partner’ model we are searching for a partner name, if the partner does not exist create a partner in the database.
Now click on the Import Journal Entry button, then navigate to the journal entries menu using the following path Accounting / Invoicing -> Accounting -> Journal Entries. Here, you can view the journal entry you have created.
Furthermore, you can also import products, inventory adjustments, partners, etc. Moreover, you need to change the Python code according to your excel data. Additionally, reading and retrieving data from the excel sheet is an important part of this process which is the same as the starting line of the Python function, and then followed by our code.
This is an example of importing journal entry into Odoo, hope this blog was useful.
Watch Video: