Update SQL output data into Existing Excel in respective sheet

[naveedraza]

Hi All,

My Name Is Mohammad Naveed and this is my First post.

i am new to Python programming and seeking for some help/guidance in correcting my python code.

Here my query is.
1. I have one Excel file which has (7 Tabs).
2. I have one Folder which contains 7 different text file and each text file contains respective Tab SQL Query and each text file name is same as the Tab Name which is available in Excel File.

i have a written a Python code to loop through all the text file one by one and execute that each text file sql query and whatever data will come in output that output data should dump into existing excel file in that respective sheet/tab. i am using pandas to do this however code is working fine but while updating data into excel pandas is removing all existing sheets from file and updating only current output data into excel file.

Example: if python code execute a text file(Filename: Data) and after executing this SQL query we got some data and this data should dump into excel file (sheetname: Data).

[naveedraza]

here is my code

import pypyodbc
import pandas as pd
import os
import ctypes
from pandas import ExcelWriter
fpath = r"C:\MNaveed\DataScience\Python Practice New\SQL Queries"
xlfile = r"C:\MNaveed\DataScience\Python Practice New\SQL Queries\Open_Case_Data.xlsx"
cnxn = pypyodbc.connect('Driver={SQL Server};Server=MyServerName;Database=MyDatabaseName;Trusted_Connection=Yes')
cursor = cnxn.cursor()
 
for subdir, dirs, files in os.walk(fpath):
    for file in files:
        #print(os.path.join(subdir,file))
        filepath = os.path.join(subdir,file)
        #print("FilePath: ", filepath)
 
        if filepath.endswith(".txt"):
            if file != "ClosedAging_Cont.txt":
                txtdata = open(filepath, 'r')
                script = txtdata.read().strip()
                txtdata.close()
                cursor.execute(script)
                if file == "ClosedAging.txt":
                    txtdata = open(os.path.join(subdir,"ClosedAging_Cont.txt"), 'r')
                    script = txtdata.read().strip()
                    txtdata.close()
                    cursor.execute(script)
 
                col = [desc[0] for desc in cursor.description]
                data = cursor.fetchall()
                df = pd.DataFrame(list(data),columns=col)
 
                #save_xls(df,xlfile)
 
                writer = pd.ExcelWriter(xlfile)
                flnm = file.replace('.txt','').strip()
                df.to_excel(writer,sheet_name=flnm,index=False)
                writer.save()
 
                print(file, " : Successfully Updated.")
            else:
                print(file, " : Ignoring this File")
        else:
            print(file, " : Ignoring this File")
 
ctypes.windll.user32.MessageBoxW(0,"Open Case Reporting Data Successfully Updated","Open Case Reporting",1)
 

[naveedraza]

Anyone can help me out with this query...

[naveedraza]

Hi All,

anyone can help me out with this query..