[SOLVED] Scan a file for values and convert into columns

Issue

I have an ldif file that contains millions of records and some of them start with the value dn: serv=CSPS. After that value could be a random number of rows that start with APNIDx: (being x a number from 1 to 40) and a number. I want to convert those three values into a row separated by commas.

I have these:

dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three
structuralObjectClass: CP1
objectClass: CP1
objectClass: CUDBServiceAuxiliary
objectClass: CP2
objectClass: CP3
objectClass: CP4
objectClass: CP5
objectClass: CP6
objectClass: CP7
objectClass: CP8
objectClass: CP9
objectClass: CPA
objectClass: CPB
objectClass: CPC
objectClass: CPD
objectClass: CPE
objectClass: CPF
objectClass: CPG
SUBSRSAVERS: 24
APNID10: 1
APNID11: 144
APNVERS11: 1
EQOSIDV11:: AA0B
EQOSIDV12:: AA0B
APNID12: 124
APNVERS12: 1
APNID13: 126
APNVERS13: 1
APNID14: 125
APNVERS14: 1
APNID15: 2
APNVERS15: 1
EQOSIDV13:: AA0B
EQOSIDV14:: AA0B
EQOSIDV15:: AA0B

I want a file that contains this for each dn: serv=CSPS:

dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID10,1
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID11,144
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID12,124
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID13,126
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID14,125
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID15,2

Please can someone help?

Solution

If you are open to using awk, you could try the following:

$ awk '/dn: serv=CSPS/ {r=$0} /APNID/ { sub(":","",$1); printf "%s,%s,%s\n", r,$1,$2 }' src.ldif 
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID10,1
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID11,144
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID12,124
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID13,126
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID14,125
dn: serv=CSPS,mscId=000070a852934078a32d08af1bb78c45,ou=multiSCs,dc=three,APNID15,2

Details:

/dn: serv=CSPS/ {r=$0} -- capture row into 'r' variable if matches the 'dn: serv=CSPS' string.

/APNID/ { sub(":","",$1); -- if a row matches the 'APNID' string, then remove the ':' colon from the first field in the row and then use printf "%s,%s,%s\n", r,$1,$2 to output the data in CSV format.

Answered By - j_b

Answer Checked By - Candace Johnson (WPSolving Volunteer)