How to format Excel
6 views (last 30 days)
Show older comments
Khairul Afifi
on 29 Nov 2014
Commented: Khairul Afifi
on 30 Nov 2014
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/146267/image.png)
I would like to ask the format for this type of file. Here are the computer program I did so far.
format = '%02d/%02d/%04d %02d:%02d %s %9.2f %9.2f %9.2f %9.2f %9.2f %9.2f';
[dd,mm,yyyy,hh,mm,stn,T,L,A,N,E,Z] = xlsread(filename,format,'headerlines',1);
but it state in the command window,
??? Error using ==> xlsread
Too many output arguments.
Error in ==> ReadExcelv7 at 10
[dd,mm,yyyy,hh,mm,T,L,A,N,E,Z] = xlsread(filename,format,'headerlines',1);
0 Comments
Accepted Answer
Image Analyst
on 29 Nov 2014
You should not use xlsread() for that. If you have R2013b or later you should use readtable:
t = readtable(filename);
You will get a "table" - a special kind of variable meant exactly for the kind of data you have there. It's much simpler and much easier to deal with. You will avoid a bunch of headaches like you would have if you used xlsread() and cell arrays.
3 Comments
Image Analyst
on 29 Nov 2014
No, you're stuck using xlsread(). Use the rawdata, the 3rd return argument, and extract each column one at a time.
column2 = raw(:,2);
and so on.
More Answers (1)
Geoff Hayes
on 29 Nov 2014
Khairul - please review the documentation/usage for the xlsread function. You will be able to read the data from the Excel spreadsheet, but only once you have read the data, can you massage it into the format that you wish.
Calling xlsread as
xlsread(filename,format,'headerlines',1);
is invalid because you cannot supply a format nor indicate how many header lines there are. As for the output,
[dd,mm,yyyy,hh,mm,stn,T,L,A,N,E,Z] = xlsread(filename,format,'headerlines',1);
this function only allows you to split the output data into numeric, text, or raw data. You cannot break apart (for example) the date column into its day, month, year components. If you wish to do that, you would need to do so on the data once you have read it in.
I suggest that you start with
[~, ~, rawData] = xlsread(filename);
and then manipulate the (for example) date column by splitting it apart into its components. Note that rawData will be a cell array, and that rawData(:,1) should be an array of date strings. In order to manipulate one of these date strings into its components, try
strsplit(char(datestr(rawData(1,1),'mm,dd,yyyy,HH,MM,SS')),',')
to format the date as a comma-separated string of the month, day, year, hour, minute, and second components. We then use strsplit to split the string on the comma into a 1x6 cell array.
0 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!