How to read a specefic column of several xls file and create a matrix

Hello,

I am a beginner in using MATLAB and I am trying to develop a code that helps me to read a specefic column of several xls file. The files are named '1_2019', '2_2019'....'12_2019' and I want to read the column C from cell C2 to cell C2900. After reading them, I want to introduce all the data in a 2900x12 matrix in order in order tu sum each column afterwards. How could I do it?

Thank you very much.

Answer 1

[Jon]

The readmatrix function can be used to read in the.Use the range option to specify the column you want from each .xls file. Type doc readmatrix on the command line for details.

Preallocate an array of zeros to hold your result, then make a for loop that reads the data from each file and saves it to a new column in your result array.

If you don't know the exact names of the files you can use the dir command to get a list of files and there names, type doc dir on the command line to get details

[Jon]

Here is a simple example that you could build off of

% parameters
range = 'B1:B5'; %  range  to read
numRows = 5; % number of rows to save

% get list of .xlsx files
list = dir('myfile*.xlsx') % use wild card to get matching file names, e.g myfile01, myfile02 etc

% determine how many files there are
numFiles = length(list);

% prellocate an array to hold the results
results = zeros(numRows,numFiles);

% loop through .xlsx files reading data from desired column specified by range
for k = 1:numFiles
    filename = list(k).name;
    % read data and store  as column in result array
    results(:,k) = readmatrix(filename,'Range',range); 
end

[José Javier Rubio Rubio]

Hello, Jon,

Thank you very much for your answer. I will try it and I hope it works.

Regards.

[Jon]

Hope you were able to get everything working. Please post more if you have any issues

[José Javier Rubio Rubio]

Hi Jon,

It works perfectly but I have a problem with it. Mi xlsx files are named as follow: 2019_1.xslx, 2019_2.xslx,....,2019_12.xlsx. When I use list = dir(), it creates a 12x1 matrix but the columns are disorganized. Instead of being from 1 to 12 (first column = 2019_1 .... twelfh column = 2019_12) they are 1-10-4-5-6-7-2-3-12-8-9-11. I have been trying to figure it out but I haven't been able to solve it. Could you help me?

Thank you very much and sorry for the inconvenience.

[Jon]

Hi

The problem is that it is sorting them in dictionary order so for example 2019_11 is next after 2019_1. Easiest would be to rename the files to include a leading zero, e.g. 2019_01, 2019_02, etc. In general if in the future you have any say in how the files are named you should always use leading zeros so they get sorted properly.

Here is one way of sorting them if you can't rename them. Probably someone could come up with something much simpler, but I think this will work

% get the list of data files
list = dir('2019*.xlsx')

% get the names of the data files
names = {list(:).name}

% preallocate array to hold the file sequence numbers
numFiles = length(names);
seqNumbers = zeros(numFiles,1);

% loop to get sequence numbers
for k = 1:numFiles
    % get the string for sequence number 
    % e.g. c = 1 x 3 cell array {'2019'},{'2'},{'xlsx'}
    c = strsplit(names{k},{'_','.'});
    % convert the string which gives the sequence number to a numerical value
    seqNumbers(k) = str2double(c{2});
end

% get the sort order
[~,isrt] = sort(seqNumbers);

% reorder the files
list = list(isrt);

[José Javier Rubio Rubio]

Hi,

I have changed the names and it works perfectly now. I feel foolish doing this kind of questions.... Very kind of you to help me.

Thank you very much.

[Jon]

Glad you have your program working. No need to feel foolish, we all help each other

[José Javier Rubio Rubio]

Hi, Jon,

Another question... I want to develop the same code but instead of naming the excel files as 2019_01 ... 2019_12 I would like to name them as follow: January 2019, February 2019 ..... December 2019. How could I do it?

I have had to change the code because I need to read two columns of the excel and it is now as below (it works perfectly but as I said I would like to change the files names to the original way).

Thanks in advance.

% parameters
range='C:D';
% get list of .xlsx files
d=dir('2019_*.xlsx');       % 'list' is built-in MATLAB function -- don't alias it...
numFiles = length(d);
energy=zeros(numFiles,2);   % allocate for the energy sums
for k = 1:numFiles
  energy(k,:)=sum(readmatrix(d(k).name,'Range',range,'NumHeaderLines',1),'omitnan');
end

[Jon]

I would not recommend naming the files as you have suggested. Using the month names makes it very cumbersome to sort the files into chronological order. Also even looking at them for example in Windows File Explorer they will not be sorted correctly (they will be in dictionary order).

I would suggest instead naming your files 201901.xlsx 201902.xlsx etc for January 2019, February 2019, etc if you have multiple days within the month, you could use for example 20190422.xlsx for April 22, 2019 this way you immediately can sort the file names in dictionary order and they will also be in chronological order

If you must name them January 2019, February 2019 etc., Then here are some ideas for getting them sorted into chronological order

% get list of files
d = dir('*2019.xlsx')

% convert file names to string array elements are for example "March 2019.xlsx"
filenames = string({d.name})

% make loop to convert just the name part, e.g. "March 2019" to a datetime so it can be sorted
numFiles = length(filenames)
dates = NaT(numFiles,1); % preallocate array to hold dates
for k = 1:numFiles
      [~,name] = fileparts(filenames(k))% get just the name without the extension
      dates(k) = datetime(name,'InputFormat','MMMM yyyy') % put into datetime array
end

% get the sort order
[~,isrt] = sort(dates)

% sort the files
d = d(isrt);

By the way I saw your comment about list being a built in MATLAB function. I could not seem to find any documentation regarding a function called list