Python Forum
Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Multiple excel files
#1
Hello,

I am so new at using python that I don’t know how to start the code.

I have 26 excel files (file1, file2, file3, etc) and I want to create a for loop that will open each file one at a time and copy sheet 1 and paste it into a new excel file called template.xlsx sheet 1, and then save the template as “new file 1”. Then open file2 copy data from sheet1 and paste it into template.xlsx sheet1 and do save as “new file 2”. And continue to do this process 24 more times.

Thank you,
Kristen
Reply
#2
A good start would be to look at a package called "Pandas".
Python + pandas makes it easy to open excel files.

Paul
It is more important to do the right thing, than to do the thing right.(P.Drucker)
Better is the enemy of good. (Montesquieu) = French version for 'kiss'.
Reply
#3
You can put all the file names in a list (say names).
Next, you import pandas(install if not installed already.
import a files and merge them together.
Next convert this dataframe to excel file back.Here is an example.
names=["file1.xlsx",file2.xlsx"]
for i in files:
    df=pd.read_excel(i,sheet_name='Sheet1') 
    df=df.megre(df1) #you can modify this according to your files
df.to_excel("output.xlsx")  
This should give you an idea to solve your problem.
Reply
#4
I feel like maybe I didn't explain what I'm trying to do very well. Sorry, super new to this.

I have 26 excel files: File1 File2 File3 File4 Etc...

And I have one template.xlsx file that has formulas in it.

I want to create a for loop that will open File1 copy all the data inside sheet1, (A1:N) N cell always varies in range.

Then open the template file and paste the copied data to sheet1 (B13:O). Then do a save as “new file 1”.

Then repeat this process 25 more times. Open file2 copy data from sheet1 (A1:N), open template paste data to sheet1 (B13:O) save as “new file 2”.

I’ve already downloaded appropriate packages.
Reply
#5
Well, however you describe it, your best bet is to
import pandas, and the many examples available will
show you exactly how to do what you want.

paul
It is more important to do the right thing, than to do the thing right.(P.Drucker)
Better is the enemy of good. (Montesquieu) = French version for 'kiss'.
Reply
#6
I have googled for day's and everything I try doesn't do what I want. I'm just looking for some help on how to write code for this. I figured a python forum would be the best place for this.
Reply
#7
an example of a for loop to open 26 files
for number in range(1, 27):
    print(f'open file{number}')
    # code to deal with the excel file
Output:
open file1 open file2 ... ... ... open file25 open file26
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Copy Paste excel files based on the first letters of the file name Viento 2 347 Feb-07-2024, 12:24 PM
Last Post: Viento
  python convert multiple files to multiple lists MCL169 6 1,436 Nov-25-2023, 05:31 AM
Last Post: Iqratech
  splitting file into multiple files by searching for string AlphaInc 2 815 Jul-01-2023, 10:35 PM
Last Post: Pedroski55
  Merging multiple csv files with same X,Y,Z in each Auz_Pete 3 1,085 Feb-21-2023, 04:21 AM
Last Post: Auz_Pete
  unittest generates multiple files for each of my test case, how do I change to 1 file zsousa 0 918 Feb-15-2023, 05:34 PM
Last Post: zsousa
  Find duplicate files in multiple directories Pavel_47 9 2,926 Dec-27-2022, 04:47 PM
Last Post: deanhystad
  How to loop through all excel files and sheets in folder jadelola 1 4,331 Dec-01-2022, 06:12 PM
Last Post: deanhystad
  Creating csv files from Excel file azizrasul 40 5,327 Nov-03-2022, 08:33 PM
Last Post: azizrasul
  Extract parts of multiple log-files and put it in a dataframe hasiro 4 2,022 Apr-27-2022, 12:44 PM
Last Post: hasiro
  Search multiple CSV files for a string or strings cubangt 7 7,842 Feb-23-2022, 12:53 AM
Last Post: Pedroski55

Forum Jump:

User Panel Messages

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