MATLAB Answers

Combining and reading data from Excel (.xlsx) into Matlab

27 views (last 30 days)
BenL
BenL on 12 Jan 2017
Answered: Aaditya Kalsi on 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 Comments

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 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.
As for reading all the files, there are many ways to do this. One old style option:
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 Comments

Sign in to comment.

More Answers (1)

Aaditya Kalsi
Aaditya Kalsi on 18 Jan 2017
I believe the simplest way you can achieve this is with spreadsheetDatastore .
ssds=spreadsheetDatastore('folderName/*.xlsx');
mergedData = readall(ssds);

  0 Comments

Sign in to comment.