Error using readtable: Cannot interpret data in the file (.txt)

Hi guys,

I made the following script to import a .txt a file and export to .csv in a certain format that i need for the program GoldenCheetah:

filename = ('Power2MaxVergelijking.airstreeem.txt'); %open file
Data = readtable(filename,'delimiter','\t'); %sets tab as delimiter for text file and stores it as data
%-------------------------------------------------------------
%Naming variables
Time = Data(:,1); %in seconds
Power = Data(:,2); %in watt
Velocity = Data(:,3); %in km/h
Cadence = Data(:,4); %in rpm
HeartRate = Data(:,5); %in bpm
%-------------------------------------------------------------
%Adjusting variables according to data needed for GoldenCheetah
TimeArray = table2array(Time); %convert table to array
Minutes = Time{:,1}./60; %in minuts
Watts = table2array(Power); %convert table to array
VelocityArray = table2array(Velocity); %convert table to array
VelocityDots = strrep(VelocityArray,',','.'); %converts commas to dot
Kmh = str2double(cellstr(VelocityDots)); %makes char into integer
CadenceA = table2array(Cadence); %convert table to array
Hrate = table2array(HeartRate); %convert table to array
Blanc = TimeArray; %gets the length of an origal array
Blanc(:,1)=0; %set all the values of the original array to zero
%-------------------------------------------------------------
%Writing data into .csv file to open in GoldenCheetah
T = table(Minutes,Blanc,Kmh,Watts,Blanc,CadenceA,Hrate,Blanc,Blanc); %make a table and state which arrays should be in it
T.Properties.VariableNames = {'Minutes' 'Torq' 'Kmh' 'Watts' 'Km' 'Cadence' 'Hrate' 'ID' 'Altitude'}
%change the names of the arrays according to GoldenCheetah
writetable(T,'Power2MaxVergelijking.csv','Delimiter',',') %write the file
type 'Power2MaxVergelijking.csv' %view the file in command window

This worked perfectly for the testfile i had. However, when I tried to do the same thing with a file that's basically the same i got an error:

Error using readtable (line 129)
Cannot interpret data in the file 'Power2MaxVergelijking.airstreeem.txt'.  Found 5 variable names but 6 data columns.  You may need to specify a different format string, delimiter,
or number of header lines.

I attached the files which i used; TestFelixTest.airstreeem.txt (the file which worked), Power2MaxVergelijking.airstreeem.txt (the file that got me the error) and ExportProgram.m (the matlab script).

Any help would be highly appreciated!!!

BR Felix

[Jeremy Hughes]

In R2016b, you can use detectImportOptions to detect the format of the file and set a custom decimal separator.

opts = detectImportOptions('L:\Power2MaxVergelijking.airstreeem.txt');
opts = setvartype(opts,3,'double');
opts = setvaropts(opts,3,'DecimalSeparator',',');
opts.ExtraColumnsRule = 'ignore'; % There's an extra tab at the end of some of the rows, this prevents that from being imported.
T = readtable('L:\Power2MaxVergelijking.airstreeem.txt',opts)

Hope this helps.

Jeremy

Answer 1

[Walter Roberson]

readtable() cannot use comma as the decimal indicator. textscan cannot either. You need to change the commas to '.'

I recommend using fileread to read the entire file as text, then use logical indexing to change the ',' to '.' and then use textscan to parse the string. The first parameter to textscan is usually a file identifier but you can instead use a character string in that position to parse the string

[Walter Roberson]

power_file = 'Power2MaxVergelijking.airstreeem.txt';

power_ncol = 5;

power_fmt = repmat('%f',1,power_ncol);
filecontent = fileread( power_file );
filecontent(filecontent == ',') = '.';

power_header_fmt = repmat('%s', 1, power_ncol);
power_header = textscan(filecontent, power_header_fmt, 1);

power_cell = textscan(filecontent, power_fmt, 'HeaderLines', 1, 'CollectOutput', 1);

power_variables = cellfun(@genvarname, power_header );
power_data = power_cell{1};

power_table = array2table(power_data, 'VariableNames', power_variables);

The code is exactly the same for the other file, except with different variable names. You could put the reading code into a function that you pass in the file name and get back the table.

Answer 2

[Star Strider]

I can’t get around your using commas as decimal separators, so I can’t read your file correctly.

I leave you to experiment with this:

fidi = fopen('Feliz Power2MaxVergelijking.airstreeem.txt', 'rt');
FirstLine = fgets(fidi);
Data = textscan(fidi, '%f%f%f%f%f', 'Delimiter','\t', 'EndOfLine','\r\n', 'CollectOutput',1);

Answer 3

[Feliz]

I dont really know what you did there, but i figured it out on my own. I adjusted this part:

   filename = ('Power2MaxVergelijking.airstreeem.txt'); %open file
Data = readtable(filename,'delimiter','\t','ReadVariableNames',false);