Jul-10-2017, 07:20 PM
(This post was last modified: Jul-10-2017, 08:05 PM by naveedraza.)
Hi All,
i am need of guidance, help as i got struck in getting data from SQL to Excel using Pandas.
here my queries is, i have one Excel workbook with 9 worksheet sheets and in a folder i have kept 9 text files and each text file has respective excel sheet tab SQL query and each text file name has same name as Excel Sheet tab name.
Task is: Python should open each text file one by one , read the SQL Query and execute it and convert the data using Panda Data Frame and write to excel sheet tab in respective sheet tab (text file name).
below is my python code, its perfectly working, however while writing data to excel sheet its deleting all the worksheets tab and writing only current data frame output data.
can anyone please help me out with this.
Hi All,
please let me know if my explanation not in a understandable way.
Thank you
Mohammad Naveed
i am need of guidance, help as i got struck in getting data from SQL to Excel using Pandas.
here my queries is, i have one Excel workbook with 9 worksheet sheets and in a folder i have kept 9 text files and each text file has respective excel sheet tab SQL query and each text file name has same name as Excel Sheet tab name.
Task is: Python should open each text file one by one , read the SQL Query and execute it and convert the data using Panda Data Frame and write to excel sheet tab in respective sheet tab (text file name).
below is my python code, its perfectly working, however while writing data to excel sheet its deleting all the worksheets tab and writing only current data frame output data.
can anyone please help me out with this.
import pypyodbc import pandas as pd import os import ctypes from pandas import ExcelWriter fpath = r"C:folder1\DataScience\folder2\SQL Queries" xlfile = r"C:\folder1\DataScience\folder2\SQL Queries\Open_Case_Data.xlsx" cnxn = pypyodbc.connect('Driver={SQL Server};Server=MyServername;Database=mydbname;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)
Hi All,
please let me know if my explanation not in a understandable way.
Thank you
Mohammad Naveed