Doug’s Pick of the Week
August 13th, 2007
Video series: Reading Excel data into MATLAB with a GUI
I 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
Refactor to script into a function
Build the GUI
12:09 pm |
Posted in Excel, GUI or GUIDE, Practical example, Video |
Permalink |
You can follow any responses to this entry through the RSS 2.0 feed.
You can skip to the end and leave a response. Pinging is currently not allowed.
Leave a Reply
|
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