Remove last row from csv using csvread

Hi, I have the following code to read all the csv files in a directory.

close all;

csvfiles = dir('*.csv');

for file = csvfiles'
    datafromfile = csvread(file.name, 17, 0);
    freq = datafromfile(:,1);
    power = datafromfile(:,2);
end

However, the csv files contain a cell with END in the last cell, as follows. This results in an error when the script runs.

HEADER    HEADER    HEADER
HEADER    HEADER    HEADER  
29950250  3.46649905459066  0  0  0
30000000  5.23598452822347  0  0  0
END        

How can I remove the last element/row from the csv so the script runs?

EDIT: I've included an example csv file.

Answer 1

[Adam Danz]

If you must use csvread() and you know the rows and columns of the file that should be read, use the following syntax to limit the section of the file that is read into Matlab.

M = csvread(filename,R1,C1,[R1 C1 R2 C2]) r

However, if you're using a recent release of Matlab, consider using readtable() instead of csvread().

[Walter Roberson]

this will not work. csvread uses textscan in a mode that can skip leading rows and leading columns, but everything else in the file must be numeric. It handles ranges by reading it all and then throwing the extra away.

[Ted Baker]

Hi Adam, many thanks for the very fast reply.

I'm using csvread over readtable as readtable cannot find the right variable names with my csv file.

In this case, tried adding your line such that my code is now:

close all;

csvfiles = dir('*.csv');

for file = csvfiles'
    datafromfile = csvread(file.name, 17, 0);
    datafromfile(1:end-1, 1);
    freq = datafromfile(:,1);
    power = datafromfile(:,2);
end

However, I still get the same error:

Error using dlmread (line 147)
Mismatch between file and format character vector.
Trouble reading 'Numeric' field from file (row number 402, field number 1) ==> END\n

Error in csvread (line 48)
    m=dlmread(filename, ',', r, c);

Error in Plot (line 6)
    datafromfile = csvread(file.name, 17, 0);
 

So it seems that the issue is during the import. I've attached an actual example of a csv file im using for reference.

[Adam Danz]

Based on the file you provided, here's an example of my answer but note that this only reads in the numeric section of your file (401x5 matrix) and you need to know the row numbers where the numeric data start and stop.

T = csvread('FILE1_.csv', 17, 0, [17 0 417 4]);

size(T)
ans =
   401     5
   
% Show 1st and last rows
T([1,end],:)
ans =
          150       91.476            0            0            0
      2.5e+09       12.633            0            0            0

[Ted Baker]

Thanks Adam.

[Adam Danz]

Note that you can compute the start and end rows of numeric data in the CSV file by matching the "BEGIN" and "END" rows using these two lines.

filelines = strtrim(strsplit(fileread('FILE1_.csv'), newline()));
startstop = find(ismember(upper(filelines), {'BEGIN','END'})) - [0,2];

The -[0,2] is to offset the row numbers for the csvread inputs.

startstop =
    17   417

[Ted Baker]

Even better thank you! As a quick question - is there a quick way of reading the contents of a cell with text? Specifically I would like to read what is in column 1, row 16 (DATA UNIT). readtable doesn't work when I try reading the csv with it...

[Adam Danz]

Hint: see the first line of code in my previous comment (filelines{16}).

However, you'll need to trim some of the text if you want to isolate the "DATA UNIT" part because the full string is '! DATA UNIT dB?V'.

See the string tools listed in the link below, particularly in sections Find and Replace, Edit, and Regular Expressions.

https://www.mathworks.com/help/matlab/characters-and-strings.html

[Ted Baker]

I think I see how this works using readfile, but when I run the code:

close all;

csvfiles = dir('*.csv');

for file = csvfiles'
    filelines = strtrim(strsplit(readfile('FILE1_.csv'), newline()));
    startstop = find(ismember(upper(filelines), {'BEGIN','END'})) - [0,2];
    freq = datafromfile(:,1);
    power = datafromfile(:,2);
end

I get the following error:

Error using strsplit (line 80)
First input must be either a character vector or a string scalar.

Error in Plot (line 6)
    filelines = strtrim(strsplit(readfile('FILE1_.csv'), newline()));

Not sure why this is as the first string should be "! FILETYPE CSV".

[Adam Danz]

I just noticed my comment uses readfile from the file exchange rather than Matlab's fileread. The prior is a useful function provided by one of the talented contributors of this forum but I don't have much experience with it compared to fileread. I'll update my comment so that it uses the fileread function.

Using fileread, what's the output for the iteration that causes the error?

Also, you should replace the hard-coded file name with your file.name variable.

T = fileread('FILE1_.csv')  % ?

[Ted Baker]

I noticed that readfile and downloaded it :). With fileread I get no problem, thank you. On one last note, I see the data is stored in cells as a comma-delimited string. Is there a quick way of putting the first and second elements of this into variables (called freq and pow), each as a column matrix? I tried adding the following to split the string:

    line = regexp(filelines, ',', 'split');
    line = line(1:end-1);

But that seems to just create a cell of 5 elements inside the line array.

[Ted Baker]

Apologies, I had a brain fart moment. For reference my final code is:

close all;

csvfiles = dir('*.csv');

for file = csvfiles'
    filelines = strtrim(strsplit(fileread(file.name), newline()));
    startstop = find(ismember(upper(filelines), {'BEGIN','END'})) - [0,2];
    
    T = csvread(file.name, startstop(1), 0, [startstop(1) 0 startstop(2) 4]);
    
    freq = T(:,1);
    power = T(:,2);
end

Massive thank you Adam.

[Adam Danz]

"I see the data is stored in cells as a comma-delimited string. "

That's true of some of the lines but not all of them.

filelines(1)  % not true
ans =
  1×1 cell array
    {'! FILETYPE CSV'}
    
filelines(200)  % true
ans =
  1×1 cell array
    {'1137500081.75,10.3549267889657,0,0,0'}      

"Is there a quick way of putting the first and second elements of this into variables "

That's what the csvread is doing. To isolate only col 1 and only col 2

freq = csvread('FILE1_.csv', 17, 0, [17 0 417 0]); % col 1
pow =  csvread('FILE1_.csv', 17, 1, [17 1 417 1]); % col 2

Although tables are usually a way to keep data organized.

T = csvread('FILE1_.csv', 17, 0, [17 0 417 1]);
Tbl = array2table(T,'VariableNames',{'freq','pow'})
head(Tbl)  % Check out the first few rows

Technically you could do it directly from the filelines cell array but it's messy.