Parse text file???

Hi, i have an txt.File like this:

with ca. 35040 rows for Each Elemnt.

I need to parse out and collect the Data that is between each header line. I need the header lines also for every block of Data.

Can anyone suggest me something.

Here is the original text file, but i think it is easier manuly to Change the , with the . and the / with _ so i can match the Element. xpr = '(?m)^Element\s+\d+\s+\w+\s+\w+\s*$';

Maybe this can be also an imporant fact, that i have 214 Elements and the Date goes from 01.01.2016 00:00 to 01.01.2017 00:00 in 15min steps.

[Stephen23]

@FishermanJack: we cannot test code on a screenshot. Please edit your question and upload a sample file by clicking the paperclip button.

[FishermanJack]

Here is the original File that Needs to be converted

[per isakson]

I updated the code to read original.txt before reading your new question. See my answer.

Now, I assume the blocks are separated by lines starting with "Elements" followed by whitespace, one or more digits and whatever up till the end of the line. And there are no other lines starting with "Elements" followed by whitespace and one digit.

I didn't see a clean way to parse the date-time, but it seems to work. The values of date-time should not be a problem.

The decimal separator, comma, isn't a problem. The new code works with either. But not with comma as list separator!

"[...] but i think it is easier manuly to Change the ,[...]" Test my new code first. If it works, don't change anything. Test edge cases. Until tomorrow.

[FishermanJack]

i recognized that the Code runs with Matlab, but has following issues with Octave.

warning: called from fileread at line 35 column 7 cssm at line 3 column 11

warning: textscan: 'headerlines' ignored when reading from strings

warning: strread: unknown property 'HeaderLines'

error: datevec: DATE not parsed correctly with given format

error: called from datevec at line 147 column 11 datenum at line 104 column 40 cssm at line 24 column 15.

Answer 1

[per isakson]

Similar questions have been answered by me and others, e.g. https://se.mathworks.com/matlabcentral/answers/312599-how-do-i-parse-this-complex-text-file-with-textscan#answer_243764. In that answer I describe my approach to read and parse text files with repeated blocks of data. I don't repeat that description here.

To read your file I made a few specific assumptions:

• you have enough RAM to read the entire file to one string. "with 36000 rows for Each Elemnt" fine, but how many elements (/blocks)?
• the blocks are separated by an entire line that consists of "Element", whitespace, one [or more] digit, whitespace, one [or more] letter, whitespace, one [or more] letter and optionally trailing whitespace. (I matched letter with \w, which also matches digits and underscore.)
• the columns of the file are separated by tab or space.
• the data line matches '%s%s%f%f%f%f%f%f'

"[or more]" added later, but it was in the code from the beginning.

Try

>> Out = cssm( 'h:\m\cssm\BlockOfText.txt' )
Out = 
1x6 struct array with fields:
    BlockHead
    ColHead
    Data
>> Out(1)
ans = 
    BlockHead: 'Element 1 A B'
      ColHead: {'Date I P Q Ploss Qloss'}
         Data: [3x7 double]
>> Out(1).Data
ans =
   1.0e+05 *
    7.3633    0.0000   -0.0001    0.0001    0.0002    0.0000   -0.0000
    7.3633    0.0000   -0.0001    0.0001    0.0002    0.0000   -0.0000
    7.3633    0.0000   -0.0001    0.0001    0.0002    0.0000   -0.0000
>> datestr( Out(1).Data(:,1) )
ans =
01-Jan-2016 00:00:00
01-Jan-2016 00:15:00
01-Jan-2016 00:30:00
>>

where

