readtable error : read all columns into a single column
Show older comments
I am using readtable to read a *.txt file into table
data = readtable('test.txt', 'Format','auto')
the data file have 10 columns, but in the loaded data table, all columns are cancatecated into a single column, any ideas how to fix this?
thank you.
4 Comments
Guohua
on 9 Nov 2023
The data looks like this:
After table in Matlab looks like this:
Cris LaPierre
on 9 Nov 2023
Edited: Cris LaPierre
on 9 Nov 2023
Please attach a sample data file.
I suspect the issue is that, since there are variable numbers of items in each row, it has not been able to successfully determine the number of variables in the file.
A first attempt might be to specify the expected number of variables.
data = readtable('test.txt', "ExpectedNumVariables",10)
Just a comment that I count more than 10 columns of data in the screenshot you shared.
dpb
on 9 Nov 2023
You forgot to attach the data file so we have to guess...
First, try
data = readtable('test.txt');
with no additional parameters; generally the input scanner is pretty competent about finding the delimiter on its own.
If that fails, then telling it what the delimiter is will be next; the image you attached is from Excel where you imported it, it is NOT the text file; the Q? is whether Excel was able to detect the delimiter on its own or did you have to use the whizard to separate columns -- if the text file is fixed-width, that is generally the case and would be one likely cause for your result in MATLAB.
Guohua
on 9 Nov 2023
Answers (2)
It looks like MATLAB is not correctly identifying the delimitar, which is a tab. You can manually specify that.
Some fo the datatypes are also incorrectly selected. You can use theImport Tool to ineratively set the import parameters and then generate a script.
You could also do that manually.
opts = delimitedTextImportOptions("NumVariables",13);
opts.Delimiter = "\t";
opts.VariableNamesLine = 1;
opts.DataLines = [2,inf];
opts = setvartype(opts,["datetime", "string", "string", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double"]);
data = readtable("data_20091130.txt",opts)
1 Comment
Using your second approach also works, but with some additional specifications.
readtable("data_20091130.txt","Format",'%{MM/dd/yyyy}D%s%s%f%f%f%f%f%f%f%f%f%f',...
'Delimiter','\t','TextType','string')
filename = 'data_20091130.txt';
% detect import options, specifying tab as delimiter:
opts = detectImportOptions(filename,'Delimiter','\t');
% set variable 2 and 3's data to be interpreted as character vectors:
opts = setvartype(opts,opts.VariableNames([2 3]),{'char','char'});
% read the file:
T = readtable(filename,opts)
Categories
Find more on Spreadsheets in Help Center and File Exchange
on 10 Nov 2023
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
