Forum Discussion
Power Query or any other solution
Dear Experts,
I have a data in the below format(attached txt file too).
Each Packet( example for Packet-1) has the same structure.
I want to export , it to excel , with the Packet and the data under "Meta Log Buffer" as below for 1st packet.
Attached the result for just 1st Packet, the actual log file may have more than ~1000s of such Packets.
Could you please share some PQ script etc to fetch the data from the attached txt file(seems txt file not supported as attachment so zipped it) into the above format.
Thanks in Advance,
Br,
Anupam
anupambit1797 In case you are still interested in a PQ solution, the attached file contains a pretty straight-forward one. Just change the source path to the correct location on your own system and see it it produces the correct results.
Re:Power Query or any other solution
PHP as other solution
<?php
function parseBody(){
$files=glob('*.txt');
$handleOut=fopen('fileOut.csv','w');
$pattern='/Slot\|Frame\|Timetick\|PDU/';
foreach($files as $filename){
$handle = fopen ($filename, "r");
$new_line="";
$prefix="";$rowId=0;
$blnNewLine=false;
while (!feof ($handle)){
$buffer = fgets($handle, 4096);
$rowId++;
$line = trim($buffer);
if(preg_match('/2024 /',$line,$m)){
$prefix=$line;
}
if(preg_match($pattern,$line)){
$blnNewLine=true;
$buffer = fgets($handle, 4096);
// $line = trim($buffer);
$rowId++;
}else{
if(empty($line) && $blnNewLine==true){
$blnNewLine=false;
}
if(!empty($line) && $blnNewLine==true){
echo $line."\t";
echo $rowId."\n";
// die;
$new_line=str_replace('|',",",$line);
//$new_line.=str_replace('|',"\t",$line);
$new_line=trim($new_line,',');
//$new_line=rtrim($new_line,"\t");
$new_line=$prefix.','.$new_line."\r\n";
// $new_line.= $rowId."\n";
fwrite($handleOut,$new_line);
}
}
}fclose ($handle);
}
fclose($handleOut);
}
parseBody();anupambit1797 In case you are still interested in a PQ solution, the attached file contains a pretty straight-forward one. Just change the source path to the correct location on your own system and see it it produces the correct results.
Thanks Riny_van_Eekelen , If I may ask on the same question:-
Q1) How to add a Column for the corresponding Length for each line?
I tried to do the same , but it fetch me an Err as below
Q2) Secondly , if I want to add a Column with the Number of (Count of ) "|" in each string/line how to achieve that?
Feel free to use the same sample txt file for the above exercise..
Many Thanks in Advance,
Br,
Anupam
Regarding 1, I did this:
= Table.AddColumn(Source, "Length", each Text.Length([Column1]), Int64.Type)It seems you are missing some parentheses.
Regarding 2, I did this:
= Table.AddColumn(Source, "Custom", each Text.Length (Text.Select ([Column1], "|")))
