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

1 visualización (últimos 30 días)
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.

Respuesta aceptada

Jon
Jon el 12 de Nov. de 2020
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
  9 comentarios
José Javier Rubio Rubio
José Javier Rubio Rubio el 19 de Nov. de 2020
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
Jon el 19 de Nov. de 2020
Editada: Jon el 19 de Nov. de 2020
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

Iniciar sesión para comentar.

Más respuestas (0)

Etiquetas

Productos


Versión

R2020a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by