reading content of a text file by readtable

Hello

I have this file exported by another software so I cannot make an Excel of it. I used readtable command to read numeric content below different variables. But it reads the second variables like BSTEN, BPR, BDENO AND BDENG but not the variables of the first row like FOE. How can I get all the columns of it?

I appreciate it in advance

fid=fopen('FAR.txt','w');
copyfile FAR.RSM FAR.txt
fclose(fid);
fid = fopen('FAR.txt');
opts = detectImportOptions('FAR.txt');
T=readtable('FAR.txt',opts);
T.FOE(3:end,1)

[dpb]

detectImportOptions is pretty smart, but not THAT smart!!!! :)

It interprets everything before the last data block as header information because it isn't a regular file but a (very! -- been a long time since I've seen the printer control in column one!!!) old Fortran printer output file you're trying to parse.

You'll have to write specific code for this...

NB: You do not need (and generally wouldn't want) to open a file you're going to create by copyfile -- on some systems this could lock the file so you couldn't actually do the copy.

COPYFILE() does it all by itself...but, you don't need to rename the file anyway...just open it directly; it's still a text file even though it doesn't have ".txt" for the extension.

[ali mohebi]

thanks for ur comment. yes it is kind of oldtran.but i need to get the contents

[dpb]

I've got meeting in town...

It's a simple-enough file format to parse; you'll just need to write specific code around textscan that has the right headerdline count and format for each section and then put that in a loop for the two sections and append that data.

Unfortunately, readtable doesn't have the ability to read a file in parts...

The potential kicker here is that there doesn't appear to be anyway other than scanning the file to find out the number of data entries in each section so the textscan read is probably going to suck up the second page feed '1' before it fails which may/probably will get the headerline count off for the second set.

It's not terribly difficult, just somewhat painful to get everything to work first time...

If there were enough of these in your future, it might be worth writing a routine that would reconstruct a clean, one-page file from the input file that could then just use readtable on...

[ali mohebi]

awesome.u think textscan could get what i want?

[dpb]

Sure...and, of course, you can build the table from the results of reading the file via textscan; the alternative suggested would be to not actually parse the file in detail in the first step but rearrange it to catenate the two pages into one.

The Q? on that regards do you need a general reader for any size of file or are these the only possible variables and number of variables?

It can, I presume, have any number of TIME values?

Also, what about the funky "3 5 38" values in some of the columns? Are (or could) those of any significance?

[Walter Roberson]

Do you need all of the variables, or just the first block?

[ali mohebi]

unfortunately all variables.

Answer 1

[dpb]

Given the long conversation after the first rudimentary "throwaway" solution, I'll post a more general and robust solution as separate answer...

function t=readRunFar(file)
% return table containing data from input file
  fid=fopen(file,'r');
  % read the fullfile as cell array, convert to string array, ignore the FORTRAN formfeed
  c=textscan(fid,'%s','delimiter','\n','Whitespace','','CommentStyle','1','CollectOutput',0);
  fid=fclose(fid);
  c=string(c{:});
  ix=find(contains(c,"SUMMARY"))-1;       % get section starting lines
  N=numel(ix);                            % the number of sections in file
  iyr=find(contains(c,"YEARS"));          % the start of second column of data
  iyr=strfind(c(iyr(1)),"YEARS")-1;
  c(ix(2):end)=extractAfter(c(ix(2):end),iyr); % remove the TIME column after first set
  iend=find(char(c(end))~=' ',1,'last');  % find last data on last section
  d=c(1:ix(2)-1);                         % begin a new data array with first section
  for i=2:N-1                             % join subsequent sections to first
    d=strcat(d, c(ix(i):ix(i+1)-1));
  end
  d=strcat(d, extractBefore(c(ix(N):end),iend+1));
  d=d(4:end);                             % trash the beginning header lines
  d=d(~all(char(d)==' '|char(d)=='-' ,2));% and the other extraneous text
  data=str2num(char(d(4:end)));           % convert to numeric
  vnames=split(d(1));                     % get the variable names from header
  vnames=vnames(~(vnames==""));           % split() leaves empties...
  vnames=categorical(vnames,unique(vnames,'stable')); % create ordered categorical variable
  cats=categories(vnames);                % the category names for logical addressing
  t=table(data(:,1),'VariableNames',cats(1)); % initial table entry
  for i=2:numel(cats)                     % and build the output table
    t=[t table(data(:,vnames==cats(i)),'VariableNames',cats(i))];
  end
end

The above reads the whole file into memory and mungs on it to rearrange it into one section by catenating the subsequent sections to the right of the first; snipping the TIME column from subsequent sections. It then parses the header line and creates a table--I ended up using a categorical variable and its facility to be used as logical addressing look to find associated columns instead of the alternative binning technique--that actually worked quite nicely! :)

