MATLAB Answers

Readtable, problems reading identically structured data files

2 views (last 30 days)
There are two *.txt-files of identical structure. Both contain values as a 52866 x 9 matrix without any header. Unfortunately the columns are separated by spaces (due to another software).
Reading file number 1 using
T = readtable('File1.txt');
returns a table as a 475794 x 1 vector without any NaN entries.
However, reading file number 2 using
T = readtable('File2.txt');
returns a table as a 54599 x 60 matrix with NaN and blank (" ") entries.
Eventually, this yields to the error
"Error using table2array: Unable to concatenate the table variables 'Var1' and 'Var59', because their types are double and cell."
Strange to say, if the first 35 columns of File 2 are removed, the output of readtable('File2.txt') would be a table as a 475479 x 1 vector.
Has anyone encountered this problem as well, or knows how to workaround that issue?

  0 Comments

Sign in to comment.

Accepted Answer

Bhaskar R
Bhaskar R on 5 Feb 2020
Edited: Bhaskar R on 5 Feb 2020
If you are not specific on to use only table try this
f1 = fopen('File1.txt', 'r');
f2 = fopen('File2.txt', 'r');
% read files according to your text file format
data_1 = textscan(f1, '%d%d%d%f%f%f%f%f%f', 'CollectOutput', 1);
data_file1 = [double(data_1{1}),data_1{2}];
data_2 = textscan(f2, '%d%d%d%f%f%f%f%f%f', 'CollectOutput', 1);
data_file2 =[double(data_2{1}),data_2{2}];
% close identifiers
fclose(f1);
fclose(f2);
% concatanate two files data
data_final = [data_file1;data_file2];
% convert to table
T = array2table(data_final);

  2 Comments

Markus Franke
Markus Franke on 5 Feb 2020
Thank you very much. Actually, this is pretty awesome, since it increases i/o-speed immensly.
Just for my interest. Do you know, where the problem in using readtable on that specific issue is?
Stephen Cobeldick
Stephen Cobeldick on 5 Feb 2020
Simpler with dlmread:
>> M1 = dlmread('File1.txt');
>> M2 = dlmread('File2.txt');
>> T = array2table([M1;M2]);
"Do you know, where the problem in using readtable on that specific issue is?"
You need to set the "MultipleDelimsAsOne" option to true.

Sign in to comment.

More Answers (1)

Guillaume
Guillaume on 5 Feb 2020
Edited: Guillaume on 5 Feb 2020
There is something odd with the first few rows of your 2nd file that I haven't worked out yet. In theory,
T = readtable(filename, 'Delimiter', ' ', 'MultipleDelimsAsOne', true);
would read your files properly but for some reason with your 2nd file, it misses the first 19 rows.
Never mind, when readtable messes, detectImportOptions can usually solve the problem. Indeed:
opts = detectImportOptions('File1.txt', 'Delimiter', ' ', 'ConsecutiveDelimitersRule', 'join', 'ExtraColumnsRule', 'ignore');
T1 = readtable('File1.txt', opts);
T2 = readtable('File2.txt', opts);
read both files without issue. And since I've specified ExtraColumnsRule', 'ignore' you don't even get an extra column of NaN.
You could also tell customise the import options to specify the variable names, so that you get more meaningfull names directly out of readtable:
opts = detectImportOptions('File1.txt', 'Delimiter', ' ', 'ConsecutiveDelimitersRule', 'join', 'ExtraColumnsRule', 'ignore');
opts.VariableNames = {'someindex', 'someotherindex', 'something', 'aaa', 'bbb', 'cde', 'f', 'g', 'h'};
T1 = readtable('File1.txt', opts);
T2 = readtable('File2.txt', opts);
Note that if you want matrices and not tables, then use readmatrix instead of readtable. The detectimportoptions would be the same.

  0 Comments

Sign in to comment.

Sign in to answer this question.