Combining and reading data from Excel (.xlsx) into Matlab
4 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
BenL
le 12 Jan 2017
Réponse apportée : Aaditya Kalsi
le 18 Jan 2017
There are two parts of my query:
1) I have multiple .xlsx files stored in a folder, a total of 1 year's worth (~ 365 .xlsx files). They are named according to date: ' A_ddmmmyyyy.xlsx' (e.g. A_01Jan2016.xlsx). Each .xlsx has 5 columns of data: Date, Quantity, Latitude, Longitude, Measurement. The problem is, each .xlsx file consists about 400,000 rows of data and although I have scripts in Excel to merge them, the inherent row restriction in Excel prevents me from merging all the data together.
(i) Is there a way to read recursively the data from each .xlsx sheet into MATLAB, and specifying the variable name (i.e. Date, Quantity etc) for each column(variable) within MATLAB (there are no column headings in the .xlsx files)?
(ii) How can I merge the data for each column from each .xlsx together?
Thank you Jefferson
0 commentaires
Réponse acceptée
Guillaume
le 12 Jan 2017
(i) The easiest way to read an excel file in matlab is with readtable. You can specify the column names of the table after creation
t = readtable('someexcelfile.xlsx', 'ReadVariableNames', false);
t.Properties.VariableNames = {'nameofcol1', 'nameofcol2', 'etc.'};
(ii) You can vertically concatenate tables. Store them all in a cell array as you read them, then:
mergedtables = vertcat(cellarrayoftables{:});
is all that is needed.
path = 'C:\somewhere\on your drive';
filelist = dir(fullfile(path, '*.xlsx'));
filescontent = cell(size(filelist));
for fileidx = 1:numel(filelist)
filescontent{fileidx} = readtable(fullfile(path, filescontent(fileidx).name), 'ReadVariablenames', false);
end
mergedcontent = vertcat(filescontent{:});
mergedcontent.Properties.VariableNames = {'something', 'somethingelse', 'etc.'};
Alternatively, use a datastore which may make this even simpler (I'm not familiar enough with it to say).
0 commentaires
Plus de réponses (1)
Aaditya Kalsi
le 18 Jan 2017
ssds=spreadsheetDatastore('folderName/*.xlsx');
mergedData = readall(ssds);
0 commentaires
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Produits
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!