This returns each duplicated column into the table with the generic name as an array of M columns; it is left as "Exercise for Student" to parse the units line and the node IDs to go with the variables.

I also attached the m file to be a little more convenient given its size

[dpb]

Result of above for the lastest file posted...

> readRunFar('far03.txt')
ans =
  14×14 table
    TIME     YEARS        FOE        FGOR      FPR         FOIP       FOPR    FGIT          BFLOGK                  BVGAS                        BSTEN                      BPR                BDENO               BDENG      
    ____    ________    ________    ______    ______    __________    ____    ____    ___________________    ____________________    _____________________________    ________________    ________________    ________________
      1     0.002738     0.00014    1.3567    3977.7    7.1239e+05    100     0       0.42135    0.073642           0           0          0          0          0    3837.9    3829.2    37.995    37.985         0         0
      3     0.008214    0.000421     1.334    3974.5    7.1219e+05    100     0       0.53486     0.14939    0.028387    0.028243    0.66255    0.68029    0.67887    3768.1    3748.1    38.147    38.205    14.706    14.628
    6.5     0.017796    0.000912    1.3411    3968.7    7.1184e+05    100     0        0.6265     0.15661       0.028    0.027747     0.7114    0.74509    0.74677    3713.8    3678.1    38.305    38.408    14.496    14.357
     10     0.027379    0.001403    1.3484    3962.8    7.1149e+05    100     0       0.67827     0.17083    0.027769    0.027466    0.74217    0.78437    0.78839      3681    3637.6    38.399    38.523    14.369    14.201
     15     0.041068    0.002104    1.3496    3954.4    7.1099e+05    100     0       0.72239     0.18983    0.027563    0.027218    0.77053    0.82053    0.82731    3651.6    3601.7    38.483    38.625    14.256    14.063
     20     0.054757    0.002806    1.3465    3946.2    7.1049e+05    100     0       0.78213     0.20923    0.027403    0.027011    0.79331    0.85189    0.86131    3628.4    3571.9    38.548     38.71    14.167    13.947
     30     0.082136    0.004209    1.3398    3930.2    7.0949e+05    100     0       0.85478      0.2375    0.027056    0.026597    0.84474    0.91793    0.93244    3578.3    3510.7    38.689    38.881    13.973    13.713
     40      0.10951    0.005611    1.3357    3914.8     7.085e+05    100     0       0.90514     0.24961    0.026729     0.02621    0.89594    0.98412     1.0023    3529.4    3453.1    38.822    39.041    13.789    13.491
     50      0.13689    0.007015    1.3316    3900.1     7.075e+05    100     0       0.93321      0.2584     0.02645    0.025898    0.94197     1.0406     1.0621    3487.8    3406.2    38.935    39.169     13.63     13.31
     60      0.16427    0.008417     1.327    3886.1     7.065e+05    100     0       0.95788     0.26673    0.026225    0.025646    0.98041     1.0884     1.1131    3452.9    3367.3    39.024    39.273    13.501    13.163
     70      0.19165     0.00982    1.3226    3872.6     7.055e+05    100     0       0.95869     0.26979    0.026027    0.025437     1.0157     1.1298     1.1569    3422.7    3335.4    39.103    39.359    13.387     13.04
     80      0.21903    0.011223    1.3181    3859.9     7.045e+05    100     0       0.96569     0.27689    0.025863     0.02526     1.0455     1.1656     1.1956    3396.8    3307.3    39.167    39.431    13.292    12.935
     90      0.24641    0.012626    1.3141      3848     7.035e+05    100     0       0.98006     0.28581    0.025717    0.025098     1.0727     1.1994     1.2327    3374.2      3282    39.225    39.496    13.207    12.839
    100      0.27379    0.014029    1.3105    3836.8     7.025e+05    100     0       0.99436     0.29473    0.025584     0.02495     1.0982     1.2312     1.2677    3353.1    3258.2    39.277    39.556     13.13     12.75
