Rearrange data in columns.



  • Hello, I have certain data that I want to rearrange I have tried multiple approaches but I can’t get it to work.

    Here is an example of the text:

    DATA1="8DE" DATA2="322" DATA3="20" DATA4="19.99" DATA5="0.01"
    DATA1="FE4" DATA2="222" DATA4="400" DATA3="400" DATA5="0.00"
    DATA1="CE3" DATA2="444" DATA4="60" DATA5="0.00" DATA3="60"
    DATA1="MME" DATA3="20" DATA4="20" DATA5="0.00"
    DATA2="667" DATA4="30" DATA3="30" DATA5="0.00" DATA1="MH4"
    

    As you can see its not always in the same order and sometimes certain data its missing, what I want its to rearrange the data in its consecutive order and in case there is not data related it will be replaced with a blank data.

    This should be the output:

    8DE		322		20		19.99		0.01
    FE4		222		400		400		0.00
    CE3		444		60		60		0.00
    MH4		667		30		30		0.00
    MME				20		20		0.00
    

    I have tried the following but to no avail:

    FIND:

    DATA1=\"(.*?)\"|DATA2=\"(.*?)\"|DATA3=\"(.*?)\"|DATA4=\"(.*?)\"|DATA5=\"(.*?)\"
    

    REPLACE:

    \1	\2	\3	\4	\5
    

    and

    FIND:

    DATA1=\"(?<d1>.*?)\"|DATA2=\"(?<d2>.*?)\"|DATA3=\"(?<d3>.*?)\"|DATA4=\"(?<d4>.*?)\"|DATA5=\"(?<d5>.*?)\"
    

    REPLACE:

    $+{d1} $+{d2} $+{d3} $+{d4} $+{d5}
    

    I would be happy if someone can help or direct me to the right answer (and sorry for any misunderstanding as english is not my first languaje) I tried in other ways (deleting the data, renaming, linebreaks) but I can’t figure how to arrange the data as dessired, and by far I’m pretty new to REGEX and just grasp the basics.

    Thank you all.



  • Hello, @jorge-luis-alvarez,

    Before looking further on, about regexes, some basic questions. Approximatively,

    • How many lines DATA#="..." ...... DATA#="..." contain your file ?

    • Is DATA5 the maximum data or you may have up to DATA9 or even the form DATA## with 2 digits ?

    • May the values be possibly empty like, for instance, DATA3="" ?

    • Should, in the output file, data be sorted, according to the values of DATA1 ?

    Best Regards,

    guy038


Log in to reply