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

96 次查看（过去 30 天）

Jonas Freiheit 2021-8-31

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1443949-extracting-specific-data-from-multiple-excel-files-and-create-a-single-matrix-from-those

评论： 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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

采纳的回答

Mathieu NOE 2021-8-31

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1443949-extracting-specific-data-from-multiple-excel-files-and-create-a-single-matrix-from-those#answer_777844

在 MATLAB Online 中打开

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更早的评论隐藏 12更早的评论

Mathieu NOE 2021-9-1

在 MATLAB Online 中打开

OUT.xlsx

hello again

I simply modified one of your data file (ARTEMIS_SET_control_10%.xlsx) from xlsx to CSV format so they all have the same format. If you need to dig with a mix of CSV and XLSX files , I could update my code.

After that , I had not much to do and this code works like a charm, at least for this batch of 24 files;

see also at the end of my code , if you wish to have the filenames also stored in the OUT file, attached FYI

slightly updated code :

clc
clearvars
fileDir = cd;
outfile = 'OUT.xlsx'; % output file name 
fileNames = dir(fullfile(fileDir,'*.csv')); % get list of files in directory
fileNames_sorted = natsortfiles({fileNames.name}); % sort file names into order (https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)
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)); % without filenames 
% writecell([fileNames_sorted' second_row'],fullfile(cd,outfile)); % with filenames stored in column 1
    

(seems to me a lot of files had the same data inside , just the file names are different)

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 .

Jonas Freiheit 2021-9-1

Cool, heres the link to the question:

https://au.mathworks.com/matlabcentral/answers/1444599-creating-a-5046x1-cell-with-specific-category-names?s_tid=srchtitle

请先登录，再进行评论。

更多回答（1 个）

Ive J 2021-8-31

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1443949-extracting-specific-data-from-multiple-excel-files-and-create-a-single-matrix-from-those#answer_777829

编辑：Ive J 2021-8-31

在 MATLAB Online 中打开

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