How do I pull data from multiple excel spreadsheets located in the same folder?

7 次查看（过去 30 天）
Abigail
Abigail 2024-7-23
评论： dpb 2024-7-24
Hello!
I'm trying to record a specific data point from multiple excel files that are located in the same folder and dump them into their own table.
To elaborate, I have thousands of excel files all under the path 'C:\Users\Me\DataFiles'. I want to open each file, take the data from cell M18, and put it into its own table with all the data points.
I currently have a line using the readmatrix function and I'm able to read just the first file in the folder, but nothing else. I already have a table populated with serial number information, so I get an error that the table variables must have the same number of rows.
Any help would be greatly appreciated, thank you!
  3 个评论
显示 1更早的评论
Abigail
Abigail 2024-7-23
Hi Cris! It's difficult for me to share the code and data because it's on a different server.
But I want to take all of the M18 data points and put it into one singular excel file so there will be x amount of rows for the amount of files that it takes data from.

请先登录，再进行评论。

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

回答（2 个）

Cris LaPierre
Cris LaPierre 2024-7-23
I would use a fileDatastore to load all the data into a single variable. You can see an example of how to use one to do this in this video from the Data Processing with MATLAB specialization on Coursera.
Here is the final code from that example. You can modify this to work for your data.
flightsDataStore = fileDatastore("flights*.csv","ReadFcn",@importFlightsData,"UniformRead",true);
flightsAll = readall(flightsDataStore)
Once complete, all the data from all files with names matching the pattern "flights*.csv" are loaded into the variable flightsAll.
  4 个评论
显示 2更早的评论
Cris LaPierre
Cris LaPierre 2024-7-24
What does your code look like? Are you getting any sort of error message?
Perhaps try using fullfile to build your paths?
dpb
dpb 2024-7-24
"...try using fullfile to build your paths?"
Indeeed. If you will use fullfile at the command line, it will do auto-complete of the file path, easily verifying that you're going where you think you are first and checking the wildcard will match, later.

请先登录，再进行评论。

dpb
dpb 2024-7-23
"But I want to take all of the M18 data points and put it into one singular excel file so there will be x amount of rows for the amount of files that it takes data from."
Basically a trivial exercise albeit it may take a while...
SHT='YourSheet'; % which sheet to read (name or numeric)
CELL='M18'; % the cell address of interest
baseFolder='Yourfolderpath'; % where they're located
baseFileName='YourFileName*.xlsx'; % a wildcard filename that will match those of interest
FQFN=fullfile(basefolder,baseFileName); % build the fully-qualified file name
d=dirFQFN); % return the dir struct of those that match
N=numel(d); % how many files are there???
data=nan(N,1); % preallocate a column vector of that many
for i=1:
data(i)=readmatrix(FQFN,'Sheet',SHT,'Range',CELL); % populate the array
end
data=array2table(data,'VariableNames',CELL); % convert to a table
Best practice would be to encapsulate the above in a function that can be called with the appropriate input data and return the result.
  1 个评论
Walter Roberson
Walter Roberson 2024-7-23
for i=1:
data(i)=readmatrix(FQFN,'Sheet',SHT,'Range',CELL); % populate the array
? You are missing the upper bound of the for loop ?
The body of the loop does not appear to vary with the loop ?

请先登录，再进行评论。

类别

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

标签

产品


版本

R2023b

Community Treasure Hunt

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

Start Hunting!

Translated by