function    Out = cssm( ffs )
    % Out = cssm( 'h:\m\cssm\BlockOfText.txt' );
    str = fileread( ffs );
    xpr = '(?m)^Element\s+\d+\s+\w+\s+\w+\s*$';
    [ block_list, headers ] = strsplit( str, xpr ...
        ,   'DelimiterType','RegularExpression'  ...
        ,   'CollapseDelimiters',false           );
    block_list(1) = []; % Assumption: the first line is a delimiter
    Out = struct( 'BlockHead',headers, 'ColHead',{''}, 'Data',{[]} );

      for jj = 1 : length( block_list )

          Out(jj).BlockHead = strtrim( headers{jj} );

          block = strtrim( block_list{jj} );

          cac = textscan( block, '%s', 1, 'Delimiter','\n' );
          Out(jj).ColHead = cac{:};

          cac = textscan( block   , '%s%s%f%f%f%f%f%f' ...
              ,   'HeaderLines'   , 1                  ... % strsplit captured one
              ,   'Delimiter'     , {'\t',char(32)}    ...
              ,   'CollectOutput' , true               );

          dt  = strcat( char(cac{1}(:,1)),  char(cac{1}(:,2)) );
          sdn = datenum( dt, 'dd-mmm-yyyyHH:MM' ); % serial date number
          Out(jj).Data = cat( 2, sdn, cac{2} );
      end
  end

and where BlockOfText.txt contains six blocks. (I copied the image in the question and used OCR to get a text file.)

Element 1 A B
Date I P Q Ploss Qloss
01-Jan-2016 00:00 0.07 -11.22 9.67 16.53 0.04 -0.32
01-Jan-2016 00:15 0.07 -11.22 9.67 16.53 0.04 -0.32
01-Jan-2016 00:30 0.07 -11.47 10.17 17.13 0.04 -0.31
Element 2 A C
....

The page "Import Large Text File Data in Blocks" in the documentation describes a different approach. textscan reads "until it cannot read a block of data in the format specified by formatSpec". At that point textscan doesn't throw an error, instead it stops, outputs the data read so far and the location of file pointer is remembered. This is then repeated (while-loop) from the current location of the file pointer (until EOF).

.

New version

>> Out = cssm( 'h:\m\cssm\original.txt' )
>> Out
Out = 
1x3 struct array with fields:
    BlockHead
    ColHead
    Data
>> Out(2).Data
ans =
   1.0e+05 *
    7.3633    0.0000    0.0002    0.0001    0.0001    0.0000   -0.0000
    7.3633    0.0000    0.0002    0.0001    0.0001    0.0000   -0.0000
    7.3633    0.0000    0.0002    0.0001    0.0001    0.0000   -0.0000
    7.3633    0.0000    0.0002    0.0001    0.0001    0.0000   -0.0000
>> datestr( Out(2).Data(:,1), 31 )
ans =
2016-01-01 00:00:00
2016-01-01 00:15:00
2016-01-01 00:30:00
2016-01-01 00:45:00
>> 
>> Out(2).Data(:,5)
ans =
   10.9051
   10.9051
   10.5748
   10.3330

where

function    Out = cssm( ffs )
    % Out = cssm( 'h:\m\cssm\original.txt' );

      str = fileread( ffs );
      xpr = '(?m-s)^Element\s+\d+.+$';
      [ block_list, headers ] = strsplit( str, xpr    ...
          ,   'DelimiterType','RegularExpression'     ...
          ,   'CollapseDelimiters',false              );
      block_list(1) = [];
      Out = struct( 'BlockHead',headers, 'ColHead',{''}, 'Data',{[]} );

      for jj = 1 : length( block_list )

          Out(jj).BlockHead = strtrim( headers{jj} );

          block = strtrim( block_list{jj} );

          cac = textscan( block, '%s', 1, 'Delimiter','\n' );
          Out(jj).ColHead = cac{:};

          block = strrep( block, ',', '.' );
          cac = textscan( block , '%s%s%s%s%f%f%f%f%f%f'  ...
                      ,   'HeaderLines'   , 1             ...
                      ,   'Delimiter'     , '\t'          ...
                      ,   'CollectOutput' , true          );

          dd  = regexprep( cac{1,1}(:,3), '\<(\d)\>', '0$1' );
          dt  = cat( 2, char(cac{1,1}(:,1)), char(cac{1,1}(:,2)) ...
                      , char(dd)           , char(cac{1,1}(:,4)) );

          sdn = datenum( dt, 'yyyymmmddHH:MM' );
          Out(jj).Data = cat( 2, sdn, cac{2} );
      end
  end

and where original.txt

