script to create tilde seperate output file and the count

Vinit can u please specify how to run the script.....

I created a file DataFile with the input data as shown above and ran the script as follows

./script DataFile

it's giving me the following errors

sort: open failed: +1: No such file or directory
~~1~~~~

can you please check on this

Thanks in advance

Sorry about that. Can you please attached your script here(not the data file)

Regards
Vinit

I just copied your code into a file by name script and executed the script vinit. I got the above mentioned error. Any idea on y I got that error

mv this file to scrv.doc to scrv.ksh and try to run

regards
vinit

Vinit i am getting the same error. Also i tried to keep the code until DataFile and tried executing the script. I am not getting the desired output. If second name's mail id is missing its taking firstname's mail id. Vinit is there any other means of communication i can reach you at as i don't have much time left to report this.

as you already run upto first part, can you please copy few lines of output, which you think is incorrect.

save the outout and run
cat SavedFile|sort -n  +1 -t\~

let me know if you get another error on sort

I am here, and waiting for your reply
Vinit

sort: open failed: +1: No such file or directory

I am getting this error vinit.....

I fixed the problem with second mail issue

check the code below

#!/bin/ksh
awk -F\: '{
if($1~/sno/)
{
if(I>0)
{
print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2
}
sno=$2
X=0
id=" "
firstname=" "
mail=" "
secondname=" "
mail2=" "
}
if($1~/id/) id=$2
if($1~/firstname/) firstname=$2
if(($1~/mail/)&&(X==0)) {mail=$2;X=1;YY=NR}
if($1~/secondname/) {secondname=$2;X=1}
if(($1~/mail/)&&(X==1)&&(NR != YY))mail2=$2
I=1
}END { print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2 }' DataFile
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:

vinit.........if the sno is missing then its not printing the whole line........for example if sno's 12 and 13 are missing its not printing the whole line......in such case the data will be missing

change the sort command to 'sort -t\~ -k2 -n' and check if you still get the error

cat save|sort -t\~ -k2 -n

when i did this on saved file....its not giving any errors....

instead of sno, I changed the mandatory field as id now. Please verify

#!/bin/ksh
awk -F\: '{
if($1~/sno/) newsno=$2
if($1~/id/)
{
if(I>0)
{
print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2
X=0
id=" "
firstname=" "
mail=" "
secondname=" "
mail2=" "
}
id=$2
sno=newsno
}
if($1~/firstname/) firstname=$2
if(($1~/mail/)&&(X==0)) {mail=$2;X=1;YY=NR}
if($1~/secondname/) {secondname=$2;X=1}
if(($1~/mail/)&&(X==1)&&(NR != YY))mail2=$2
I=1
}END { print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2 }' DataFile
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

That is good,

Check the complete script below

Vinit

#!/bin/ksh
awk -F\: '{
if($1~/sno/) newsno=$2
if($1~/id/)
{
if(I>0)
{
print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2
X=0
id=" "
firstname=" "
mail=" "
secondname=" "
mail2=" "
}
id=$2
sno=newsno
}
if($1~/firstname/) firstname=$2
if(($1~/mail/)&&(X==0)) {mail=$2;X=1;YY=NR}
if($1~/secondname/) {secondname=$2;X=1}
if(($1~/mail/)&&(X==1)&&(NR != YY))mail2=$2
I=1
}END { print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2 }' DataFile |sort -t\~ -k2 -n |awk -F\~ ' BEGIN {I=1} {
if( F2 == $2 )
{
I=I+1
}
else
{
if(NR>1)
{
print F1"~"F2"~"I"~"F3"~"F4"~"F5"~"F6
}
F1=$1; F2=$2; F3=$3; F4=$4; F5=$5; F6=$6
I=1
}
} END { print F1"~"F2"~"I"~"F3"~"F4"~"F5"~"F6 }'
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

vinit if a sno is missing it's taking the previous sno now...........when there are sno's 11 to 15 missing.......for all of them its taking 10 as the sno.....can you check this and in the output file i am getting this as my first line

~~2~~~~

is it taking count for spaces or what?

can we do the code without keeping anything mandatory........because there are more than 5000 records.....what if id goes missing somewhere .....we will not know what is missing......

Old serial no. fixed.

about that first line, can you run the script upto sort and let me know the top three  lines

Vinit

#!/bin/ksh
awk -F\: '{
if($1~/sno/) newsno=$2
if($1~/id/)
{
if(I>0)
{
print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2
X=0
sno=" "
id=" "
firstname=" "
mail=" "
secondname=" "
mail2=" "
}
id=$2
sno=newsno
}
if($1~/firstname/) firstname=$2
if(($1~/mail/)&&(X==0)) {mail=$2;X=1;YY=NR}
if($1~/secondname/) {secondname=$2;X=1}
if(($1~/mail/)&&(X==1)&&(NR != YY))mail2=$2
I=1
}END { print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2 }' DataFile |sort -t\~ -k2 -n |awk -F\~ ' BEGIN {I=1} {
if( F2 == $2 )
{
I=I+1
}
else
{
if(NR>1)
{
print F1"~"F2"~"I"~"F3"~"F4"~"F5"~"F6
}
F1=$1; F2=$2; F3=$3; F4=$4; F5=$5; F6=$6
I=1
}
} END { print F1"~"F2"~"I"~"F3"~"F4"~"F5"~"F6 }'
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:

