+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: {SOLVED]Help getting formula value in Excel to a csv (/thread-34951.html)
{SOLVED]Help getting formula value in Excel to a csv - Pedroski55 - Sep-18-2021
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??
RE: Help getting formula value in Excel to a csv - Pedroski55 - Sep-20-2021
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.