"Export Data" to Excel stores non-standard xlsx

MartinHBramwell · April 22, 2021, 5:26pm

I exported about 100 records from Item to xlsx format.

I then tried to read that data back into my Python app using

import pandas as pd

SOURCE = "/opt/erpnext_workfiles/Item.xlsx"

@frappe.whitelist()
def loadItems(company):
    wkbk = pd.read_excel(SOURCE)

The result was:

18:35:53 web.1            | Traceback (most recent call last):
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/apps/frappe/frappe/app.py", line 67, in application
18:35:53 web.1            |     response = frappe.api.handle()
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/apps/frappe/frappe/api.py", line 58, in handle
18:35:53 web.1            |     return frappe.handler.handle()
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/apps/frappe/frappe/handler.py", line 30, in handle
18:35:53 web.1            |     data = execute_cmd(cmd)
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/apps/frappe/frappe/handler.py", line 70, in execute_cmd
18:35:53 web.1            |     return frappe.call(method, **frappe.form_dict)
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/apps/frappe/frappe/__init__.py", line 1145, in call
18:35:53 web.1            |     return fn(*args, **newargs)
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/apps/MyApp/MyApp/MyModule/doctype/MyDoc/MyLoader.py", line 186, in loadItems
18:35:53 web.1            |     wkbk = pd.read_excel(SOURCE)
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/env/lib/python3.8/site-packages/pandas/util/_decorators.py", line 299, in wrapper
18:35:53 web.1            |     return func(*args, **kwargs)
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/env/lib/python3.8/site-packages/pandas/io/excel/_base.py", line 344, in read_excel
18:35:53 web.1            |     data = io.parse(
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/env/lib/python3.8/site-packages/pandas/io/excel/_base.py", line 1170, in parse
18:35:53 web.1            |     return self._reader.parse(
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/env/lib/python3.8/site-packages/pandas/io/excel/_base.py", line 566, in parse
18:35:53 web.1            |     output[asheetname] = parser.read(nrows=nrows)
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/env/lib/python3.8/site-packages/pandas/io/parsers.py", line 1057, in read
18:35:53 web.1            |     index, columns, col_dict = self._engine.read(nrows)
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/env/lib/python3.8/site-packages/pandas/io/parsers.py", line 2496, in read
18:35:53 web.1            |     alldata = self._rows_to_cols(content)
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/env/lib/python3.8/site-packages/pandas/io/parsers.py", line 3189, in _rows_to_cols
18:35:53 web.1            |     self._alert_malformed(msg, row_num + 1)
18:35:53 web.1            |   File "/home/erpdev/frappe-bench-DELS/env/lib/python3.8/site-packages/pandas/io/parsers.py", line 2948, in _alert_malformed
18:35:53 web.1            |     raise ParserError(msg)
18:35:53 web.1            | pandas.errors.ParserError: Expected 2 fields in line 14, saw 136

As a quick sanity check I loaded the file into LibreOffice, made a minor change, saved it and tried loading that. It worked without problem!

I’d like to look at the Data Export code. Where can I find it?

MartinHBramwell · April 22, 2021, 6:49pm

So here’s a putrid sort of workaround:

import openpyxl as pxl

@frappe.whitelist()
def loadItems(company):
    wkbk = pxl.load_workbook(SOURCE)
    wkbk.save(SOURCE)
    wkbk = pd.read_excel(SOURCE)

kennethsequeira · April 22, 2021, 6:50pm

This should be a good starting point:

github.com

frappe/frappe/blob/develop/frappe/core/doctype/data_import/exporter.py

# Copyright (c) 2019, Frappe Technologies Pvt. Ltd. and Contributors
# License: MIT. See LICENSE

import frappe
from frappe import _
from frappe.model import display_fieldtypes, no_value_fields
from frappe.model import table_fields as table_fieldtypes
from frappe.utils import flt, format_duration, groupby_metric
from frappe.utils.csvutils import build_csv_response
from frappe.utils.xlsxutils import build_xlsx_response


class Exporter:
	def __init__(
		self,
		doctype,
		export_fields=None,
		export_data=False,
		export_filters=None,
		export_page_length=None,

This file has been truncated. show original

MartinHBramwell · April 22, 2021, 6:51pm

Oh Cool! Thanks.

I’ll try to dig into it a bit.

MartinHBramwell · April 22, 2021, 6:59pm

I suspect that frappe.utils.xlsxutils is badly out of date. The comments to this answer suggest as much:

Stack Overflow ==> Pandas read excel : ValueError

The code could save to a temporary file, then immediately load it and save it again with openpyxl then return it to the user. It would be a pragmatic workaround.