Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting need help in Parsing a CSV file and generate a new output file
# 15  
Old 07-31-2008
The problem is more complicated.
Try and adapt the following script :
Code:
awk -v Columns="LEVEL,NET,TOTAL,MCAP"                   \
    -v Header_cols="index_id,currency,index_pro"        \
'

#
# Initializations
#

BEGIN {
  OFS = FS = ",";

  Infos_by_set = gsub(",", OFS, Header_cols) + 1;

  Columns_count = split(Columns, Columns_label, ",");
  for (i=1; i<=Columns_count; i++) {
     Columns_pos[Columns_label[i]] = i;
  }

  gsub(/,/, OFS, Columns);
  print "Date", Header_cols, tolower(Columns);

}

#
# Header line
#

$1 == "Header" {
   gsub(/ /,"");
   Sets_by_line   = int((NF -1) / Infos_by_set);

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

   next;
}

#
# Date line
#

$1 == "Date" {
   gsub(/ /,"");

   Fields_by_set  = int((NF-1) / Sets_by_line);
   Fields_by_line = 1 + Fields_by_set * Sets_by_line;

   for (i=1; i<=Columns_count; i++)
      Columns_field[i] = 0;
   for (i=1; i<=Fields_by_set; i++) {
      if ($(i+1) in Columns_pos)
        Columns_field[Columns_pos[$(i+1)]] = i;
   }

   next;
}

#
# Datas line
#

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++) {
      field = 1 + (set-1)*Fields_by_set
      datas = $1 OFS Header_infos[set];
      for (col=1; col<=Columns_count; col++) {
         datas = datas OFS
         if (Columns_field[col])
            datas = datas $(field+Columns_field[col]);
      }
      print datas
   }
}

' inputfile

Inputfile:
Code:
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
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
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
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

Output:
Code:
Date,index_id,currency,index_pro,level,net,total,mcap
22-Dec-97,4567,USD,SFR, 27.06, 34, 20.92, 11
22-Dec-97,4568,EUR,SBFR, 26.17, 1, 20.60, 88
23-Dec-97,4567,USD,SFR, 27.11, 44, 20.9, 223
23-Dec-97,4568,EUR,SBFR, 26.50, 2, 20.86, 97
24-Dec-97,4567,USD,SFR, 27.17, 2, 21.01, 44
24-Dec-97,4568,EUR,SBFR, 26.53, 3, 20.89, 54
25-Dec-97,4567,USD,SFR, 27.17, 56, 21.02, 55
25-Dec-97,4568,EUR,SBFR, 26.6, 4, 20.99, 23
26-Dec-97,4567,USD,SFR, 27.13, 10, 20.98, 77
26-Dec-97,4568,EUR,SBFR, 26.55, 5, 20.91, 45
22-Dec-97,4567,USD,SBFR,27.06,,20.92,
22-Dec-97,4568,EUR,SBCC,26.17,,20.60,
22-Dec-97,4569,JPY,FACT,26.06,,20.92,
22-Dec-97,4570,USD,FACT,27.06,,20.92,
23-Dec-97,4567,USD,SBFR,27.11,,20.96,
23-Dec-97,4568,EUR,SBCC,26.50,,20.86,
23-Dec-97,4569,JPY,FACT,25.06,,20.92,
23-Dec-97,4570,USD,FACT,21.06,,20.92,
24-Dec-97,4567,USD,SBFR,27.17,,21.01,
24-Dec-97,4568,EUR,SBCC,26.53,,20.89,
24-Dec-97,4569,JPY,FACT,24.06,,20.92,
24-Dec-97,4570,USD,FACT,21.06,,20.92,
25-Dec-97,4567,USD,SBFR,27.17,,21.02,
25-Dec-97,4568,EUR,SBCC,26.66,,20.99,
25-Dec-97,4569,JPY,FACT,23.06,,20.92,
25-Dec-97,4570,USD,FACT,21.06,,20.92,
26-Dec-97,4567,USD,SBFR,27.13,,20.98,
26-Dec-97,4568,EUR,SBCC,26.55,,20.91,
26-Dec-97,4569,JPY,FACT,29.06,,20.92,
26-Dec-97,4570,USD,FACT,21.064,,20.92,
22-Dec-97,4567,USD,SBFR,,27.06,20.92,
22-Dec-97,4568,EUR,SBCC,,26.17,20.60,
22-Dec-97,4569,JPY,FACT,,26.06,20.92,
22-Dec-97,4570,USD,FACT,,27.06,20.9289574,
23-Dec-97,4567,USD,SBFR,,27.11,20.96,
23-Dec-97,4568,EUR,SBCC,,26.50,20.86,
23-Dec-97,4569,JPY,FACT,,25.06,20.92,
23-Dec-97,4570,USD,FACT,,21.06,20.9289574,
24-Dec-97,4567,USD,SBFR,,27.17,21.01,
24-Dec-97,4568,EUR,SBCC,,26.53,20.89,
24-Dec-97,4569,JPY,FACT,,24.06,20.92,
24-Dec-97,4570,USD,FACT,,21.06,20.9289574,
25-Dec-97,4567,USD,SBFR,,27.17,21.02,
25-Dec-97,4568,EUR,SBCC,,26.66,20.99,
25-Dec-97,4569,JPY,FACT,,23.06,20.92,
25-Dec-97,4570,USD,FACT,,21.06,20.9289574,
22-Dec-97,4567,USD,SBFR,,,20.92,27.06
22-Dec-97,4568,EUR,SBCC,,,20.60,26.17
22-Dec-97,4569,JPY,FACT,,,20.92,26.06
22-Dec-97,4570,USD,FACT,,,20.928,27.06
23-Dec-97,4567,USD,SBFR,,,20.96,27.11
23-Dec-97,4568,EUR,SBCC,,,20.86,26.50
23-Dec-97,4569,JPY,FACT,,,20.92,25.06
23-Dec-97,4570,USD,FACT,,,20.925,21.06

