Readtable Delimiters on two similar files gives differing result

Is there any reason why using Readtable to open the following 2 csv files produces different results

Im using readtable as it has the ability to auto detect how many lines to skip, and generally works well - except for the case above and I can't see why. My aim is to get the real data into a uitable

        try
             [file,folder]=uigetfile({'*.csv';'*.xls'},'Open Image',app.startfolder);
        catch
            [file,folder]=uigetfile({'*.csv';'*.xls'},'Open Image','C:\');
        end    
        fullpath=fullfile(folder,file);
        app.startfolder=folder;
        T = readtable(fullpath,MissingRule="omitrow",Delimiter=",");      %Delimiter="tab"     
        app.UITable.Data=table2array(T);
            

This is what I am seeing:

I have tried omitting the Delimiter option in readtable, but with no luck

(Note my header files can be different which is why I want to try and avoid skipping " a known number of " rows.)

[Stephen23]

"Is there any reason why using Readtable to open the following 2 csv files produces different results"

Yes: because there is no such thing as an infallible algorithm. The more your file deviates from an actual comma-separated-values file, the more likely that READTABLE's automagical algorithm will struggle. This is always true: no matter what heuristic algorithm is implemented, there will always be (edge)cases that will confound and confuse it.

The more help you give READTABLE to guide it when importing the more likely it is to import the data you expect.

[Jason]

OK thanks, I dont suppose you see an easy fix to this case above with any of the readtable parameters (ratrher than modifying the text files themselves)

[Jason]

One thing for sure is that all my data is under the line begining with "Point"

[Eric Sofen]

If the header structure is consistent between files, the NumHeaderLines argument in readtable, will help to start parsing the CSV from the right line and not get tripped up by the commas in the date line.

Answer 1

[Voss]

One problem seems to be that the date/time line in the header has 3 commas in it, which for file B causes readtable to try to treat that line as part of the data section since there are also 3 commas per line there. (The data section in file A has 5 commas per line, so the date/time line is not confused for data in that case.) I couldn't find a way around that using various options in readtable/readmatrix (but I didn't try very hard - there may well be a way to do it).

One solution is to write your own reading function. I've written one such function (read_this_file), and it's given below.

type('A.txt') % show file contents for reference

Study name: with water drop(-2D simp-)
Plot type: Thermal Thermal1
16:39, Friday, June 07, 2024


X Title: Time (sec)
Y Title: Temp (Celsius)

Point, X, Y1 (Node 483), Y2 (Node 6229), Y3 (Node 469)
1, 1,52.947      ,27.51       ,40.407      ,
2, 2,51.021      ,27.549      ,40.252      ,
3, 3,57.473      ,27.635      ,39.968      ,

MA = read_this_file('A.txt') % get numeric matrix data

MA = 3x5

1.0000 1.0000 52.9470 27.5100 40.4070 2.0000 2.0000 51.0210 27.5490 40.2520 3.0000 3.0000 57.4730 27.6350 39.9680

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

type('B.txt')  % show file contents for reference

Study name: with air (initial)(-2D simp-)
Plot type: Thermal Thermal1
15:23, Monday, June 10, 2024


X Title: Time (sec)
Y Title: Temp (Celsius)

Point, X, Y1 (Node 469)
1, 1,59.52       ,
2, 2,58.677      ,
3, 3,57.473      ,

MB = read_this_file('B.txt') % get numeric matrix data

MB = 3x3

1.0000 1.0000 59.5200 2.0000 2.0000 58.6770 3.0000 3.0000 57.4730

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

function M = read_this_file(F)
% read lines of file F into string array S
S = readlines(F);
% keep the line after the one that starts with 'Point', and all 
% the lines after that, and replace the commas with spaces
S = strrep(S(find(startsWith(S,'Point'),1)+1:end),',',' ');
% run sscanf(_'%f') on each line, capturing the numbers they contain
C = arrayfun(@(s)sscanf(s,'%f'),S,'UniformOutput',false);
% put those numbers into a matrix with the correct orientation
M = [C{:}].';
end

[Voss]

Note that my read_this_file function returns a matrix, not a table, since your intention seemed to be to convert the table into a matrix before placing it in the uitable anyway ("My aim is to get the real data into a uitable ... app.UITable.Data=table2array(T);").

[Jason]

Fantastic!

Thankyou Voss.

[Voss]

You're welcome!

[Voss]

Here's a modification to read_this_file that also optionally returns the column names, so you can use them in the uitable.

[MA,HA] = read_this_file('A.txt') % get numeric matrix data

MA = 3x5

1.0000 1.0000 52.9470 27.5100 40.4070 2.0000 2.0000 51.0210 27.5490 40.2520 3.0000 3.0000 57.4730 27.6350 39.9680

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

HA = 5x1 string array
    "Point"
    "X"
    "Y1 (Node 483)"
    "Y2 (Node 6229)"
    "Y3 (Node 469)"

f = figure('Position',[1 1 510 120]);
t = uitable(f,'Position',[10 10 490 100]);
t.Data = MA;
t.ColumnName = HA;

[MB,HB] = read_this_file('B.txt') % get numeric matrix data

MB = 3x3

1.0000 1.0000 59.5200 2.0000 2.0000 58.6770 3.0000 3.0000 57.4730

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

HB = 3x1 string array
    "Point"
    "X"
    "Y1 (Node 469)"

f = figure('Position',[1 1 510 120]);
t = uitable(f,'Position',[10 10 490 100]);
t.Data = MB;
t.ColumnName = HB;

function [M,H] = read_this_file(F)
% read lines of file F into string array S
S = readlines(F);
% find the line that starts with 'Point'
idx = find(startsWith(S,'Point'),1);
% if column names were requested, take them from this line
if nargout > 1
    H = strtrim(split(S(idx),','));
end
% keep all the lines after that line, and replace the commas with spaces
S = strrep(S(idx+1:end),',',' ');
% run sscanf(_'%f') on each line, capturing the numbers they contain
C = arrayfun(@(s)sscanf(s,'%f'),S,'UniformOutput',false);
% put those numbers into a matrix with the correct orientation
M = [C{:}].';
end

Answer 2

[Stephen23]

"One thing for sure is that all my data is under the line begining with "Point""

tA = myread('A.txt')

tA = 3x5 table
    Point    X    Y1 (Node 483)    Y2 (Node 6229)    Y3 (Node 469)
    _____    _    _____________    ______________    _____________

      1      1       52.947             27.51           40.407    
      2      2       51.021            27.549           40.252    
      3      3       57.473            27.635           39.968    

tB = myread('B.txt')

tB = 3x3 table
    Point    X    Y1 (Node 469)
    _____    _    _____________

      1      1        59.52    
      2      2       58.677    
      3      3       57.473    

function T = myread(F)
N = find(startsWith(readlines(F),'Point'))-1;
T = readtable(F, 'NumHeaderLines',N, 'Delimiter',',', 'VariableNamingRule','preserve');
end

[Jason]

Thankyou Stephen, I actually prefer this answer to the 2nd part of my question - very simple and compact!