how to put data in order

[omoo]

Hi,

I have data in this format:

"1L516313";;"P21P";"T";200;200;"00";24;0;"";"";"";"";"YI136ET001";"001A";0;"L9";"EXEC";"!";;;;;;; 0;0;;;;;;;;;;;;;;;24;"0";120;0;120;0;1;;"1L516315";;"P21P";"T";200;200;"00";21;0;"";"";"";"";"YI136ET001";"001A";0;"L9";"EXEC";"!";;;;;;; 0; 0;;;;;;;;;;;;;;;17;"0";85;0;85;0; 1;;

and I need my final output to be in this format:

"1L516313";;"P21P";"T";200;200;"00";24;0;"";"";"";"";"YI136ET001";"001A";0;"L9";"EXEC";"!";;;;;;; 0;0;;;;;;;;;;;;;;;24;"0";120;0;120;0; 1;;
"1L516315";;"P21P";"T";200;200;"00";21;0;"";"";"";"";"YI136ET001";"001A";0;"L9";"EXEC";"!";;;;;;; 0;0;;;;;;;;;;;;;;;17;"0";85;0;85;0; 1;;

As you can see, the original data is all in a single line but the fields are acually repeated. Is there a way I can use awk command to put it in columns with new line for every set of repeated data fields? If possible, pls possible, pls provide code for this formatting script. Thank you very much!

 

[omoo]

I have tried this:

nawk '{sub(/^1L/,"&\n");print}'

but it says the record is too long. It seems like the original data is too big for this code to run? I am not sure. Anyone can help?

 

[PHV]

You may try something like this:
awk '
BEGIN{RS=";"}
{a[NR%49]=$0}
!(NR%49){
for(i=1;i<49;++i)printf "%s;",a
print a[0];split("",a)
}
' /path/to/input > output

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[omoo]

Hi,

the output file shd be in this format:

"1L516313";;"P21P";"T";200;200;"00";24;0;"";"";"";"";"YI136ET001";"001A";0;"L9";"EXEC";"!";;;;;;; 0;0;;;;;;;;;;;;;;;24;"0";120;0;120;0; 1;;
"1L516315";;"P21P";"T";200;200;"00";21;0;"";"";"";"";"YI136ET001";"001A";0;"L9";"EXEC";"!";;;;;;; 0;0;;;;;;;;;;;;;;;17;"0";85;0;85;0; 1;;

with all the semi-colons replaced by commas and one point which might help is that all the new lines start with "1Lxxxxxx.

 

[omoo]

Hi PH,

I have tried ur code but it gives a syntax error near line 4.

 

[PHV]

Try nawk or gawk instead of awk.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[omoo]

Hi,

Thanks a lot for the help.

I have another additional problem:

This data is not the start of the datafile. Meaning to say the file look like this:

ABCD / 4.75 / 20050523;"text1";"text2";"text3";"header1";"header2";;;;;;;;;;;"1L516313";;"P21P";"T";200;200;"00";24;0;"";"";"";"";"YI136ET001";"001A";0;"L9";"EXEC";"!";;;;;;; 0;0;;;;;;;;;;;;;;;24;"0";120;0;120;0;1;;"1L516315";;"P21P";"T";200;200;"00";21;0;"";"";"";"";"YI136ET001";"001A";0;"L9";"EXEC";"!";;;;;;; 0; 0;;;;;;;;;;;;;;;17;"0";85;0;85;0; 1;;

The output file shd look like this:

ABCD 4.75 20050523
text1
text2
text3
"header1";"header2"
;;;;;
;;;;;
"1L516313";;"P21P";"T";200;200;"00";24;0;"";"";"";"";"YI136ET001";"001A";0;"L9";"EXEC";"!";;;;;;; 0;0;;;;;;;;;;;;;;;24;"0";120;0;120;0;1;;
"1L516315";;"P21P";"T";200;200;"00";21;0;"";"";"";"";"YI136ET001";"001A";0;"L9";"EXEC";"!";;;;;;; 0; 0;;;;;;;;;;;;;;;17;"0";85;0;85;0; 1;;

The number of fields before the "1L is not fixed. and also, all the semi-colons shd be replaced by commas.
I am not sure if it is possible to continue using the previous part that you have advised.

 

[PHV]

You may try this awk program as a starting point:

BEGIN{RS=";"}
NR==1{gsub(" /","");print;next}
NR<=4{gsub(/"/,"");print;next}
NR==5{printf "%s,",$0;getline;print;next}
/^"1L/{printf "\n"}
$0=="\n"{exit}
{printf "%s,",$0}
END{printf "\n"}

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[omoo]

thanks. i am working on it