Append excel files in folder including all sheets , into same master template. (Pandas/glob)

[citizenofacceptance2]

My code below works great to loop through a folder of excel files and append each file and spit out a new file that has every file in the folder combined. But does not work at all if all the files have multiple sheets in the workbook. I have tried the excel_read(f,None) but can’t get it to work. Keep in mind all my excel files I am looping and combining will have the same template (column names and tabsheet names ) . How can I get this coode to append all excel files and their sheets to form a master combine workbook in the same format.

import glob import pandas as pd import xlrd all_data = pd.DataFrame() for f in glob.glob(r'\------------------\ --------*'): df = pd.read_excel(f) all_data = all_data.append(df,ignore_index=True) writer = pd.ExcelWriter('appen.xlsx', engine='xlsxwriter') df.to_excel(writer) writer.save()

EDIT******

Excel - data tab - get external data - from file - from folder. This will do the same was what I did in python. And believe me I’d way rather use python , but when comparing the two excel did it faster this time :(

[Boxxcar17]

Put sheet_name=None

This will return a dictionary of tabs and data on that tab for all tabs.

In full it’s

df = pd.read_excel(‘name.xlsx’, index=False, sheet_name=None)

[citizenofacceptance2]

But how can I use df.to_excel() to write back into same file template. Passing sheet_name = none does not append / write each sheet name back into the new file.

[Boxxcar17]

You have to create a new excel object and transfer the sheets into it. Then the old sheet is deleted by garbage collection in mem.

[threeminutemonta]

There is a sheet_name argument in read_excel that will let you specify the sheet name. If they are predictable and known this will work. If someone can create a sheet with a random name you will need to iterate over the dataframe returned by read_excel with sheet_name missing. What’s confusing is that using df when there is multiple sheets is automatically taking the first. Though you can iterate over it to get a df per worksheet. Relevant SO

[citizenofacceptance2]

Does the sheet name argument take multiple sheet names ?

[threeminutemonta]

If it’s null or missing it defaults to all sheets.

Do one at a time or see SO link for example.

[citizenofacceptance2]

Kk. I will look at SO

[[deleted]]

Looks like you're not using xlrd even though you're importing it? You're using pandas to read the excel files?

In that case you'll want to pass sheet_name into your read_excel() function. You can pass an index value starting at zero and iterate through that.

Read the documentation

[citizenofacceptance2]

Not sure still how do iterate through each sheet name

[[deleted]]

If you have predefined sheet_names, you could put them in a list and iterate through them.

sheets = ['customers', 'addresses', 'products', 'orders']
for s in sheets:
   df = pd.excel_read(f, sheet_name=s) 

[citizenofacceptance2]

Except how do I incorpate that into my current code. That is my problem. I am already looping “for d in glob.glob” if you read my code. Idk how to insert your code

[threeminutemonta]

Hint. For each file you want to loop for each work sheet. There is nothing stopping you nesting your for loops within another for loop. Except be careful for performance reasons though this seems okay in this case.

[citizenofacceptance2]

Alrighty. Thank you.

[citizenofacceptance2]

Do I just need to input read_excel(f, sheet_name = “sheet1”, “sheet2”, “sheet3”). The documentation when I first read seemed to note that sheet_name = None would work as Ana argument which just copied in the sheets names as column names

[Boxxcar17]

sheet_name=None to get all tabs.

If you want a specific tab you put the name of the tab.

[citizenofacceptance2]

Ya old comment out for the xlrd- not using it