Visit Our UNIX and Linux User Community


help!!!!!! in creating csv report


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting help!!!!!! in creating csv report
# 1  
Old 10-17-2007
Data help!!!!!! in creating csv report

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

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
HTML Code:
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  
Old 10-18-2007
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}
' "file"

output:
Code:
# ./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  
Old 10-18-2007
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:---

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

HTML Code:
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
help!!!!!! in creating csv report-report_requiredbmp
# 4  
Old 10-18-2007
awk

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:

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[i]","user[i]","display[i]","description[i]","account[i]","email[i]","comments[i]
}' filename

# 5  
Old 10-18-2007
Dear Summer_cherry
Thanks for your help

firstly i have gawk installed on Linux box and when i run your code with gawk
HTML Code:
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[i]","user[i]","display[i]","description[i]","account[i]","email[i]","comments[i]
}' filename
all I m getting is
HTML Code:
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  
Old 10-18-2007
Dear All

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

HTML Code:
 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
help!!!!!! in creating csv report-output_gettingjpg
help!!!!!! in creating csv report-report_requiredjpg
# 7  
Old 10-18-2007
Data

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

Previous Thread | Next Thread
Test Your Knowledge in Computers #746
Difficulty: Medium
The Z80 is an 8-bit microprocessor introduced by Zilog.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need help for creating the report

Hi, I have a file that contains below information: provider APESD APESD Annual Turn $67000 Provider XXXX Annual Turn $92000 Provider FERHG FERHG Annual Turn $56700 Provider ABXX Annual Turn $99000 These are just sample rows out of some 5000. I want to create a tabular report as... (5 Replies)
Discussion started by: juzz4fun
5 Replies

2. UNIX for Dummies Questions & Answers

Creating a csv file with header in UNIX

I have a flat file that contains dynamic list of variables like a=1 b=2 c=3 . .. z=26 I need to convert the above into a csv file having the format below: a,b,c,..,z 1,2,3,..,26 Please note, I do not want a comma separating the last variable. I tried to refer the post... (4 Replies)
Discussion started by: vkumbhakarna
4 Replies

3. UNIX for Dummies Questions & Answers

Problem in creating CSV file

Hi guys, I am not experienced with Unix, so please dont mind if the question seem to be irrelevant. I have written a simple script, that connects DB & fetches few records from a table. I wanted to get those details as file in .CSV format via mail. -I stored the query o/p in a file. -I... (6 Replies)
Discussion started by: sumitburnwal88
6 Replies

4. Shell Programming and Scripting

creating a csv file from this 1 liner?

I'm trying to create a csv file by running awk and sed on a number of xml files in a directory; I'm using this below: hostname; grep "BuildDate" /dir/ABCD/configuration/*/*.xml | awk -F"/" '{ print $5 }' > /tmp/tempfile.txt; grep "BuildDate" /dir/ABCD/configuration/*/*.xml | awk -F\" '{ print $2... (2 Replies)
Discussion started by: rich@ardz
2 Replies

5. UNIX for Dummies Questions & Answers

Creating a report from csv file.

Hi Gurus, I need your help in transforming the CSV file into some what a report format. My source file looks like below Date,ProdID,TimeID,LevelID 2010-08-31,200,M,1 2010-08-31,201,Q,2 2010-08-31,202,Y,1 2010-08-31,203,M,5 Output required is ... (9 Replies)
Discussion started by: naveen.kuppili
9 Replies

6. Shell Programming and Scripting

Creating CSV files

hi, Need a lil help experts. i have a sh file and its calling one SQL file. i need the dataset returned by that SQL file in a CSV format. Can you plz help me out at the earliest. waiting eagerly .. :confused: Thanks.. (10 Replies)
Discussion started by: onlyniladri
10 Replies

7. UNIX for Dummies Questions & Answers

creating a CSV file for past 7 days

I have a requirement which will select the files with a specific naming convention which got created in past 7 days in a specific directory.Lets say the directory is /data/XYZ and the file names follow the below nomenclature like Daily_File*.txt I just need to create one CSV file which will... (12 Replies)
Discussion started by: dr46014
12 Replies

8. UNIX for Dummies Questions & Answers

Creating CSV Files

I have a requiremnt to create one CSV file with initial 5 lines as blank rows. From 5th row onwards the file is to be created from a list of varibales which gets evaluated. Say A,B,C,D,E are 5 varibales with some value associated with each of them and data type is string. So the 6th row should... (1 Reply)
Discussion started by: dr46014
1 Replies

9. UNIX for Advanced & Expert Users

Creating multiple worksheets in CSV file

Hello, I've been tasked with sending 3 types of data (file size, row count, and file name) to a csv file every month for various vendors. I have been asked to put this in one csv or xls file with each vendor being a different tab (or worksheet). Until now, we have been finding and emailing... (4 Replies)
Discussion started by: tekster757
4 Replies

10. Shell Programming and Scripting

creating a csv file in awk

Hi All I am trying to create a csv file in the korn shell and the script segment is as follows: if then # NEED TO ADD INFO TO THE EMAIL FILE ABOUT THE DRIVE THAT'S FILLING UP echo "$drive $percent% $space "|\ awk '{printf("%d/t"|"%d/t"|"%d/t\n",... (6 Replies)
Discussion started by: Segwar
6 Replies

Featured Tech Videos