By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,601 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,601 IT Pros & Developers. It's quick & easy.

Update SQL output data into Existing Excel in respective sheet

P: 4
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).
Aug 13 '17 #1
Share this Question
Share on Google+
3 Replies


P: 4
here is my code

Expand|Select|Wrap|Line Numbers
  1. import pypyodbc
  2. import pandas as pd
  3. import os
  4. import ctypes
  5. from pandas import ExcelWriter
  6. fpath = r"C:\MNaveed\DataScience\Python Practice New\SQL Queries"
  7. xlfile = r"C:\MNaveed\DataScience\Python Practice New\SQL Queries\Open_Case_Data.xlsx"
  8. cnxn = pypyodbc.connect('Driver={SQL Server};Server=MyServerName;Database=MyDatabaseName;Trusted_Connection=Yes')
  9. cursor = cnxn.cursor()
  10.  
  11. for subdir, dirs, files in os.walk(fpath):
  12.     for file in files:
  13.         #print(os.path.join(subdir,file))
  14.         filepath = os.path.join(subdir,file)
  15.         #print("FilePath: ", filepath)
  16.  
  17.         if filepath.endswith(".txt"):
  18.             if file != "ClosedAging_Cont.txt":
  19.                 txtdata = open(filepath, 'r')
  20.                 script = txtdata.read().strip()
  21.                 txtdata.close()
  22.                 cursor.execute(script)
  23.                 if file == "ClosedAging.txt":
  24.                     txtdata = open(os.path.join(subdir,"ClosedAging_Cont.txt"), 'r')
  25.                     script = txtdata.read().strip()
  26.                     txtdata.close()
  27.                     cursor.execute(script)
  28.  
  29.                 col = [desc[0] for desc in cursor.description]
  30.                 data = cursor.fetchall()
  31.                 df = pd.DataFrame(list(data),columns=col)
  32.  
  33.                 #save_xls(df,xlfile)
  34.  
  35.                 writer = pd.ExcelWriter(xlfile)
  36.                 flnm = file.replace('.txt','').strip()
  37.                 df.to_excel(writer,sheet_name=flnm,index=False)
  38.                 writer.save()
  39.  
  40.                 print(file, " : Successfully Updated.")
  41.             else:
  42.                 print(file, " : Ignoring this File")
  43.         else:
  44.             print(file, " : Ignoring this File")
  45.  
  46. ctypes.windll.user32.MessageBoxW(0,"Open Case Reporting Data Successfully Updated","Open Case Reporting",1)
  47.  
Aug 13 '17 #2

P: 4
Anyone can help me out with this query...
Aug 13 '17 #3

P: 4
Hi All,

anyone can help me out with this query..
Aug 14 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.