CODEWITHSUNDEEP
bashcsv
Massimo De Beni
Massimo De Beni

Reputation: 1

Create "smart" csv file using shell script

I have a txt file which has some records in the following format:

Name=Kevin
Age=33
Gender=M
Street=LA Road
Occupation=Service
Name=Josh
Age=22
Gender=M
Occupation=None
Name=Sarah
Street=First Avenue
Occupation=Administrator
...

As you can see I have a different set of properties that identify some persons, one after another, but not all of the properties may be set for all the persons. The only property that will ALWAYS be set is the Name value, so I would likely use that as a separator (as every value between tho different Name values are surely referred to the first of the Name values).

I want to generate a csv file from the above file as follows

File: output.csv ->

Name;Age;Gender;Street;Occupation
Kevin;33;M;LA Road;Service
Josh;22;M;<**BLANK**>;None
Sarah;<**BLANK**>;<**BLANK**>;First Avenue;Administrator
...

Can you please help me out to write a shell/perl script , as I want to upload this data into a database table.

Upvotes: 0

Views: 90

Answers (2)

Michael Back
Michael Back

Reputation: 1871

The following creates a table that grows to the right as we receive new key/val pairs and grows down as we see "Name" keywords. The advantage is that except for "Name," there is no hard-coded expectation of keywords in the script -- it just adapts and grows the table as it sees new things.

#! /usr/bin/awk -f
BEGIN {
        FS  = "="
        OFS = ";"
        key = 1
        val = 2
        split("", head)
        split("", table)
        row = 0
        col = 0
        name = 0
        blank = "<**BLANK**>"
}
NF == 2 {
        if (!name && $key != "Name")
                table[0,head["Name"]=col++] = "Name"
        name = 1
        if (!($key in head)) {
                for (i=1; i<=row; i++)
                        table[i, col] = blank
                table[0,head[$key]=col++] = $key
        }
        if ($key == "Name") {
                row++
                for(i=0; i<col; i++)
                         table[row,i] = blank
        table[row,head[$key]] = $val
}
END {
        for (i=0; i<=row; i++) {
                if (!col)
                        break
                x = table[i,0]
                for (j=1; j<col; j++)
                        x = x OFS table[i,j]
                print x
        }
}

The output follows:

Name;Age;Gender;Street;Occupation
Kevin;33;M;LA Road;Service
Josh;22;M;<**BLANK**>;None
Sarah;<**BLANK**>;<**BLANK**>;First Avenue;Administrator

Upvotes: 0

Jeff Y
Jeff Y

Reputation: 2456

This looks simple enough for awk; perl might be overkill:

awk -F= 'BEGIN {OFS=";"; print "Name;Age;Gender;Street;Occupation"}
         $1=="Name"{if(name)print name,a,g,s,o;
           name=$2; a=g=s=o="<**BLANK**>"}
         $1=="Age"        {a=$2}
         $1=="Gender"     {g=$2}
         $1=="Street"     {s=$2}
         $1=="Occupation" {o=$2}
         END {if(name)print name,a,g,s,o}'

Upvotes: 1

Related Questions