Extracting specific data from multiple excel files and create a single matrix from those

53 次查看（过去 30 天）
Jonas Freiheit
Jonas Freiheit 2021-8-31
评论： Jonas Freiheit 2021-9-1
Hi, I have a file on my computer with close to 1000 excel files and I don't want to manually extract the second row from every excel file manually and combine into a single excel file.
I was wondering how I can do this on Matlab through reading every Excel file in that specific file and taking out all of the columns from the second rows and then combining all that into a single matrix?
Thanks for the help

请先登录，再回答此问题。

采纳的回答

Mathieu NOE
Mathieu NOE 2021-8-31
hello
this is one example if you want to work out the entire folder
I assumed it would be numeric data so I used importdata (faster)
I also sorted the files names in natural order in case it might be relevant
(from FEX : Natural-Order Filename Sort - File Exchange - MATLAB Central (mathworks.com) )
It works even if your files have different size (number of columns)
clc
clearvars
fileDir = cd;
outfile = 'OUT.xlsx'; % output file name
fileNames = dir(fullfile(cd,'*.xlsx')); % get list of all excel files in directory
fileNames_sorted = natsortfiles({fileNames.name}); % sort file names into order
M= length (fileNames_sorted);
for f = 1:M
% option # 1 for numeric data only using importdata
raw = importdata( fullfile(fileDir, fileNames_sorted{f}));
second_row{f} = raw(2,:); % extract the second row
end
% write all second row lines into a matrix and store it in excel file
writecell(second_row',fullfile(cd,outfile));
  14 个评论
显示 12更早的评论
Mathieu NOE
Mathieu NOE 2021-9-1
Hi Jonas
it's not just a question of votes but yes indeed each question / topic should be addressed in a separate post
this way you can also get ore answers because it's not burried in the original post .

请先登录，再进行评论。

更多回答（1 个）

Ive J
Ive J 2021-8-31
编辑：Ive J 2021-8-31
You can use readmatrix (assuming all values are numeric, otherwise use readtable) or fileDatastore to read those files. Something like this should work:
myfiles = ["file1.xlsx", "file2.xlsx"]; % file names: use dir to generate file names within the target directory
data = [];
for i = 1:numel(myfiles)
tab = readmatrix(myfiles(i)); % see readmatrix for more options
data = [data; tab(2, :)]; % aggregate 2nd row from all files into a new matrix
end
  13 个评论
显示 11更早的评论
Ive J
Ive J 2021-9-1
Please attach some of these 1000 files (with 936 columns) you're trying to work with.
Also, please be more specific with ...doesn't work for some reason.. What exact error do you get in command window when running my snippet?
Jonas Freiheit
Jonas Freiheit 2021-9-1
Sorry, the error was that it was printing out only 401 and 0. The problem has been solved now I really appreciate the help.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品

Community Treasure Hunt

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

Start Hunting!

Translated by