Element  1  118/3C  188/3B
year  month  day  t [hh:mm]  I [kA]  P [MW]  Q [MVAr]  Auslastung [%]  Ploss [MW]  Qloss [MVAr]
2016  Jan  1  00:00  0,023196  -4,075936  2,191843  3,596235  0,010932  -0,584301
2016  Jan  1  00:15  0,023196  -4,075936  2,191843  3,596235  0,010932  -0,584301
2016  Jan  1  00:30  0,023912  -4,236306  2,194084  3,707329  0,011152  -0,583560
2016  Jan  1  00:45  0,025770  -4,660033  2,171770  3,995393  0,011752  -0,581561
Element  1  120/8B  170/8B
....

[FishermanJack]

thanks for the new Code. i recognized that the Code runs with Matlab, but has following issues with Octave.

warning: called from
    fileread at line 35 column 7
    cssm at line 3 column 11

warning: textscan: 'headerlines' ignored when reading from strings

warning: strread: unknown property 'HeaderLines'

error: datevec: DATE not parsed correctly with given format

error: called from
    datevec at line 147 column 11
    datenum at line 104 column 40
    cssm at line 24 column 15.

[per isakson]

• The functions works here with my toy text file, BlockOfText.txt.
• Your text file differs from mine in some respect that I didn't think of. And I cannot guess.
• Your structure, Out, is scalar. Does your text file only contain one element?
• "missing data" is a common reason for failures in reading text files
• Were there no warning or error message? There is admittedly no error checking in my code.
• If you upload (with the paperclip-button) a more realistic text file I'll fix the code.
• Debugging by comments is not effective.
• Try to read my toy text file, BlockOfText.txt, which I attached to this comment.

[FishermanJack]

thanks for your answer, i attached the original file that Needs to be converted in the question i edited.

[per isakson]

"issues with Octave" Matlab is a moving target and Octave have problems keeping up. I've never used Octave. I guess, it would easy for someone who knows both to port the code.

[FishermanJack]

ok, thanks for the Answer.

Answer 2

[Motasem Mustafa]

I used to have the same issue abd I have posted my question yesterday :

'' Dears,

I am using the code below to do parsing for date-time cells in an MS Excel sheet with date-time form of ( 01/05/2019 00:00) as in the screenshot below.

clc,clear,close all;
[num1,data] = xlsread('Book_new.xlsx','sheet1','A1:A30');
a=datevec(data,'dd/mm/yyyy HH:MM:SS');
date=datestr(datenum(a),'dd/mm/yyyy');
time=datestr(datenum(a),'HH:MM:SS');
Year=datestr(datenum(a),'yyyy');
mm=datestr(datenum(a),'mm');
dd=datestr(datenum(a),'dd');
yy=datestr(datenum(a),'yyyy');
[status,message] =xlswrite('motasem.xlsx',str2num(yy),'sheet1','A1:A30');
[status,message] =xlswrite('motasem.xlsx',str2num(mm),'sheet1','B1:B30');
[status,message] =xlswrite('motasem.xlsx',str2num(dd),'sheet1','C1:C30');
[status,message] =xlswrite('motasem.xlsx',string(time),'sheet1','D1:D30');

When I run the code for example for the 1st 30 readings (half hourly readings) it gives me the following error :

"Error using dtstr2dtvecmx

Failed to convert from text to date number.

Error in datevec (line 123)

y = dtstr2dtvecmx(t,icu_dtformat);

Error in motasem (line 4)

a=datevec(data,'dd/mm/yyyy HH:MM:SS');"

But when I change the range of data to avoid the first reading which contains the time 00:00:00 it works and gives the below output :

Any suggestions please ?

"

The new code that works is using readtable function as follows :

clc,clear,close all;
data = readtable('Book_new.xlsx','Range','A1:A60','ReadVariableNames',false);
A = table2array(data);
yy=datestr(datenum(A),'yyyy');
mm=datestr(datenum(A),'mm');
dd=datestr(datenum(A),'dd');
time=datestr(datenum(A),'HH:MM:SS');
[status,message] =xlswrite('motasem.xlsx',str2num(yy),'sheet1','A1:A30');
[status,message] =xlswrite('motasem.xlsx',str2num(mm),'sheet1','B1:B30');
[status,message] =xlswrite('motasem.xlsx',str2num(dd),'sheet1','C1:C30');
[status,message] =xlswrite('motasem.xlsx',string(time),'sheet1','D1:D30');

Hope this will help you

All the best