Click here to Skip to main content
15,392,679 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good afternoon :)

I am new at python and I would like to desperately ask for your help, currently I was assigned a task that has the following requirements :

#The script must read,run,extract relevant updated data weekly from a folder that contains multiple excel files. #The updated data is added on an excel sheet as it follows KPI/SW/WEEK (to be able to check the improvement of KPI's per week #Requirements are : -to extract and do a graphical representation of the KPI1/SWC/WEEK #-to extract the KPI 1and 2 + the Average KPI of 1and 2 and to do a graphical representation /SWC/WEEK #-the graphs should show the results in % #-from master excel list (the list that need update every week) we extract from column G 2 data /hcp/swc/week and do a graphically representation. #-all the new RN's will be read and have the exact data extracted to be able to updated data and added to the master excel sheet. My code is as it follows:

from openpyxl import load_workbook

data_file = 'C:\\Users\IGal\\Desktop\\OneDrive_1_6-7-2022.xlsx'

# Load the entire workbook.
wb = load_workbook(data_file)

# Load one worksheet.
ws = wb['Data']
all_columns = list(ws.columns)
all_rows = list(ws.rows)
#print(f"Found {len(all_columns)} columns of data.")
#print(f"Found {len(all_rows)} rows of data")

#print("\nFirst columns of data:")
#print("\nFirst rows of data:")
for row in all_columns[:0]:
 for columns in all_rows[:0]:
    #print(columns)
    #print(rows)

    # Load the entire workbook.
    wb = load_workbook(data_file)

    # Load one worksheet.
    ws = wb['C:\\Users\\IGal\\Desktop\\SWKPIsCW.xlsx']
    all_rows = list(ws.rows)

    for cell in all_rows[0]:
        print(cell.value)

# Pull information from specific cells.
for row in all_rows[1:455]:
    state = row[0].value
    percent_cw = row[2].value
    #percent_cw = row[4].value

    print(f"{state}")
    print(f" {percent_cw}% of cw kpi")
    print(f" {percent_cw}% of cw kpi")

# Pull information from specific cells.
for row in all_rows[1:455]:
  SWC = row[1:455].value
  percent_SWC = int(round(row[1:455].value, 2) * 100)
  percent_cw = int(round(row[1:455].value, 2) * 100)


print(SWC)

What I have tried:

<pre>
I have tried to read the data that I need to append to the excel master sheet without any success.
I don't know where to go from here , I am stuck literally .I need help
Posted
Updated 8-Jun-22 4:47am
v4
Comments
Richard MacCutchan 8-Jun-22 10:21am
   
"without any successs."
Unfortunately we cannot guess what that means. Please use the Improve question link above, and add complete details of what is not working, including any error messages.
Ionela Daniela Gal 8-Jun-22 10:39am
   
Hi Richard MacCutchan I have tried to read multiple excel files from a folder , extract specific data from those files , this data is then added to a master excel sheet every week. Every week a new column will be added to this excel sheet. What I have tried is to read the data from the excel sheets , extract data , add a new column , add new data in that column , than to do a graphically representation showing in % the improvement / week of the data extracted from the excel files.
Richard MacCutchan 8-Jun-22 10:44am
   
Please read my comment again.
Ionela Daniela Gal 8-Jun-22 11:12am
   
I will explain here,I would like to know how to create a script that can read, run and extract specific updated data from multiple excel files , the data is extracted weekly to update weekly the master excel sheet(this master excel sheet is separate from the other excel sheets), The excel sheet (master ) has 28 columns and 454 rows, each column is named differently.The data that is extracted must be represented graphically (example KPI 1 has a separate graph, KPI1 AND KPI2 a separate graph, Average KPI1 and KPI2 has a separate graph ) the progress /week is measured in %.
If there is a way to do this python script I would like to ask you kindly to explain to me how can I do it , thank you very much 😊
Richard MacCutchan 8-Jun-22 11:22am
   
Well the answer is the you need to write some code. You are already using openpyxl, so if you need more details on how to use that library then check the documentation. If you want to know how to find multiple files in a directory you can use glob — Unix style pathname pattern expansion — Python 3.10.5 documentation[^].

But without a specific detailed technical question it is difficult to know what more to say.
Ionela Daniela Gal 8-Jun-22 11:24am
   
I have started to write this piece of code :
from openpyxl import load_workbook

data_file = 'C:\\Users\IGal\\Desktop\\OneDrive_1_6-7-2022.xlsx'

# Load the entire workbook.
wb = load_workbook(data_file)

# Load one worksheet.
ws = wb['Data']
all_columns = list(ws.columns)
all_rows = list(ws.rows)
#print(f"Found {len(all_columns)} columns of data.")
#print(f"Found {len(all_rows)} rows of data")

#print("\nFirst columns of data:")
#print("\nFirst rows of data:")
for row in all_columns[:0]:
for columns in all_rows[:0]:
#print(columns)
#print(rows)

# Load the entire workbook.
wb = load_workbook(data_file)

# Load one worksheet.
ws = wb['C:\\Users\\IGal\\Desktop\\SWKPIsCW.xlsx']
all_rows = list(ws.rows)

for cell in all_rows[0]:
print(cell.value)

# Pull information from specific cells.
for row in all_rows[1:455]:
state = row[0].value
percent_cw = row[2].value
#percent_cw = row[4].value

print(f"{state}")
print(f" {percent_cw}% of cw kpi")
print(f" {percent_cw}% of cw kpi")

# Pull information from specific cells.
for row in all_rows[1:455]:
SWC = row[1:455].value
percent_SWC = int(round(row[1:455].value, 2) * 100)
percent_cw = int(round(row[1:455].value, 2) * 100)


print(SWC)

My problem is that I a stuck here and I don't know how to extract the data I need and added to the master excel sheet.
Richard MacCutchan 8-Jun-22 11:26am
   
I am sorry but this is the Quick Answers forum for specific questions. There is not space to provide programming tutorials. The openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.0.10 documentation[^] provides the information you need.
Ionela Daniela Gal 8-Jun-22 11:28am
   
Thank you :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900