Shell Programming and Scripting

This script should works with more sets assuming your file has a fixed structure e.g. if your file has 3 sets the header should looks like:

Header,4567,USD,BMI,,4568,USD,BMI,,4569,USD,BMI,

And the data with 3 sets:

22-Dec-97,27.06492573,,20.9289574,,26.17032566,,20.60702136,,26.17032500,,20.60702199,

Script:

Code:

awk 'BEGIN{FS=",";print "Date, index_id,currency,index_pro, level, net, total, mcap"}
NR==1{
  nSets=(NF-1)/4
  j=2
  for(i=1;i<=nSets;i++) {
    s[i]=$j FS $(j+1) FS $(j+2)
    j+=4
  }
  getline; next
}
{
  j=2
  for(i=1;i<=nSets;i++) {
    printf("%s,%s,%s,%s,%s,\n", $1,s[i],$j,$(j+1),$(j+2))
    j+=4
  }
}' awk.dat

Regards

awk:

Code:

awk 'BEGIN{
FS=","
print "Date, index_id,currency,index_pro, level, net, total, mcap"}
{
if(NR==1){
        index1=$2
        cur1=$3
        pro1=$4
        index2=$6
        cur2=$7
        pro2=$8
}
if(NR>=3){
        print $1","index1","cur1","pro1","$2","$3","$4","
        print $1","index2","cur2","pro2","$6","$7","$8","
}
}' file

perl:

Code:

print "Date, index_id,currency,index_pro, level, net, total, mcap\n";
open (FH,"<a");
while(<FH>){
	@arr=split(",",$_);
	if($.==1){
		$index1=$arr[1];
		$cur1=$arr[2];
		$pro1=$arr[3];
		$index2=$arr[5];
		$cur2=$arr[6];
		$pro2=$arr[7];
	}
	if($.>=3){
		print $arr[0],",",$index1,",",$cur1,",",$pro1,",",$arr[1],",",$arr[2],",",$arr[3],",\n";
		print $arr[0],",",$index2,",",$cur2,",",$pro2,",",$arr[5],",",$arr[6],",",$arr[7],",\n";
	}
}
close(FH);

Hi Franklin,
I tried to redirect the output to a file by
print "Date, index_id,currency,index_pro, level, net, total, mcap"} >> output_file.dat ( or $2 as an argument so that i can give at the command line.) but I get an an error.
could you please lhelp me how i can redirect the output to a file and at the same time i should be able to give the input file from the command line.
Thanks
vkr

Hi Aigles,
I am trying to test the script that you wrote but aim getting the following errors.
awk: syntax error near line 17 ---- for (col=1; col<=Infos_by_set; col++) {
awk: illegal statement near line 17
awk: syntax error near line 26 ----- }
awk: bailing out near line 26

Could please help me resolve this so that i will try to test and see the results.

Thanks for all your help.
vkr

An example how you can append the value of the 2nd argument of a shell script to a file with awk:

Code:

#!/bin/sh

awk -v MyVar=$2 'BEGIN{print MyVar >> "output_file.dat"}'

The script works fine on my box (awk is gawk).
Try to replace awk by nawk or gawk.

Jean-Pierre.

Hi Gurus,
your help was a big help till now but i am in more deep trouble in solving this issue. this time the problem is more complicated.

I am trying to adapt with the above script that Jean and franklin52 wrote .

I have a problem in parsing the below mentioned type of files and obtain the desired output.
Input could be in any form.below are the few samples

Note: the set columns in the file could be in any order. but once the first set is defined in a certain order the rest of the sets will follow the same order of the columns in Input file.

INPUT File
File type1
Header, 4567, USD, SFR, 4568, EUR, SBFR,
Date, LEVEL, NET, TOTAL, MCAP, LEVEL,NET TOTAL MCAP
22-Dec-97, 27.06, 34, 20.92, 11, 26.17, 1, 20.60, 88
23-Dec-97, 27.11, 44, 20.9, 223, 26.50, 2, 20.86, 97
24-Dec-97, 27.17, 2, 21.01, 44, 26.53, 3, 20.89, 54
25-Dec-97, 27.17, 56, 21.02, 55, 26.6, 4, 20.99, 23
26-Dec-97, 27.13, 10, 20.98, 77, 26.55, 5, 20.91, 45

INPUT file Type2
file2
Header,4567,USD,SBFR,4568,EUR,SBCC,4569,JPY,FACT,4570,USD,FACT
Date, LEVEL,TOTAL,LEVEL,TOTAL,LEVEL,TOTAL,LEVEL,TOTAL
22-Dec-97,27.06,20.92,26.17,20.60,26.06,20.92,27.06,20.92
23-Dec-97,27.11,20.96,26.50,20.86,25.06,20.92,21.06,20.92
24-Dec-97,27.17,21.01,26.53,20.89,24.06,20.92,21.06,20.92
25-Dec-97,27.17,21.02,26.66,20.99,23.06,20.92,21.06,20.92
26-Dec-97,27.13,20.98,26.55,20.91,29.06,20.92,21.064,20.92

Input file type 3
file type3
Header,4567,USD,SBFR,4568,EUR,SBCC,4569,JPY,FACT,4570,USD,FACT
Date, NET, TOTAL,NET, TOTAL, NET,TOTAL,NET,TOTAL
22-Dec-97,27.06 20.92,26.17,20.60,26.06,20.92,27.06,20.9289574
23-Dec-97,27.11 20.96,26.50,20.86,25.06,20.92,21.06,20.9289574
24-Dec-97,27.17 21.01,26.53,20.89,24.06,20.92,21.06,20.9289574
25-Dec-97,27.17 21.02,26.66,20.99,23.06,20.92,21.06,20.9289574


file type 4

Header,4567,USD,SBFR,4568,EUR,SBCC,4569,JPY,FACT,4570,USD,FACT
Date, MCAP, TOTAL,MCAP, TOTAL, MCAP,TOTAL,MCAP,TOTAL
22-Dec-97,27.06 20.92,26.17,20.60,26.06,20.92,27.06,20.928
23-Dec-97,27.11 20.96,26.50,20.86,25.06,20.92,21.06,20.925




it can be in any combination but i need to get the out put in the below format

Desired output format for file 4
Date, index_id, currency, index_pro, level, net, total, mcap
22-Dec-97,4567, USD, SBFR, , , 20.92,27.06
22-Dec-97,4568, EUR, SBCC, , , 20.60,26.06
22-Dec-97,4569, JPY, FACT, , , 20.92,26.06
22-Dec-97,4570, USD, FACT, , , 20.928,27.0
23-Dec-97,4567, USD, SBFR, , , 20.96, 27.11
23-Dec-97,4568, EUR, BCC, , , 20.86,26.50
23-Dec-97,4569, JPY, FACT, , , 20.92,25.06
23-Dec-97,4570, USD, FACT, , , 20.925,21.06


I appreciate if you could please help me solve this problem. Your help will be appreciated.
Thanks
VKR