Readtable, problems reading identically structured data files

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?

Answer 1

[Bhaskar R]

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);

[Markus Franke]

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?

[Stephen23]

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.

Answer 2

[Guillaume]

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.