help!!!!!! in creating csv report

Shell Programming and Scripting
1

Dear All,
I need the help of all the genius minds to find solution to my problem. This problem has left me in a situation like I am in middle of sea with a rubber boat , so please help me out to get out of this.

Problem:--
I m using the informix database with installed on linux machine and I access my database through putty to generate reports from informix

Now the one of my report that I m getting is in the following form .
E.g:---- cat xyz_report.txt


contact_date  2007-08-15 00:00:22
user_id       074631
display_name  xander
comments
vci done cust was not able recharge his account ..prob done ..told the cust his issue has been forwarded to the concerned dep
artment and it wil b resolved with in next 24 hrs ..so try recharging  account after 24 hrs..
description   C-Cust not able to do Manual Recharge 
account_no     123456789
email         abc@xyz.com

contact_date  2007-08-15 00:00:27
user_id       074612
display_name  joe
comments
VCI DONE,
Cust called and he said that he is not able to recharge his account manaully through the webiste so told the cust that i will
 forward this concern to my concerned department
Informed the customer about the taxes and and told the cust that if he do the recharge then cust will recive an auto genrated
 email
tat : 24 hours

description   C-Cust not able to do Manual Recharge 
account_no    987654321
email         abc@xyz.com

contact_date  2007-08-15 00:00:46
user_id       75129
display_name  philip
comments
vci done ....
cust unable to recharge his a/c ....getting error processing req ...tat given for 24hrs ...taxes and auto-generated mail info
rmed ....
description   C-Cust not able to do Manual Recharge 
account_no    702345698
email         abc@xyz.com

contact_date  2007-08-15 00:04:35
user_id       76547
display_name  hsingh
comments
VCI DONE...CUST NOT ABLE TO RECHARGE HIS ACCOUNT MANUALLY CONFIRMED HIM HE IS FILLING UP ALL THE BILLING ADDRESS DETAILS AND
CREDIT CARD DETAILS CORRECTLY I.E HIS CVV NO ..CREDIT CARD EXPIRY DATE AND THE MAKE OF THE CREDIT CARD. SO TLD THE CUST TO TR
Y TO RECHARGE HIS ACCOUNT AFTER  24 hrs ..TAXES also tld for recharge OVER AND ABOVE the plan value as per the BILLING ADDRES
S...and the cust will recieve a AUTOGENERATED MAIL after successful recharge of his account.

description   C-Cust not able to do Manual Recharge 
S...and the cust will recieve a AUTOGENERATED MAIL after successful recharge of his account.

description   C-Cust not able to do Manual Recharge 
account_no    612654897
email         abc@xyz.com

contact_date  2007-08-15 00:05:06
user_id       76374
display_name  shivk
comments
vci not required and askd fo rthe promo offer available and for DTT offer. so told that i will forward the feedback to the co
ncern departement for DTT offer however we have Independenmce day offer as of now and told about the conmplete offers as The
Independence Day Offer is

valid till August 31, 2007 (EST)
if recharge/register for 25, 50,

100, 200 dolars then will get

the extra talk time as per the

offer.
* Users who are eligible for the

Independence Day Offer will

receive an email from Airtel

CallHome within 4 working

days of the transaction ( for

$50,$100 & $200 signups or

recharges). To avail the offer a

reply to this email (with the

required details) must be sent

during the promo period or five

days from receiving the email

whichever is later.
* Saris will be delivered only to

the intended addressee in India

within 30 days of the receipt of

the reply mail from the

customer.
* Customer must compulsorily

choose a sari code representing

one out of six types of saris

mentioned in the email. There

is no choice of color.
* There are no delivery charges

for Sari.
** The regular minutes

calculation is based on calling

from USA to India. and told that

he can recharge any numbr of

time in offer and told about the

taxes and mail for the

confirmation of recharge... TAT:

24 hrs for the extra talk time to

be credited.
and told about the taxes and

mail for the confirmation of

recharge... TAT: 24 hrs for the

extra talk time to be credited.
description   Q-Promotional Offer
account_no    612123456
email         	abc@xyz.com

Now as you can see that there are 7 fields in rows these are :--

1.contact_date
2.user_id
3.display_name
4.comments
5.description
6.account_no
7.email

There is single line data in front of each field except the comments field which has its data in multiple lines.

Now i want to create a report in csv format so that i can open it in spreadsheet for analysis .I want only the data in front of each fields (not the names of field ) to be arranged in 7 columns of the csv file so that i can open these in spreadsheet.

My biggest problem is how to manipulate the data of the comments field.

Also I would like to tell that if i remove the comments field i can easily find the solution to my problem with awk i.e