>> 

[ali mohebi]

i cant say anything. just thank youuuu. it worths too much.

[dpb]

'Tis OK...

If it does solve the problem, go ahead and ACCEPT an Answer so others won't keep coming back thinking they might just answer but hadn't had time...

I would suggest you study what I did to see how worked through the issues as learning experience.

None is all THAT difficult but does, granted, use a fair amount of past experience to know what features of Matlab are the ones for a given purpose--and, of course, it doesn't hurt that have been writing code for over 50 years now...which is scary to write! :)

It dawned on me an easy way to extract the units data so I've attached an updated version that incorporates them -- they only show up when you query the properties directly or use the summary function so they don't have the usefulness they might--don't know why TMW doesn't echo them at the command line if they're not empty strings--one more line of output doesn't seem anything to fret over and if the user went to the trouble to set them, seems rude to not see them easily--but that's just me.

The coordinates triplets could be retrieved as cell strings to display by the same logic using the next line in the string array. But, they're not so easily associated to the variable by having used the arrays for the common names; there's no way to assign them to the specific column.

Alternatively, one could back up a little and generate a distinct variable name for the repeated columns like those that are echo'ed by the summary function. All depends on what is really needed in the end.

Answer 2

[dpb]

function t=readRunFar(file)
% return table containing data from input file
  fid=fopen(file,'r');
  % read the two sections -- first sucks up the formfeed as data
  fmt1=repmat('%f',1,10);
  d1=cell2mat(textscan(fid,fmt1,'headerlines',9,'collectoutput',1));
  d1=d1(1:end-1,:);  % get rid of the line with only the formfeed
  fmt2=repmat('%f',1,5);
  d2=cell2mat(textscan(fid,fmt2,'headerlines',8,'collectoutput',1));
  d2=d2(:,2:end);      % and the duplicate time column ditto
  frewind(fid)          % go get the header lines and units
  h1=textscan(fid,'%s',10,'headerlines',4);     % first section
  h2=textscan(fid,'%s',5,'headerlines',4+size(d1,1)+5); % second
  fid=fclose(fid);
  t=array2table([d1 d2]);
  t.Properties.VariableNames=[h1{:};h2{:}(2:end)];
end

for your file returns...

>> readRunFar('far.txt')
ans =
  14×14 table
    TIME     YEARS        FOE        FGOR      FPR         FOIP       FOPR    FGIT    BFLOGK      BVGAS       BSTEN      BPR      BDENO     BDENG 
    ____    ________    ________    ______    ______    __________    ____    ____    _______    ________    _______    ______    ______    ______
      1     0.002738     0.00014    1.3567    3977.7    7.1239e+05    100     0       0.42135           0          0    3837.9    37.995         0
      3     0.008214    0.000421     1.334    3974.5    7.1219e+05    100     0       0.53486    0.028387    0.66255    3768.1    38.147    14.706
    6.5     0.017796    0.000912    1.3411    3968.7    7.1184e+05    100     0        0.6265       0.028     0.7114    3713.8    38.305    14.496
     10     0.027379    0.001403    1.3484    3962.8    7.1149e+05    100     0       0.67827    0.027769    0.74217      3681    38.399    14.369
     15     0.041068    0.002104    1.3496    3954.4    7.1099e+05    100     0       0.72239    0.027563    0.77053    3651.6    38.483    14.256
     20     0.054757    0.002806    1.3465    3946.2    7.1049e+05    100     0       0.78213    0.027403    0.79331    3628.4    38.548    14.167
     30     0.082136    0.004209    1.3398    3930.2    7.0949e+05    100     0       0.85478    0.027056    0.84474    3578.3    38.689    13.973
     40      0.10951    0.005611    1.3357    3914.8     7.085e+05    100     0       0.90514    0.026729    0.89594    3529.4    38.822    13.789
     50      0.13689    0.007015    1.3316    3900.1     7.075e+05    100     0       0.93321     0.02645    0.94197    3487.8    38.935     13.63
     60      0.16427    0.008417     1.327    3886.1     7.065e+05    100     0       0.95788    0.026225    0.98041    3452.9    39.024    13.501
     70      0.19165     0.00982    1.3226    3872.6     7.055e+05    100     0       0.95869    0.026027     1.0157    3422.7    39.103    13.387
     80      0.21903    0.011223    1.3181    3859.9     7.045e+05    100     0       0.96569    0.025863     1.0455    3396.8    39.167    13.292
     90      0.24641    0.012626    1.3141      3848     7.035e+05    100     0       0.98006    0.025717     1.0727    3374.2    39.225    13.207
    100      0.27379    0.014029    1.3105    3836.8     7.025e+05    100     0       0.99436    0.025584     1.0982    3353.1    39.277     13.13
