Azzera filtri
Azzera filtri

Readtable Delimiters on two similar files gives differing result

3 visualizzazioni (ultimi 30 giorni)
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 Commenti
Jason
Jason il 12 Giu 2024
One thing for sure is that all my data is under the line begining with "Point"
Eric Sofen
Eric Sofen il 17 Giu 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.

Accedi per commentare.

Risposta accettata

Voss
Voss il 12 Giu 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 Commenti
Voss
Voss il 12 Giu 2024
Modificato: Voss il 12 Giu 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

Accedi per commentare.

Più risposte (1)

Stephen23
Stephen23 il 18 Giu 2024
Modificato: Stephen23 il 18 Giu 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 Commento
Jason
Jason il 18 Giu 2024
Thankyou Stephen, I actually prefer this answer to the 2nd part of my question - very simple and compact!

Accedi per commentare.

Categorie

Scopri di più su Environment and Settings in Help Center e File Exchange

Prodotti


Release

R2023b

Community Treasure Hunt

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

Start Hunting!

Translated by