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

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

Answer 1

[Guillaume]

(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).

Answer 2

[Aaditya Kalsi]

I believe the simplest way you can achieve this is with spreadsheetDatastore .

ssds=spreadsheetDatastore('folderName/*.xlsx');
mergedData = readall(ssds);