need help in Parsing a CSV file and generate a new output file

vkr · #1 (**permalink**) 07-26-2008

Hi Scripting Gurus,
I am trying to parse a csv file and generate a new output file.
The input file will be a variable length in turns of rows and columns.
output file will have 8 columns. we have three columns from the header for each set.
just to give little bit more clarification each row will have n number of sets as seen in the input file we have two sets .
we can have two solutions one is split each row into two rows or bring set2 under set 1 and so on if we have more sets.

|-----------set 1----------|----------set 2----------------|
INPUT FILE
Header,4567,USD,BMI,,4568,USD,BMI,
Date,LEVEL,NET,TOTAL,MCAP,LEVEL,NET,TOTAL,MCAP
22-Dec-97,27.06492573,,20.9289574,,26.17032566,,20.60702136,
23-Dec-97,27.11627283,,20.96866346,,26.50090757,,20.86732796,
24-Dec-97,27.17448579,,21.01367878,,26.53347955,,20.89299111,
25-Dec-97,27.17854775,,21.02091981,,26.66484879,,20.99775394,
26-Dec-97,27.13228338,,20.98513719,,26.55463469,,20.91096371,

Desired output

Date, index_id,currency,index_pro, level, net, total, mcap
22-Dec-97, 4567, USD, BMI, 27.06492573, , 20.9289574 ,
22-Dec-97, 4568, USD, BMI, 26.17032566, , 20.60702136,
23-Dec-97, 4567, USD, BMI, 27.11627283, , 20.96866346,
23-Dec-97, 4568, USD, BMI, 26.50090757, , 20.86732796,
24-Dec-97, 4567, USD, BMI, 27.17448579, , 21.01367878,
24-Dec-97, 4568, USD, BMI, 26.53347955, ,20.89299111,
25-Dec-97, 4567, USD, BMI, 27.17854775, ,21.02091981,
25-Dec-97, 4568, USD, BMI, 26.66484879, ,20.99775394,
26-Dec-97, 4567, USD, BMI, 27.13228338, ,20.98513719,
26-Dec-97, 4568, USD, BMI, 26.55463469, ,20.91096371,

here is the script i wrote and iam having issues geting the proper out put.

Code:

#!/bin/sh

hdr=`cat inputfile.csv|grep 'Header'|awk -F"," '{ t += NF - 1 } END { print t }'`
hdr_str=`cat inputfile.csv|grep 'Header'`
hdr=`expr $hdr + 1`
while read line
do
  i=2;
  j=1;
  dte=`echo $line | awk -F"," '{ print $1 }'`
  k=2;
  str_tmp="";
  l=1;
  while [ $k -le ${hdr} ]
  do
    if [ $l -le 3 ]; then
      str_id_cur_iprv=`echo $hdr_str | awk -F"," '{ print $'$k' }'`
      if [ $l -eq 1 ]; then
        str_tmp=$str_id_cur_iprv
        l=`expr $l + 1`
        k=`expr $k + 1`
      else
        l=`expr $l + 1`
        k=`expr $k + 1`
        str_tmp=$str_tmp","$str_id_cur_iprv
      fi
    else
      break
    fi
  done
  str=${dte}","$str_tmp
  while [ $i -le ${hdr} ]
  do
    if [ $j -le 4 ]; then
      var=`echo $line | awk -F"," '{ print $'$i' }'`
      str=${str}","${var}
      i=`expr $i + 1`
      j=`expr $j + 1`
    else
      echo $str >> output_file.dat
      str=${dte}","$str_tmp
      j=1;
    fi
  done
  if [ $j -ne 1 ]; then
    echo $str >> output_file.dat
    str=${dte}","$str_tmp
  fi
done < inputfile.csv

here is the out put i get with the above script.

Header,4567,USD,BMI,4567,USD,BMI,
Header,4567,USD,BMI,4568,USD,BMI,
Date,4567,USD,BMI,LEVEL,NET,TOTAL,MCAP
Date,4567,USD,BMI,LEVEL,NET,TOTAL,MCAP
22-Dec-97,4567,USD,BMI,27.06492573,,20.9289574,
22-Dec-97,4567,USD,BMI,26.17032566,,20.60702136,
23-Dec-97,4567,USD,BMI,27.11627283,,20.96866346,
23-Dec-97,4567,USD,BMI,26.50090757,,20.86732796,
24-Dec-97,4567,USD,BMI,27.17448579,,21.01367878,
24-Dec-97,4567,USD,BMI,26.53347955,,20.89299111,
25-Dec-97,4567,USD,BMI,27.17854775,,21.02091981,
25-Dec-97,4567,USD,BMI,26.66484879,,20.99775394,
26-Dec-97,4567,USD,BMI,27.13228338,,20.98513719,
26-Dec-97,4567,USD,BMI,26.55463469,,20.91096371,

Gurus can any one take a look at my script and modify it to get the correct form.
Thanks
vkr

ghostdog74 · #2 (**permalink**) 07-26-2008

put your script in code tags

Franklin52 · #3 (**permalink**) 07-26-2008

You can do the job with 1 awk command:

Code:

awk 'BEGIN{FS=OFS=",";print "Date, index_id,currency,index_pro, level, net, total, mcap"}
NR==1{s1=$2 FS $3 FS $4; s2=$6 FS $7 FS $8; getline; next}
{printf("%s,%s,%s,%s,%s,\n", $1,s1,$2,$3,$4)}
{printf("%s,%s,%s,%s,%s,\n", $1,s2,$6,$7,$8)}
' inputfile.csv

