1

I have file looks like that

User Charts
User ID:
40944827
User Name:
Joe, Neo
Gender:
M
DOB:
3/20/2000 12:00:00 AM - Age: 20 yr. 10 mo. old

I want to use awk or sed to get results in format of

LastName, First Name,Gender,DOB

Joe,Neo,M,3/20/2000
  • This might help with GNU sed as first step: sed '/:/{N;s/\n//}' file – Cyrus Feb 15 at 20:39
  • Where do "LastName" and "First Name" come from? They're not in your input file. Will "User Name" always be FirstName, LastName? Does the file only have those lines you show or can you have the data for multiple users in the same file? – terdon Feb 15 at 22:17
  • hi@terdon LastName" and "First Name is just an example for now I can get the results using NR in awk ``` awk 'NR>=5 && NR<=5 { print }' ``` – Mohamed Zaki Feb 15 at 23:10
  • If your real data has more than 1 record then show more than 1 record in your sample input/output. Things like how the records are separated matter. wrt the script in your comment awk 'NR>=5 && NR<=5 { print }' - think about for which value of NR it'd be true that NR>=5 && NR<=5. Big hint: NR==5. – Ed Morton Feb 16 at 17:55
1

Assuming your real input file contains more than 1 record, e.g.:

$ cat file
User Charts
User ID:
40944827
User Name:
Joe, Neo
Gender:
M
DOB:
3/20/2000 12:00:00 AM - Age: 20 yr. 10 mo. old
User ID:
1234
User Name:
Bob, Slob
Gender:
X
DOB:
5/28/2000 12:00:00 AM - Age: 20 yr. 10 mo. old

Here's how you can convert that to a CSV without coupling the code to the values of the input lines:

$ cat tst.awk
BEGIN { OFS="," }
NR==1   { next }
!(NR%2) { sub(/:.*/,""); hdrs[++numFlds]=$0 }
NR%2    { vals[numFlds]=$0 }
!((NR-1)%8) {
    if ( !doneHdr++ ) {
        for (fldNr=1; fldNr<=numFlds; fldNr++) {
            printf "\"%s\"%s", hdrs[fldNr], (fldNr<numFlds ? OFS : ORS)
        }
    }
    for (fldNr=1; fldNr<=numFlds; fldNr++) {
        printf "\"%s\"%s", vals[fldNr], (fldNr<numFlds ? OFS : ORS)
    }
    numFlds = 0
}

$ awk -f tst.awk file
"User ID","User Name","Gender","DOB"
"40944827","Joe, Neo","M","3/20/2000 12:00:00 AM - Age: 20 yr. 10 mo. old"
"1234","Bob, Slob","X","5/28/2000 12:00:00 AM - Age: 20 yr. 10 mo. old"

It's not exactly the output you were looking for but it might actually be more useful for you and hopefully you can see that it's just a tweak in the for loops to make any decisions based on data values and change the output for the selected field(s) as you like.

|improve this answer|||||
1

Here's a solution with awk since that is what you asked for. The solution is valid for any number of records in your data file, called datafile:

$ awk 'BEGIN {print "FirstName,LastName,Gender,DOB"}
       /User Name:|Gender:/ {dob=0;rtp=NR+1} 
       NR==rtp && dob==0 {printf $1 $2 ","} 
       /DOB:/ {dob=1;rtp=NR+1} 
       NR==rtp && dob==1 {print $1}' datafile
[output]
FirstName,LastName,Gender,DOB
Joe,Neo,M,3/20/2000

Line 1: print output's header:"FirstName,LastName,Gender,DOB"

Line 2: if record contains either "User name:" or "Gender:" set internal variables dob and rtp to 0 and NR+1 respectively.

Line 3: if record number is rtp and dob is 0, print two first field if they are non-empty

Line 4: if record contains "DOB:" set internal variables dob and rtp to 1 and NR+1 respectively.

Line 5: if record number is rtp and dob is 1, print first field.

|improve this answer|||||
0

You might want to take a look at Miller. Like awk, it has a built-in concept of records and fields - but unlike awk, it natively handles key-value pairs as well.

Ex.

mlr --idkvp --irs '\0' --ifs '\n' --ips ':\n' --ocsvlite put -S '
  u = splitnvx(${User Name},", "); $FirstName = u[1]; $LastName = u[2]; 
  d = splitnvx($DOB," "); $DOB = d[1]
' then cut -o -f 'FirstName,LastName,Gender,DOB' file
FirstName,LastName,Gender,DOB
Joe,Neo,M,3/20/2000
|improve this answer|||||
0

Here is a solution with sed:

sed -e 'N;N;N;N;N;N;N;N;s/.*Name:\n\([^[:space:]]*, [^[:space:]]*\)\nGender:\n\(.\)\nDOB:\n\([^[:space:]]*\).*/LastName, First Name,Gender,DOB\n\n\1,\2,\3/'  

whereby I think the output should look more like this: ;-)

sed -e 'N;N;N;N;N;N;N;N;s/.*Name:\n\([^[:space:]]*, [^[:space:]]*\)\nGender:\n\(.\)\nDOB:\n\([^[:space:]]*\).*/LastName, FirstName, Gender, DOB\n\n\1, \2, \3/'  
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.