How can i take only the number of data and delete all the header so i can save it to xls?

I have data that that i've attached. This data contain header every several rows. How can i just get the number (in table)? so i can save the table of number to xls and i can edit the header manually.

soperating frequency = 1575.420 MHz
PRN     UT        LT         azimuth     elevation     sat.lat      sat.long      S4     sigma_phi Angular deviation  Coh. length       PLL         DLL      C / N     Proba of LoL
                              (deg)         (deg)       (deg)        (deg)                 (rad)         (mr)             (km)         (deg)       ( m.)

   6    11.000    17.687       235.867        8.366      -31.422       36.279       0.00      0.00           0.00          0.00           0.00        0.00      0.00         0.000
  12    11.000    17.687        54.551       12.873       31.175      158.771       0.00      0.00           0.00          0.00           0.00        0.00      0.00         0.000
  14    11.000    17.687       350.843       21.669       54.277       87.315       0.00      0.00           0.00          0.00           0.00        0.00      0.00         0.000
  18    11.000    17.687        66.853       54.594       10.231      125.741       0.00      0.00           0.00          0.00           0.00        0.00      0.00         0.000
  21    11.000    17.687       170.715       42.325      -36.873      107.311       0.00      0.00           0.00          0.00           0.00        0.00      0.00         0.000
  22    11.000    17.687         4.442       46.250       33.893      103.307       0.00      0.00           0.00          0.00           0.00        0.00      0.00         0.000
  25    11.000    17.687        77.902       41.379        7.292      137.881       0.00      0.00           0.00          0.00           0.00        0.00      0.00         0.000
  29    11.000    17.687       135.787        9.676      -41.239      158.633       0.00      0.00           0.00          0.00           0.00        0.00      0.00         0.000
  30    11.000    17.687       200.767       24.016      -48.863       74.646       0.00      0.00           0.00          0.00           0.00        0.00      0.00         0.000
  31    11.000    17.687       264.066       53.997       -2.944       72.583       0.00      0.00           0.00          0.00           0.00        0.00      0.00         0.000

 10 satellites in view

Answer 1

[Walter Roberson]

s = fileread('6jan2008s4.txt');
temp = regexp( regexprep(s, {'^.*[a-zA-Z].*', '^\s+'}, {'', ''}, 'lineanchors', 'dotexceptnewline'), '\r?\n', 'split');
temp(cellfun(@isempty, temp)) = [];
temp2 = regexp(temp,'\s+', 'split');
num = str2double( vertcat(temp2{:}) );

Now num is all numeric and you can write it as appropriate.

[Tian]

wow, thx sir. really fast and accurate answer. do you mind to tell me about the code, so i can understand it? thx again sir, i really appreciate it.

[Walter Roberson]

The code reads the file in as one string. Then it uses the regular expression replacement routine to change certain patterns of code.

In particular, it first detects the pattern of a line that might start and end with anything, but in the middle of the line has at least one alphabetic character in ['a':'z', 'A':'Z']. When it finds such lines, it changes the entire line to '' which is the empty line.

regexprep then takes the lines that are left and changes all whitespace at the beginning of lines to the empty string. This has two benefits: it changes lines that are all-blank to be empty; and it gets rid of the leading spaces on the other lines.

With those two text patterns altered, the text is then split into lines, giving temp as the output.

Then all of the empty lines in temp are deleted: these are the lines that were empty in the original file, together with the liens that were changed to be empty because at least one letter was found on the line.

With the empty lines deleted, what is left in temp is just the lines containing the numbers. If all you needed was a text file of those, that could be easily written without converting to number.

The lines of text containing numbers is then split at every run of spaces. This gives a cell array in which there is one entry per line and those entries are a cell array with one entry per column. The vertcat() call arranges all of those columns on top of each other, still as text. Then the str2double() call converts the strings representing numbers into numbers. There are other ways that the strings representing the numbers could have been converted to numeric, but this is one of the safest ways.

[Tian]

Thx again sir. I have add this code at the last