>> 

[dpb]

You could, of course, add code to read the units to add to the table as well...I didn't because having the missing element in the one column is a real pain with C format scanning -- it "doesn't know nuthink!" about fixed-width files so one has to do some major gyrations to deal with it.

TMW added some tools but I've yet to have time to 'splore them enough to know just how to use them...and didn't have time at the moment to go try to learn, sorry...

Maybe someone else who has will stumble across the thread and can chime in.

[dpb]

Well I went and looked at the 'FixedWidthImportOptions' object -- it's a start that will solve quite a few problems but TMW still hasn't gotten past that every file must be regular from beginning to end and could have sections such as yours does--and the object is only useful for readtable which can't handle repeated calls on the same file excepting by directly calling it mulitple times with an externally computed 'headerlines' argument. Hence, it's of little practical use for your case.

[ali mohebi]

i really areciate it. it is a complicated case the case u mentioned above '3 5 8' is one of them,it may change to for example two BSTEM,one having '3 5 8' and theother one '2 4 5' or any other number under it.

[Walter Roberson]

Could you give us an example of a file like that?

[ali mohebi]

ofcourse.

As u see in the new file attached, in the second part BPR is repeated in two columns and this may exceed to any number and may happen to other variables. each part contains 10 columns of variables and when first part is completed remaining variables written in next part. begginer variable of new parts is 'TIME'.

[dpb]

Can you define that name to have a suffix A, B or such to make them unique?

If not, would have to scan the list to find the duplicates and add such before creating the table,

I didn't investigate the cause but the default scanning in textscan without the counted format string ended up with two extra columns so if the number of columns in the file changes will need to figure that out.

[Walter Roberson]

The '3 5 38' part looks like it might be fixed width two or three digit numbers. Is that the case? Is it possible for the numbers to exceed 99, such as '19 87122' ?

[dpb]

Actually, that's probably what "screwed up" the column count in textscan w/o the counted format string -- one of the three would match w/ one of the data columns with two more that would 'splain the Nx12 array returned.

[ali mohebi]

yes they are digit numbers less than a hundred. actually they are X, Y and Z cordinates of a point in cartesian coordinate. to explain it more, all the variable in that file like 'BSTEN' that have the coordinates under it in the main variable rows can have the same number as coordinates and i need to get all of them. for example i have the coordinates: '1 2 3', '1 2 4', '1 2 5'. i would have three 'BFLOGK', three 'BVGAS', three 'BSTEN', three 'BPR', three 'BDENO', and three 'BDENG'. each in seperate columns. i can explain more if there is any ambiguity.

thanks

[dpb]

" i have the coordinates: '1 2 3', '1 2 4', '1 2 5'. i would have three 'BFLOGK', three 'BVGAS', three 'BSTEN', three 'BPR', three 'BDENO', and three 'BDENG'...."

That isn't so in the sample file -- there are seven columns overall with the 3-digit triumvirate but only one of those associated variable names is duplicated and there are only two of them, not three.

So, parsing the number of elements under the variable is NOT an indicator of how many times that variable will be repeated in the output.

[ali mohebi]

Yes u r right. those are for showing the same property of two different points. in the case of 'BSTEN', columns under this variable shows this property for two different points with increasing time. but how many points? MATLAB should define that by the columns or whatever i dont know.

[dpb]

Well, no... :)

Now there are:

• 2 BFLOGK
• 2 BVGAS
• 3 BSTEN
• 2 BPR
• 2 BDENO
• 2 BDENG

So, it isn't that the coordinates tell how many there are going to be; otoh there are some number of duplicated columns (that is apparently user-specified as to which) and all the triplet does is identify which coordinate that column happens to be.

