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

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!

[Cris LaPierre]

Sorry, I'm not exactly clear of what you want. Can you clarify? Better yet, share your code and some sample data files.

You want to take the data from cell M18, and put it into its own table? So the result is thousands of tables, each containnig the value of cell M18 from a single excel file?

You say you want to put it into its own table with all the data points. All what data points? I thought there was just a single value.

[KD]

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.

[Voss]

https://www.mathworks.com/matlabcentral/answers/57446-faq-how-can-i-process-a-sequence-of-files

Answer 1

[Cris LaPierre]

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.

[dpb]

I forget about those, @Cris LaPierre

[KD]

This seems like the perfect solution! However, when I import the location of my files, it isn't able to find them, even if I type in the exact path. Any ideas?

[Cris LaPierre]

What does your code look like? Are you getting any sort of error message?

Perhaps try using fullfile to build your paths?

[dpb]

"...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.

Answer 2

[dpb]

"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.

[Walter Roberson]

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 ?