{SOLVED]Help getting formula value in Excel to a csv

[Pedroski55]

I have an Excel file allscoresCLASS.xlsx, where CLASS might be 20BE or similar.

Column 1 is id, column 2 is name, column 3 in studentnr.

Then I have columns 4 to 19 as Week1 .... Week19

The last column is your_score, it is the sum of the scores in the columns Week1 to Week19 (e.g. =SUM(D2:V2)).

I read the student numbers and scores each week from my webpage with pymsql and put them in the timetable.

Now I need this Excel file as a CSV file, to import it back into the MySQL table allstudentsScoresCLASS, so the students can click a button on the webpage and see their scores.

I don't want to keep opening the Excel by hand and then "save as .csv" when Python can do that.

Pandas seemed a quick way to do this:

# the target file is already saved with the newest scores
# I need it as a .csv to import into MySQL on the webpage

import pandas as pd

target = f'allscores{clas}.xlsx'
pathTarget = '/var/www/mycsvfiles/'

print('Now converting to csv .... ')
newname = target.split('.')
savename = newname[0] + '.csv'
read_file = pd.read_excel (pathTarget + target)
read_file.to_csv (pathTarget + savename, index = None, header=True)

This works great, but I don't get the value in the last column, your_score. That column in the csv is empty when pandas makes the csv.

I can't think of a trick to make pandas get the numerical value of the formula in column your_score.

Any tips please??

[Pedroski55]

Ah well, wasn't so tricky, just read the Excel a dataframe, then:

df.to_csv(pathTarget + savename)

Never use pandas really, so no experience.