I do not know about the data. How can I assume that new record started if there is no field is mandatory.

can I assume blank row as record separator?

Vinit

This is the first line i am getting
~~~~~

yes vinit we can assume blank row as a record seperator...........as there will definitely be a blank row after each record in my data.

can you please remove blank lines from the top(not all). The first record shouldn't be blank

remove the blank lines from the top.....i did not get you vinit.......can you make that clear........thank you very much for your time vinit...........i really appreciate it.......r u from USA or?

Hi,

Changed the code to blank line as data seperator
 Fix the first line issue.

Check now
Vinit

#!/bin/ksh
awk -F\: '{
if(NF==0)
{
if(I>0)
{
print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2
}
X=0
sno=" "
id=" "
firstname=" "
mail=" "
secondname=" "
mail2=" "
I=0
}
if($1~/sno/){sno=$2;I=1}
if($1~/id/){id=$2;I=1}
if($1~/firstname/){firstname=$2;I=1}
if(($1~/mail/)&&(X==0)){mail=$2;X=1;YY=NR;I=1}
if($1~/secondname/) {secondname=$2;X=1;I=1}
if(($1~/mail/)&&(X==1)&&(NR != YY)){mail2=$2;I=1}
}END { print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2 }' DataFile |sort -t\~ -k2 -n |awk -F\~ ' BEGIN {I=1} {
if( F2 == $2 )
{
I=I+1
}
else
{
if(NR>1)
{
print F1"~"F2"~"I"~"F3"~"F4"~"F5"~"F6
}
F1=$1; F2=$2; F3=$3; F4=$4; F5=$5; F6=$6
I=1
}
} END { print F1"~"F2"~"I"~"F3"~"F4"~"F5"~"F6 }'
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

working great vinit.....vinit if i want to insert this code into another script can i do that.......I will be getting the DataFile output when i run a script...if i can insert how to insert it......

Yes you can insert it any script.
 add all lines except (#!/bin/ksh) in other script.

assume 'cat xfile' command generating the output same as DataFile file


then change your script as below, remove name of the file from awk and piped the output of previous command to awk.

Vinit

'cat xfile|awk -F\: '{
if(NF==0)
{
if(I>0)
{
print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2
}
X=0
sno=" "
id=" "
firstname=" "
mail=" "
secondname=" "
mail2=" "
I=0
}
if($1~/sno/){sno=$2;I=1}
if($1~/id/){id=$2;I=1}
if($1~/firstname/){firstname=$2;I=1}
if(($1~/mail/)&&(X==0)){mail=$2;X=1;YY=NR;I=1}
if($1~/secondname/) {secondname=$2;X=1;I=1}
if(($1~/mail/)&&(X==1)&&(NR != YY)){mail2=$2;I=1}
}END { print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2 }' |sort -t\~ -k2 -n |awk -F\~ ' BEGIN {I=1} {
if( F2 == $2 )
{
I=I+1
}
else
{
if(NR>1)
{
print F1"~"F2"~"I"~"F3"~"F4"~"F5"~"F6
}
F1=$1; F2=$2; F3=$3; F4=$4; F5=$5; F6=$6
I=1
}
} END { print F1"~"F2"~"I"~"F3"~"F4"~"F5"~"F6 }'

Thanks a lot dude........vinit i need to automate this script.........i need to run this script probably every night and i need to run this against four different files generated that day.......for every four hours the files will be rotated.........so  i need to concatenate these four files together........doing so.........there will be some duplicates......for example

id 15001 is in all the four files ......in the first file it came twice ......second file it came thrice........third one four times and fourth file one time...then the count should become 10. i asked a question on this before.....u gave the answer also.

can you write me a loop for that....

it should go into a directory ......search for that files generated that day and the script should run against each file......the output from all the four files must be clubbed together into one file and i should get the count.........can we inculde that in this script or should it be another one????

Use file and cat as below and attach the script

Replace the Directory and file name in below script. it will run on all the four files.

I can give you right 'find', if you give me the name of data files

Vinit

find /FullDirectoryPath -name "DataFile*" -mtime -1 -exec cat {} |awk -F\: '{
if(NF==0)
{
if(I>0)
{
print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2
}
X=0
sno=" "
id=" "
firstname=" "
mail=" "
secondname=" "
mail2=" "
I=0
}
if($1~/sno/){sno=$2;I=1}
if($1~/id/){id=$2;I=1}
if($1~/firstname/){firstname=$2;I=1}
if(($1~/mail/)&&(X==0)){mail=$2;X=1;YY=NR;I=1}
if($1~/secondname/) {secondname=$2;X=1;I=1}
if(($1~/mail/)&&(X==1)&&(NR != YY)){mail2=$2;I=1}
}END { print sno"~"id"~"firstname"~"mail"~"secondname"~"mail2 }' |sort -t\~ -k2 -n |awk -F\~ ' BEGIN {I=1} {
if( F2 == $2 )
{
I=I+1
}
else
{
if(NR>1)
{
print F1"~"F2"~"I"~"F3"~"F4"~"F5"~"F6
}
F1=$1; F2=$2; F3=$3; F4=$4; F5=$5; F6=$6
I=1
}
} END { print F1"~"F2"~"I"~"F3"~"F4"~"F5"~"F6 }'

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

