Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In honor of the dear Click Sense developers
I have tables of goals for 2018 and 2019 in the model, which I chained, but I want to create a situation where every new goal file that arrives in a folder on the computer will be automatically chained to the model. That is, I have to create a loop and connection of all the data in an orderly manner.
I want to generate the script so that it is generic. that if a file from the year 2020 / 2021 is added for example to the folder - he will upload it to me as well.
I need to use a loop that will extract the year from the name of the file into a variable and use the variable to extract the appropriate tab from Excel.
How do I write the script?
import os
import openpyxl
def extract_year(filename):
year = filename[-4:]
return int(year)
def get_tab(excel_file, year):
sheet_name = f"{year} Goals"
return excel_file[sheet_name]
def main():
# Get the folder where the goal files are stored
folder_path = "C:\\Users\\your_username\\Desktop\\goals"
# Get a list of all the files in the folder
files = os.listdir(folder_path)
# Create a loop to iterate through the files
for file in files:
# Extract the year from the file name
year = extract_year(file)
# Open the Excel file
excel_file = openpyxl.load_workbook(f"{folder_path}/{file}")
# Get the appropriate tab from the Excel file
tab = get_tab(excel_file, year)
# Chain the tab to the model
tab.chain()
if __name__ == "__main__":
main()