def get_transaction_entries(filename, headers):
header_index = {}
rows, transactions = [], []
if (filename.lower().endswith("xlsx")):
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(file_id=filename)
elif (filename.lower().endswith("csv")):
from frappe.utils.file_manager import get_file_path
from frappe.utils.csvutils import read_csv_content
filepath = get_file_path(filename)
with open(filepath,'rb') as csvfile:
rows = read_csv_content(csvfile.read())
elif (filename.lower().endswith("xls")):
rows = get_rows_from_xls_file(filename)
else:
frappe.throw("Only .csv and .xlsx files are supported currently")
stmt_headers = headers.values()
for row in rows:
if len(row) == 0 or row[0] == None or not row[0]: continue
#print("Processing row {0}".format(row))
if header_index:
transaction = get_transaction_info(stmt_headers, header_index, row)
transactions.append(transaction)
elif is_headers_present(stmt_headers, row):
header_index = get_header_index(stmt_headers, row)
return transactions
def generate_data_from_excel(file_doc, extension, as_dict=False):
content = file_doc.get_content()
if extension == "xlsx":
rows = read_xlsx_file_from_attached_file(fcontent=content)
elif extension == "xls":
rows = read_xls_file_from_attached_file(content)
data = []
headers = rows[0]
del rows[0]
for row in rows:
if as_dict:
data.append({
frappe.scrub(header): row[index]
for index, header in enumerate(headers)
})
else:
if not row[1]:
row[1] = row[0]
row[3] = row[2]
data.append(row)
return data
def get_transaction_entries(filename, headers):
header_index = {}
rows, transactions = [], []
if (filename.lower().endswith("xlsx")):
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(file_id=filename)
elif (filename.lower().endswith("csv")):
from frappe.utils.file_manager import get_file_path
from frappe.utils.csvutils import read_csv_content
filepath = get_file_path(filename)
with open(filepath,'rb') as csvfile:
rows = read_csv_content(csvfile.read())
elif (filename.lower().endswith("xls")):
rows = get_rows_from_xls_file(filename)
else:
frappe.throw("Only .csv and .xlsx files are supported currently")
for row in rows:
if len(row) == 0 or row[0] == None or not row[0]: continue
#print("Processing row {0}".format(row))
if header_index:
transaction = get_transaction_info(headers, header_index, row)
transactions.append(transaction)
elif is_headers_present(headers, row):
header_index = get_header_index(headers, row)
return transactions
def setup_warehouses():
system_doc = frappe.get_doc("System Setup")
company = frappe.db.get_single_value('Global Defaults', 'default_company')
abbr = frappe.get_value("Company",
filters={'name': company},
fieldname='abbr')
if system_doc.no_warehouses:
delete_nongroup_warehouse_groups()
if not frappe.db.exists("Warehouse", 'General Warehouse - ' + abbr):
doc = frappe.new_doc("Warehouse")
doc.warehouse_name = 'General Warehouse'
doc.parent_warehouse = frappe.db.sql(
"select name from `tabWarehouse` where is_group = 1 order by creation asc limit 1"
)[0][0]
doc.insert()
elif system_doc.warehouses_attachment:
file = frappe.get_doc("File",
{"file_url": system_doc.warehouses_attachment})
filename = file.get_full_path()
company = frappe.db.get_single_value('Global Defaults',
'default_company')
abbr = frappe.get_value("Company",
filters={'name': company},
fieldname='abbr')
with open(filename, "r", encoding="utf8") as infile:
if frappe.safe_encode(filename).lower().endswith(
"csv".encode('utf-8')):
rows = read_csv_content(infile.read())
elif frappe.safe_encode(filename).lower().endswith(
"xls".encode('utf-8')):
content = file.get_content()
rows = read_xls_file_from_attached_file(fcontent=content)
elif frappe.safe_encode(filename).lower().endswith(
"xlsx".encode('utf-8')):
content = file.get_content()
rows = read_xlsx_file_from_attached_file(fcontent=content)
else:
frappe.throw(
_("Only CSV and Excel files can be used to for importing data. Please check the file format you are trying to upload"
))
delete_nongroup_warehouse_groups()
for index, row in enumerate(rows):
if index != 0:
if not frappe.db.exists("Warehouse",
row[0] + ' - ' + abbr):
doc = frappe.new_doc("Warehouse")
doc.warehouse_name = row[0]
doc.parent_warehouse = frappe.db.sql(
"select name from `tabWarehouse` where is_group = 1 order by creation asc limit 1"
)[0][0]
doc.insert()
def get_transaction_entries(file_url, headers):
header_index = {}
rows, transactions = [], []
if (file_url.lower().endswith("xlsx")):
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(file_url=file_url)
elif (file_url.lower().endswith("csv")):
from frappe.utils.csvutils import read_csv_content
_file = frappe.get_doc("File", {"file_url": file_url})
filepath = _file.get_full_path()
with open(filepath, 'rb') as csvfile:
rows = read_csv_content(csvfile.read())
elif (file_url.lower().endswith("xls")):
filename = file_url.split("/")[-1]
rows = get_rows_from_xls_file(filename)
else:
frappe.throw(_("Only .csv and .xlsx files are supported currently"))
stmt_headers = headers.values()
for row in rows:
if len(row) == 0 or row[0] == None or not row[0]: continue
#print("Processing row {0}".format(row))
if header_index:
transaction = get_transaction_info(stmt_headers, header_index, row)
transactions.append(transaction)
elif is_headers_present(stmt_headers, row):
header_index = get_header_index(stmt_headers, row)
return transactions
def upload_csv_bank_statement():
if frappe.safe_encode(frappe.local.uploaded_filename).lower().endswith(
"csv".encode("utf-8")):
from frappe.utils.csvutils import read_csv_content
rows = read_csv_content(frappe.local.uploaded_file)
elif frappe.safe_encode(frappe.local.uploaded_filename).lower().endswith(
"xlsx".encode("utf-8")):
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(
fcontent=frappe.local.uploaded_file)
elif frappe.safe_encode(frappe.local.uploaded_filename).lower().endswith(
"xls".encode("utf-8")):
from frappe.utils.xlsxutils import read_xls_file_from_attached_file
rows = read_xls_file_from_attached_file(frappe.local.uploaded_file)
else:
frappe.throw(_("Please upload a csv, xls or xlsx file"))
column_row = rows[0]
columns = [{"field": x, "label": x} for x in column_row]
rows.pop(0)
data = []
for row in rows:
data.append(dict(zip(column_row, row)))
return {"columns": columns, "data": data}
def setup_suppliers():
system_doc = frappe.get_doc("System Setup")
company = frappe.db.get_single_value('Global Defaults', 'default_company')
abbr = frappe.get_value("Company",
filters={'name': company},
fieldname='abbr')
if system_doc.no_suppliers:
delete_nongroup_supplier_groups()
insert_suppliers_group(company, "General Group")
if not frappe.db.exists("Supplier", "General Supplier"):
doc = frappe.new_doc("Supplier")
doc.supplier_name = "General Supplier"
doc.supplier_group = "General Group"
doc.supplier_type = "Individual"
doc.insert()
elif system_doc.suppliers_attachment:
file = frappe.get_doc("File",
{"file_url": system_doc.suppliers_attachment})
filename = file.get_full_path()
with open(filename, "r", encoding="utf8") as infile:
if frappe.safe_encode(filename).lower().endswith(
"csv".encode('utf-8')):
rows = read_csv_content(infile.read())
elif frappe.safe_encode(filename).lower().endswith(
"xls".encode('utf-8')):
content = file.get_content()
rows = read_xls_file_from_attached_file(fcontent=content)
elif frappe.safe_encode(filename).lower().endswith(
"xlsx".encode('utf-8')):
content = file.get_content()
rows = read_xlsx_file_from_attached_file(fcontent=content)
else:
frappe.throw(
_("Only CSV and Excel files can be used to for importing data. Please check the file format you are trying to upload"
))
delete_nongroup_supplier_groups()
for index, row in enumerate(rows):
if index != 0:
insert_suppliers_group(company, row[1])
if not frappe.db.exists("Supplier", row[0]):
doc = frappe.new_doc("Supplier")
doc.supplier_name = row[0]
doc.supplier_group = row[1]
if row[2].lower() == "individual":
doc.supplier_type = "Individual"
elif row[20].lower() == "company":
doc.supplier_type = "Company"
else:
frappe.throw(
_("Supplier Type column values must be Company or Individual"
))
doc.insert()
def test_excel_import(self):
if frappe.db.exists("Event", "EV00001"):
frappe.delete_doc("Event", "EV00001")
exporter.get_template("Event", all_doctypes="No", with_data="No", from_data_import="Yes", excel_format="Yes")
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
content = read_xlsx_file_from_attached_file(fcontent=frappe.response.filecontent)
content.append(["", "EV00001", "_test", "Private", "05-11-2017 13:51:48", "0", "0", "", "1", "blue"])
importer.upload(content)
self.assertTrue(frappe.db.get_value("Event", "EV00001", "subject"), "_test")
def test_excel_import(self):
if frappe.db.exists("Event", "EV00001"):
frappe.delete_doc("Event", "EV00001")
exporter.export_data("Event", all_doctypes=True, template=True, file_type="Excel")
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
content = read_xlsx_file_from_attached_file(fcontent=frappe.response.filecontent)
content.append(["", "_test", "Private", "05-11-2017 13:51:48", "Event", "0", "0", "", "1", "", "", 0, 0, 0, 0, 0, 0, 0, "blue"])
importer.upload(content)
self.assertTrue(frappe.db.get_value("Event", {"subject": "_test"}, "name"))
def read_content(self, content, extension): if extension == "csv": data = read_csv_content(content) elif extension == "xlsx": data = read_xlsx_file_from_attached_file(fcontent=content) elif extension == "xls": data = read_xls_file_from_attached_file(content) self.header_row = data[0] self.data = data[1:]
def test_excel_import(self):
if frappe.db.exists("Event", "EV00001"):
frappe.delete_doc("Event", "EV00001")
exporter.get_template("Event", all_doctypes="No", with_data="No", from_data_import="Yes", excel_format="Yes")
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
content = read_xlsx_file_from_attached_file(fcontent=frappe.response.filecontent)
content.append(["", "EV00001", "_test", "Private", "05-11-2017 13:51:48", "0", "0", "", "1", "blue"])
importer.upload(content)
self.assertTrue(frappe.db.get_value("Event", "EV00001", "subject"), "_test")
def readfile(file_url, data_format, fcontent=None, filepath=None):
if data_format == "XLSX":
ext_rows = read_xlsx_file_from_attached_file(file_url, fcontent,
filepath)
else:
file_att = frappe.get_doc("File", {"file_url": file_url})
filename = file_att.get_full_path()
if data_format == "CSV":
with open(filename, "r") as infile:
ext_rows = read_csv_content(infile.read())
if data_format == "JSON":
with open(filename, 'r') as infile:
try:
aa = str(infile.read())
aa = aa.replace("[", "").replace("]",
"").replace(",{",
"#{").split("#")
ext_rows = []
ext_rows.append([])
j = 1
while j <= len(aa):
bb = ast.literal_eval(aa[j - 1])
ext_rows.append([])
for x in bb.values():
ext_rows[j].append(str(x))
j += 1
bb = ast.literal_eval(aa[0])
for x, y in bb.items():
ext_rows[0].append(str(x))
except ValueError:
print("bad json: {0}".format(file_url))
raise
if data_format == "XML":
with open(filename, 'r') as infile:
try:
tree = ET.parse(infile)
root = tree.getroot()
ext_rows = []
ext_rows.append([])
for child in root[0]:
ext_rows[0].append(str(child.tag))
i = 0
for child in root:
ext_rows.append([])
i += 1
for subchild in child:
ext_rows[i].append(str(subchild.text))
except ValueError:
print("bad xml: {0}".format(file_url))
raise
if ext_rows:
if not isinstance(ext_rows, list):
ext_rows = [ext_rows]
return ext_rows
def parseXLS(self):
file_url = self.get_full_path(
) # file attachment only the first one attached
fname = os.path.basename(file_url)
fxlsx = re.search("^{}.*\.xlsx".format(self.doctype), fname)
if (fxlsx): # match
with open(file_url, "rb") as upfile:
fcontent = upfile.read()
if frappe.safe_encode(fname).lower().endswith(
"xlsx".encode('utf-8')):
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(fcontent=fcontent)
columns = rows[0]
rows.pop(0)
data = rows
try:
for row in rows:
name = row[0]
full_name = row[1]
isid = row[2]
dm_id = row[3]
if frappe.db.exists('MR', name):
doc = frappe.get_doc('MR', name)
doc.full_name = full_name
doc.berno_id = isid
doc.dm_id = dm_id
doc.save()
else:
doc = frappe.get_doc({
"doctype": "MR",
"name": name,
"full_name": full_name,
"email": name.lower() + "@ksp.ksp",
"berno_id": isid,
"dm_id": dm_id
}).insert()
frappe.db.commit()
frappe.msgprint("Done")
except:
frappe.db.rollback()
frappe.msgprint("Error has occurred")
return {"columns": columns, "data": data}
else:
return {
"status": "Error",
"filename": fname,
"doctype": self.doctype
}
def setup_customers(self):
company = frappe.db.get_single_value('Global Defaults', 'default_company')
abbr = frappe.get_value("Company", filters = {'name': company}, fieldname = 'abbr')
if self.no_customers:
self.delete_nongroup_customer_groups()
self.delete_nongroup_territories()
self.insert_customers_group(company, "General Group")
self.insert_territories("General Territory")
if not frappe.db.exists("Customer", "General Customer"):
doc = frappe.new_doc("Customer")
doc.customer_name = "General Customer"
doc.customer_group = "General Group"
doc.territory = "General Territory"
doc.insert()
elif self.customers_attachment:
file = frappe.get_doc("File", {"file_url": self.customers_attachment})
filename = file.get_full_path()
with open(filename, "r", encoding = "utf8") as infile:
if frappe.safe_encode(filename).lower().endswith("csv".encode('utf-8')):
rows = read_csv_content(infile.read())
elif frappe.safe_encode(filename).lower().endswith("xls".encode('utf-8')):
content = file.get_content()
rows = read_xls_file_from_attached_file(fcontent=content)
elif frappe.safe_encode(filename).lower().endswith("xlsx".encode('utf-8')):
content = file.get_content()
rows = read_xlsx_file_from_attached_file(fcontent=content)
else:
frappe.throw(_("Only CSV and Excel files can be used to for importing data. Please check the file format you are trying to upload"))
self.delete_nongroup_customer_groups()
self.delete_nongroup_territories()
for index, row in enumerate(rows):
if index != 0:
self.insert_customers_group(company, row[1])
self.insert_territories(row[2])
if not frappe.db.exists("Customer", row[0]):
doc = frappe.new_doc("Customer")
doc.customer_name = row[0]
doc.customer_group = row[1]
doc.territory = row[2]
doc.insert()
else:
frappe.throw(_("Please attach a file"))
def _get_data(file_url):
get_header = excepts(StopIteration, first, lambda _: [])
get_rows = compose(list, partial(drop, 1))
if not file_url:
return [], []
file = frappe.get_doc("File", {"file_url": file_url})
filename, file_extension = file.get_extension()
if file_extension == ".xlsx":
data = read_xlsx_file_from_attached_file(file_url=file_url)
return get_header(data), get_rows(data)
if file_extension == ".csv":
data = read_csv_content(file.get_content())
return get_header(data), get_rows(data)
frappe.throw(frappe._("Unsupported File Format"))
def read_content(self, content, extension):
error_title = _("Template Error")
if extension not in ("csv", "xlsx", "xls"):
frappe.throw(
_("Import template should be of type .csv, .xlsx or .xls"),
title=error_title)
if extension == "csv":
data = read_csv_content(content)
elif extension == "xlsx":
data = read_xlsx_file_from_attached_file(fcontent=content)
elif extension == "xls":
data = read_xls_file_from_attached_file(content)
return data
def parseXLS(self):
file_url = self.get_full_path(
) # file attachment only the first one attached
fname = os.path.basename(file_url)
fxlsx = re.search("^{}.*\.xlsx".format(self.doctype), fname)
if (fxlsx): # match
with open(file_url, "rb") as upfile:
fcontent = upfile.read()
if frappe.safe_encode(fname).lower().endswith(
"xlsx".encode('utf-8')):
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(fcontent=fcontent)
columns = rows[0]
rows.pop(0)
data = rows
return {"columns": columns, "data": data}
else:
return {"status": "Error", "filename": fname}
def upload_bank_statement():
if getattr(frappe, "uploaded_file", None):
with open(frappe.uploaded_file, "rb") as upfile:
fcontent = upfile.read()
else:
from frappe.utils.file_manager import get_uploaded_content
fname, fcontent = get_uploaded_content()
if frappe.safe_encode(fname).lower().endswith("csv"):
from frappe.utils.csvutils import read_csv_content
rows = read_csv_content(fcontent, False)
elif frappe.safe_encode(fname).lower().endswith("xlsx"):
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(fcontent=fcontent)
columns = rows[0]
rows.pop(0)
data = rows
return {"columns": columns, "data": data}
def upload_bank_statement():
if getattr(frappe, "uploaded_file", None):
with open(frappe.uploaded_file, "rb") as upfile:
fcontent = upfile.read()
else:
from frappe.utils.file_manager import get_uploaded_content
fname, fcontent = get_uploaded_content()
if frappe.safe_encode(fname).lower().endswith("csv".encode('utf-8')):
from frappe.utils.csvutils import read_csv_content
rows = read_csv_content(fcontent, False)
elif frappe.safe_encode(fname).lower().endswith("xlsx".encode('utf-8')):
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(fcontent=fcontent)
columns = rows[0]
rows.pop(0)
data = rows
return {"columns": columns, "data": data}
def read_content(self, content, extension):
error_title = _("Template Error")
if extension not in ("csv", "xlsx", "xls"):
frappe.throw(
_("Import template should be of type .csv, .xlsx or .xls"), title=error_title
)
if extension == "csv":
data = read_csv_content(content)
elif extension == "xlsx":
data = read_xlsx_file_from_attached_file(fcontent=content)
elif extension == "xls":
data = read_xls_file_from_attached_file(content)
if len(data) <= 1:
frappe.throw(
_("Import template should contain a Header and atleast one row."), title=error_title
)
self.header_row = data[0]
self.data = data[1:]
def parseXLS(self):
file_url = self.get_full_path() # file attachment only the first one attached
fname = os.path.basename(file_url)
fxlsx = re.search("^{}.*\.xlsx".format("Dx"), fname)
if(fxlsx): # match
with open( file_url , "rb") as upfile:
fcontent = upfile.read()
if frappe.safe_encode(fname).lower().endswith("xlsx".encode('utf-8')):
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(fcontent=fcontent)
columns = rows[0]
rows.pop(0)
data = rows
res = check_dx_list(self.name, rows)
if res:
columns[0] = '<span style="color:red">Error ID Not Found</span>'
return {"columns": columns, "data": res, "filename": self.filename}
frappe.enqueue(import_loan, name=self.name, rows=rows, now=True if len(rows) < 200 else False)
return {"columns": columns, "data": data, "filename": self.filename}
else:
return {"status" : "Error", "filename": fname}
def setup_items():
system_doc = frappe.get_doc("System Setup")
company = frappe.db.get_single_value('Global Defaults', 'default_company')
abbr = frappe.get_value("Company",
filters={'name': company},
fieldname='abbr')
if system_doc.no_items:
delete_nongroup_item_groups()
#delete items also
insert_items_group(company, "General Group")
if not frappe.db.exists("Item", {"item_code": "General Stock"}):
i_doc = frappe.new_doc("Item")
i_doc.item_code = "General Stock"
i_doc.description = "General Stock"
i_doc.is_stock_item = 1
i_doc.include_item_in_manufacturing = 0
i_doc.item_group = "General Group"
i_doc.insert()
if not frappe.db.exists("Item", {"item_code": "General Service"}):
i_doc = frappe.new_doc("Item")
i_doc.item_code = "General Service"
i_doc.description = "General Service"
i_doc.is_stock_item = 0
i_doc.include_item_in_manufacturing = 0
i_doc.item_group = "General Group"
i_doc.insert()
elif system_doc.items_attachment:
file = frappe.get_doc("File",
{"file_url": system_doc.items_attachment})
filename = file.get_full_path()
with open(filename, "r", encoding="utf8") as infile:
if frappe.safe_encode(filename).lower().endswith(
"csv".encode('utf-8')):
rows = read_csv_content(infile.read())
elif frappe.safe_encode(filename).lower().endswith(
"xls".encode('utf-8')):
content = file.get_content()
rows = read_xls_file_from_attached_file(fcontent=content)
elif frappe.safe_encode(filename).lower().endswith(
"xlsx".encode('utf-8')):
content = file.get_content()
rows = read_xlsx_file_from_attached_file(fcontent=content)
else:
frappe.throw(
_("Only CSV and Excel files can be used to for importing data. Please check the file format you are trying to upload"
))
delete_nongroup_item_groups()
for index, row in enumerate(rows):
if index != 0:
insert_items_group(company, row[3])
if not frappe.db.exists("Item", {"item_code": row[0]}):
i_doc = frappe.new_doc("Item")
i_doc.item_code = row[0]
i_doc.description = row[1]
if row[2].lower() == "service":
i_doc.is_stock_item = 0
elif row[2].lower() == "stock":
i_doc.is_stock_item = 1
else:
frappe.throw(
_("Item Type must be Service or Stock"))
i_doc.include_item_in_manufacturing = 0
i_doc.item_group = row[3]
i_doc.insert()
def upload(rows = None, submit_after_import=None, ignore_encoding_errors=False, no_email=True, overwrite=None,
update_only = None, ignore_links=False, pre_process=None, via_console=False, from_data_import="No",
skip_errors = True):
"""upload data"""
frappe.flags.in_import = True
# extra input params
params = json.loads(frappe.form_dict.get("params") or '{}')
if params.get("submit_after_import"):
submit_after_import = True
if params.get("ignore_encoding_errors"):
ignore_encoding_errors = True
if not params.get("no_email"):
no_email = False
if params.get('update_only'):
update_only = True
if params.get('from_data_import'):
from_data_import = params.get('from_data_import')
if not params.get('skip_errors'):
skip_errors = params.get('skip_errors')
frappe.flags.mute_emails = no_email
def get_data_keys_definition():
return get_data_keys()
def bad_template():
frappe.throw(_("Please do not change the rows above {0}").format(get_data_keys_definition().data_separator))
def check_data_length():
max_rows = 5000
if not data:
frappe.throw(_("No data found"))
elif not via_console and len(data) > max_rows:
frappe.throw(_("Only allowed {0} rows in one import").format(max_rows))
def get_start_row():
for i, row in enumerate(rows):
if row and row[0]==get_data_keys_definition().data_separator:
return i+1
bad_template()
def get_header_row(key):
return get_header_row_and_idx(key)[0]
def get_header_row_and_idx(key):
for i, row in enumerate(header):
if row and row[0]==key:
return row, i
return [], -1
def filter_empty_columns(columns):
empty_cols = list(filter(lambda x: x in ("", None), columns))
if empty_cols:
if columns[-1*len(empty_cols):] == empty_cols:
# filter empty columns if they exist at the end
columns = columns[:-1*len(empty_cols)]
else:
frappe.msgprint(_("Please make sure that there are no empty columns in the file."),
raise_exception=1)
return columns
def make_column_map():
doctype_row, row_idx = get_header_row_and_idx(get_data_keys_definition().doctype)
if row_idx == -1: # old style
return
dt = None
for i, d in enumerate(doctype_row[1:]):
if d not in ("~", "-"):
if d and doctype_row[i] in (None, '' ,'~', '-', 'DocType:'):
dt, parentfield = d, None
# xls format truncates the row, so it may not have more columns
if len(doctype_row) > i+2:
parentfield = doctype_row[i+2]
doctypes.append((dt, parentfield))
column_idx_to_fieldname[(dt, parentfield)] = {}
column_idx_to_fieldtype[(dt, parentfield)] = {}
if dt:
column_idx_to_fieldname[(dt, parentfield)][i+1] = rows[row_idx + 2][i+1]
column_idx_to_fieldtype[(dt, parentfield)][i+1] = rows[row_idx + 4][i+1]
def get_doc(start_idx):
if doctypes:
doc = {}
for idx in range(start_idx, len(rows)):
if (not doc) or main_doc_empty(rows[idx]):
for dt, parentfield in doctypes:
d = {}
for column_idx in column_idx_to_fieldname[(dt, parentfield)]:
try:
fieldname = column_idx_to_fieldname[(dt, parentfield)][column_idx]
fieldtype = column_idx_to_fieldtype[(dt, parentfield)][column_idx]
d[fieldname] = rows[idx][column_idx]
if fieldtype in ("Int", "Check"):
d[fieldname] = cint(d[fieldname])
elif fieldtype in ("Float", "Currency", "Percent"):
d[fieldname] = flt(d[fieldname])
elif fieldtype == "Date":
if d[fieldname] and isinstance(d[fieldname], string_types):
d[fieldname] = getdate(parse_date(d[fieldname]))
elif fieldtype == "Datetime":
if d[fieldname]:
if " " in d[fieldname]:
_date, _time = d[fieldname].split()
else:
_date, _time = d[fieldname], '00:00:00'
_date = parse_date(d[fieldname])
d[fieldname] = get_datetime(_date + " " + _time)
else:
d[fieldname] = None
elif fieldtype in ("Image", "Attach Image", "Attach"):
# added file to attachments list
attachments.append(d[fieldname])
elif fieldtype in ("Link", "Dynamic Link") and d[fieldname]:
# as fields can be saved in the number format(long type) in data import template
d[fieldname] = cstr(d[fieldname])
except IndexError:
pass
# scrub quotes from name and modified
if d.get("name") and d["name"].startswith('"'):
d["name"] = d["name"][1:-1]
if sum([0 if not val else 1 for val in d.values()]):
d['doctype'] = dt
if dt == doctype:
doc.update(d)
else:
if not overwrite:
d['parent'] = doc["name"]
d['parenttype'] = doctype
d['parentfield'] = parentfield
doc.setdefault(d['parentfield'], []).append(d)
else:
break
return doc
else:
doc = frappe._dict(zip(columns, rows[start_idx][1:]))
doc['doctype'] = doctype
return doc
def main_doc_empty(row):
return not (row and ((len(row) > 1 and row[1]) or (len(row) > 2 and row[2])))
users = frappe.db.sql_list("select name from tabUser")
def prepare_for_insert(doc):
# don't block data import if user is not set
# migrating from another system
if not doc.owner in users:
doc.owner = frappe.session.user
if not doc.modified_by in users:
doc.modified_by = frappe.session.user
def is_valid_url(url):
is_valid = False
if url.startswith("/files") or url.startswith("/private/files"):
url = get_url(url)
try:
r = requests.get(url)
is_valid = True if r.status_code == 200 else False
except Exception:
pass
return is_valid
def attach_file_to_doc(doctype, docname, file_url):
# check if attachment is already available
# check if the attachement link is relative or not
if not file_url:
return
if not is_valid_url(file_url):
return
files = frappe.db.sql("""Select name from `tabFile` where attached_to_doctype='{doctype}' and
attached_to_name='{docname}' and (file_url='{file_url}' or thumbnail_url='{file_url}')""".format(
doctype=doctype,
docname=docname,
file_url=file_url
))
if files:
# file is already attached
return
save_url(file_url, None, doctype, docname, "Home/Attachments", 0)
# header
if not rows:
from frappe.utils.file_manager import get_file_doc
file_doc = get_file_doc(dt='', dn="Data Import", folder='Home', is_private=1)
filename, file_extension = os.path.splitext(file_doc.file_name)
if file_extension == '.xlsx' and from_data_import == 'Yes':
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(file_id=file_doc.name)
elif file_extension == '.csv':
from frappe.utils.file_manager import get_file
from frappe.utils.csvutils import read_csv_content
fname, fcontent = get_file(file_doc.name)
rows = read_csv_content(fcontent, ignore_encoding_errors)
else:
frappe.throw(_("Unsupported File Format"))
start_row = get_start_row()
header = rows[:start_row]
data = rows[start_row:]
doctype = get_header_row(get_data_keys_definition().main_table)[1]
columns = filter_empty_columns(get_header_row(get_data_keys_definition().columns)[1:])
doctypes = []
column_idx_to_fieldname = {}
column_idx_to_fieldtype = {}
attachments = []
if submit_after_import and not cint(frappe.db.get_value("DocType",
doctype, "is_submittable")):
submit_after_import = False
parenttype = get_header_row(get_data_keys_definition().parent_table)
if len(parenttype) > 1:
parenttype = parenttype[1]
# check permissions
if not frappe.permissions.can_import(parenttype or doctype):
frappe.flags.mute_emails = False
return {"messages": [_("Not allowed to Import") + ": " + _(doctype)], "error": True}
# allow limit rows to be uploaded
check_data_length()
make_column_map()
if overwrite==None:
overwrite = params.get('overwrite')
# delete child rows (if parenttype)
parentfield = None
if parenttype:
parentfield = get_parent_field(doctype, parenttype)
if overwrite:
delete_child_rows(data, doctype)
ret = []
def log(msg):
if via_console:
print(msg.encode('utf-8'))
else:
ret.append(msg)
def as_link(doctype, name):
if via_console:
return "{0}: {1}".format(doctype, name)
else:
return getlink(doctype, name)
error = False
total = len(data)
for i, row in enumerate(data):
# bypass empty rows
if main_doc_empty(row):
continue
row_idx = i + start_row
doc = None
# publish task_update
frappe.publish_realtime("data_import_progress", {"progress": [i, total]},
user=frappe.session.user)
try:
doc = get_doc(row_idx)
if pre_process:
pre_process(doc)
if parentfield:
parent = frappe.get_doc(parenttype, doc["parent"])
doc = parent.append(parentfield, doc)
parent.save()
log('Inserted row for %s at #%s' % (as_link(parenttype,
doc.parent),text_type(doc.idx)))
else:
if overwrite and doc["name"] and frappe.db.exists(doctype, doc["name"]):
original = frappe.get_doc(doctype, doc["name"])
original_name = original.name
original.update(doc)
# preserve original name for case sensitivity
original.name = original_name
original.flags.ignore_links = ignore_links
original.save()
log('Updated row (#%d) %s' % (row_idx + 1, as_link(original.doctype, original.name)))
doc = original
else:
if not update_only:
doc = frappe.get_doc(doc)
prepare_for_insert(doc)
doc.flags.ignore_links = ignore_links
doc.insert()
log('Inserted row (#%d) %s' % (row_idx + 1, as_link(doc.doctype, doc.name)))
else:
log('Ignored row (#%d) %s' % (row_idx + 1, row[1]))
if attachments:
# check file url and create a File document
for file_url in attachments:
attach_file_to_doc(doc.doctype, doc.name, file_url)
if submit_after_import:
doc.submit()
log('Submitted row (#%d) %s' % (row_idx + 1, as_link(doc.doctype, doc.name)))
except Exception as e:
if not skip_errors:
error = True
if doc:
frappe.errprint(doc if isinstance(doc, dict) else doc.as_dict())
err_msg = frappe.local.message_log and "\n\n".join(frappe.local.message_log) or cstr(e)
log('Error for row (#%d) %s : %s' % (row_idx + 1,
len(row)>1 and row[1] or "", err_msg))
frappe.errprint(frappe.get_traceback())
finally:
frappe.local.message_log = []
if error:
frappe.db.rollback()
else:
frappe.db.commit()
frappe.flags.mute_emails = False
frappe.flags.in_import = False
return {"messages": ret, "error": error}
def on_submit(self): rows = read_xlsx_file_from_attached_file(file_id=self.import_file) entry = _create_entry(rows, self.posting_date) _set_bulk_timesheet(self.name, entry) frappe.db.commit()
if files:
# file is already attached
return
save_url(file_url, None, doctype, docname, "Home/Attachments", 0)
# header
if not rows:
from frappe.utils.file_manager import save_uploaded
file_doc = save_uploaded(dt=None, dn="Data Import", folder='Home', is_private=1)
filename, file_extension = os.path.splitext(file_doc.file_name)
if file_extension == '.xlsx' and from_data_import == 'Yes':
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(file_id=file_doc.name)
elif file_extension == '.csv':
from frappe.utils.file_manager import get_file
from frappe.utils.csvutils import read_csv_content
fname, fcontent = get_file(file_doc.name)
rows = read_csv_content(fcontent, ignore_encoding_errors)
else:
frappe.throw(_("Unsupported File Format"))
start_row = get_start_row()
header = rows[:start_row]
data = rows[start_row:]
doctype = get_header_row(get_data_keys_definition().main_table)[1]
columns = filter_empty_columns(get_header_row(get_data_keys_definition().columns)[1:])
def setup_employees_and_users():
system_doc = frappe.get_doc("System Setup")
if system_doc.employees_attachment:
file = frappe.get_doc("File",
{"file_url": system_doc.employees_attachment})
filename = file.get_full_path()
company = frappe.db.get_single_value('Global Defaults',
'default_company')
abbr = frappe.get_value("Company",
filters={'name': company},
fieldname='abbr')
with open(filename, "r", encoding="utf8") as infile:
if frappe.safe_encode(filename).lower().endswith(
"csv".encode('utf-8')):
rows = read_csv_content(infile.read())
elif frappe.safe_encode(filename).lower().endswith(
"xls".encode('utf-8')):
content = file.get_content()
rows = read_xls_file_from_attached_file(fcontent=content)
elif frappe.safe_encode(filename).lower().endswith(
"xlsx".encode('utf-8')):
content = file.get_content()
rows = read_xlsx_file_from_attached_file(fcontent=content)
else:
frappe.throw(
_("Only CSV and Excel files can be used to for importing data. Please check the file format you are trying to upload"
))
delete_nongroup_departments()
delete_employment_types()
delete_genders()
delete_designations()
delete_leave_types()
delete_salary_components()
insert_salary_components()
insert_leave_types(system_doc.annual_leave_type)
insert_leave_policy()
for index, row in enumerate(rows):
if index != 0:
if not row[0]:
frappe.throw(_("Employee Number column is mandatory"))
if not row[1]:
frappe.throw(_("Password column is mandatory"))
if not row[2]:
frappe.throw(_("First Name column is mandatory"))
user_email = row[0] + '@' + company + '.com'
insert_user(user_email, row[2], row[1], last_name=row[3])
if row[9]:
insert_department(company, abbr, row[9])
else:
frappe.throw(_("Department column is mandatory"))
if row[10]:
insert_designation(row[10])
else:
frappe.throw(_("Designation column is mandatory"))
if row[10]:
insert_employment_type(row[8])
if row[4]:
insert_gender(row[4])
if not row[4]:
frappe.throw(_("Gender column is mandatory"))
if not row[6]:
frappe.throw(_("Date of Birth column is mandatory"))
if not row[7]:
frappe.throw(_("Date of Joining column is mandatory"))
set_employee_as_employee_number()
if not frappe.db.exists("Employee", row[0]):
doc = frappe.new_doc("Employee")
doc.employee_number = row[0]
doc.first_name = row[2]
doc.last_name = row[3]
doc.company = company
doc.gender = row[4]
doc.country = row[5]
doc.date_of_birth = row[6]
doc.date_of_joining = row[7]
doc.employment_type = row[8]
doc.department = row[9] + ' - ' + abbr
doc.designation = row[10]
doc.cell_number = row[11]
doc.user_id = row[0] + '@' + company + '.com'
doc.leave_policy = frappe.db.sql(
"select name from `tabLeave Policy` order by creation desc limit 1"
)[0][0]
doc.insert(ignore_permissions=True)
frappe.db.commit()
allocate_leaves(system_doc.annual_leave_type, row[0],
row[18])
if not row[12]:
frappe.throw(_("Basic Salary column is mandatory"))
else:
basic = row[12]
doj = row[7]
transportation = row[13]
housing = row[14]
mobile = row[15]
others = row[16]
gosi = row[17]
if not transportation:
transportation = 0
if not housing:
housing = 0
if not mobile:
mobile = 0
if not others:
others = 0
if not gosi:
gosi = "No"
insert_allowances(row[0], basic, doj, transportation,
housing, mobile, others, gosi)
def upload(rows=None,
submit_after_import=None,
ignore_encoding_errors=False,
no_email=True,
overwrite=None,
update_only=None,
ignore_links=False,
pre_process=None,
via_console=False,
from_data_import="No"):
"""upload data"""
frappe.flags.in_import = True
# extra input params
params = json.loads(frappe.form_dict.get("params") or '{}')
if params.get("submit_after_import"):
submit_after_import = True
if params.get("ignore_encoding_errors"):
ignore_encoding_errors = True
if not params.get("no_email"):
no_email = False
if params.get('update_only'):
update_only = True
if params.get('from_data_import'):
from_data_import = params.get('from_data_import')
frappe.flags.mute_emails = no_email
def get_data_keys_definition():
return get_data_keys()
def bad_template():
frappe.throw(
_("Please do not change the rows above {0}").format(
get_data_keys_definition().data_separator))
def check_data_length():
max_rows = 5000
if not data:
frappe.throw(_("No data found"))
elif not via_console and len(data) > max_rows:
frappe.throw(
_("Only allowed {0} rows in one import").format(max_rows))
def get_start_row():
for i, row in enumerate(rows):
if row and row[0] == get_data_keys_definition().data_separator:
return i + 1
bad_template()
def get_header_row(key):
return get_header_row_and_idx(key)[0]
def get_header_row_and_idx(key):
for i, row in enumerate(header):
if row and row[0] == key:
return row, i
return [], -1
def filter_empty_columns(columns):
empty_cols = filter(lambda x: x in ("", None), columns)
if empty_cols:
if columns[-1 * len(empty_cols):] == empty_cols:
# filter empty columns if they exist at the end
columns = columns[:-1 * len(empty_cols)]
else:
frappe.msgprint(_(
"Please make sure that there are no empty columns in the file."
),
raise_exception=1)
return columns
def make_column_map():
doctype_row, row_idx = get_header_row_and_idx(
get_data_keys_definition().doctype)
if row_idx == -1: # old style
return
dt = None
for i, d in enumerate(doctype_row[1:]):
if d not in ("~", "-"):
if d and doctype_row[i] in (None, '', '~', '-', 'DocType:'):
dt, parentfield = d, doctype_row[i + 2] or None
doctypes.append((dt, parentfield))
column_idx_to_fieldname[(dt, parentfield)] = {}
column_idx_to_fieldtype[(dt, parentfield)] = {}
if dt:
column_idx_to_fieldname[(dt,
parentfield)][i +
1] = rows[row_idx +
2][i + 1]
column_idx_to_fieldtype[(dt,
parentfield)][i +
1] = rows[row_idx +
4][i + 1]
def get_doc(start_idx):
if doctypes:
doc = {}
for idx in range(start_idx, len(rows)):
if (not doc) or main_doc_empty(rows[idx]):
for dt, parentfield in doctypes:
d = {}
for column_idx in column_idx_to_fieldname[(
dt, parentfield)]:
try:
fieldname = column_idx_to_fieldname[(
dt, parentfield)][column_idx]
fieldtype = column_idx_to_fieldtype[(
dt, parentfield)][column_idx]
d[fieldname] = rows[idx][column_idx]
if fieldtype in ("Int", "Check"):
d[fieldname] = cint(d[fieldname])
elif fieldtype in ("Float", "Currency",
"Percent"):
d[fieldname] = flt(d[fieldname])
elif fieldtype == "Date":
d[fieldname] = getdate(
parse_date(d[fieldname])
) if d[fieldname] else None
elif fieldtype == "Datetime":
if d[fieldname]:
if " " in d[fieldname]:
_date, _time = d[fieldname].split()
else:
_date, _time = d[
fieldname], '00:00:00'
_date = parse_date(d[fieldname])
d[fieldname] = get_datetime(_date +
" " +
_time)
else:
d[fieldname] = None
elif fieldtype in ("Image", "Attach Image",
"Attach"):
# added file to attachments list
attachments.append(d[fieldname])
except IndexError:
pass
# scrub quotes from name and modified
if d.get("name") and d["name"].startswith('"'):
d["name"] = d["name"][1:-1]
if sum([0 if not val else 1 for val in d.values()]):
d['doctype'] = dt
if dt == doctype:
doc.update(d)
else:
if not overwrite:
d['parent'] = doc["name"]
d['parenttype'] = doctype
d['parentfield'] = parentfield
doc.setdefault(d['parentfield'], []).append(d)
else:
break
return doc
else:
doc = frappe._dict(zip(columns, rows[start_idx][1:]))
doc['doctype'] = doctype
return doc
def main_doc_empty(row):
return not (row and ((len(row) > 1 and row[1]) or
(len(row) > 2 and row[2])))
users = frappe.db.sql_list("select name from tabUser")
def prepare_for_insert(doc):
# don't block data import if user is not set
# migrating from another system
if not doc.owner in users:
doc.owner = frappe.session.user
if not doc.modified_by in users:
doc.modified_by = frappe.session.user
def is_valid_url(url):
is_valid = False
if url.startswith("/files") or url.startswith("/private/files"):
url = get_url(url)
try:
r = requests.get(url)
is_valid = True if r.status_code == 200 else False
except Exception:
pass
return is_valid
def attach_file_to_doc(doctype, docname, file_url):
# check if attachment is already available
# check if the attachement link is relative or not
if not file_url:
return
if not is_valid_url(file_url):
return
files = frappe.db.sql(
"""Select name from `tabFile` where attached_to_doctype='{doctype}' and
attached_to_name='{docname}' and (file_url='{file_url}' or thumbnail_url='{file_url}')"""
.format(doctype=doctype, docname=docname, file_url=file_url))
if files:
# file is already attached
return
file = save_url(file_url, None, doctype, docname, "Home/Attachments",
0)
# header
if not rows:
from frappe.utils.file_manager import save_uploaded
file_doc = save_uploaded(dt=None,
dn="Data Import",
folder='Home',
is_private=1)
filename, file_extension = os.path.splitext(file_doc.file_name)
if file_extension == '.xlsx' and from_data_import == 'Yes':
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(file_id=file_doc.name)
elif file_extension == '.csv':
from frappe.utils.file_manager import get_file
from frappe.utils.csvutils import read_csv_content
fname, fcontent = get_file(file_doc.names)
rows = read_csv_content(fcontent, ignore_encoding_errors)
else:
frappe.throw(_("Unsupported File Format"))
start_row = get_start_row()
header = rows[:start_row]
data = rows[start_row:]
doctype = get_header_row(get_data_keys_definition().main_table)[1]
columns = filter_empty_columns(
get_header_row(get_data_keys_definition().columns)[1:])
doctypes = []
column_idx_to_fieldname = {}
column_idx_to_fieldtype = {}
attachments = []
if submit_after_import and not cint(
frappe.db.get_value("DocType", doctype, "is_submittable")):
submit_after_import = False
parenttype = get_header_row(get_data_keys_definition().parent_table)
if len(parenttype) > 1:
parenttype = parenttype[1]
# check permissions
if not frappe.permissions.can_import(parenttype or doctype):
frappe.flags.mute_emails = False
return {
"messages": [_("Not allowed to Import") + ": " + _(doctype)],
"error": True
}
# allow limit rows to be uploaded
check_data_length()
make_column_map()
if overwrite == None:
overwrite = params.get('overwrite')
# delete child rows (if parenttype)
parentfield = None
if parenttype:
parentfield = get_parent_field(doctype, parenttype)
if overwrite:
delete_child_rows(data, doctype)
ret = []
def log(msg):
if via_console:
print(msg.encode('utf-8'))
else:
ret.append(msg)
def as_link(doctype, name):
if via_console:
return "{0}: {1}".format(doctype, name)
else:
return getlink(doctype, name)
error = False
total = len(data)
for i, row in enumerate(data):
# bypass empty rows
if main_doc_empty(row):
continue
row_idx = i + start_row
doc = None
# publish task_update
frappe.publish_realtime("data_import_progress",
{"progress": [i, total]},
user=frappe.session.user)
try:
doc = get_doc(row_idx)
if pre_process:
pre_process(doc)
if parentfield:
parent = frappe.get_doc(parenttype, doc["parent"])
doc = parent.append(parentfield, doc)
parent.save()
log('Inserted row for %s at #%s' %
(as_link(parenttype, doc.parent), unicode(doc.idx)))
else:
if overwrite and doc["name"] and frappe.db.exists(
doctype, doc["name"]):
original = frappe.get_doc(doctype, doc["name"])
original_name = original.name
original.update(doc)
# preserve original name for case sensitivity
original.name = original_name
original.flags.ignore_links = ignore_links
original.save()
log('Updated row (#%d) %s' %
(row_idx + 1, as_link(original.doctype,
original.name)))
doc = original
else:
if not update_only:
doc = frappe.get_doc(doc)
prepare_for_insert(doc)
doc.flags.ignore_links = ignore_links
doc.insert()
log('Inserted row (#%d) %s' %
(row_idx + 1, as_link(doc.doctype, doc.name)))
else:
log('Ignored row (#%d) %s' % (row_idx + 1, row[1]))
if attachments:
# check file url and create a File document
for file_url in attachments:
attach_file_to_doc(doc.doctype, doc.name, file_url)
if submit_after_import:
doc.submit()
log('Submitted row (#%d) %s' %
(row_idx + 1, as_link(doc.doctype, doc.name)))
except Exception as e:
error = True
if doc:
frappe.errprint(
doc if isinstance(doc, dict) else doc.as_dict())
err_msg = frappe.local.message_log and "\n\n".join(
frappe.local.message_log) or cstr(e)
log('Error for row (#%d) %s : %s' %
(row_idx + 1, len(row) > 1 and row[1] or "", err_msg))
frappe.errprint(frappe.get_traceback())
finally:
frappe.local.message_log = []
if error:
frappe.db.rollback()
else:
frappe.db.commit()
frappe.flags.mute_emails = False
frappe.flags.in_import = False
return {"messages": ret, "error": error}
def upload(rows=None,
submit_after_import=None,
ignore_encoding_errors=False,
no_email=True,
overwrite=None,
update_only=None,
ignore_links=False,
pre_process=None,
via_console=False,
from_data_import="No",
skip_errors=True,
data_import_doc=None,
validate_template=False,
user=None):
"""upload data"""
# for translations
if user:
frappe.cache().hdel("lang", user)
frappe.set_user_lang(user)
#frappe.msgprint(data_import_doc.overwrite)
if data_import_doc and isinstance(data_import_doc, string_types):
data_import_doc = frappe.get_doc("Data Import", data_import_doc)
#frappe.msgprint("found in database")
#frappe.msgprint(data_import_doc.overwrite)
if data_import_doc and from_data_import == "Yes":
no_email = data_import_doc.no_email
ignore_encoding_errors = data_import_doc.ignore_encoding_errors
update_only = data_import_doc.only_update
submit_after_import = data_import_doc.submit_after_import
overwrite = data_import_doc.overwrite
#frappe.msgprint("overwrite %s" % overwrite)
skip_errors = data_import_doc.skip_errors
else:
# extra input params
params = json.loads(frappe.form_dict.get("params") or '{}')
if params.get("submit_after_import"):
submit_after_import = True
if params.get("ignore_encoding_errors"):
ignore_encoding_errors = True
if not params.get("no_email"):
no_email = False
if params.get('update_only'):
update_only = True
if params.get('from_data_import'):
from_data_import = params.get('from_data_import')
if not params.get('skip_errors'):
skip_errors = params.get('skip_errors')
frappe.flags.in_import = True
frappe.flags.mute_emails = no_email
def get_data_keys_definition():
return get_data_keys()
def bad_template():
frappe.throw(
_("Please do not change the rows above {0}").format(
get_data_keys_definition().data_separator))
def check_data_length():
if not data:
frappe.throw(
_("No data found in the file. Please reattach the new file with data."
))
def get_start_row():
for i, row in enumerate(rows):
if row and row[0] == get_data_keys_definition().data_separator:
return i + 1
bad_template()
def get_header_row(key):
return get_header_row_and_idx(key)[0]
def get_header_row_and_idx(key):
for i, row in enumerate(header):
if row and row[0] == key:
return row, i
return [], -1
def filter_empty_columns(columns):
empty_cols = list(filter(lambda x: x in ("", None), columns))
if empty_cols:
if columns[-1 * len(empty_cols):] == empty_cols:
# filter empty columns if they exist at the end
columns = columns[:-1 * len(empty_cols)]
else:
frappe.msgprint(_(
"Please make sure that there are no empty columns in the file."
),
raise_exception=1)
return columns
def make_column_map():
doctype_row, row_idx = get_header_row_and_idx(
get_data_keys_definition().doctype)
if row_idx == -1: # old style
return
dt = None
for i, d in enumerate(doctype_row[1:]):
if d not in ("~", "-"):
if d and doctype_row[i] in (None, '', '~', '-',
_("DocType") + ":"):
dt, parentfield = d, None
# xls format truncates the row, so it may not have more columns
if len(doctype_row) > i + 2:
parentfield = doctype_row[i + 2]
doctypes.append((dt, parentfield))
column_idx_to_fieldname[(dt, parentfield)] = {}
column_idx_to_fieldtype[(dt, parentfield)] = {}
if dt:
column_idx_to_fieldname[(dt,
parentfield)][i +
1] = rows[row_idx +
2][i + 1]
column_idx_to_fieldtype[(dt,
parentfield)][i +
1] = rows[row_idx +
4][i + 1]
def get_doc(start_idx):
if doctypes:
doc = {}
attachments = []
last_error_row_idx = None
for idx in range(start_idx, len(rows)):
last_error_row_idx = idx # pylint: disable=W0612
if (not doc) or main_doc_empty(rows[idx]):
for dt, parentfield in doctypes:
d = {}
for column_idx in column_idx_to_fieldname[(
dt, parentfield)]:
try:
fieldname = column_idx_to_fieldname[(
dt, parentfield)][column_idx]
fieldtype = column_idx_to_fieldtype[(
dt, parentfield)][column_idx]
if not fieldname or not rows[idx][column_idx]:
continue
d[fieldname] = rows[idx][column_idx]
if fieldtype in ("Int", "Check"):
d[fieldname] = cint(d[fieldname])
elif fieldtype in ("Float", "Currency",
"Percent"):
d[fieldname] = flt(d[fieldname])
elif fieldtype == "Date":
if d[fieldname] and isinstance(
d[fieldname], string_types):
d[fieldname] = getdate(
parse_date(d[fieldname]))
elif fieldtype == "Datetime":
if d[fieldname]:
if " " in d[fieldname]:
_date, _time = d[fieldname].split()
else:
_date, _time = d[
fieldname], '00:00:00'
_date = parse_date(d[fieldname])
d[fieldname] = get_datetime(_date +
" " +
_time)
else:
d[fieldname] = None
elif fieldtype in ("Image", "Attach Image",
"Attach"):
# added file to attachments list
attachments.append(d[fieldname])
elif fieldtype in ("Link", "Dynamic Link",
"Data") and d[fieldname]:
# as fields can be saved in the number format(long type) in data import template
d[fieldname] = cstr(d[fieldname])
except IndexError:
pass
# scrub quotes from name and modified
if d.get("name") and d["name"].startswith('"'):
d["name"] = d["name"][1:-1]
if sum([0 if not val else 1 for val in d.values()]):
d['doctype'] = dt
if dt == doctype:
doc.update(d)
else:
if not overwrite and doc.get("name"):
d['parent'] = doc["name"]
d['parenttype'] = doctype
d['parentfield'] = parentfield
doc.setdefault(d['parentfield'], []).append(d)
else:
break
#frappe.msgprint(doc)
return doc, attachments, last_error_row_idx
else:
doc = frappe._dict(zip(columns, rows[start_idx][1:]))
doc['doctype'] = doctype
return doc, [], None
# used in testing whether a row is empty or parent row or child row
# checked only 3 first columns since first two columns can be blank for example the case of
# importing the item variant where item code and item name will be blank.
def main_doc_empty(row):
if row:
for i in range(3, 0, -1):
if len(row) > i and row[i]:
return False
return True
def validate_naming(doc):
autoname = frappe.get_meta(doctype).autoname
if autoname:
if autoname[0:5] == 'field':
autoname = autoname[6:]
elif autoname == 'naming_series:':
autoname = 'naming_series'
else:
return True
if (autoname not in doc) or (not doc[autoname]):
from frappe.model.base_document import get_controller
if not hasattr(get_controller(doctype), "autoname"):
frappe.throw(_(
"{0} is a mandatory field".format(autoname)))
return True
users = frappe.db.sql_list("select name from tabUser")
def prepare_for_insert(doc):
# don't block data import if user is not set
# migrating from another system
if not doc.owner in users:
doc.owner = frappe.session.user
if not doc.modified_by in users:
doc.modified_by = frappe.session.user
def is_valid_url(url):
is_valid = False
if url.startswith("/files") or url.startswith("/private/files"):
url = get_url(url)
try:
r = requests.get(url)
is_valid = True if r.status_code == 200 else False
except Exception:
pass
return is_valid
def attach_file_to_doc(doctype, docname, file_url):
# check if attachment is already available
# check if the attachement link is relative or not
if not file_url:
return
if not is_valid_url(file_url):
return
files = frappe.db.sql(
"""Select name from `tabFile` where attached_to_doctype='{doctype}' and
attached_to_name='{docname}' and (file_url='{file_url}' or thumbnail_url='{file_url}')"""
.format(doctype=doctype, docname=docname, file_url=file_url))
if files:
# file is already attached
return
save_url(file_url, None, doctype, docname, "Home/Attachments", 0)
# header
filename, file_extension = ['', '']
if not rows:
from frappe.utils.file_manager import get_file # get_file_doc
fname, fcontent = get_file(data_import_doc.import_file)
filename, file_extension = os.path.splitext(fname)
if file_extension == '.xlsx' and from_data_import == 'Yes':
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(
file_id=data_import_doc.import_file)
#frappe.msgprint("%d" % len(rows))
#frappe.msgprint(rows)
elif file_extension == '.csv':
from frappe.utils.csvutils import read_csv_content
rows = read_csv_content(fcontent, ignore_encoding_errors)
else:
frappe.throw(_("Unsupported File Format"))
start_row = get_start_row()
header = rows[:start_row]
data = rows[start_row:]
try:
doctype = get_header_row(get_data_keys_definition().main_table)[1]
columns = filter_empty_columns(
get_header_row(get_data_keys_definition().columns)[1:])
except:
frappe.throw(_("Cannot change header content"))
doctypes = []
column_idx_to_fieldname = {}
column_idx_to_fieldtype = {}
if skip_errors:
data_rows_with_error = header
if submit_after_import and not cint(
frappe.db.get_value("DocType", doctype, "is_submittable")):
submit_after_import = False
parenttype = get_header_row(get_data_keys_definition().parent_table)
if len(parenttype) > 1:
parenttype = parenttype[1]
# check permissions
if not frappe.permissions.can_import(parenttype or doctype):
frappe.flags.mute_emails = False
return {
"messages": [_("Not allowed to Import") + ": " + _(doctype)],
"error": True
}
# Throw expception in case of the empty data file
check_data_length()
make_column_map()
total = len(data)
if validate_template:
if total:
data_import_doc.total_rows = total
return True
if overwrite == None:
overwrite = params.get('overwrite')
# delete child rows (if parenttype)
parentfield = None
if parenttype:
parentfield = get_parent_field(doctype, parenttype)
if overwrite:
frappe.msgprint("still overwrtie")
delete_child_rows(data, doctype)
import_log = []
def log(**kwargs):
if via_console:
print(
(kwargs.get("title") + kwargs.get("message")).encode('utf-8'))
else:
import_log.append(kwargs)
def as_link(doctype, name):
if via_console:
return "{0}: {1}".format(doctype, name)
else:
return getlink(doctype, name)
# publish realtime task update
def publish_progress(achieved, reload=False):
if data_import_doc:
frappe.publish_realtime(
"data_import_progress", {
"progress": str(int(100.0 * achieved / total)),
"data_import": data_import_doc.name,
"reload": reload
},
user=frappe.session.user)
error_flag = rollback_flag = False
batch_size = frappe.conf.data_import_batch_size or 1000
for batch_start in range(0, total, batch_size):
batch = data[batch_start:batch_start + batch_size]
for i, row in enumerate(batch):
# bypass empty rows
if main_doc_empty(row):
continue
#frappe.msgprint("in batch %s" % overwrite)
#frappe.msgprint("docName %s" % doc.get("name"))
#frappe.msgprint("exist db %s" % frappe.db.exists(doctype,doc["name"]))
row_idx = i + start_row
doc = None
publish_progress(i)
#frappe.msgprint("in batch %s" % doc)
try:
doc, attachments, last_error_row_idx = get_doc(row_idx)
validate_naming(doc)
if pre_process:
pre_process(doc)
original = None
if parentfield:
parent = frappe.get_doc(parenttype, doc["parent"])
doc = parent.append(parentfield, doc)
parent.save()
else:
if overwrite and doc.get("name") and frappe.db.exists(
doctype, doc["name"]):
original = frappe.get_doc(doctype, doc["name"])
#frappe.msgprint("overwite %s" % original.name)
original_name = original.name
#frappe.msgprint(original)
#frappe.msgprint(doc)
original.update(doc)
#frappe.msgprint(original)
# preserve original name for case sensitivity
original.name = original_name
original.flags.ignore_links = ignore_links
original.save()
doc = original
else:
frappe.msgprint("not overwrite")
if not update_only:
doc = frappe.get_doc(doc)
prepare_for_insert(doc)
doc.flags.ignore_links = ignore_links
doc.insert()
if attachments:
# check file url and create a File document
for file_url in attachments:
attach_file_to_doc(doc.doctype, doc.name, file_url)
if submit_after_import:
doc.submit()
# log errors
if parentfield:
log(
**{
"row":
doc.idx,
"title":
'Inserted row for "%s"' %
(as_link(parenttype, doc.parent)),
"link":
get_absolute_url(parenttype, doc.parent),
"message":
'Document successfully saved',
"indicator":
"green"
})
elif submit_after_import:
log(
**{
"row":
row_idx + 1,
"title":
'Submitted row for "%s"' %
(as_link(doc.doctype, doc.name)),
"message":
"Document successfully submitted",
"link":
get_absolute_url(doc.doctype, doc.name),
"indicator":
"blue"
})
elif original:
log(
**{
"row":
row_idx + 1,
"title":
'Updated row for "%s"' %
(as_link(doc.doctype, doc.name)),
"message":
"Document successfully updated",
"link":
get_absolute_url(doc.doctype, doc.name),
"indicator":
"green"
})
elif not update_only:
log(
**{
"row":
row_idx + 1,
"title":
'Inserted row for "%s"' %
(as_link(doc.doctype, doc.name)),
"message":
"Document successfully saved",
"link":
get_absolute_url(doc.doctype, doc.name),
"indicator":
"green"
})
else:
log(
**{
"row": row_idx + 1,
"title": 'Ignored row for %s' % (row[1]),
"link": None,
"message": "Document updation ignored",
"indicator": "orange"
})
except Exception as e:
error_flag = True
# build error message
if frappe.local.message_log:
err_msg = "\n".join([
'<p class="border-bottom small">{}</p>'.format(
json.loads(msg).get('message'))
for msg in frappe.local.message_log
])
else:
err_msg = '<p class="border-bottom small">{}</p>'.format(
cstr(e))
error_trace = frappe.get_traceback()
if error_trace:
error_log_doc = frappe.log_error(error_trace)
error_link = get_absolute_url("Error Log",
error_log_doc.name)
else:
error_link = None
log(
**{
"row":
row_idx + 1,
"title":
'Error for row %s' %
(len(row) > 1 and frappe.safe_decode(row[1]) or ""),
"message":
err_msg,
"indicator":
"red",
"link":
error_link
})
# data with error to create a new file
# include the errored data in the last row as last_error_row_idx will not be updated for the last row
if skip_errors:
if last_error_row_idx == len(rows) - 1:
last_error_row_idx = len(rows)
data_rows_with_error += rows[row_idx:last_error_row_idx]
else:
rollback_flag = True
finally:
frappe.local.message_log = []
start_row += batch_size
if rollback_flag:
frappe.db.rollback()
else:
frappe.db.commit()
frappe.flags.mute_emails = False
frappe.flags.in_import = False
log_message = {"messages": import_log, "error": error_flag}
if data_import_doc:
data_import_doc.log_details = json.dumps(log_message)
import_status = None
if error_flag and data_import_doc.skip_errors and len(data) != len(
data_rows_with_error):
import_status = "Partially Successful"
# write the file with the faulty row
from frappe.utils.file_manager import save_file
file_name = 'error_' + filename + file_extension
if file_extension == '.xlsx':
from frappe.utils.xlsxutils import make_xlsx
xlsx_file = make_xlsx(data_rows_with_error,
"Data Import Template")
file_data = xlsx_file.getvalue()
else:
from frappe.utils.csvutils import to_csv
file_data = to_csv(data_rows_with_error)
error_data_file = save_file(file_name, file_data, "Data Import",
data_import_doc.name,
"Home/Attachments")
data_import_doc.error_file = error_data_file.file_url
elif error_flag:
import_status = "Failed"
else:
import_status = "Successful"
data_import_doc.import_status = import_status
data_import_doc.save()
if data_import_doc.import_status in [
"Successful", "Partially Successful"
]:
data_import_doc.submit()
publish_progress(100, True)
else:
publish_progress(0, True)
frappe.db.commit()
else:
return log_message
def upload(rows = None, submit_after_import=None, ignore_encoding_errors=False, no_email=True, overwrite=None,
update_only = None, ignore_links=False, pre_process=None, via_console=False, from_data_import="No",
skip_errors = True, data_import_doc=None, validate_template=False, user=None):
"""upload data"""
# for translations
if user:
frappe.cache().hdel("lang", user)
frappe.set_user_lang(user)
if data_import_doc and isinstance(data_import_doc, string_types):
data_import_doc = frappe.get_doc("Data Import", data_import_doc)
if data_import_doc and from_data_import == "Yes":
no_email = data_import_doc.no_email
ignore_encoding_errors = data_import_doc.ignore_encoding_errors
update_only = data_import_doc.only_update
submit_after_import = data_import_doc.submit_after_import
overwrite = data_import_doc.overwrite
skip_errors = data_import_doc.skip_errors
else:
# extra input params
params = json.loads(frappe.form_dict.get("params") or '{}')
if params.get("submit_after_import"):
submit_after_import = True
if params.get("ignore_encoding_errors"):
ignore_encoding_errors = True
if not params.get("no_email"):
no_email = False
if params.get('update_only'):
update_only = True
if params.get('from_data_import'):
from_data_import = params.get('from_data_import')
if not params.get('skip_errors'):
skip_errors = params.get('skip_errors')
frappe.flags.in_import = True
frappe.flags.mute_emails = no_email
def get_data_keys_definition():
return get_data_keys()
def bad_template():
frappe.throw(_("Please do not change the rows above {0}").format(get_data_keys_definition().data_separator))
def check_data_length():
if not data:
frappe.throw(_("No data found in the file. Please reattach the new file with data."))
def get_start_row():
for i, row in enumerate(rows):
if row and row[0]==get_data_keys_definition().data_separator:
return i+1
bad_template()
def get_header_row(key):
return get_header_row_and_idx(key)[0]
def get_header_row_and_idx(key):
for i, row in enumerate(header):
if row and row[0]==key:
return row, i
return [], -1
def filter_empty_columns(columns):
empty_cols = list(filter(lambda x: x in ("", None), columns))
if empty_cols:
if columns[-1*len(empty_cols):] == empty_cols:
# filter empty columns if they exist at the end
columns = columns[:-1*len(empty_cols)]
else:
frappe.msgprint(_("Please make sure that there are no empty columns in the file."),
raise_exception=1)
return columns
def make_column_map():
doctype_row, row_idx = get_header_row_and_idx(get_data_keys_definition().doctype)
if row_idx == -1: # old style
return
dt = None
for i, d in enumerate(doctype_row[1:]):
if d not in ("~", "-"):
if d and doctype_row[i] in (None, '' ,'~', '-', _("DocType") + ":"):
dt, parentfield = d, None
# xls format truncates the row, so it may not have more columns
if len(doctype_row) > i+2:
parentfield = doctype_row[i+2]
doctypes.append((dt, parentfield))
column_idx_to_fieldname[(dt, parentfield)] = {}
column_idx_to_fieldtype[(dt, parentfield)] = {}
if dt:
column_idx_to_fieldname[(dt, parentfield)][i+1] = rows[row_idx + 2][i+1]
column_idx_to_fieldtype[(dt, parentfield)][i+1] = rows[row_idx + 4][i+1]
def get_doc(start_idx):
if doctypes:
doc = {}
attachments = []
last_error_row_idx = None
for idx in range(start_idx, len(rows)):
last_error_row_idx = idx # pylint: disable=W0612
if (not doc) or main_doc_empty(rows[idx]):
for dt, parentfield in doctypes:
d = {}
for column_idx in column_idx_to_fieldname[(dt, parentfield)]:
try:
fieldname = column_idx_to_fieldname[(dt, parentfield)][column_idx]
fieldtype = column_idx_to_fieldtype[(dt, parentfield)][column_idx]
if not fieldname or not rows[idx][column_idx]:
continue
d[fieldname] = rows[idx][column_idx]
if fieldtype in ("Int", "Check"):
d[fieldname] = cint(d[fieldname])
elif fieldtype in ("Float", "Currency", "Percent"):
d[fieldname] = flt(d[fieldname])
elif fieldtype == "Date":
if d[fieldname] and isinstance(d[fieldname], string_types):
d[fieldname] = getdate(parse_date(d[fieldname]))
elif fieldtype == "Datetime":
if d[fieldname]:
if " " in d[fieldname]:
_date, _time = d[fieldname].split()
else:
_date, _time = d[fieldname], '00:00:00'
_date = parse_date(d[fieldname])
d[fieldname] = get_datetime(_date + " " + _time)
else:
d[fieldname] = None
elif fieldtype in ("Image", "Attach Image", "Attach"):
# added file to attachments list
attachments.append(d[fieldname])
elif fieldtype in ("Link", "Dynamic Link", "Data") and d[fieldname]:
# as fields can be saved in the number format(long type) in data import template
d[fieldname] = cstr(d[fieldname])
except IndexError:
pass
# scrub quotes from name and modified
if d.get("name") and d["name"].startswith('"'):
d["name"] = d["name"][1:-1]
if sum([0 if not val else 1 for val in d.values()]):
d['doctype'] = dt
if dt == doctype:
doc.update(d)
else:
if not overwrite and doc.get("name"):
d['parent'] = doc["name"]
d['parenttype'] = doctype
d['parentfield'] = parentfield
doc.setdefault(d['parentfield'], []).append(d)
else:
break
return doc, attachments, last_error_row_idx
else:
doc = frappe._dict(zip(columns, rows[start_idx][1:]))
doc['doctype'] = doctype
return doc, [], None
# used in testing whether a row is empty or parent row or child row
# checked only 3 first columns since first two columns can be blank for example the case of
# importing the item variant where item code and item name will be blank.
def main_doc_empty(row):
if row:
for i in range(3,0,-1):
if len(row) > i and row[i]:
return False
return True
def validate_naming(doc):
autoname = frappe.get_meta(doctype).autoname
if autoname:
if autoname[0:5] == 'field':
autoname = autoname[6:]
elif autoname == 'naming_series:':
autoname = 'naming_series'
else:
return True
if (autoname not in doc) or (not doc[autoname]):
from frappe.model.base_document import get_controller
if not hasattr(get_controller(doctype), "autoname"):
frappe.throw(_("{0} is a mandatory field".format(autoname)))
return True
users = frappe.db.sql_list("select name from tabUser")
def prepare_for_insert(doc):
# don't block data import if user is not set
# migrating from another system
if not doc.owner in users:
doc.owner = frappe.session.user
if not doc.modified_by in users:
doc.modified_by = frappe.session.user
def is_valid_url(url):
is_valid = False
if url.startswith("/files") or url.startswith("/private/files"):
url = get_url(url)
try:
r = requests.get(url)
is_valid = True if r.status_code == 200 else False
except Exception:
pass
return is_valid
def attach_file_to_doc(doctype, docname, file_url):
# check if attachment is already available
# check if the attachement link is relative or not
if not file_url:
return
if not is_valid_url(file_url):
return
files = frappe.db.sql("""Select name from `tabFile` where attached_to_doctype='{doctype}' and
attached_to_name='{docname}' and (file_url='{file_url}' or thumbnail_url='{file_url}')""".format(
doctype=doctype,
docname=docname,
file_url=file_url
))
if files:
# file is already attached
return
save_url(file_url, None, doctype, docname, "Home/Attachments", 0)
# header
filename, file_extension = ['','']
if not rows:
from frappe.utils.file_manager import get_file # get_file_doc
fname, fcontent = get_file(data_import_doc.import_file)
filename, file_extension = os.path.splitext(fname)
if file_extension == '.xlsx' and from_data_import == 'Yes':
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(file_id=data_import_doc.import_file)
elif file_extension == '.csv':
from frappe.utils.csvutils import read_csv_content
rows = read_csv_content(fcontent, ignore_encoding_errors)
else:
frappe.throw(_("Unsupported File Format"))
start_row = get_start_row()
header = rows[:start_row]
data = rows[start_row:]
try:
doctype = get_header_row(get_data_keys_definition().main_table)[1]
columns = filter_empty_columns(get_header_row(get_data_keys_definition().columns)[1:])
except:
frappe.throw(_("Cannot change header content"))
doctypes = []
column_idx_to_fieldname = {}
column_idx_to_fieldtype = {}
if skip_errors:
data_rows_with_error = header
if submit_after_import and not cint(frappe.db.get_value("DocType",
doctype, "is_submittable")):
submit_after_import = False
parenttype = get_header_row(get_data_keys_definition().parent_table)
if len(parenttype) > 1:
parenttype = parenttype[1]
# check permissions
if not frappe.permissions.can_import(parenttype or doctype):
frappe.flags.mute_emails = False
return {"messages": [_("Not allowed to Import") + ": " + _(doctype)], "error": True}
# Throw expception in case of the empty data file
check_data_length()
make_column_map()
total = len(data)
if validate_template:
if total:
data_import_doc.total_rows = total
return True
if overwrite==None:
overwrite = params.get('overwrite')
# delete child rows (if parenttype)
parentfield = None
if parenttype:
parentfield = get_parent_field(doctype, parenttype)
if overwrite:
delete_child_rows(data, doctype)
import_log = []
def log(**kwargs):
if via_console:
print((kwargs.get("title") + kwargs.get("message")).encode('utf-8'))
else:
import_log.append(kwargs)
def as_link(doctype, name):
if via_console:
return "{0}: {1}".format(doctype, name)
else:
return getlink(doctype, name)
# publish realtime task update
def publish_progress(achieved, reload=False):
if data_import_doc:
frappe.publish_realtime("data_import_progress", {"progress": str(int(100.0*achieved/total)),
"data_import": data_import_doc.name, "reload": reload}, user=frappe.session.user)
error_flag = rollback_flag = False
batch_size = frappe.conf.data_import_batch_size or 1000
for batch_start in range(0, total, batch_size):
batch = data[batch_start:batch_start + batch_size]
for i, row in enumerate(batch):
# bypass empty rows
if main_doc_empty(row):
continue
row_idx = i + start_row
doc = None
publish_progress(i)
try:
doc, attachments, last_error_row_idx = get_doc(row_idx)
validate_naming(doc)
if pre_process:
pre_process(doc)
original = None
if parentfield:
parent = frappe.get_doc(parenttype, doc["parent"])
doc = parent.append(parentfield, doc)
parent.save()
else:
if overwrite and doc.get("name") and frappe.db.exists(doctype, doc["name"]):
original = frappe.get_doc(doctype, doc["name"])
original_name = original.name
original.update(doc)
# preserve original name for case sensitivity
original.name = original_name
original.flags.ignore_links = ignore_links
original.save()
doc = original
else:
if not update_only:
doc = frappe.get_doc(doc)
prepare_for_insert(doc)
doc.flags.ignore_links = ignore_links
doc.insert()
if attachments:
# check file url and create a File document
for file_url in attachments:
attach_file_to_doc(doc.doctype, doc.name, file_url)
if submit_after_import:
doc.submit()
# log errors
if parentfield:
log(**{"row": doc.idx, "title": 'Inserted row for "%s"' % (as_link(parenttype, doc.parent)),
"link": get_url_to_form(parenttype, doc.parent), "message": 'Document successfully saved', "indicator": "green"})
elif submit_after_import:
log(**{"row": row_idx + 1, "title":'Submitted row for "%s"' % (as_link(doc.doctype, doc.name)),
"message": "Document successfully submitted", "link": get_url_to_form(doc.doctype, doc.name), "indicator": "blue"})
elif original:
log(**{"row": row_idx + 1,"title":'Updated row for "%s"' % (as_link(doc.doctype, doc.name)),
"message": "Document successfully updated", "link": get_url_to_form(doc.doctype, doc.name), "indicator": "green"})
elif not update_only:
log(**{"row": row_idx + 1, "title":'Inserted row for "%s"' % (as_link(doc.doctype, doc.name)),
"message": "Document successfully saved", "link": get_url_to_form(doc.doctype, doc.name), "indicator": "green"})
else:
log(**{"row": row_idx + 1, "title":'Ignored row for %s' % (row[1]), "link": None,
"message": "Document updation ignored", "indicator": "orange"})
except Exception as e:
error_flag = True
# build error message
if frappe.local.message_log:
err_msg = "\n".join(['<p class="border-bottom small">{}</p>'.format(json.loads(msg).get('message')) for msg in frappe.local.message_log])
else:
err_msg = '<p class="border-bottom small">{}</p>'.format(cstr(e))
error_trace = frappe.get_traceback()
if error_trace:
error_log_doc = frappe.log_error(error_trace)
error_link = get_url_to_form("Error Log", error_log_doc.name)
else:
error_link = None
log(**{
"row": row_idx + 1,
"title": 'Error for row %s' % (len(row)>1 and frappe.safe_decode(row[1]) or ""),
"message": err_msg,
"indicator": "red",
"link":error_link
})
# data with error to create a new file
# include the errored data in the last row as last_error_row_idx will not be updated for the last row
if skip_errors:
if last_error_row_idx == len(rows)-1:
last_error_row_idx = len(rows)
data_rows_with_error += rows[row_idx:last_error_row_idx]
else:
rollback_flag = True
finally:
frappe.local.message_log = []
start_row += batch_size
if rollback_flag:
frappe.db.rollback()
else:
frappe.db.commit()
frappe.flags.mute_emails = False
frappe.flags.in_import = False
log_message = {"messages": import_log, "error": error_flag}
if data_import_doc:
data_import_doc.log_details = json.dumps(log_message)
import_status = None
if error_flag and data_import_doc.skip_errors and len(data) != len(data_rows_with_error):
import_status = "Partially Successful"
# write the file with the faulty row
from frappe.utils.file_manager import save_file
file_name = 'error_' + filename + file_extension
if file_extension == '.xlsx':
from frappe.utils.xlsxutils import make_xlsx
xlsx_file = make_xlsx(data_rows_with_error, "Data Import Template")
file_data = xlsx_file.getvalue()
else:
from frappe.utils.csvutils import to_csv
file_data = to_csv(data_rows_with_error)
error_data_file = save_file(file_name, file_data, "Data Import",
data_import_doc.name, "Home/Attachments")
data_import_doc.error_file = error_data_file.file_url
elif error_flag:
import_status = "Failed"
else:
import_status = "Successful"
data_import_doc.import_status = import_status
data_import_doc.save()
if data_import_doc.import_status in ["Successful", "Partially Successful"]:
data_import_doc.submit()
publish_progress(100, True)
else:
publish_progress(0, True)
frappe.db.commit()
else:
return log_message
