MATLAB: How work with Datastore from two or more file excel sheets

dataSpreadsheet Link

Hi.

I want to work with many Excel files, and I tried to work with the Data Store. The problem is that I need data from two excel sheets, with different amounts of columns, and that the ds file groups them into a single table. the fixed work of the leaves are the same, only the columns are different.

How should I write the Datastore code to read both sheets and create a single tall array for everything?

Thank you.

Best Answer

Hi,

I don't think that is possible using Datastore, but one possible workaround could be:

path = 'Source';
files = dir(fullfile(path, '*.xlsx'));
result = table();         
filecontent = readtable(fullfile(path, files(1).name)); 
filecontent2 = readtable(fullfile(path, files(2).name)); 
vars1 = [filecontent.Properties.VariableNames]
vars2 = [filecontent2.Properties.VariableNames]
R = {[vars1 vars2]}
result = array2table([table2array(filecontent) table2array(filecontent2)],'VariableNames',R{:});

Hope this helps!

Related Solutions

MATLAB: Calculating hourly averages, monthly averages and mean, median for long term data

Hi Devendra,

Here's an example how to calculate monthly mean with your sample data. But before we get to that example, there's some clean-up to do.

How did you import these data? Many of the columns in your 'filecontent' table are numerical but being stored as strings. For example, '5', '6', '7', instead of [5 6 7]. I suggest importing your data again and taking care of assigning the correct class for each column of data.

Another way to solve that problem is to clean it up after import but this is not the better option. Since I cannot import your data, I clean up some of it.

You've got a lot of clean-up to do with these data before you can start analysis. I've done some of the cleanup in this example (but not nearly all of it).

First, give meaningful names to the columns of your table. This will reduce mistakes during analysis and will make your code readable. This can (and should) be done during import but I've done it here:

% Give meaningful names to columns in the table
currentVarNames = filecontent.Properties.VariableNames; 
currentVarNames(1:3) = {'FileIdx', 'SampleNum', 'DateTime'}; 
filecontent.Properties.VariableNames = currentVarNames;

I assume your first row are headers but some of the values in the first row are numerical so be careful during import and be careful when you're cleaning up the dataset.

% Remove the first row (I assume they are headers)
filecontent(1,:) = [];

Here is where I converted some of your columns to numbers (from strings).

% Convert strings to numbers (other columns ignored)
filecontent.FileIdx = str2double(filecontent.FileIdx); 
filecontent.SampleNum = str2double(filecontent.SampleNum);

Here is where I convert your dates to "datetime objects" which are easier to work with in matlab.

% Convert date strings to datetime objects
filecontent.DateTime = datetime(filecontent.DateTime, 'format', 'yyyy/M/dd HH:mm:ss');

Now I convert you table to a timetable which allows us to use the retime() function later.

% Convert your table to a timetable
filecontentTT = table2timetable(filecontent);

Lastly, compute the mean for each month. Since some of the columns in your table are non-numerical, I've only selected variables 4-7 (which are horrible names but you'll clean that up). 'monthlyMeanTable' will be a table of unique months and the means for the 4 variables I selected.

% Compute the mean for each month for var4, var5, var6, var7
colNames = {'Var4', 'Var5', 'Var6', 'Var7'};
colIdx = ismember(filecontentTT.Properties.VariableNames, colNames); 
monthlyMeanTable = retime(filecontentTT(:,colIdx), 'monthly', 'mean');

Look at the means for 'var5'

monthlyMeanTable.var5

If you are using an earlier version of Matlab that does not have the retime() function, we can do this in a loop easily and I could show you how.

Let me know if you get stuck.

MATLAB: How to import several files of excel into a table in Matlab

http://matlab.wikia.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F

Best Answer

Related Solutions

MATLAB: Calculating hourly averages, monthly averages and mean, median for long term data

MATLAB: How to import several files of excel into a table in Matlab

Related Question