excel files

2 vues (au cours des 30 derniers jours)
ricco
ricco le 15 Nov 2011
I have a folder with several excel spreadsheets, each excel spreadsheet contains data for a certain time period i.e. the first spreadsheet contains data from day 1:20, then the second contains data from 21:40 and so on towards day 365 (so I have 19 spreadsheets in total). Each of the spreadsheets then contain several worksheets which correspond to different days of that time period but with the first and last worksheet corresponding to notes. So, I need to take the worksheets between the first and last worksheets and make them into one matrix. After making each spreadsheet into one matrix I would then like to combine the matrices into one matrix (all will have the same number of columns).
I am totally stuck so any ideas you have will be much appreciated.
thanks

Réponses (1)

Fangjun Jiang
Fangjun Jiang le 15 Nov 2011
Show your code and show where you were stuck.
xlsread() can read individual sheet. You just need two for-loops nested to read the data and concatenate them together.
  2 commentaires
ricco
ricco le 15 Nov 2011
Ive got it to work now, although it does take a long time to run, which I guess is due to size of the matrix (131400x33).
The code is:
files = dir('*.xls');
for i=1:length(files);
File_Name{i}=files(i,1).name;%Removes the file names from 'files'
data{i}=importdata(File_Name{i});%import the data using 'File_Name'
end
for i=1:length(files);
q{i}=struct2cell(data{1,i}.data);%convert the structure into a cell
q{i}=q{i}(1:length(q{1,i})-1);%removes the worksheet with the notes which is always the last worksheet).
q{i}=cell2mat(q{i});%converts the cell array into matrices
Final_data=cell2mat(q');%finally create a matrix containing the
%entire dataset
end
M=cell2mat(q');
So, now I have all of the data within one matrix and all seems fine. I am having trouble with the column for time though, the first column should be time as in 00:05 towards 23:55 but after importing them into matlab they change to 0.0035 and so on. How is it possible to keep the time as it is, I tried using datestr but that just converted the column into a number of columns where 0 was the first then 0 again, then : and so on.
thanks
ricco
ricco le 15 Nov 2011
*avoid the last M=cell2mat(q');*

Connectez-vous pour commenter.

Community Treasure Hunt

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

Start Hunting!

Translated by