Vinit the output for DataFile is obatained by running another script against the four files for that day.......so can do need to call out the four files in another script?

The output for the DataFile will be obatined by running it against one of the four files for that day..........so can we do it this way.........write another script to find the four files from the directory and run agianst each file.....concatenate the data and get the count as mentioned. do we need to write a loop of just use find exec command?

what I understood

You have four files

one script generate the output as DataFile for each file.

You can change the above script, call four times (run in a loop )

for file in `File1 File2 File3 File 4`
do
{
Script1
}
done|  Script2 ( What I created (without find))

Vinit

If you get 4 DataFile ( data as attached in question)
then concatenate  this four files and run script. no need to loop the script.

Vinit

Hi yerruhari

The following bash script should meet your requirements. Just run it from the command line. For example:

bash ./script_for_tilde_separated_output.sh < data_for_tilde_separated_output_file.txt

Note that there is a slight discrepancy in the output from my script since I am using your input data where sno:14 has a second name of 'donald' not 'don' and also has a correspondingly shortened email. Otherwise my output matches your sample output.

Hope this helps.

#!/bin/bash
fields=(sno id firstname firstmail secondname secondmail)
declare -a arry idlist idcount
reset_keys () {
  for r in ${fields[*]}; do
    eval "$r=\ "
  done
}
add_record () {
  if [[ "${idlist[$id]}" ]]; then
    idcount[$id]=$((${idcount[$id]} + 1))
  else
    idlist[$id]=1
    idcount[$id]=1
    arry[${#arry[*]}]="$id~$sno~$id~COUNT~$firstname~$firstmail~$secondname~$secondmail" 
  fi
  reset_keys
}
reset_keys
while read rec; do
  key=${rec%:*}
  val=${rec#*:}
  val=${val// /}
  if [[ "$rec" =~ ^[:blank:]*$ ]]; then
    [[ "${#id}" -gt 1 ]] && add_record
  else
    if [[ "$key" == "mail" ]]; then
      if [[ "${#secondname}" -gt 1 ]]; then
        key=secondmail
      else
        key=firstmail
      fi
    fi
    eval "$key='$val'"
  fi
done 
[[ "${#id}" -gt 1 ]] && add_record
for ((i=0; i<${#arry[*]};i++)); do
  line=${arry[i]}
  id=${line%%~*}
  line=${line#*~}
  count=${idcount[$id]}
  line=${line/COUNT/$count}
  echo $line
done

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:

Vinit.........

I have a total of 5 servers for which i need to run the script

for 3 servers then files are at /home/data/Datafiles

for other two servers the files are at different location /home/records/Datafiles

can you write a loop for this dude with the above mentioned requirement.......

vinit........For the code which you wrote..........can you write explanation comments if you don't mind...........

Yerruhari,

Sorry for late reply, I was away.

You have 5 servers, does it means 5 files or 5*4 files?

Vinit

Vinit,

I need to have something like this

for server in `01 02 03 04 05`
do
   for file in `file1 file2 file3 file4`
   do
   script1
done
done

But i have some concerns here.....

for server 01,02,03 the files are at /home/data/Datafiles

for server 04 and 05 the files are at /home/records/Datafiles

I need to automate the script so that it gets only the files within last 24 hours. so first i need to search for the files in the directories concerned to their respective server and run the script1 for each and every file and redirect the whole output to a file. can you give me the correct code to do this. Also note that the script1 will be at following path /home/scripts/script1.

Thanks in advance....can u do that as soon as you can....if possible...can u look at my other question too....I really appreciate your help vinit...

Also can you explain the previous script you wrote with comments if possible......Thanks a lot in advance

To connect to different servers you need to add login information as well

for server in "01 02 03 04 05"
do
{
if [ server=="01" -o server=="02" -o server=="03" ]
then
{
for file in `find /home/data/Datafiles -name "file[1-4]" -type f -mtime -1`
do
{

script to run

}
done
}
else
{
for file in `find /home/records/Datafiles -name "file[1-4]" -type f -mtime -1`
do
{

script to run

}
done
}
fi
}
done

Vinit,

Also please do take into consideration that i need to run the script against all the four files...and not a single file......concatenate the data into one file and from this file i should get the count based on the field 3 and field 4 with the following code you provided. can you let me know where in this script i should include this and give me some final code.

awk -F\, '{ if(F3==$3) { F4=F4+$4 } else { if(NR>1) { print F1","F2","F3","F4","F5","F6 } F1=$1;F2=$2;F3=$3;F4=$4;F5=$5;F6=$6 }}  END {print F1","F2","F3","F4","F5","F6 }' File1

File1 here is the concatenated output file mentioned above