Created
April 16, 2020 16:06
-
-
Save andrew-curthoys/a6fd5b52a1fc87fe699cfccd44a28feb to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from pathlib import Path | |
from sxl import Workbook | |
from datetime import datetime | |
from datetime import timedelta | |
# Get today's date, end date, and initilize dictionaries | |
today = datetime.today().replace(hour=0, minute=0, second=0, microsecond=0) | |
end_date = today + timedelta(days=3) | |
sap_index = {} | |
data_dict = {} | |
# Get data from Excel file | |
file_path = Path(__file__).resolve().parent / 'harmony_export.xlsm' | |
workbook = Workbook(file_path) | |
sheet_names = ['Oil', 'Water', 'Gas'] | |
# Set up dictionaries to store values | |
# Get SAP IDs from header data on first sheet | |
sheet = workbook.sheets[sheet_names[0]] | |
head = sheet.head(10) | |
first_data_row = 5 | |
for i, row in enumerate(head): | |
if row[0] == 'SAP ID': | |
for j, sap_id in enumerate(row): | |
sap_index[j] = sap_id | |
elif row[0] == 'Date': | |
first_data_row = i + 1 | |
# Set up SAP ID keys | |
for sap_id in list(sap_index.values())[1:]: | |
data_dict[sap_id] = {} | |
# Set up Date keys | |
for i, row in enumerate(sheet.rows): | |
if i < first_data_row: | |
continue | |
elif row[0] < today: | |
continue | |
elif row[0] < end_date: | |
row_date = str(row[0]) | |
for j, date in enumerate(sheet.rows): | |
if j == 0: | |
continue | |
if j > len(data_dict): | |
break | |
data_dict[sap_index[j]][row_date] = {} | |
# Loop through all sheets and populate dictionary | |
for product in sheet_names: | |
sheet = workbook.sheets[product] | |
for i, row in enumerate(sheet.rows): | |
if i < first_data_row: | |
continue | |
elif row[0] < today: | |
continue | |
elif row[0] < end_date: | |
row_date = str(row[0]) | |
for j, data in enumerate(row): | |
if j == 0: | |
continue | |
print(data) | |
data_dict[sap_index[j]][row_date][product] = data | |
print(data_dict) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Tip: if you change the name of the file to be a
.py
extension, it'll render the code with Python syntax highlighting!