awk -F" " '{ print $2 }' xyz_report.txt | awk 'BEGIN { FS="\n" ; RS="" } { print $1","$2","$3","$4","$5","$6 }' > finalreport.csv

I dont find any solution on how to manipulate the 4th field which is comments field.

kindly inform if some other details are required .
Thanks in advance

2 
awk 'BEGIN{flag=0;}
     !/comments/&&flag!=1{$1="";l=l","$0 }
     /comments/{flag=1}
     flag&&!/comments/{s=s$0}
     /description/{flag=0;l=l","s;s=""}     
     END{print l}
' "file"

output:

# ./test.sh
, 2007-08-15 00:00:22, 074631, xander,vci done cust was not able recharge his account ..prob done ..told the cust his issue has been forwarded to the concerned department and it wil b resolved with in next 24 hrs ..so try recharging  account after 24 hrs..description   C-Cust not able to do Manual Recharge , 123456789, abc@xyz.com,, 2007-08-15 00:00:27, 074612, joe,VCI DONE,Cust called and he said that he is not able to recharge his account manaully through the webiste so told the cust that i will forwa............
.........

I leave it to you to format the results...

3

Dear ghostdog74
Thanks for your valuable effort towards helping me !!!!!!

firstly there is some mistake when i posted this problem there were some extra lines which are printed in between by mistake therefore retyping in correct format , the file xyz_report.txt now looks like:---

contact_date  2007-08-15 00:00:22
user_id       074631
display_name  xander
comments
vci done cust was not able recharge his account ..prob done ..told the cust his issue has been forwarded to the concerned dep
artment and it wil b resolved with in next 24 hrs ..so try recharging  account after 24 hrs..
description   C-Cust not able to do Manual Recharge 
account_no     123456789
email         abc@xyz.com

contact_date  2007-08-15 00:00:27
user_id       074612
display_name  joe
comments
VCI DONE,
Cust called and he said that he is not able to recharge his account manaully through the webiste so told the cust that i will
 forward this concern to my concerned department
Informed the customer about the taxes and and told the cust that if he do the recharge then cust will recive an auto genrated
 email
tat : 24 hours

description   C-Cust not able to do Manual Recharge 
account_no    987654321
email         abc@xyz.com

contact_date  2007-08-15 00:00:46
user_id       75129
display_name  philip
comments
vci done ....
cust unable to recharge his a/c ....getting error processing req ...tat given for 24hrs ...taxes and auto-generated mail info
rmed ....
description   C-Cust not able to do Manual Recharge 
account_no    702345698
email         abc@xyz.com

contact_date  2007-08-15 00:04:35
user_id       76547
display_name  hsingh
comments
VCI DONE...CUST NOT ABLE TO RECHARGE HIS ACCOUNT MANUALLY CONFIRMED HIM HE IS FILLING UP ALL THE BILLING ADDRESS DETAILS AND
CREDIT CARD DETAILS CORRECTLY I.E HIS CVV NO ..CREDIT CARD EXPIRY DATE AND THE MAKE OF THE CREDIT CARD. SO TLD THE CUST TO TR
Y TO RECHARGE HIS ACCOUNT AFTER  24 hrs ..TAXES also tld for recharge OVER AND ABOVE the plan value as per the BILLING ADDRES
S...and the cust will recieve a AUTOGENERATED MAIL after successful recharge of his account.

description   C-Cust not able to do Manual Recharge 
account_no    612654897
email         abc@xyz.com

contact_date  2007-08-15 00:05:06
user_id       76374
display_name  shivk
comments
vci not required and askd fo rthe promo offer available and for DTT offer. so told that i will forward the feedback to the co
ncern departement for DTT offer however we have Independenmce day offer as of now and told about the conmplete offers as The
Independence Day Offer is

valid till August 31, 2007 (EST)
if recharge/register for 25, 50,

100, 200 dolars then will get

the extra talk time as per the

offer.
* Users who are eligible for the

Independence Day Offer will

receive an email from Airtel

CallHome within 4 working

days of the transaction ( for

$50,$100 & $200 signups or

recharges). To avail the offer a

reply to this email (with the

required details) must be sent

during the promo period or five

days from receiving the email

whichever is later.
* Saris will be delivered only to

the intended addressee in India

within 30 days of the receipt of

the reply mail from the

customer.
* Customer must compulsorily

choose a sari code representing

one out of six types of saris

mentioned in the email. There

is no choice of color.
* There are no delivery charges

for Sari.
** The regular minutes

calculation is based on calling

from USA to India. and told that

he can recharge any numbr of

time in offer and told about the

taxes and mail for the

confirmation of recharge... TAT:

24 hrs for the extra talk time to

