I have a simple script here that is:
- reading through a folder for excel files.
- Reads each one and combines the multiple excel files into a main excel file.
My issue here is that two of those fields in the excel file are calculated based off values in a couple other fields....and they do not populate when they write to the new excel file
I saw that you could calculate them in python but have NO idea how to do that..
In the EXCEL FILE on the field
=IF(I2="value1","Value2",IF(I2="Value3","Value4",IF(I2="Value5","Value4","")))
Can I loop through the new Excel file and create an if field reading the Column names and populate this data?
Im not sure how to Update the newly created excel file after its created or should I do it while its reading the individual excel files ???
Thanks in Advance.... Cheers
# READ EXCEL FILE AND LOOP THROUGH EACH ROW VALUE
if row['Field1'].month == "value1":
# set Field 4 = "some value 1"
elif row['Field1'].month == "value2":
# set Field 4 = "some value 2"
else:
# set Field 4 = "some value 3"
import glob
import pandas as pd
import warnings
# specifying the path to csv files
path = r"C:\Users\CreateMasterExcelFile"
# csv files in the path
file_list = glob.glob(path + "/*.xlsx")
# list of excel files we want to merge.pd.read_excel(file_path) reads the excel data into pandas dataframe.
excl_list = []
excl_nameList = []
for file in file_list:
warnings.simplefilter(action='ignore', category=UserWarning)
print(file)
excl_nameList.append(file)
excl_list.append(pd.read_excel(file, index_col=None, na_values=['NA'], usecols="A:D,H,I"))
# create a new dataframe to store the
# merged excel file.
excl_merged = pd.DataFrame()
for excl_file in excl_list:
# appends the data into the excl_merged
# dataframe.
excl_merged = excl_merged.append(
excl_file, ignore_index=True)
# exports the dataframe into excel file with
# specified name.
excl_merged.to_excel('total_records.xlsx', index=False)
