Convert XLSX to CVS in Python: Keep Values, not Formulas

Question

TLDR: How can I convert the .xlsx over to .csv and preserve the values of formulas, instead of the formulas themselves?

My code combines two .xlsx sheets together to generate emails for new org users. The first .xlsx contains a formula that concatenates the user's name and our domain, while the other .xlsx contains the queried list of new users. When combined, the newly generated .xlsx, titled 'users.xlsx' includes the desired information - but the emails generated are done so using the formula, still - not values. If asked to read data_only via pandas, it doesn't seem to work at all and no emails are generated on this newly created 'users' xlsx sheet.

This is all fine and works well, but the final step is converting the .xlsx over to .csv Because the emails are technically generated through the concatenating formula, the conversion doesn't preserve the user's emails.

How can I convert the .xlsx over to .csv and preserve the values of formulas, instead of the formulas themselves? Is this possible? Can I force the third .xlsx to preserve values only and then do the conversion?

Things I've tried (While they all successfully convert into a .cvs, the data within formulas is lost):

Lenged:

combined_xlsx_2
# The .xlsx product after combining two xlsx (user info + emails)
# This product is 'users.xlsx' - I need it converted to a .csv

Code 1:

# Read and store content
# of an excel file 
read_file = pd.read_excel (combined_xlsx_2)

# Write the dataframe object
# into csv file
filedir = combined_xlsx_2.replace("users_2.xlsx","users.csv")
read_file.to_csv (filedir, 
                index = None,
                header=True,
                encoding='utf-8')

# read csv file and convert 
# into a dataframe object
df = pd.DataFrame(pd.read_csv(filedir))

df

Code 2:

filename = (combined_xlsx_2)
filedir = (filename.replace("/users.xlsx",""))

path_to_excel_files = glob.glob(filedir)
for excel in path_to_excel_files:
    out = excel.split('.')[0]+'.csv'
    df = pd.read_excel(excel)
    df.to_csv(out)

Code 3:

wb = xlrd.open_workbook(combined_xlsx_2)
sh = wb.sheet_by_name('Sheet1')
your_csv_file = open(combined_xlsx_2.replace('.xlsx', '.csv'), 'w')
wr = csv.writer(your_csv_file, quoting=csv.QUOTE_ALL)

for rownum in range(sh.nrows):
    wr.writerow(sh.row_values(rownum))

your_csv_file.close()

Thank you for your time and assistance!

UPDATE 1:

I was able to accomplish this using 'convert-api' https://www.convertapi.com/xlsx-to-csv#snippet=python

While not what I had in mind, it will at least get me by. Still hoping there's a better solution for this. Just wanted to share this just in case anyone else had a similar question.

Convert XLSX to CVS in Python: Keep Values, not Formulas

0 Answers