be credited.
and told about the taxes and

mail for the confirmation of

recharge... TAT: 24 hrs for the

extra talk time to be credited.
description   Q-Promotional Offer
account_no    612123456
email         	abc@xyz.com

============================================

also Please find the two attached files in csv format

  1. First attached file (report_required.csv) is in the format which is desired
  2. Second attached file(output_getting.csv) has the format i m getting
    when i run the code
awk 'BEGIN{flag=0;}
     !/comments/&&flag!=1{$1="";l=l","$0 }
     /comments/{flag=1}
     flag&&!/comments/{s=s$0}
     /description/{flag=0;l=l","s;s=""}     
     END{print l}
' xyz_report.txt > output_getting.csv

====================================================
1)Also i would like to mention is if you look to the 4th column in report_required.csv which contains the data of comments field every cell has the format preserved in the same way it is in the file xyz_report.txt
---------------------------------------------------------------
2) One trick i have found is that i copied the data between comments and description and then saved in another file (abc) between double quotes ("")
and then did

cp abc abc.csv

this created the cell in spreadsheet abc.csv with data in same way as is given in comments field .what i mean to say is if we insert the data of comments field between double quotes ("") then we can obtain the result in same format as is the file (report_required.csv)

May be this point could be of more help towards achieving the solution.

kindly inform if some other details are required .
Thanks

4

Hi,
This one should be ok. The main idea is:
put all of the element in 7 different array, after finishing scan the whole file, out put them from the array.

code:

nawk 'BEGIN{flag=0;num=1}
/^contact/ 
{
	contact[num]=substr($0,length($1)+1,length($0)-length($1))
}
/^user_id/ 
{
	user[num]=substr($0,length($1)+1,length($0)-length($1))
}
/^display_name/ 
{
	display[num]=substr($0,length($1)+1,length($0)-length($1));
}
/^comments/
{
	flag=1
	next
}
flag {  tmp=tmp$0  }
/^description/ 
{
	description[num]=substr($0,length($1)+1,length($0)-length($1))
	comments[num]=tmp
	tmp=""
	flag=0
}
/^account_no/ 
{
	account[num]=substr($0,length($1)+1,length($0)-length($1))
}
/^email/ 
{
	email[num]=substr($0,length($1)+1,length($0)-length($1));num++
}
END{
for (i=1;i<=num-1;i++)
	print contact","user","display","description","account","email","comments
}' filename
5

Dear Summer_cherry
Thanks for your help

firstly i have gawk installed on Linux box and when i run your code with gawk

gawk 'BEGIN{flag=0;num=1}
/^contact/ 
{
	contact[num]=substr($0,length($1)+1,length($0)-length($1))
}
/^user_id/ 
{
	user[num]=substr($0,length($1)+1,length($0)-length($1))
}
/^display_name/ 
{
	display[num]=substr($0,length($1)+1,length($0)-length($1));
}
/^comments/
{
	flag=1
	next
}
flag {  tmp=tmp$0  }
/^description/ 
{
	description[num]=substr($0,length($1)+1,length($0)-length($1))
	comments[num]=tmp
	tmp=""
	flag=0
}
/^account_no/ 
{
	account[num]=substr($0,length($1)+1,length($0)-length($1))
}
/^email/ 
{
	email[num]=substr($0,length($1)+1,length($0)-length($1));num++
}
END{
for (i=1;i<=num-1;i++)
	print contact","user","display","description","account","email","comments
}' filename

all I m getting is

contact_date  2007-08-15 00:00:22
user_id       074631
display_name  xander
comments
contact_date  2007-08-15 00:00:27
user_id       074612
display_name  joe
comments
contact_date  2007-08-15 00:00:46
user_id       75129
display_name  philip
comments
contact_date  2007-08-15 00:04:35
user_id       76547
display_name  hsingh
comments
contact_date  2007-08-15 00:05:06
user_id       76374
display_name  shivk
comments

Thanks

6

Dear All

I have achieved some success to this problem. what I did is

 less xyz_report.txt | sed -e 's/^comments/&\n"vcidone/' -e 's/^description/"\n&/' > output_getting.csv

when I open the output_getting.csv file I am getting the all the rows in proper cells but horizontally .Please find the attached snapshot

But now the main concern is i have to remove the 7 fields heading

1.contact_date
2.user_id
3.display_name
4.comments
5.description
6.account_no
7.email

and also arrange the lines vertically so that they look like the second image file attached

Please !!!!!!!!!!!!!!! help
Thanks

7

Dear All

I m new to shell scripting and I m in very need to solve this problem

Some one Please help me for this issue.!!!!!!!!!!!!!!!!!!

Thanks