Reading columns from a poorly-formatted text file
Show older comments
I need to read data from an old text file which has fixed columns for the data. I have tried using readtable, which was partly succesfull but seems to have been fooled by the data in places.
A shortened version of the file is attached. It has the following 'features' :
- All columns are not always there
- Sometimes there is no space between the columns
- It uses -998 or -998.0 for NaN;
- There are blank lines.
Is there an easy way to read this file, or do I need to be old-fashioned and read it line-by-line and then parse for the values?
Accepted Answer
This file looks like a fixed-width file, but it's not quite, because lines 68 and 69 differ in format from the other lines.
dbtype COUNT.txt 65:70
For example, if you use fixed field sizes to parse line 65 like this
% 29 JUN 97 00:00 0 01109 40
% ^^^^^^^^^^^^^^^ 29 JUN 97 00:00
% ^^^^ 0
% ^^^^ 0
% ^^^^ 1109
% ^^^^ 40
Then the same parsing for line 68 gives this:
% 05 SEP 97 00:00 0 16 5 68
% ^^^^^^^^^^^^^^^ 05 SEP 97 00:00
% ^^^^ 0
% ^^^^ 1
% ^^^^ 6 5
% ^^^^ 68
Notice the 16 gets split apart: that field becomes 1 and the 6 is included in the next field, which becomes '6 5' (which will be interpreted as NaN).
On the off chance that lines 68 and 69 were inadverdently modified during the shortening process and the original file doesn't have this problem, you can use some code like this to read it:
opts = fixedWidthImportOptions( ...
'VariableNamesLine',3, ...
'DataLines',5, ...
'NumVariables',7, ...
'VariableWidths',[15 4 4 4 4 4 6], ...
'VariableTypes',{'datetime','double','double','double','double','double','double'});
opts = setvaropts(opts,1,'InputFormat','dd MMM yy HH:mm','DatetimeFormat','preserveinput');
T = readtable('COUNT.txt',opts);
T = standardizeMissing(T,-998);
disp(T)
But again, for this file, the data from lines 68 and 69 (05 and 06 SEP 97) is incorrect because of the misalignment of the 16 and the 15 on those respective lines:
tail(T)
More Answers (1)
Steven Lord
on 29 Aug 2024
0 votes
I would experiment with the settings controllable using the Import Tool to try to import the data. You could try running Import Tool with your smaller data set, import the data, and check that it imported as you expected. Then generate a script and run it on the larger file.
The conditions you identified that could make it difficult to read are:
- All columns are not always there
- Sometimes there is no space between the columns
- It uses -998 or -998.0 for NaN;
- There are blank lines.
If you specify that the file is fixed-width I think the second condition is okay. Unimportable cell handling may take care of the first condition. I think the blank lines will be imported as a row of unimportable cells, which you could repair after the fact with the missing-handling functions like rmmissing. Once you've imported the data, you could use standardizeMissing to replace -998 with NaN.
Categories
Find more on Text Files in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
