Import, sort and extract multiple excel files into 1 table/vector

Hi I have 12 .csv files (1 for each month) with a fair amount of data in each, although they are presented the same way. I want to bring in all 12 files into MATLAB, and store the same 3 columns of data (columnns 2-4, rows 6-36(or end)) from each file sequentially in 3 vectors date, min, max. I have been researching and struggling all morning, and the below code is the best I have

% tell MATLAB to look for all .csv files in currently directory
files15 = dir('*.csv');
% Loop the same amount of times as number of files
for i = 1:numel(files15)
    % store the file name in a column cell
    x(:,i) = double(files15(i).name)
    % Set a placeholder inside the file name to remove string
    fNum = find((x),'TEST')
    % Extract the number from the file name
    out(:,i)=(fNum(fNum+5:end-4))
    % sort files numerically 1-12
    neworder = sort(out)
    % import numbers and text as separate components for each file
    [import15,import15t] = xlsread (files15);
    % Store all dates in one column
    d6 = import5t(7:end,2);
    % Store all min and max temperatures in two columns
    Tmin6 = import5(1:end,1);
    Tmax6 = import5(1:end,2);
end

I think I am making it more complicated than it needs to be, any suggestions on the simplest way to achieve this?

Thanks in advance Anthony

[Image Analyst]

Can you attach 2 or 3 of the files so people can try to help you?

[Anthony Cook]

Sorry, please see attached. It is for an assignment so I don't want a copy paste code, just some pointers in the right direction or which functions to use/try. Thanks in advance

[dpb]

You'd have to modify the sscanf line a little in my posting; didn't mention about the YYYY- string before. Easy enough to do use

fmt='TEST %d-%d.csv';

and can return both values.

As a way to avoid all this grief about order, any time you create files sequentially such as this, use a format when creating the file names that includes the preceding '0's in the number fields so that the files are sorted numerically as well as lexically automagically --

fn=sprintf('TEST %d-%02d.csv',year,mo);

will produce

>> fn=sprintf('TEST %d-%02d.csv',2017,8)
fn =
  'TEST 2017-08.csv'
>>

etc., and then dir will return a list of files sorted in the order desired already.

[Anthony Cook]

Thank you for your help. I agree with just renaming the files to be in order, but not allowed in this case :) Thanks again

Answer 1

[dpb]

Good start but have made it a little more complicated than need...to get the files in numerical instead of lexical order

d=dir('*.csv');
[~,ix]=sort(cellfun(@(s) sscanf(s,'TEST%d.csv'),cellstr({d.name})));
d=d(ix);

Now, as IA suggests, it would be good to know what the format of the files actually is; I'm guessing there are 6 header lines from the indexing expression for the dates so something like

dates=[];
data=[];
for i=1:length(d)
  % import numbers and text as separate components for each file
  [v,t] = xlsread(d(i).name);
  dates=[dates;t(7:end,2)];
  data=[data;v];
end

Probably this would be a good application for a table and readtable, but need the details of the data structure.

[Anthony Cook]

Thank you, I have managed to get all 365 days of information into 3 variables. For sorting the data, I can see what your first code is doing but I still can't seem to sort the data. There is 12 files from July to July. So MATLAB reads October first (10) over August (8). e.g.

TEST 2017-8
TEST 2017-9
TEST 2017-10

I have also tried using regexp but that doesn't seem to be working either

 [~, index] = sort( str2double( regexp( {d.name}, '\d+', 'match', 'once' )))
 d = d(index) ;

I see there is a FEX submission 'natsortfiles' but I would like to solve using in house MATLAB functions.

[dpb]

See the comment above -- with both the year/month in the file name you've got to do some more work...the above format doesn't match the filename string. If the year were always constant you could just encode it into the format string and the rest would still work--assume you're looking for a known year to process, then enter the year as input at the beginning and then

infmt=num2str(yr,'TEST %d-*.csv');    % wild card string for given year
rdfmt=num2str(yr,'TEST %d-%%d.csv');  % fmt for given year read month
d=dir(infmt);
[~,ix]=sort(cellfun(@(s) sscanf(s,rdfmt),cellstr({d.name})));
d=d(ix);

If you were to want to do multiple years, then it gets a little more complicated in that you have to return both year and month and sort both.

I'm sure regexp can do it; I'm just not practiced enough in writing the parsing expressions to even give it a go; just not an area I've made any attempt to learn the syntax of, sorry. :)

[dpb]

ADDENDUM The line

d=d(idx);

does the sort for you by rearranging the dir() structure in numeric order of the files. Thus, when you iterate through the reordered structure, you'll get them in order.

Why it didn't work before is that I thought the filenames were of the form 'TEST-NN.csv' not 'TEST YYYY-NN.csv' so the format used to read the month number was wrong and thus the sort would fail as would get empty vector for n. Unfortunately, that's a silent failure so no error was generated as I didn't include any error handling code to test for the case.

[Anthony Cook]

Thanks again for your comments.

[dpb]

Glad to help...just happened to notice the comment and dawned on me you may have not understood the underlying cause of the failure.