MATLAB Answers

reading multiple excel files

37 views (last 30 days)
I have afolder with 150 excel file and I want to read all of them one after another and save them in last in another excel sheet

  2 Comments

Azzi Abdelmalek
Azzi Abdelmalek on 7 Jun 2015
Can you give more details about your data in each Excell file
Ahmed Khalifa
Ahmed Khalifa on 7 Jun 2015
every excel file has one sheet with 4 columns A B C D

Sign in to comment.

Accepted Answer

Azzi Abdelmalek
Azzi Abdelmalek on 7 Jun 2015
folder='E:\fold1'
d=dir(folder)
e={d.name}
f=e(~cellfun(@isempty,regexp(e,'.+(?=\.xlsx)','match')))
for k=1:numel(f)-1
data{k,1}=xlsread(f{k})
end
M=cell2mat(data)
xlswrite('new_file',M)

  1 Comment

Ahmed Khalifa
Ahmed Khalifa on 7 Jun 2015
Thanks soooo much alittle nother question ,sorry if i have many excel files with 11 columns for every one and I want to read only column 5,6,7 and write them for all files in only one single file.

Sign in to comment.

More Answers (1)

Image Analyst
Image Analyst on 7 Jun 2015
Don't use xlsread() for this unless you're prepared to wait a very long time. If you have Windows, you can use ActiveX and get this done in a few seconds. With xlsread(), each time you call it, it has to launch Excel, open your workbook, haul over the data (probably using ActiveX), and finally shut down Excel. With ActiveX, you launch Excel just once, and shut it down just once, so it will be about 150 times faster for 150 workbooks. I attach a demo on how to read and write Excel files with ActiveX. If I have more than 2 files, I never use xlsread or xlswrite - I use ActiveX. If I want more precise control over formatting (decimal places, alignment, coloring, borders, etc.) I use ActiveX, or use a template. It's not hard to learn so don't be afraid - it's just like any other object oriented programming you're used to.

  6 Comments

Show 3 older comments
Image Analyst
Image Analyst on 2 Apr 2020
Maxwell: It should have worked, and it did for me when I tried it. But anyway, since I was taking another look at it, I made some improvements to it. Please download the latest version, attached here.
If you still have trouble, then attach your m-file and I'll try it.
naty liber
naty liber on 27 May 2020
hey, there is an error saying that excel is already open but your code opens it...
Image Analyst
Image Analyst on 27 May 2020
I ran it and it ran fine. The message just suggests one possible error, not necessarily the exact reason. The reason is that the Excel variable no longer exists by the time you got to the line of code that called xlswrite1. Did you perhaps set a breakpoint somewhere and then type clear or clear all? Did you shutdown Excel after the breakpoint but before calling xlswrite1()? Again, it works fine for me. If it doesn't for you then run PSR (type that into the Windows search field and run the Steps Recorder) to do screen captures and upload your zip file so I can see what you did.

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!