CODEWITHSUNDEEP
pythonpandasdatabaseextract
Sam
Sam

Reputation: 229

How to extract the same sheet from many Excel workbooks

I am trying to import the same sheet from many Excel workbooks so that they become many data frames, but am not sure how to do so in the right way. The specific sheet to import is 'Sheet1' from every workbook. I am avoiding using paths, and instead have a pop up box where I can select all of the workbooks at once. Is there a straight forward way to accomplish importing the specific sheets?

I have the following code, which allows me to select the workbooks, but can't get to the step of importing the sheets and creating data frames to work with. I imagine a for loop is needed, but can't get it:

import tkinter as tk
from tkinter import filedialog
from tkinter import messagebox
import pandas as pd

root = tk.Tk()
root.withdraw()
root.databases =  filedialog.askopenfilenames(initialdir = "C:/",title = "Select the location of your files (you may select multiple files)", filetypes = (("Excel Files","*.xlsx"),("all files","*.*")))
db_list = root.tk.splitlist(root.databases)

Upvotes: 0

Views: 91

Answers (1)

Mose Wintner
Mose Wintner

Reputation: 308

Here is a pure pandas solution.

import pandas as pd
import glob
import os

workbook_dir = 'C:\mydir'
workbook_fnames = glob.glob(os.path.join(workbook_dir,'*.xlsx'))
sheet_name = 'mysheet'

workbooks = {fname: pd.read_excel(fname, sheet_name=sheet_name) for fname in workbook_fnames}

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

Upvotes: 1

Related Questions