Need guidance to loop directory, import .csv, get corresponding data from 2nd row and filter first, extract this corresponding data.

I have been working on CFD for my workplace and there is a lot of manual work that could be solved by a MATLAB program. However, I am not familiar with the syntax and logics of coding and MATLAB and I was wondering if you could guide me in the right direction with this.

My task right now is:

- I probe and extract data from the CFD, it gets stored as a . csv file

- I have to do this for every component of a building so I have around 30-40 .csv files

- The .csv files data then has to be copied to corresponding sheets of a master excel file (with pre existing formulas that are based on cells that calculate values) where the formulas would be applied.

What I want to do:

Write a program that :

- loads all .csv files into Matlab as tables or arrays in a loop

- Extracts some values from the .csv, for instance

-Apply formula after extracting value and store the answers

FOR INSTANCE:

From this table:

Case1

Var 1 Var 2 Var3

Area | 262.452 | ft^2

Avg. Temperature | 113.302 | Fahrenheit

Case 2

Area | 262.452 | ft^2

Avg. Temperature | 1234.302 | Fahrenheit

Case 3

Area | 262.452 | ft^2

Avg. Temperature | 11233.302 | Fahrenheit

<COUPLE OF ADDITIONAL ROWS>

Total area | 828.093 | ft^2

I would like to extract all average temp 113.302,1234.302,11233.302 and the total area 828.093 from all the tables > apply a formula to these values > and then store in an excel file.

They are all in the same position in the .csv files

Where I am getting stuck:

-Loop around files in a directory

- Cannot create an array and can only create table

-Do not know how to get corresponding table values for "Avg Temperature" and "Total area

- extracting location from table to apply a formula

Answer 1

[Mathieu NOE]

hi

below some simple code to loop in a folder - we could also loop over multiple folders if we need to

there will be still a little bit of work to do then the data processing, but for that it would definitively help to have some csv files from you

% A simple, efficient solution is to use the structure returned by DIR:
P = pwd;  % get working directory (or specify it)
S = dir(fullfile(P, '*.csv'));
% S = natsortfiles(S); % sort folders / file names into order (https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)

for k = 1:numel(S)
    F = fullfile(S(k).folder, S(k).name);
    S(k).data = csvimport(F); % or READTABLE or whatever.
    % here do your post processing 
    
end

% FYI, the structure S contains all of your file data and the corresponding filenames
% For example, the 2nd filename and its data:
% S(2).name
% S(2).data

[Mathieu NOE]

this is one option

it will generate an array C that we save as excel spreadsheet

for the sake of the demo I sily took the mean temperature from the 3 raw data and use that in the output file

you can of course do your own computations here

please use natsortfiles (from here : Natural-Order Filename Sort - File Exchange - MATLAB Central (mathworks.com) ) , if it matters to you that the files are truly processed in the naturally sorted way (what matlab dir alone does not do)

hope it helps !

P = pwd;  % get working directory (or specify it)
S = dir(fullfile(P, 'GEN*.csv'));
% S = natsortfiles(S); % sort folders / file names into order (https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)

for k = 1:3 % numel(S)
    F = fullfile(S(k).folder, S(k).name);
    data = readcell(F,"Delimiter",","); % import as char or string array
    [m,n] = size(data);
    % data is a cell array
    
    % let's find where the temperature data is 
    ind=(strcmp(data(:,n),'Fahrenheit')); % we have now the rows indexes (logical)
    Temperature = data(ind,n-1); % again a cell array
    Temperature = [Temperature{:}]; % convert to numerical array
    
    % Total area data is in the last row
    Total_area = data(m,n-1) ;
    Total_area = [Total_area{:}]; % convert to numerical array
    
    % some basic post processing
    Temperature_mean = mean(Temperature);
    
    % let put everything together (filename, data)
    out{k,1} = S(k).name;
    out{k,2} = Temperature_mean;
    out{k,3} = Total_area;
    
end

% % export to excel file 
col_headers = {' File ','Mean Temp (F)','Total area (ft^2)'}
C = [ col_headers; out]; % vertical concatenation of headers and data

writecell(C, "out.xlsx");

[Devansh Mishra]

Thank you for answering my query. i do get a "Index exceeds the number of array elements. Index must not exceed 0." error when I run this code. If you have any solution, please let me know. Thanks again, really appreciated

[Mathieu NOE]

hello again

did the error occurs with the 3 csv files you provided or with other files ? if this is the case can you share some files that generate the error ?

[Mathieu NOE]

also can you copy here exactly the error message in your command window (where is the error in the code)

[Devansh Mishra]

These are the files and the error message that shows up

[Mathieu NOE]

hello

I have improved the code and made it more robust , especially if the files are not present or anything that make S empty

also do not initialize out with : out = ones(1,3);

try this new code :

P = pwd;  % get working directory (or specify it)

% Select which files you want : 
% S = dir(fullfile(P, 'GEN*.csv')); % only csv files which names start with "GEN"
% S = dir(fullfile(P, 'CHILLER*.csv')); % only csv files which names start with "CHILLER"
S = dir(fullfile(P, '*.csv')); % all csv files in folder

S = natsortfiles(S); % sort folders / file names into order (https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)

 %check if S is empty (no files found according to filter above)
 nS = numel(S);
 
 if nS<1 % S is  empty => throw an warning message
     msgbox(' no file found  / no data processing !! ');
     
 else % S is non empty => we can process the data
    for k = 1:nS
        F = fullfile(S(k).folder, S(k).name);
        data = readcell(F,"Delimiter",","); % import as char or string array
        [m,n] = size(data);
        % data is a cell array

        % let's find where the temperature data is 
        ind=(strcmp(data(:,n),'Fahrenheit')); % we have now the rows indexes (logical)
        Temperature = data(ind,n-1); % again a cell array
        Temperature = [Temperature{:}]; % convert to numerical array

        % Total area data is in the last row
        Total_area = data(m,n-1) ;
        Total_area = [Total_area{:}]; % convert to numerical array

        % some basic post processing
        Temperature_mean = mean(Temperature);

        % let put everything together (filename, data)
        out{k,1} = S(k).name;
        out{k,2} = Temperature_mean;
        out{k,3} = Total_area;

    end
    % % export to excel file 
    col_headers = {' File ','Mean Temp (F)','Total area (ft^2)'};
    C = [ col_headers; out]; % vertical concatenation of headers and data
    writecell(C, "out.xlsx");
     msgbox([num2str(nS) ' files processed successfully !! ']);

 end

[Devansh Mishra]

I would really like to thank you for your help. There seems to be another error that I have come across. It says "Unable to perform assignment because brace indexing is not supported for variables of this type."

So sorry for too many messages.

[Mathieu NOE]

No problem

can you share the file(s) that generated this error ?

[Mathieu NOE]

hello again

did the problem disappear ?