To create multiple records for each record with addition of 2 columns

[kumariaru]

Hi All,
I have a requirement where I need to generate multiple records for each record with addition of two columns.

EXAMPLE:

Input:
COL1,COL2,COL3
SAM,VICTOR,09/08/2006
BEN,THOMAS,06/05/2006
JOHN,RHYS,08/07/2005

Output:
COL1,COL2,COL3,COL4,COL5
SAM,VICTOR,09/08/2006,1,I.D
SAM,VICTOR,09/08/2006,2,I.B
SAM,VICTOR,09/08/2006,3,I.C
BEN,THOMAS,06/05/2006,1,I.D
BEN,THOMAS,06/05/2006,2,I.B
BEN,THOMAS,06/05/2006,3,I.C
JOHN,RHYS,08/07/2005,1,I.D
JOHN,RHYS,08/07/2005,2,I.B
JOHN,RHYS,08/07/2005,3,I.C

The file is csv file. I need to add two columns with default values to the respective record.For each record I need to add 1,2,3 in col4 and I.D,I.B,I.C in col5. I am not really good at unix scripting.But I think this can be done through scripting.If it is possible how can I proceed to write a script(mean basic idea).Hope I am clear with my question.

Thanks in advance,
Kumari

 

[PHV]

A starting point (typed, untested):
awk '{print $0",1,I.D\n"$0",2,I.B\n"$0",3,I.C"}' /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

 

[feherke]

Hi

And the same with [tt]sed[/tt] :

sed 's/\(.*\)/\1,1,I.D\n\1,2,I.B\n\1,3,I.C/' /input/file

[gray]# or my favorite, it is more sedish[/gray]

sed 'h;s/$/,1,I.D/;p;g;s/$/,2,I.B/;p;g;s/$/,3,I.C/' /input/file

Tested with GNU [tt]sed[/tt].

Feherke.

http://rootshell.be/~feherke/

 

[columb]

Or a realy easy ksh script

#!/bin/ksh

while read line
do
  echo ${line},1,I.D
  echo ${line},2,I.B
  echo ${line},3,I.C
done < infile > outfile

or, slightly more complex,

#!/bin/ksh

while read line
do
  for extra in ,1,I.D ,2,I.B ,3,I.C
  do
    echo ${line}$extra}
  done
done < infile > outfile

Ceci n'est pas une signature
Columb Healy

 

[kumariaru]

Thanks a lot for all of you.
I will work with solutions given by you all.

Thanks,
Kumari

 

[kumariaru]

Hi All,
I worked with all of your solutions but finally I picked :
#!/bin/ksh

while read line
do
echo ${line},1,I.D
echo ${line},2,I.B
echo ${line},3,I.C
done < infile > outfile

Because I do not have enough rights to work with sed.
The script is running fine for different input file and output file. But I for one file it is not happening, I am getting 0 rows overwriting. Shall I use mv command for outfile to infile.

And there slight change in my business requirement. I have two different files where I need to generate records and addition of columns based on input value.
For File1:
I need to generate 5 records with two columns added for each record (same as the previous one).
File2
Example:COL1,COL2,COL3,COL4,COL5
SAM,VICTOR,09/08/2006,1,I.D
SAM,VICTOR,09/08/2006,2,I.B
SAM,VICTOR,09/08/2006,3,I.C

if the COL4 value is 1 then I need to generate 5 records with addition of two columns. And for value 2 then 2*5 10 records with manually assigned values for the two extra columns. Is it possible to work with one script for two different files with different logic applied to them. I may expect fixed width files in future, if so How my logic is going to get changed.

Hope I am clear with my question.

Thanks in advance,
Kumari

 

[columb]

I can't quite follow the logic of what you are trying to do. In the first example the logic was easy. Writing it in pseudo-code:-

for each line in the file
  write that line with ,1,I.D appended
  write that line with ,2,I.B appended
  write that line with ,3,I.C appended

Can you put what you want to do to the second file in a similar manner, please? From what I can gather it looks something like:-

for each line in the file
  if col4 = 1
    write five records (but how are these records generated?)
  if col4 = 2 
    read extra values
    write ten records

Once the logic flow has been planned then we can look at how we implement it.

Ceci n'est pas une signature
Columb Healy