def get_clean_data_from_file(xlsx_filename, sheetname='Sheet1', by_id=False):
if type(xlsx_filename) == BytesIO:
data = getDataFromFile(xlsx_filename, xlsx_filename.read(), sheetname,
by_id)
else:
with open(xlsx_filename, 'rb') as xlsxfile:
data = getDataFromFile(xlsx_filename, xlsxfile.read(), sheetname,
by_id)
return list(map(lambda row: excl_last_updated(row), data))
def test_exports_xlsx(self, client):
route = url_for('exports.activities_xlsx',
domestic_external="external")
res = client.get(route)
xlsx_file = BytesIO(res.get_data())
xlsx_file_as_json = getDataFromFile(xlsx_file, xlsx_file.read(),
'Data1')
assert len(xlsx_file_as_json) == 10
def load_transactions_from_file(input_file):
workbook = openpyxl.load_workbook(filename=input_file,
read_only=True)
sheet = workbook[workbook.sheetnames[0]]
fromto_cell = sheet['A5'].value
_period_start, _period_end = re.match("(?:.*) from (.*) to (.*)", fromto_cell).groups()
period_start = datetime.datetime.strptime(_period_start, '%d-%b-%Y')
period_end = datetime.datetime.strptime(_period_end, '%d-%b-%Y')
input_file.seek(0)
data = xlsx_to_csv.getDataFromFile(
input_file.filename, input_file.read(), 0, True, 5)
for transaction in data:
if transaction['Project'] == None: break
try:
add_row(input_file.filename, period_start, period_end, transaction)
except sa.exc.IntegrityError:
db.session.rollback()
continue
def import_transactions_from_file(fiscal_year=None):
file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)),
"..", "lib", "import_files", "WORLD BANK DISBURSEMENT.xlsx")
data = xlsx_to_csv.getDataFromFile(
file_path, open(file_path, "rb").read(), "all", True, 5)
return import_transactions(data, 'FY2018/19')
def import_transactions_from_upload(uploaded_file, fiscal_year=None):
data = xlsx_to_csv.getDataFromFile(
uploaded_file.filename,
uploaded_file.read(), 0, True)
return import_transactions(data, fiscal_year)
def import_transactions_from_file(fiscal_year=None):
file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)),
"..", "lib", "import_files", "psip-transactions.xlsx")
data = xlsx_to_csv.getDataFromFile(
file_path, open(file_path, "rb").read(), 0, True)
return import_transactions(data, fiscal_year)
def xlsx_to_csv_jsonfile(jsonfile):
fn = 'tests/artefacts/testdata.xlsx'
with open(fn, 'rb') as xlsxfile:
data = getDataFromFile(fn, xlsxfile.read(), 'Sheet1')
json.dump(data, jsonfile, cls=JSONEncoder)
def import_xls_new(input_file, _type, disbursement_cols=[]):
num_updated_activities = 0
messages = []
activity_id = None
file_contents = BytesIO(input_file.read())
xl_workbook = openpyxl.load_workbook(file_contents)
num_sheets = len(xl_workbook.sheetnames)
cl_lookups = get_codelists_lookups()
cl_lookups_by_name = get_codelists_lookups_by_name()
def filter_mtef(column):
pattern = r"(\S*) \(MTEF\)$"
return re.match(pattern, column)
def filter_counterpart(column):
pattern = r"(\S*) \(GoL counterpart fund request\)$"
return re.match(pattern, column)
if "Instructions" in xl_workbook.sheetnames:
currency = xl_workbook["Instructions"].cell(6, 3).value
begin_sheet = 1
else:
currency = "USD"
begin_sheet = 0
try:
for sheet_id in range(begin_sheet, num_sheets):
input_file.seek(0)
data = xlsx_to_csv.getDataFromFile(
input_file.filename, input_file.read(), sheet_id, True)
if _type == 'mtef':
mtef_cols = list(filter(filter_mtef, data[0].keys()))
counterpart_funding_cols = list(
filter(filter_counterpart, data[0].keys()))
if len(mtef_cols) == 0:
raise Exception("No columns containing MTEF projections data \
were found in the uploaded spreadsheet!")
elif _type == 'disbursements':
for _column_name in disbursement_cols:
if _column_name not in data[0].keys():
raise Exception("The column {} containing financial data was not \
found in the uploaded spreadsheet!".format(_column_name))
for row in data: # each row is one ID
try:
activity_id = int(row["ID"])
except TypeError:
messages.append("Warning, activity ID \"{}\" with title \"{}\" was not found in the system \
and was not imported! Please create this activity in the \
system before trying to import.".format(row['ID'], row['Activity Title']))
continue
activity = qactivity.get_activity(activity_id)
activity_iati_preferences = [
pref.field for pref in activity.iati_preferences]
if not activity:
messages.append("Warning, activity ID \"{}\" with title \"{}\" was not found in the system \
and was not imported! Please create this activity in the \
system before trying to import.".format(row['ID'], row['Activity Title']))
continue
existing_activity = activity_to_json(activity, cl_lookups)
if _type == 'mtef':
# FIXME quick fix for now
if 'forwardspend' in activity_iati_preferences:
continue
updated = {
'activity': update_activity_data(activity, existing_activity,
row, cl_lookups_by_name),
# Parse MTEF projections columns
'mtef_years': parse_mtef_cols(currency, mtef_cols, existing_activity,
row, activity_id),
# Parse counterpart funding columns
'counterpart_years': parse_counterpart_cols(counterpart_funding_cols,
activity, row, activity_id),
}
elif _type == 'disbursements':
# FIXME quick fix for now
if 'disbursement' in activity_iati_preferences:
continue
updated = {
'activity': update_activity_data(activity, existing_activity,
row, cl_lookups_by_name),
'disbursements': parse_disbursement_cols(currency, disbursement_cols,
activity, existing_activity, row)
}
# Mark activity as updated and inform user
update_message, num_updated_activities = make_updated_info(
updated, activity, num_updated_activities)
if update_message is not None:
messages.append(update_message)
except Exception as e:
if activity_id is not None:
messages.append("""There was an unexpected error when importing your
projects, there appears to be an error around activity ID {}.
The error was: {}""".format(activity_id, e))
else:
messages.append("""There was an error while importing your projects,
the error was: {}""".format(e))
db.session.commit()
return messages, num_updated_activities
def read_file(FILENAME=os.path.join(basedir, "..", "lib/import_files/", "AMCU_Master_Database.xlsx")):
f = open(FILENAME, "rb")
return xlsx_to_csv.getDataFromFile(f, f.read(), sheet="Main Database June 2017")
