In every organization, data plays a critical role. Therefore, data import, in other words, file importing remains a significant part of every application. The user can import any kind of data in different formats (Xlsx, CSV, etc…) using Odoo ERP. This will help to import details like contacts, products, bank statements, journal entries, sales orders, etc. easily. However, in some cases, we may need to build a new module to import CSV or XLSX files to make importation easier.
Here, I will explain how to read XLSX and CSV files using python. We must know some packages for reading XLSX and CSV files in order to move towards importing.
Packages that use for import XLSX file:
Tempfile: Decors with temporary files and directories. Tempfile works in all supported platforms.
Binance: Comes embedded with a number of methods for converting binary and various ASCII-encoded binary representations.
Xlrd: To aid in formatting information and reading data from Excel.
Packages that used for importing CSV files
Base64: This module encodes your data in a hassle-free way, or decodes it into a human-readable format.
Io: The Io module provides Python’s main facilities for dealing with various types of I/O
CSV: The CSV module implements classes to read and write tabular data in CSV format.
The above described are the packages to read XLSX and CSV file
How to Import Packages?
import xlrd
import tempfile
import binascii
These are some examples to import packages. You can import any other python packages like this. However, note: before the package name uses the keyword import.
Example for import xlsx file:
Here creates a temporary file suffix as .xlsx
import xlrd
import tempfile
import binascii
def import_file(self):
try:
fp = tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx")
fp.write(binascii.a2b_base64(self.file))
fp.seek(0)
vals = {}
workbook = xlrd.open_workbook(fp.name)
sheet = workbook.sheet_by_index(0)
except:
raise Warning(_("File not Valid"))
fp = tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx")
Here creates a temporary file suffix as .xlsx.
fp.write(binascii.a2b_base64(self.file))
This helps to convert data
fp.seek(0)
Here sets the file's current position at the offset.
workbook = xlrd.open_workbook(fp.name)
Open a spreadsheet file for data extraction
sheet = workbook.sheet_by_index(0)
All sheets which not already loaded will be loaded here.
Example For import CSV files:
import base64
import io
import CSV
def import_file(self):
try:
file = base64.b64decode(self.file)
data = io.StringIO(file.decode("utf-8"))
data.seek(0)
file_reader = []
csv_reader = csv.reader(data, delimiter=',')
file_reader.extend(csv_reader)
except:
raise Warning(_("File is not Valid!")file = base64.b64decode(self.file)
Decode a Base64 encoded string. s is the string to decode.
data = io.StringIO(file.decode("utf-8"))Here opening the file as text mod.
data.seek(0)
Here sets the file's current position at the offset.
csv_reader = csv.reader(data, delimiter=',')
Reading a CSV file with an alternate format:
file_reader.extend(csv_reader)
Adding each element to the list and extending the list.
Hope the blog was useful.