Save Excel file as CSV via Python

Question

Language: Python 3.8
Platform: MacOS 11 | Windows 10
Filetypes: .xlsx | .csv.
Task: File/Format Conversion

Synopsis: My excel file has cells with functions/formulas. I want to save this file as a .csv while preserving the value of the formulas (not the actual string of the function, itself)

What works: Pause script, prompt user to open Excel > 'Save As' .csv // Excel processes the functions within the cells and preserves the values before saving as .csv

What hasn't worked: Using pandas or openpyxl to convert the excel file to a .csv (such as 'wb.save' and 'df.to_csv' // The produced .csv file does not process the function cells and instead outputs nothing within those cells.

Question: Anyway of leveraging excel's 'process the function and save the values' function within the Python script?

Thank you!

Sample Code - Pandas

df = pd.read_excel('file.xlsx')
df.to_csv('file.csv')

Sample Code - Openpyxl

wb = openpyxl.load_workbook('file.xlsx', data_only=True)
sheet = wb.active
with open('file.csv', 'w', newline="") as f:
c = csv.writer(f)
for r in sheet.iter_rows():
c.writerow([cell.value for cell in r])
wb.save('file.csv')

Sample Problem

Excel Columns:

A: ['First Initial']
B: ['Last Name']
C: ['Email']

Formula in all rows within column C:

C1: [=CONCATENATE(A1,".",B1,"@domain.net")]
C2: [=CONCATENATE(A2,".",B2,"@domain.net")]
C3: [=CONCATENATE(A3,".",B3,"@domain.net")]
etc.

Output of 'file.xlsx' through excel & 'file.csv' (via excel > 'Save As' .csv):

A1: ['j']
B1: ['doe']
C1: ['[email protected]']

Output of 'file.csv' after following the Pandas Sample Code:

A1: ['j']
B1: ['doe']
C1: ['']

if a cell does not contain a formula, the conversion outputs correct values within the cells. For the cells with formulas, the cells are empty (since .csv is just plain-text). Is there a way to replicate excel's behavior of running the functions first > save output value into cell > save as .csv?

UPDATE:

So I found the issue, although not sure how to go about solving this. Pandas works as intended when I created a fresh .xlsx and tried the sample code. But it didn't work with the .xlsx in my script - and I narrowed it down to this step

The following is a snippet from my script that copies values from one excel file into another:

wb1 = xl.load_workbook('/file1.xlsx')
ws1 = wb1.worksheets[0]

wb2 = xl.load_workbook('/file2.xlsx')
ws2 = wb2.active

mr = ws1.max_row
mc = ws1.max_column

for i in range (1, mr + 1):
    for j in range (1, mc + 1):
        c = ws1.cell(row = i, column = j)
        ws2.cell(row = i, column = j).value = c.value


wb2.save('file2.xlsx')

The file ('file2.xlsx'), while seemingly opens and functions just like a regular excel file, DOES NOT preserve its values within cells that have formulas after converting it to a .csv via pandas.

The file ('file1.xlsx') however, does this just fine.

BUT, if I open 'file2.xlsx' and just simply save it (without changing anything), and then try converting it via pandas - it DOES end up preserving the values within formulas.

So there's definitely something wrong in my code (surprise, surprise) that does this. Not sure why, though.

Answer 1

SOLVED

I was able to solve my own question - posting it here for anyone else who has a similar issue (searching this problem led me believe ya'll exist, so here you go.)

Note: This only works on a Windows system, with Excel installed

import win32com.client as win32
from win32com.client import Dispatch
from win32com.client import constants as c

    excel = Dispatch('Excel.Application') # Calls Excel
    excel.DisplayAlerts = False # Disables prompts, such as asking to overwrite files
    wb = excel.Workbooks.Open("/file.xlsx") # Input File
    wb.SaveAs("/file.csv"), c.xlCSV) # Output File
    excel.Application.Quit() # Close Excel
    excel.DisplayAlerts = True # Turn alerts back on

Answer 2

This can be done using Pandas library. Here ,this might help : https://www.geeksforgeeks.org/convert-excel-to-csv-in-python/