Populated calculated Fields via Python

kapalczynski — Tue, 20 Dec 2022 17:32:03 GMT

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)

Re: Populated calculated Fields via Python

RhettZufelt — Tue, 20 Dec 2022 21:49:14 GMT

Does it need to be dynamic?

Seems you could open a copy of the Excel file, select ALL, Copy, Paste Special (Values).

Then feed this excel into the script as the calculated cells will be the value now, and not the formula.

topic Re: Populated calculated Fields via Python in Python Questions

Populated calculated Fields via Python

Re: Populated calculated Fields via Python