Parse text file???

5 次查看（过去 30 天）
FishermanJack
FishermanJack 2017-11-6
回答： Motasem Mustafa 2020-10-23
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.
  4 个评论
显示 2更早的评论
per isakson
per isakson 2017-11-13
编辑：per isakson 2017-11-13
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
FishermanJack 2017-11-13
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
per isakson 2017-11-13
编辑：per isakson 2017-11-13
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
....
  5 个评论
显示 3更早的评论
per isakson
per isakson 2017-11-13
"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
FishermanJack 2017-11-13
ok, thanks for the Answer.

请先登录，再进行评论。

更多回答（1 个）

Motasem Mustafa
Motasem Mustafa 2020-10-23
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

类别

在 Help Center 和 File Exchange 中查找有关 Data Type Identification 的更多信息

标签

产品

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by