Python Forum
Panda Data Frame to Existing Multiple Sheets
Thread Rating:
  • 2 Vote(s) - 3.5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Panda Data Frame to Existing Multiple Sheets
#1
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.

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
Reply
#2
Hi All,

can anyone please guide me on this how can i achieve my requirement.

please let me know if my explanation not in understandable manner.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Grouping in pandas/multi-index data frame Aleqsie 3 606 Jan-06-2024, 03:55 PM
Last Post: deanhystad
  Filtering Data Frame, with another value NewBiee 9 1,329 Aug-21-2023, 10:53 AM
Last Post: NewBiee
  Exporting data frame to excel dyerlee91 0 1,604 Oct-05-2021, 11:34 AM
Last Post: dyerlee91
  Pandas Data frame column condition check based on length of the value aditi06 1 2,655 Jul-28-2021, 11:08 AM
Last Post: jefsummers
  Adding a new column to a Panda Data Frame rsherry8 2 2,082 Jun-06-2021, 06:49 PM
Last Post: jefsummers
  grouped data frame glitter 0 1,576 Feb-02-2021, 11:22 AM
Last Post: glitter
  how to filter data frame dynamically with the columns psahay 0 2,377 Aug-24-2020, 01:10 PM
Last Post: psahay
  Dropping Rows From A Data Frame Based On A Variable JoeDainton123 1 2,186 Aug-03-2020, 02:05 AM
Last Post: scidam
  How to shift data frame rows of specified column Mekala 0 1,858 Jul-21-2020, 02:42 PM
Last Post: Mekala
  HELP- DATA FRAME INTO TIME SERIES- BASIC bntayfur 0 1,732 Jul-11-2020, 09:04 PM
Last Post: bntayfur

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020