Regards

vkr · #4 (**permalink**) 07-26-2008

hi Franklin,
This is a static script. I want it to be generic where I get variable sets. it can be 2 or 3 or 4 or for that matter any number of sets side by side. could you please give me a generic script where i can generate an out put file.
I appreciate all your help.
Thanks
vkr

vkr · #5 (**permalink**) 07-26-2008

hi Franklin,
This is a static script. I want it to be generic where I get variable sets. it can be 2 or 3 or 4 or for that matter any number of sets side by side. could you please give me a generic script where i can generate an out put file.
I appreciate all your help.
Thanks
vkr

aigles · #6 (**permalink**) 07-27-2008

Try and adapt the following script :

Code:

awk '

BEGIN {
  OFS = FS = ",";
  Columns_by_set = 4;
  Infos_by_set   = 3; # Output columns count from header for each set
  print "Date","index_id","currency","index_pro",
               "level","net","total","mcap";
}

$1 == "Header" {
   Sets_by_line   = int((NF -1) / Columns_by_set);
   Fields_by_line = NF;

   for (set=1; set<=Sets_by_line; set++) {
      infos = "";
      field = 2 + (set-1)*Columns_by_set
      for (col=1; col<=Infos_by_set; col++) {
         infos = infos $(field+col-1) (col<Infos_by_set ? OFS : "");
      }
      Header_infos[set] = infos;
   }

   next;
}

$1 == "Date" {
   next;
}

Sets_by_line {
   if (NF != Fields_by_line) {
      printf("Invalid datas line %d: Fields count is %d, must be %d\n",
             NR, NF, Fields_by_line);
      next;
   }

   for (set=1; set<=Sets_by_line; set++) {
      datas = $1 OFS Header_infos[set];
      field = 2 + (set-1)*Columns_by_set
      for (col=1; col<=Infos_by_set; col++) {
         datas = datas OFS $(field+col-1);
      }
      print datas
   }
}
' inputfile

Inputfile:

Code:

Header,4567,USD,BMI,,4568,USD,BMI,
Date,LEVEL,NET,TOTAL,MCAP,LEVEL,NET,TOTAL,MCAP
22-Dec-97,27.06492573,,20.9289574,,26.17032566,,20.60702136,
23-Dec-97,27.11627283,,20.96866346,,26.50090757,,20.86732796,
24-Dec-97,27.17448579,,21.01367878,,26.53347955,,20.89299111,
25-Dec-97,27.17854775,,21.02091981,,26.66484879,,20.99775394,
26-Dec-97,27.13228338,,20.98513719,,26.55463469,,20.91096371,
Header,9876,EUR,BMI,
Date,LEVEL,NET,TOTAL,MCAP
22-Dec-97,27.06492573,,20.9289574,
23-Dec-97,27.11627283,,20.96866346,
24-Dec-97,27.17448579,,21.01367878,
25-Dec-97,27.17854775,,21.02091981,
26-Dec-97,27.13228338,,20.98513719,

Output:

Code:

Date,index_id,currency,index_pro,level,net,total,mcap
22-Dec-97,4567,USD,BMI,27.06492573,,20.9289574
22-Dec-97,4568,USD,BMI,26.17032566,,20.60702136
23-Dec-97,4567,USD,BMI,27.11627283,,20.96866346
23-Dec-97,4568,USD,BMI,26.50090757,,20.86732796
24-Dec-97,4567,USD,BMI,27.17448579,,21.01367878
24-Dec-97,4568,USD,BMI,26.53347955,,20.89299111
25-Dec-97,4567,USD,BMI,27.17854775,,21.02091981
25-Dec-97,4568,USD,BMI,26.66484879,,20.99775394
26-Dec-97,4567,USD,BMI,27.13228338,,20.98513719
26-Dec-97,4568,USD,BMI,26.55463469,,20.91096371
22-Dec-97,9876,EUR,BMI,27.06492573,,20.9289574
23-Dec-97,9876,EUR,BMI,27.11627283,,20.96866346
24-Dec-97,9876,EUR,BMI,27.17448579,,21.01367878
25-Dec-97,9876,EUR,BMI,27.17854775,,21.02091981
26-Dec-97,9876,EUR,BMI,27.13228338,,20.98513719

Jean-Pierre.

Franklin52 · #7 (**permalink**) 07-27-2008

Quote:

Originally Posted by vkr

hi Franklin,
This is a static script. I want it to be generic where I get variable sets. it can be 2 or 3 or 4 or for that matter any number of sets side by side. could you please give me a generic script where i can generate an out put file.
I appreciate all your help.
Thanks
vkr

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

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
generate CSV file using AWK script	magikminox	UNIX for Dummies Questions & Answers	2	06-11-2008 10:04 PM
To generate the FTP Script file	konankir	Shell Programming and Scripting	1	04-01-2008 03:16 PM
Script to generate text file from excel file	isingh786	UNIX for Dummies Questions & Answers	1	01-24-2008 10:32 AM
problem parsing output file	ragha81	Shell Programming and Scripting	13	03-21-2007 02:06 PM
Generate csv file	rahulrathod	Shell Programming and Scripting	4	01-23-2007 10:34 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread: