Clear Filters
Clear Filters

Readtable Delimiters on two similar files gives differing result

3 views (last 30 days)
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.)
  4 Comments
Jason
Jason on 12 Jun 2024
One thing for sure is that all my data is under the line begining with "Point"
Eric Sofen
Eric Sofen on 17 Jun 2024
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.

Sign in to comment.

Accepted Answer

Voss
Voss on 12 Jun 2024
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
  4 Comments
Voss
Voss on 12 Jun 2024
Edited: Voss on 12 Jun 2024
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

Sign in to comment.

More Answers (1)

Stephen23
Stephen23 on 18 Jun 2024
Edited: Stephen23 on 18 Jun 2024
"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
  1 Comment
Jason
Jason on 18 Jun 2024
Thankyou Stephen, I actually prefer this answer to the 2nd part of my question - very simple and compact!

Sign in to comment.

Categories

Find more on Environment and Settings in Help Center and File Exchange

Products


Release

R2023b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!