Sep-18-2021, 10:57 PM
(This post was last modified: Sep-20-2021, 12:20 AM by 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
I can't think of a trick to make pandas get the numerical value of the formula in column your_score.
Any tips please??
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??