1

I am racking my brain here and have read a lot of tutorials, sites, sample code, etc. Something is not clicking for me.
Here is my desired end state.

  1. Select data from MSSQL - Sorted, not a problem
  2. Open an Excel template (xlsx file) - Sorted, not a problem
  3. Export data to this Excel template and saving it with a different name - PROBLEM.

What I have achieved so far: (this works)

  1. I can extract data from DB.
  2. I can write that data to Excel using pandas, my line of code for doing that is: pd.read_sql(script,cnxn).to_excel(filename,sheet_name="Sheet1",startrow=19,encoding="utf-8")
  3. filename variable is a new file that I create every time the for loop runs.

What my challenge is:

  1. The data needs to be export to a predefined template (template has formatting that must be present in every file)
  2. I can open the file and I can write to the file, but I do not know how to save that file with a different name through every iteration of the for loop In my for loop I use this code:

#this does not work

pd.read_sql(script,cnxn)
writer = pd.ExcelWriter(SourcePath)  #opens the source document
df.to_excel(writer)
writer.save() #how to I saveas() a different file name?????

Your help would be highly appreciated.

Dawid Mocke
  • 11
  • 1

1 Answers1

0

Your method is work. The problem is you don't need to write the data into excel file right after you read the data from the database. My suggestion is first read the data into different data frame.

df1 = pd.read_sql(script)
df2 = pd.read_sql(script)
df3 = pd.read_sql(script)

You can then write all the dataframe together to a excel file. You can refer to this link.

I hope this solution can help you. Have a nice weekend

Stack
  • 25
  • 5