Normalize flat file data

March 1, 2010 at 06:42:15
Specs: Unix
Hi All, I am having a file with contents mentioned as below:

________________________________________________
File name : XXXXX
Location : XXXXX
NAME || JOB || ID || LOCATION
XYZ || A || 1 || LA
ABC || B || 3 || NY
________________________________________________


I need the out put as

________________________________________________
NAME = XYZ
JOB = A
ID = 1
LOCATION = LA


NAME = ABC
JOB = B
ID = 3
LOCATION = NY
________________________________________________

Is it possible my using shell scripting ? If possible please give me some idea.


Thanks,
Satya


See More: Normalize flat file data

Report •

#1
March 1, 2010 at 13:11:13
There are numerous ways to do this, but I chose to read each line, skip the first two lines, parse the 3rd line for the left side of the equal sign, and, finally, parse each succeding lines for the right side of the equal sign.

Keep two things in mind:
1) I use "||" as a field seperator. This works on my Solaris 9 box - can not quarantee any other environment, but it should work on ksh and bash shells.

2) while your data assumes 4 variables, this support supports a variable number.

Let me know if you have any questions:

#!/bin/ksh

# not much error checking
cnt=0
while read line
do
   ((cnt+=1))
   # skip lines 1 and 2
   if [[ $cnt -eq 1 || $cnt -eq 2 ]]
   then
      continue
   fi

   # parse the line
   set -- $(IFS="||"; echo ${line})

   # get the left side variable names from line 3
   if [[ $cnt -eq 3 ]]
   then
      nv=$#  # save the number of variables, left side of the equal sign

      # create the variables a1, ...a4
      i=1
      while (($i <= $nv))
      do
         eval a$i=\$$i
         ((i+=1))
      done
      continue
   fi

   # get the right side of the equal sign and echo the variables
   h=1
   while (($h <= $nv))
   do
      # get the right side of the equal sign
      eval b$h=\$$h

      # get the left side of the equal sign
      v1=$(eval echo \"\$a$h\")

      # get the right side of the equal sign
      v2=$(eval echo \"\$b$h\")

      printf "%s = %s\n" $v1 $v2
      ((h+=1))
   done

   # need two blank line between blocks
   echo
   echo
done < datafile.txt


Report •

#2
March 1, 2010 at 21:01:43
Thanks a lot. It is working fine. I was trying to do it using nawk but was facing lots of problems. I am sorry , i forgot to mention that the field names will always be constant.

NAME || JOB || ID || LOCATION

we can skip the left hand side variable creation. Anyways thanks again for your reply.


Report •

#3
March 2, 2010 at 03:59:14
Hi Nails,

If there is spaces between the values the script is not able to work fine. Can we overcome it.


Report •

Related Solutions

#4
March 2, 2010 at 08:11:31

How about deleting all spaces in the line? If that works for you, insert this line so it's the first line in the while loop:

line=$(echo "$line"|tr -d " ")

.

.
while read line
do
   # remove all spaces from the line
   line=$(echo "$line"|tr -d " ")
   .
   .


Report •

Ask Question