Jean-Pierre.
# 16  
Old 08-01-2008
Hi Jean,
Thank you so much. I am learning so much from you.The way you share your knowledge is very much appreciative and motivates me to learn more.Once again thank you so much. YOU ARE A REAL GURU(MASTER).
VKR
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Script to generate .csv file

Dears,I need your help in this, I have to create a report based on the output file generated by another program. I want to write a shell script for this. The output file generated every 15 minutes but i can’t open it until the end of day so the script will get the file as an input the file will be... (8 Replies)
Discussion started by: abdul2020
8 Replies

2. Shell Programming and Scripting

Script to generate csv file

Dears, I am new in shell world and I need your help in this, I have to create a report based on the output file generated by another program. I want to write a shell script for this. The output file generated every 15 minutes but i can’t open it until the end of day so the script will get the... (3 Replies)
Discussion started by: abdul2020
3 Replies

3. Shell Programming and Scripting

Generate .csv file a text file

Hi guys, I have a text file that states: cat prod.hosts 11.29.130.14 host1 host1.test.com web17 11.29.130.15 host2 host2.test.com web18 11.29.130.16 host3 host3.test.com web19 I want a .csv file that states: ... (2 Replies)
Discussion started by: Junaid Subhani
2 Replies

4. Shell Programming and Scripting

Save output of updated csv file as csv file itself, part 2

Hi, I have another problem. I want to sort another csv file by the first field. result.csv SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw /home/intannf/foto5/2015_0313_090651_219.JPG,0.,-7.77223,110.37310,30.75,996.46,148.75,180.94,182.00,63.92 ... (2 Replies)
Discussion started by: refrain
2 Replies

5. Shell Programming and Scripting

Script to generate csv file

Hello; I need to generate a csv file that contains a list of all the files in a particular server (from the root directory ie: \) that have a permission stamp of 777. I would like to create the csv so that it contains the following: server name, file name, full path name where file exists,... (17 Replies)
Discussion started by: gvolpini
17 Replies

6. Shell Programming and Scripting

Read a CSV file and generate SQL output

Friends, This is what I need: I will pass a CSV file as an input, and I want my shell to be reading that CSV file, and based on the parameters it should generate SQLs and write those SQL in a different file in the same location. I'm new to Shell scripting. I'm currently working on a... (25 Replies)
Discussion started by: Ram.Math
25 Replies

7. Shell Programming and Scripting

to read a CSV file and generate SQL output

Friends, This is what I need: I will pass a CSV file as an input, and I want my shell to be reading that CSV file, and based on the parameters it should generate SQLs and write those SQL in a different file in the same location. I'm new to Shell scripting. I'm currently working on a... (1 Reply)
Discussion started by: Ram.Math
1 Replies

8. Shell Programming and Scripting

Need to generate .csv file

I have a csv file with the following data Please find the attachment - zip ... (6 Replies)
Discussion started by: vaas
6 Replies

9. Shell Programming and Scripting

2 problems: Mailing CSV file / parsing CSV for display

I have been trying to find a good solution for this seemingly simple task for 2 days, and I'm giving up and posting a thread. I hope someone can help me out! I'm on HPUX, using sqlplus, mailx, awk, have some other tools available, but can't install stuff that isn't already in place (without a... (6 Replies)
Discussion started by: soldstatic
6 Replies

10. Shell Programming and Scripting

Generate csv file

I have a file which has some thousand records in the following format File: input.txt -> <option value="14333">VISWANADH VELAMURI</option> <option value="17020">VISWANADHA RAMA KRISHNA</option> I want to generate a csv file from the above file as follows File: output.txt -> ... (4 Replies)
Discussion started by: rahulrathod
4 Replies
Login or Register to Ask a Question