File Exchange Pick of the WeekAugust 13th, 2007Video series: Reading Excel data into MATLAB with a GUII had a chance to work with a MATLAB user that was interested in bringing her Excel data into MATLAB and plotting different variables. This was a fairly standard file format, just columns of data. It was pretty easy to read in the data with XLSREAD: Note that three minutes into the video above, I use a neat cell mode trick that many might not know about. Once this was done, it was time to turn that script into a function so that it could be reused easily. Finally, the function needed a GUI to make it easy to view any piece of data from the file. The original data file, and all the files created during the movies are located with the movie files. (Data file posted with her permission) Read the Excel data 71 Responses to “Video series: Reading Excel data into MATLAB with a GUI”Leave a Reply |
|
|||
|
These postings are the author's and don't necessarily represent the opinions of The MathWorks. |
Excellent videos. I never made any GUI, so this lesson is going to be a start.
Hello,
the “Build a GUI” video is quite helpful.
For further videos, I propose to use a larger Matlab window (in particular width). Then I can see the whole line and do not have to switch to the source code.
Thanks
Gerd,
I completely agree that it would be nice to see the entire command line. The trade-off I am struggling with is I can only make the screen so wide if I want to embed the video in a web page. I could zoom out, but then the text is illegible.
I will try to use …
short commands or …
the commands can be …
continued like this …
so they all fit on …
screen at once.
If anyone has any other ideas, please let me know. I can make the videos taller and fit in the blog, but 500 pixels wide is all that fits nicely here (that is still 180 more across than I could get on YouTube)
Thanks,
Doug
Excellent Videos! This way form to learn MatLab it’s simply awesome. Great job!
Great videos Doug!
I got a problem when reproducing the Excel-plotting GUI… I guess I am missing something so the outcoming GUI stands by itself fine.
Here is what it happened:
While I was running the GUI from GUIDE or its M-file, everything was ok. When I opened MATLAB again (during a new session) and clicked the .FIG file on the Current Directory Window, the GUI appeared, but it started to produce the following errors:
—————–
handles =
fileName: ‘070924bgzFake.xls’
??? Reference to non-existent field ‘popupmenuX’.
Error in ==> PlotExcelGUI>pushbuttonLoadXLS_Callback at 140
setPopupmenuString(handles.popupmenuX, eventdata, handles)
Error in ==> gui_mainfcn at 95
feval(varargin{:});
Error in ==> PlotExcelGUI at 42
gui_mainfcn(gui_State, varargin{:});
??? Error using ==> PlotExcelGUI(’pushbuttonLoadXLS_Callback’,gcbo,[],guidata(gcbo))
Reference to non-existent field ‘popupmenuX’.
??? Error while evaluating uicontrol Callback
—————–
How can I make the .FIG file to run my GUI without getting into GUIDE each time?
Thanks. And again, great job for those videos!
Mauricio
I tried downloading the mainGui.fig, mainGui.m and the excel file so I can run it, and the gui came up just fine, but once I selected the excel file, I got some callback errors:
?? Reference to non-existent field ‘popupmenuX’.
Error in ==> mainGui>pushbuttonLoadXLS_Callback at 130
setPopupmenuString(handles.popupmenuX, eventdata, handles)
Error in ==> gui_mainfcn at 75
feval(varargin{:});
Error in ==> mainGui at 42
gui_mainfcn(gui_State, varargin{:});
??? Error while evaluating uicontrol Callback.
Any ideas?
Mauricio and Richard,
To run this GUI, you should run the .m file, not the .fig file. The .m file contains a lot of important initialization code that should be run.
Doug
Hi Doug,
Thanks for the prompt replay. I’m gonna ping you on something that I’m having a great deal of difficulty with. And that is, I have a text file pretty much the same format as the excel example you have. The first row is the header row (string) that specifies the names for the columns (numbers). How can I adapt your code to my needs? My goal is to come up with the gui that gives you the option to pick and choose which columns go to x and y, just like you have in your example. I think the key difference is knowing what ‘xlsread’ actually does. I’m using ‘textread’, but the real problem is the first row is a string, which ‘textread’ won’t read in. I can get ‘textread’ to read in the first column strings, but then I’ll need a separate ‘textread’ to read in the rest of the numbered data. Do you see how it complicates things?
Thanks,
Richard
Hi
Thanks a lot to you Doug Hull, I have learned a lot about MATLAB specially GUI by your video.
I am from very distance area but I am very interested in MATLAB.
In few words I am a big fan of yours
thanks again
FFA
[minor typos corrected, Doug]
Hi
Thank you very much for your videos, I am a final year electrical engineering student and your videos have helped me produce MATLAB code that will be really useful in compleating my final year project.
many thanks
Trevor
Hi,
I have a similar question about importing excel data into matlab, but about a more complicated type of file. My excel files has a lot of text near the top, and then many columns of data with x-y-z locations for 3-D points. After ALL of this data, there’s some more text, and then EMG data. I want to write a function to read through past the text, find the columns of data I’m interested in, and store them in their own variables. Do you know how I would go about doing this?
Maryam,
This is getting into reading custom formats inside of an Excel file. MATLAB gives you the ability to read an arbitrary range inside of an Excel file, and the ability to parse those results. These are the low level functions that are need to accomplish this goal. There is no built-in function that will do what you want since files can vary so much. However, you should be able to build your own.
Doug
Is this code will face problems if it used in matlab 6.5 ? because that happen to me
I mean the code used to read from excel files ..
Thanks
I want to know if it is possible to write data into an excel files using GUI Matlab program ??
Right now I am going to develop a big program that takes data from first users and make several kinds of calc as an output … excel file it is suppose to store these results (by Matlab supervision of course)… and for other users it is should to review latest results , makes same calc and then makes it is Owen results in different excel files …..
Is there any advice U can give me here?
Farasha,
I developed this code in 2007a. I just tried it in MATLAB 6.5, but it seems that XLSREAD was improved since then and this code does not work in 6.5.
For your second question, yes, you can write out Excel files. This functionality has been introduced since 6.5. You will likely want to upgrade to take advantage of these new features.
Doug
Hi,
My question is can i run an m file from excel sheet? i know i can read data from excel sheet and do calculations in matlab and then return the data to the excel sheet but this requires running of the m file. I am in the process of developing a userfriendly software and i feel combination of excel with VB will be more fit for the purpose. Please do reply.
regards
sivapriya
Sivapriya,
I think you want:
Spreadsheet link EX
http://www.mathworks.com/products/excellink/
MATLAB Builder EX
http://www.mathworks.com/products/matlabxl/index.html
Doug
I am trying to plot data in 3D. I have degrees for axis 1 across the top, degrees for axis 2 down the side, and the data (which will be the Z-axis) are what is in the matrix. How do I modify the mainGUI to incorporate x,y, and z and recognize that there isn’t a “heading” for the column 1 since that is actually one of the axis and not the data itself.
Thanks,
Jason
Jason,
This is quite a bit different from the GUI that I have here. I think you would be best served to start a new one with the knowledge you have gained in making this one.
I am not sure I understand the last part of your question. You might be better served by asking this of tech-support:
http://www.mathworks.com/support
Doug
Dear Doug,
I would like to merge data files from a few .xls files in one .mat file. Since in .xls file the max no. of column is 256 but I need 600 columns for my input data. That’s why I split data in few .xls files. I know that I can do it by cut and paste the data in .mat file but it will be time consuming. Is there any other way to do it? Please help. Thanks
regards,
Izzri
Izzri,
You can read in from several files and then concat the variables together:
a = **read file**
b = **read file**
c = [a b];
Enjoy,
Doug
Dear Doug Hull,
Hi!
I have seen your tutorials and I have to admit that I have learned a lot thanks to your efforts.
While watching the tutorials I noticed that there aren’t any which covers the table component to be used as UI in order to collect and save editable data in a file in order to eliminate the dependency of Excel. I have tried to search for a hint but failed to find any which helps. I wonder if you are kind enough to show likes of me a hint on how to save editable data from a table to a file and then load it back to the table.
Respect,
Hamid
I have not blogged it yet, but I have the video you seek on the file Exchange:
http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=19278&objectType=FILE
Enjoy,
Doug
Doug,
I have no trouble reading in files, manipulating the data and plotting. My trouble is I want to do this with quite a few data files.
Is there a way to run through all the files in a folder or directory without inputting the file names manually? Can you help me automate this process?
A.K.A.
1) I want to open a file in a folder
2) Read the data into MATLAB*
3) Manipulate it *
4) Open the next file in the folder and repeat
Matt,
I think you want this:
http://blogs.mathworks.com/pick/2008/02/26/matlab-basics-getting-a-directory-listing/
Doug
Excellent. Thanks a lot.
Hi Doug, thanks for your video about reading an Excel file from Matlab. I am pretty new to Matlab but I have been presented with a project to work on that I think Matlab will help with.
I have an Excel sheet with a lot of data that I need to make searchable for specific user-defined values. (I was thinking of putting in some user prompts). It seems like xlsread reads the entire Excel file, or a range of cells in the file. For my purposes I will only need to read one or a few cells at a time. Then I need to load a DLL for a data analysis program I have (I was thinking about using the Matlab Generic DLL Interface), and call specific functions to operate on the data, and then I was thinking about using xlswrite to print the results to a new worksheet or to the next page within the same Excel file.
For example, I run the program and I happen to be interested in Temperatures between 95 and 100. So I search for all of these values that might be in the Excel file, then let’s say I have a function in my DLL file called TEMP2. I would call that function and use it to process these temperatures.
Do you think I am on the right track? Could you possibly help me figure out how I can accomplish this?
Thank you.
Jon -
You definitely seem to be on the right track. I’ve been working with others here in MATLAB land to study the experience of new-ish MATLAB users, so I’d be happy to offer some help (in exchange for you telling us what’s easy and what’s hard for you). Drop me a line - my email is on my MATLAB Central page. (http://www.mathworks.com/matlabcentral/fileexchange/loadAuthor.do?objectType=author&objectId=919425)
-scott
Thanks Scott, I will probably send you an email.
Basically what’s hard for me is wading through the syntax when first starting out, it’s not obvious to me what certains commands do unless I can see an example of where it is used.
I am pretty good at adapting examples to my specific problem.
The main concerns I have is that the DLLs I have won’t be compatible with the Matlab DLL Interface and I’ll end up using a Fortran script (which is originally what the data software people recommend, but what do they know about Matlab?)
Hi Doug,
Following your fantastic tutorial, I try to create a GUI bringing my Excel data and doing some calculations. My excel file has 20 worksheets(20 subjects), and each worksheet has data. These are part of my code:
setPopupmenuString(handles.popupmenu1, eventdata, handles)
function setPopupmenuString(hObject, eventdata, handles)
fileName = handles.fileName;
[tpe, xlSheets] = xlsfinfo(fileName);
set(hObject, ’string’, xlSheets);
It updates all sheet names on popup menu. Then I want it automatically updates data when I select different name from popup menu. So my question is how to get data from each sheet? I tried “get(handles.popupmenu1, ‘value’)” but it doesn’t work.
Any help will be highly appreciated! Thanks.
Henry,
It is not clear to me the specific question you need answered. I think you have a pop-up menu that is correctly populated with the various sheet names from your Excel file. From there, you want to figure out the name of the sheet that was selected in the pop-up.
Value will give you an ordinal number. You need to then use this number to index into the cell array that is stored in the ’string’ property to get the actual string that represents the sheet name.
Doug
hi,
I have a question
I have a matlab code and i want to read a data from SQL server database to use it in that code
so i want to know if i can read this data? if yes, how can I do this ?
Best Regards,
Maram
Maram,
You might want to look at the Database toolbox.
http://www.mathworks.com/products/database/
Doug
I have several data files which contain some parameter like
distance and speed and each file might have different parameters and i do not include these parameters as part of the name and so when i look at the folder that i have this data in all the files have is a sequential number as part of their name but i would like to have an excel sheet which takes in the file name and parametrs like distance and speed and so when i want to know what file has what parametrs i just open the excel sheet and keep it open while i’m looking at the data. I am not sure how i would go about keeping track of what position the new parameters from a different dataset would go on the excel sheet.
I.e say its the 5th file and i would like to put its name and a distance in the distance column and a speed in the speed column, do i have to load the excel file first to see what position(line on excel sheet) i need to put the new parameters in and tehn i would have to write it to the array and reload that array to the file. Is there an easier method of doing this? Any help would be appreciated.
thanks
Hussein,
If I understand your question, you have a directory of files like this:
1.xls
2.xls
3.xls
list.xls
The numbered files have some data that represents variables, we will call them alpha and beta.
you want list to be of this form:
list.xls
fileName | alpha | beta
1.xls | 0.1 | 1.1
2.xls | 0.2 | 2.2
3.xls | 0.3 | 3.3
I think you could do something like the following:
Make a function
[fileName, alpha, beta] = getFileInfo(fileName)
This will give you the data you need to place in the list.xls
Where does the data go in the list.xls file?
Well, if you do a
fileList = dir(’*.xls’);
You will get a list of files in an array of structures. The array number for each member of the structure can be used to determine the row of list.xls to use.
You could just generate the whole .xls each time you need it bly getting the directory as mentioned above and totally remaking list.xls.
If I understand your question, this should give you some ideas.
Let me know how this goes.
Doug
I have a 100MB size text dataset starting like follows. How can I read this file in Matlab easily as it showing file is too large. I would like to do fft analysis and statistical analysis of the data too. Thanks, Niaz
IMC FAMOS
Name: c_acc
Time: 26.11.08 17:42:08
Length: 842751
Unit X-Axis: 2e-005 s
1st x-Value: 0 s
Unit Y-Axis: m/s^2
Data:
0 1.052799
0.00002 0.885308
0.00004 0.191418
0.00006 -0.370872
0.00008 -0.394799
0.0001 -0.107673
0.00012 0.107673
0.00014 0.095709
0.00016 -0.047854
0.00018 -0.155527
0.0002 -0.071782
0.00022 0.239272
0.00024 0.574254
0.00026 0.681926
0.00028 0.550327
0.0003 0.239272
0.00032 -0.167491
0.00034 -0.442654
0.00036 -0.358909
0.00038 -0.071782
Niaz,
I made a text file, of similar size, and did not have an issue.
a = rand(2500);
save foo.txt a -ascii
importdata foo.txt
How is AMTLAB telling you the file is too large? What are you doing to import the file?
you might want to try this function:
http://www.mathworks.com/access/helpdesk/help/techdoc/ref/textscan.html
-Doug
Hi,
I would like to create a 3D figure with data from excel. I can create x along row 1, y along column 1 and the z’s will correspond to the respective (x,y). How do I import this data and capture x,y,z respectively?
Thanks,
@atripathy.
http://blogs.mathworks.com/videos/2007/10/18/matlab-basics-getting-started-from-excel/
This one should help, all Excel posts are here:
http://blogs.mathworks.com/videos/category/excel/
Doug
Doug
what I need specifically is …
how do I import a matrix into matlab? In the excel sheet,
x values are in cells: A2:A30
y values are in cells: B1:X1
Z values are in cells: B2:X30
both mesh and surf require Z to be input as matrix and do not accept array input. Only plot3 does and I am not so keen on plot3
thanks,
Atripathy, for importing such a spreadsheet I would think this would work.
data = xlsread('YourSpreadsheet.xls'); %whateverx = data(2:end,1); %Mx1
y = data(1,2:end); %1xN
Z = data(2:end,2:end); %MxN
For graphing you may need to do something like this.
[Y,X] = meshgrid(y,x); %MxNNormally X comes before Y but you set them up opposite in your description, which could trip you up with dimension mismatch errors.
Does that help?
hi
i would like to know if there is a way to load all the sheets in a .xls file which have 2 specific values and get a plot of them.i need only the values at cells B36 and B6 but from every sheet.
for example sheet1:B36=5 B6=1
sheet2:B36=4 B6=3
i just need a plot of them.reading values from different sheets.
thanks
Theo,
http://www.mathworks.com/access/helpdesk/help/techdoc/ref/xlsread.html
This shows how you can specify not just the range, but also the sheet you read from. Once you do that, you would make a series of reads from the different sheets and then you could plot that data as normal.
-Doug
hi again
Ty a lot for the reply!
i need to ask something for xls files with matlab.
i used this code to load 10 sheets in a xls file but everytime i need to open the xls check the number of sheets and modify the code below. maybe there is a command or something to avoid open verytime my xls files to check the sheets?for example if i have 4 xls files i need everytime to open them check the number of sheets and modify the code which is kinda slow.there is other way to avoid this?
v=zeros(1,10);
n=size(v);
for x=1:10
v(x)=xlsread(’time.xls’,x,’B38′);
Ty again
my regards
Theo,
The SEE ALSO for the XLSREAD that I mentioned above lists
http://www.mathworks.com/access/helpdesk/help/techdoc/ref/xlsfinfo.html
That will give you the names of the other sheets in the file.
Doug
Hi, i would like to ask how can i import a set of data from excel to matlab, Fourier transform the data and use it to plot the amplitude against the number of data, N. I am new to Matlab, can you show me how can i code this? My excel contains only 2 set of data, 1 column for years and the other is the raw data for sunspot activity. I appreciate your help.
o…and the given formula is y[n]=x[n]-1/N ∑〖x[i]〗 where N=300, x[i] is the set of sunspot number and by taking the fourier transform of it then plot the amplitude against n not N. Thanks.
Sean,
This is a multi-part problem:
Import into MATLAB
FFT of data
Plot data
Which part are you stuck on, and what have you tried that does not work?
It sounds like this example in MATLAB:
http://www.mathworks.com/products/matlab/demos.html?file=/products/demos/shipping/matlab/sunspots.html
Doug
hi
i would like to convert a specific time like 01:03:55 which i load from an excel file to total seconds.
for example 01:03:55=3825seconds
maybe there is a way?
Theo, does this help?
>> 1*60^2+3*60+55
ans =
3835
>> (datenum(’1:03:55′)-datenum(’0:0:0′))*24*60*60
ans =
3835
when i run this command i get this:
(datenum(’0:01:55′)-datenum(’0:0:0′))*24*60*60
ans =
115.0000
and for the previous i get this
(datenum(’1:03:55′)-datenum(’0:0:0′))*24*60*60
ans =
3.8350e+003
hmm maybe i do something wrong?
TY alot it works!!!!!i am just rookie=)
sorry for the late reply Doug, don’t quite understand how to import the file from excel to Matlab. Do i have to save the file in any particular folder 1st? I have follow all the steps but i still cannot read anything that is useful.When i evaluate the selection it gives me the value. How it become like this? My data consists only 2 column*300 row data.
hi
i have this real time values in a xls file
13:36:31
13:36:41
when i use xlsread for each time i get
0.5670 0.5671 and with the Datestr function i get these times
13:36:28=0.5670
13:36:37=0.5671 which is kinda strange, why matlab doesnt read exactly the contents of the xls file?
Sean,
The directory that the file in does not matter, as long as the file is on the path so that MATLAB can find it. Are you able to do the example from the movie?
-Doug
Theo,
It sounds like the cell might be formatted as time or date. What happens if it is changed to something else?
Doug
hi,
the truth is that i am working on excel with matlab and i facing some problems.for example i have 2 vectors full of elements,V and t(time).At the moment my program calculate the duration of the measurement(seconds) but i need every 6min=360sec to average the elements of vector V.Also i know that every value of vector V have 3 sec time interval.i used a loop like this but its only for 6min.
u=0;
for i=1:x
if u<360
u=u+vv; %vv=3sec
v1(i)=xlsread(’time1.xls’,i,’B38′);%v1 is a vector storing my values until u<360
end
end
i need every 360 sec average the values of vector V in that time
my regards
Theo,
The problem is likely the format with which you’re displaying the date number.
format % use the default display format
y = datenum(0, 0, 0, 13, 36, 31)
datestr(0.5670, ‘HH:MM:SS’)
datestr(y, ‘HH:MM:SS’)
checkForEquality = y-0.5670
The datenum is displayed as 0.5670, but it’s not exactly 0.5670 — it’s:
format long g
y = datenum(0, 0, 0, 13, 36, 31)
In general, when trying to validate results, don’t type what MATLAB has displayed into a function call — instead, store the result in a variable and use that variable in the function call. This makes sure that you don’t lose any precision due to the display format you’re using not displaying all the decimal places.
Hi Doug,
Can you tell me how to calculate area under the curve in a plot, which is plotted using MATLAB.
Thanks
Ashish Singh
Ashish,
I think you are looking for QUAD:
http://www.mathworks.com/access/helpdesk/help/techdoc/ref/quad.html
Doug
ashish,
The answer to your question depends on whether you have a function that you evaluated to generate the data for the plot, or if you just have a set of data that you plotted. If you have a function, then use QUAD or QUADGK as Doug suggested. If you just have a set of data, use TRAPZ or CUMTRAPZ instead.
To get the two answers to agree more closely, you could change the tolerance in the QUAD call and/or sample the function more frequently in the data you pass to TRAPZ.
I found your demonstration very helpful on how to read Excel into Matlab. From Excel I’m trying to plot two different data sets against time. One data set is an order of magnitude bigger than the other so I would like to use two y axes to show both data sets on the same graph.
Best wishes,
Robert
@Robert,
Thank you for the kind words. I think you will be interested in the MATLAB fuction
PLOTYY
This will help you with your task.
Doug
@Robert,
Thank you for the kind words. I think you will be interested in the MATLAB function
PLOTYY
This will help you with your task.
Doug
Hi Doug,
Can you help me how to load a MAT file in a GUI?
For example, if I have a MAT file called test2 I can load it using
load test2.mat
If the test2.mat file contains 50 files, I can extract all the data of the file #1 using the following:
d=dataset(1);
d=load(fullfile(d.pathname, d.filename));
But this time I created a GUI and into the M-code of the GUI I’m trying to do the same in the Opening Function because I want to load the data at the beginning but I’m getting an error because it does not recognize the structure d.pathname
I appreciate your help !!
@tony,
I am not sure I understand your question. I am not sure how a .mat file can contain 50 files.
In the above code, the second line overwrites the value of d with load. If that is what you want, it is OK. This may be why your d.pathname structure is being unrecognized. Set some breakpoints there and confirm that d is what you think it is.
Doug
Doug,
Sorry if I didn’t explain well my problem. I’m analyzing bearing data and I have a MAT file that contains 984 files saved in a structure called dataset. Therefore, if I want to analyze the data from file #5 for example, in a normal M-code I just need to do the following:
load test2.mat % the name of the MAT file is test2
d=dataset(5);
d=load(fullfile(d.pathname, d.filename));
Then d contains all the data that correspond to the file #5. But now I have a GUI and in the M-code I’m trying to load the same MAT file in order to analyze the data and I couldn’t. I just want to extract all the data files from the MAT file. I hope this helps
Thank you very much
Tony Silva
Tony,
When you say the .MAT file has 984 files, you mean it has the *names* of the files.
So the real problem is that you have data in a callback in a GUI, and you are not able to pass said data to another function inside the same GUI. The fact that it came from a file originally is not particularly important.
I think that this video on GETAPPDATA and SETAPPDATA should be of interest to you.
http://blogs.mathworks.com/videos/2005/10/03/guide-video-part-two/
Depending on what you are doing with the data, and the file sizes, you might be better off just loading the data from file inside of any given callback when needed.
Doug
make sure beforing writing xlsread(’filename’) syntax, that the file exists in the current diretory, otherwise prompt out warning ,like ??? Error using==>xlsread File not found.
How can I do this if I have a third column, a z variable that I want to show in a scatter?
@Kirsten,
What part are you talking about? How to read in a another column, or how to use another input to scatter (or scatter3)
Doug