Power Query or any other solution

[anupambit1797]

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

[#2]

Hi,@anupambit1797 

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();

[#3 · Accepted solution]

@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.

[#4]

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

[#5]

@anupambit1797 

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], "|")))