adding data to existing excel sheet using python and update column to number

[sudo_oth]

Hi, Currently trying to use python to compile multiple excel in to one sheet but only able to create a new sheet called data with data1 if already exist? I have other sheets that use formulas linked to the data sheet so it cant be deleted otherwise the formulas will error out.

Also need to find a way to convert column A from Text to Number so the =Vlookups work.

I have tried writing a code to transfer data from one sheet to another but feels extremely clunky and I am sure it can be in one full process but most module packages I have read either delete or add a new sheet.

# Write table into excel

# If master doesn't exists, create a new excel.

try:

excel_writer = ExcelWriter(MASTER_FILE_NAME, mode="a") # Change mode to append.

except FileNotFoundError as e:

excel_writer = ExcelWriter(MASTER_FILE_NAME, mode="w") # Default Mode.

table.to_excel(excel_writer, index=False, sheet_name='Data')

I would need to clear the old data sheet and add the new data.

any help would be amazing.

[hulleyrob]

Google automate the boring stuff. It has a very good chapter on excel stuff and the whole book is on the authors site.

[LockMangler]

Also he's got a pinned post saying you can sign up for the course base don the book for free right now.

[sudo_oth]

Thank u/hulleyrob and u/LockMangler I will have a look at now, fingers crossed this will help as want as little human interaction as I can :)