Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

help with ksh/awk/sed script, random # of fields

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting help with ksh/awk/sed script, random # of fields
# 1  
Old 04-17-2009
help with ksh/awk/sed script, random # of fields
Hello all, I'm working on an attendance callout script for a school district. I need to change our current layout for the vendor. Currently the data is in the form of:
studentid,period,building,
Heres a sample of some made up records:
500,1,30,
500,2,30,
500,3,30,
500,6,30,
7899,2,31,
9021,1,33,
9021,6,33,
907711,5,40,
907711,6,40,
I need to reformat this, omitting the header row, to look like the following:
500,1,2,3,6,30,
7899,2,31,
9021,1,6,33,
907711,5,6,40,
I've done stuff like this with awk in the past when the number of fields were a constant. Just not sure how to loop through this when the number of fields vary. The kids can have an unexcused in any number of periods throughout the day. Some of our buildings have over 9 periods configured for attendance purposes as well.

If anyone has some code to get me going in the right direction that would be great.

I will read the file first then do something like this to compare the current record with the next record to verify its the same kid. (beyond that I'm not sure how to proceed)

Code:
awk -F, '
$1 == lastid {
         do something
}
$1 != lastid {
        lastid=$1
        do something
}'

Thanks,
# 2  
Old 04-17-2009
Code:
awk -F, -v OFS=, '{ for (I=2; I<NF; I++)
                    {print $1, $I}
                  }' <<END |\
sort -kn1,1 -kn2,2 -t, -u |\
awk -F, 'NR==1{Save0=$1}
         Save0 == $1{Line=Line "," $2; next}
         Save0 != $1 {print Save0  Line;
                      Line="," $2;
                      Save0=$1;
                     }
         END{print Save0  Line;}'
500,1,30,
500,2,30,
500,3,30,
500,6,30,
7899,2,31,
9021,1,33,
9021,6,33,
907711,5,40,
907711,6,40,

Produced

Code:
500,1,2,3,6,30
7899,2,31
9021,1,6,33
907711,5,6,40

# 3  
Old 04-17-2009
thanks!
Thanks a lot awk, I'll get to give your code a go on Monday first thing.
# 4  
Old 04-18-2009
Assuming your data is sorted:

Code:
awk -F, '
NR==1{s=$1 FS $2; b=$3; next}
b!=$3{print s FS b; s=$1 FS $2; b=$3; next}
{s=s FS $2}
END{print s FS b}' file

Regards
# 5  
Old 04-20-2009
Code:
nawk -F"," '{
if (_[$1","$3]==""){
  _[$1","$3]=$2
  next
}
_[$1","$3]=sprintf("%s,%s",_[$1","$3],$2)
}
END{
for (i in _){
 split(i,arr,",")
 print arr[1]","_[i],","arr[2]
} 
}' filename

# 6  
Old 04-21-2009
Quote:
Originally Posted by awk
Code:
awk -F, -v OFS=, '{ for (I=2; I<NF; I++)
                    {print $1, $I}
                  }' <<END |\
sort -kn1,1 -kn2,2 -t, -u |\
awk -F, 'NR==1{Save0=$1}
         Save0 == $1{Line=Line "," $2; next}
         Save0 != $1 {print Save0  Line;
                      Line="," $2;
                      Save0=$1;
                     }
         END{print Save0  Line;}'
500,1,30,
500,2,30,
500,3,30,
500,6,30,
7899,2,31,
9021,1,33,
9021,6,33,
907711,5,40,
907711,6,40,

Thanks awk, works great for me. I was wondering what the <<END is in the first awk command? Is it like a here doc? I'm trying to understand this better to figure out what order the code is being executed in.

thanks again
# 7  
Old 04-21-2009
Ideally, I should have had the word END on a line by itself after the data. It shows the korn shell where to stop the input of data to the awk program.

Still the script terminated at that point, the shell script figures out the END is implied.

Sorry, I will remember to put it in future posts.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Insert field between two fields using awk or sed

Hi All, I am trying to insert two columns in the following text. I tried awk but failed to achieve. Highly appreciate your help DATETIME="28-Sep-2013;20:09:08;" CONTROL="AB" echo "Myfile.txt;11671;7824.90;2822.48" The DATETIME will be inserted at the beginning and CONTROL will... (4 Replies)
Discussion started by: angshuman
4 Replies

2. Ubuntu

expect script for random password and random commands

Hi I am new to expect. Please if any one can help on my issue its really appreciable. here is my issue: I want expect script for random passwords and random commands generation. please can anyone help me? Many Thanks in advance (0 Replies)
Discussion started by: vanid
0 Replies

3. Shell Programming and Scripting

KSH: substitution character, AWK or SED?

Hi Gurus, I am working with a korn shell script. I should replace in a very great file the character ";" with a space. Example: 2750;~ 2734;~ 2778;~ 2751;~ 2751;~ 2752;~ what the fastest method is? Sed? Awk? Speed is dead main point, Seen the dimensions of the files Thanks (6 Replies)
Discussion started by: GERMANICO
6 Replies

4. UNIX for Dummies Questions & Answers

Random fields

Hi, there folks! Well, trying again to build exercises for my students I come across the following idea. Inputfile: csv with two fields (f1 and f2) separated by a tab. Each field contains half a sentence, which added to the one contained in the other field forms a complete sentence (s), so... (2 Replies)
Discussion started by: eldeingles
2 Replies

5. Shell Programming and Scripting

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (3 Replies)
Discussion started by: yerruhari
3 Replies

6. UNIX for Dummies Questions & Answers

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (1 Reply)
Discussion started by: yerruhari
1 Replies

7. UNIX for Advanced & Expert Users

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (1 Reply)
Discussion started by: yerruhari
1 Replies

8. UNIX for Dummies Questions & Answers

count number of fields not using SED or AWK

hi forums i need help with a little problem i am having. i need to count the number of fields that are in a saved variable so i can use that number to make a different function work properly. is there a way of doing this without using SED/AWK? anything would be greatly appreciated (4 Replies)
Discussion started by: strasner
4 Replies

9. Shell Programming and Scripting

awk sed cut? to rearrange random number of fields into 3 fields

I'm working on formatting some attendance data to meet a vendors requirements to upload to their system. With some help on the forums here, I have the data close. But they've since changed what they want. The vendor wants me to submit three fields to them. Field 1 is the studentid field,... (4 Replies)
Discussion started by: axo959
4 Replies

10. Shell Programming and Scripting

awk/sed/ksh script to cleanup /etc/group file

Many of my servers' /etc/group file have many userid's that does not exist in /etc/passwd file and they need to be deleted. This happened due to manual manipulation of /etc/passwd files. I need to do this for 40 servers. Can anyone help me in achieving this? Even reducing a step or two will be... (6 Replies)
Discussion started by: pdtak
6 Replies
Login or Register to Ask a Question