filename = 'try.xlsx';
xlswrite(filename,num)

It will make xlsx file in mfile directory. How could i do that in sequence file sir and name the xlsx same as the orignal txt file?

I've try this code, but there's error.

R = dir('*.txt')

for file = R'

    fprintf(1,'Doing something with %s\n',file.name)

    s = fileread(R);
temp = regexp( regexprep(s, {'^.*[a-zA-Z].*', '^\s+'}, {'', ''}, 'lineanchors', 'dotexceptnewline'), '\r?\n', 'split');
temp(cellfun(@isempty, temp)) = [];
temp2 = regexp(temp,'\s+', 'split');
num = str2double( vertcat(temp2{:}) );
filename = 'testdata.xlsx';
xlswrite(filename,num)

[Walter Roberson]

I just noticed, by the way, that the code I posted above will not work properly if the numbers might be in exponential format. For example if there is an entry 3.12345E-7 then the 'E' would be detected and the entire line would be thrown away. The code as posted is only for the case where there are no exponents and no complex-number indicators.

[Walter Roberson]

projectdir = '.';  %you can put in an absolute directory or leave as '.' for current directory

R = dir( fullfile(projectdir, '*.txt') );
filenames = fullfile(projectdir, {R.name});
numfile = length(filenames);

for idx = 1 : numfile
    filename = filenames{idx};

      fprintf(1,'Doing something with %s\n', filename)

      s = fileread( filename );
      temp = regexp( regexprep(s, {'^.*[a-zA-Z].*', '^\s+'}, {'', ''}, 'lineanchors', 'dotexceptnewline'), '\r?\n', 'split');
    temp(cellfun(@isempty, temp)) = [];
    temp2 = regexp(temp,'\s+', 'split');
    num = str2double( vertcat(temp2{:}) );
    [filepath, basename, ext] = fileparts(filename);
    outfile = fullfile( filepath, [basename '.xlsx'] );
    xlswrite(outfile, num);
  end

The above code assumes that the .xlsx file should go into the same directory the .txt was read from. The part after assigning to filenames does not assume that the files are all in the same directory.

[Tian]

thx again sir. It's ok that the code not work for exponents and complex-number indicators. All of data that i used were just the same as the file that i was attached. Thank you very much sir for your reply.

[Tian]

uhm,,, maybe i need some help again sir. i've try to add/change this code. I wanna add a header to excel, so its going batch too.

num2=num2cell(num);
  col_header={'PRN','UT','LT''azimuth (deg)','elevation (deg)','sat.lat (deg)','sat.long (deg)','s4 (rad)','sigma_phi (mr)','Angular Deviation (km)','Coh. Length (deg)','PLL (m.)','DLL','C/N','Proba of LoL'};     %Row cell array (for column labels)
  num3=[ col_header ; num2];     %Join cell arrays
  [filepath, basename, ext] = fileparts(filename);  
  outfile = fullfile( filepath, [basename '.xlsx'] );
  xlswrite(outfile, num3);
end

Thats code is work when i apply on this code

data=ones(10,4);     %Sample 2-dimensional data
data_cells=num2cell(data);     %Convert data to cell array
col_header={'Temperature','Pressure','X','Y'};     %Row cell array (for column labels)
output_matrix=[ col_header ; data_cells];     %Join cell arrays
xlswrite('My_file.xlsx',output_matrix);     %Write data and both headers

The error that come is like below

Error using vertcat
Dimensions of matrices being concatenated are not consistent.

Error in readnumtxt (line 15)
  num3=[ col_header ; num2];     %Join cell arrays

How to fix this sir?

[Walter Roberson]

    col_header={'PRN', 'UT', 'LT', 'azimuth (deg)', 'elevation (deg)', 'sat.lat (deg)', 'sat.long (deg)', 's4 (rad)', 'sigma_phi (mr)', 'Angular Deviation (km)', 'Coh. Length (deg)', 'PLL (m.)', 'DLL', 'C/N', 'Proba of LoL'};     %Row cell array (for column labels)

[Tian]

thx sir