I have one Excel file with Contains pivot and I want to extract values to multiple files based on their tabs' names. Below is the code which I tried as I am new to Python. I want help in 2-3 points:
- I want to read the file 2 columns till non empty values
- write to Excel to specific cell without changing other values (as I tried and it removes all the sheets and only changed the extracted value to the file).
- I want to use this for multiple files so that I can pass the file names and it gets values in all files.
import pandas as pd
'''File from where I need data (File 1)'''
df = pd.read_excel("C:\\Users\\614155097\\OneDrive - BT Plc\\Documents\\UPS-P03\\FTE 2223 P03_v1.xlsx",skiprows=2,header=0)
df.set_index("Row Labels")
df=df.fillna(method='ffill')
print (df.iloc[0:,0:4])
'''Get tab name of File 2 to extract data from above file 1'''
print("tab")
working_file="C:\\Users\\614155097\\OneDrive - BT Plc\\Documents\\UPS-P04\\Final Working Files\\AMEA_Dalian_Workflow and Capacity Review - 2223 P04.xlsx"
tabs = pd.ExcelFile(working_file).sheet_names
f1=(tabs[0]).capitalize()
print(f1)
'''Set index of file 2'''
df1=pd.read_excel(working_file,usecols=("B:BO"),header=3)
df1=df1.fillna(method='ffill')
df1.set_index("UPS",inplace=True)
'''print(df1)
print(df1.iloc[0:,63])'''
'''getting value from file 1 on the bases of the file 2 tab name'''
data=df.loc[df[df['Row Labels']==f1].index,['Count of Name']].values[0]
print(data)
''''Setting that value to the file 2 at the required loaction'''
df1.iloc[df1.index.get_loc('Total Center FTEs reported'),-1]=data
