Rearrange data in columns.

Jorge Luis Alvarez

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.

guy038

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