File-loop, for loop,importing excel data

Hi everyone,

I have the following code, I have problem with the read-in-for-loops.

-I would like to read in several .csv files then export them separetly to excels -even different sheets is okay (it would be great to have the same title/name)

-then I would like to import/read-in these new excel files and work with the 16th column to find local maximas.

I know it's fulll with mistakes, and I am sorry about that, I am really new to MatLab coding I am studying it by myself, so please instead of judging the code, could you help me out?

I would really appraciate any help, thanks in advance, Grace.

[GH]

%Read each CSV file, export them to excel, change the text data in excel to numeric, import these excels and work with them

clear all
clc

%Where is the csv file
% cd 'C:\Documents\Excel'
% dataFolder = 'C:\Documents\Excel';

dirroot='C:\Documents\Excel';

for L=1:1:2
    dirname = [dirroot,'\L',num2str(L)];
    
    files = dir(fullfile(dirname,'*.csv'));              %Listing all files in the given directory with the extension of csv
    filename_list = {files.name};                        %We need only the filenames
    number_of_files = size(files,1);                     %Getting the number of csv files we have found
    
    for i=1:number_of_files                              %if there are csv files in the folder I run a for cycle on them
        filename = filename_list(i);                     %I pass the filename to a variable
        filedir = strcat(dirname,"\",filename);          %I extend the filename with the directory's path
        csv2table = readtable(filename(i));              %Give me the csv file in a table
        
        %fid = fopen(filename(i));
        
        csv2table2cell= table2cell(csv2table);           %Give me the table in cell
        xlswrite('filename.xls', csv2table2cell);        %Write me an excel from the cell
        
%Change the first two text data to 0 and 1 - because they aren't impotartant - to get a full numerical database
        varCell = {0,1};
        xlCell = {'A1','B1'};
        cellfun(@(v,c) xlswrite('*pmtf.xls',v,1,c),varCell,xlCell);
    end
end
%%
   for ii = 1: excel_files 
    data = cell2mat(num2cell(xlsread('csv2excel.xls')));  %read in the full-numeric excel file, convert it from double to cell, 
                                                          %from cell to matrix for the following calculations
 
    data_size = size(data);                               %The size of the matrix

    number_of_elements = numel(data(:,16));               %The 16th column elements

    
    rowsWith50 = find(any(data == 50, 2));                % Find out all the rows that have a 50 in them.

   
 % If any row(s) do, then find the column with the 50 in it, and get the max of that column.
    if ~isempty(rowsWith50)
        firstRowWith50 = data(rowsWith50(1), :);

        
        columnWith50 = find(firstRowWith50 == 50, 1, 'first');  % Get its column

        
        theMax = max(data(2:number_of_elements, columnWith50)); % Find the max of this column over all rows
        
        %Local_Max = findpeaks(data(2:number_of_elements, columnWith50));
    end

[Thiago Henrique Gomes Lobato]

You don't need to save the csv to excel just to convert it to numeric. Considering your data is a string only because, in the file, they have a title or something like this, you could convert it to numeric as:

csv2table = readtable(filename(i));
NumericVal = arrayfun(@(x)num2str(x),csv2table{2:end,:},'UniformOutput',false)

[Mara]

Dear Grace, I do not understand why you want to export it to excel and import it again to have numerical values. Is the first import into MATLAB only to change the file format?

I am sure you can quickly convert it in MATLAB, too. For example if you have strings, you can use "str2double" or "str2num". In order to help you there specifically and get the import right, I would have to see one of your csv files as an example.

I could not run your code as I do not have your .csv but think that the second half can be shortened a lot by still doing what you want:

   for ii = 1: excel_files 
    data = xlsread('csv2excel.xls');                    % should be returned as a matrix already
    [rows, columns] = size(data);                       % The size of the matrix (the number of 
                                                        % elements is the same for all columns 
                                                        % in a matrix, also nr 16)
    is_max_rows      = islocalmax(data(:,16))           % logical vector of all local max in 
                                                        % column 16                                    
    local_max_col_16 = data(is_max_rows, 16)            % value of local maxima in column 16
    
    [rowsWith50, colsWith50] = find(data == 50);        % Find out all the rows/cols that have a 50 
                                                        % in them. 
    theMax = max(data(:,unique(colsWith50)))            % get the max of the columns with  a 50
                                                        %  without repetition of the columns with 
                                                        % 2x or more 50
   end
   

Best,

Mara

[GH]

Hi, yes, I know the csvread doesn't work on string and numeric data combined in a csv file, and I only knew a method which can change the value of any cell in an excel format, so, yes, I only export it to excel then back because of the text data has to be gone, to deal with the dataset as a matrix (i attached the file, you can see it is not a header, cause only the first two cells are texts)

Altough even if I don't have to convert it to excel first then back, I still have no idea how to make the for loop to work, to read in all the files(there are 6 .csv files in one folder)

Thank you so much for your advice anyways

Answer 1

[Mara]

%Read each CSV file, change the text data to numeric and work with it
clear;
clc;
dirroot='C:\Documents\Excel\';

for L=1:2
    
    dirname     = [dirroot, 'L', num2str(L)];
    files       = dir([dirname,'*.csv']);             % Listing all files in the given 
                                                            % directory with the extension of csv
    filenames   = {files.name};
    
    for i=1:size(filenames, 2)                              % load all the csv files separately
                                                  
        t           = readtable(string(filenames{i}));      % Give me the csv file in a table
        t.Var1      = str2double(string(t.Var1));           % convert the first 2 variables from characters in cells to doubles
        t.Var2      = str2double(string(t.Var2));           % text is replaced by NaN (not a number)
        data        = table2array (t);                      % convert to double array
        
        [rows, columns] = size(data);                       % The size of the matrix (the number of
                                                            % elements is the same for all columns
                                                            % in a matrix, also nr 16)
        is_max_rows      = islocalmax(data(:,16))           % logical vector of all local max in
                                                            % column 16
        local_max_col_16 = data(is_max_rows, 16)            % value of local maxima in column 16
        
        [rowsWith50, colsWith50] = find(data == 50);        % Find out all the rows/cols that have a 50
                                                            % in them.
        theMax = max(data(:,unique(colsWith50)))            % get the max of the columns with  a 50
                                                            %  without repetition of the columns with
                                                            % 2x or more 50
                                                            
        save([dirname, 'results_L_', num2str(L), '_dataset_', num2str(i)]); % save data
    end
end

It might not be the most elegant way how I did it, either. But it should work if the structure of all the files is the same (text in first 2 columns).

But lmk how you get along!