One still will have to parse the header array and count the number of repetitions to determine which is output more than once and how many times in order to either

1. Build a unique variable name, or
2. Build an array for the variable of N columns

[ali mohebi]

to make long story short, the coordinates just determine the number of columns for each variable. the number of coordinates may also differ from one variable to another.

[dpb]

"the coordinates just determine the number of columns for each variable."

But the above demonstrates that is NOT so; at least from the back end forward.

There may be (and probably is) something in the input file that determines that, but one can't tell from the output anything at all about how many there are of a given variable except by simply counting.

[ali mohebi]

ok. let me tell it in other way. consider those properties, for example 'BSTEN'. in the input file of the other software that generates this file, i import those coordinates and ask to export 'BSTEN' for that points. this file is the file generated by that software which is going to be coupled with MATLAB because the numbers under this variables are used in mathmatical calculations, i want matlab to open the exported file and use the numbers to perform calculation but, i dont know how to read them when i have for example 'n' column of a variable nemed 'BSTEN', that is the point.

[dpb]

" i import those coordinates and ask to export 'BSTEN' for that points"

Which is what I just above said had to be happening...

The easiest way would be to use that other input as the Rosetta stone from which to know ahead of time how many of which are going to be in the file instead of starting just with the file and then having to figure all that out again.

As noted, it's doable--read all the header lines and, basically, histogram them to count who and how many of each. Since you already determined that before you ever made the run, it would be far simpler to also input that data into the reader routine(*)...for one thing, it will let you precompute how many sections of 10 columns there are to deal with the formatting issue besides the column name counts.

(*) Ir wouldn't have to be direct input as an argument list; a separate data file or the actual input file for the run itself could be read. If the input file to the code isn't too hard, that might be the simplest way logistically.

[ali mohebi]

U have helped so much by now and i think completely understand it, we suppose that 'n' is the number of column,could u please generalise ur code for it?

[dpb]

Have you made any attempt to generalize the code I already supplied to incorporate either of the two ways I suggested to approach the column-counting problem? There's enough illustration there to do what you need without very much additional.

You haven't answered the question about fhe form of the input deck/file to determine how many duplicated columns there are for each of the nodes

If you just use N columns, then instead of calling textscan twice explicitly, there will be a loop of 1:fix(N/10) reads of 10 columns (imitating first call) and either one or none to read the remainder mod(N,10) (imitating second).

There is the fact that will only want to kee the time column for the very first call in the output result even though still reading 10 columns for the first loop.

[ali mohebi]

yeah, im trying to generalize it with dom and fix but its getting complicated. the funny thing is the code u write for file i sent doesnt work today. number of duplicate columns for each variable is determined by user. i ask it to give BSTEN for three points and it will have three columns of BSTEN but with different cartesian coordinates under it which we dont read those coordinates.

[dpb]

The routine I wrote will ONLY work for a case with 15 columns specifically and with no repeated column headers...that was the original problem definition! :)

It also does specifically ignore the input coordinates as there wasn't a specification saying either what they were nor that they were wanted/needed? See, I can always claim "Out of scope!" -- I wasn't consultant for 30+ years w/o learning something! <VBG>

Seriously, you'll need to take the ideas presented and exapand on them somewhat -- first of all knowing the number of columns is a big help to parse the data itself but you still will have to either pass the information regarding the duplicated columns and their positions or read them all into an array and do the counting of how many unique variables there are and how many of each of them is there to build the final table if you wish to stay with a table. You could, of course, just return a plain ol' double array and keep track by column number without the actual name and deal with that externally.

[dpb]

OK another couple clarifications --

1. Are the variable names in the file a complete set of all possible variables ever?
2. Is there likewise an exhaustive list of which variables are possibly output by node and those that aren't?
3. Are the output times/number specified in the input so that could also be passed into the routine?

[ali mohebi]

1. The variables in new attached file are all possible variables.
2. There may be another list of variables containing other variables too which not mentioned here, but it doesn’t need here.
3. Yes, the time is specified in the input file. It is like this; the software is asked to make an output file from zero to any time you want. That steps taken for time u see in the output file is taken by software and we can’t say it will be regular.

[dpb]

OK...I've got a few minutes, think I can get you a starting point to finish up with in reasonably short order.