Parse text file???
Show older comments
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 Comments
Stephen23
on 13 Nov 2017
@FishermanJack: we cannot test code on a screenshot. Please edit your question and upload a sample file by clicking the paperclip button.
FishermanJack
on 13 Nov 2017
per isakson
on 13 Nov 2017
Edited: per isakson
on 13 Nov 2017
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
on 13 Nov 2017
Accepted Answer
per isakson
on 13 Nov 2017
Edited: per isakson
on 13 Nov 2017
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 Comments
FishermanJack
on 13 Nov 2017
Edited: per isakson
on 13 Nov 2017
per isakson
on 13 Nov 2017
Edited: per isakson
on 13 Nov 2017
- 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
on 13 Nov 2017
per isakson
on 13 Nov 2017
"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
on 13 Nov 2017
More Answers (1)
Motasem Mustafa
on 23 Oct 2020
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
Categories
Find more on Characters and Strings in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
