Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk loop - substr and sub query

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk loop - substr and sub query
# 8  
Old 08-14-2012
Sorry zaxxon - didn't want to confuse anyone.
I'm PL/SQL developer not Unix and this was passed into me as a bonus :-).

The issue remains the same. When creating sql insert command I would like to remove trailing spaces before and after the string.

As you can see from the script I have to check if number of data rows (starting with 1) = number provided in the header.
This is done as per

Code:
 
/^0/ {H[h++]=substr($0,9,6) + 0}

Once the number match I have to write 4 positions (columns) form the data rows in file. It is done as per

Code:
for(i=0;i<a;i++)
                    printf "into %s (val1, val2, val3, val4) values (%c%s%c,%c%s%c,%c%s%c,%c%s%c)\n",data_table,39,A[i],39,39,P[i],39,39,S[i],39,39,D[i],39 >> Load_SQL

As you can see I use following A[i], P[i], S[i], D[i] which represent each column I need.

Each of above values are calculated as per

Code:
/^1/ {A[a++]=substr($0,2,16)} 
          {P[p++]=substr($0,18,1)}
          {S[s++]=substr($0,19,10)}
          {D[d++]=substr($0,29,11)}

Your solution suggested to replace 'A[a++] 'with 'a' and then following 'P[p++]' with 'p' and ... S and D but if I do that I get only one last row read into the output file.

If I try to adopt your code into my code so I'm changing my code and adding gsub after substr

Code:
/^1/ {A[a++]=substr($0,2,16);gsub(/ /,"",A[a])} 
          {P[p++]=substr($0,18,1);gsub(/ /,"",P[p])}
          {S[s++]=substr($0,19,10);gsub(/ /,"",S[s])}
          {D[d++]=substr($0,29,11);gsub(/ /,"",D[d])}

It is not working.

Hope it is less confusing now.

Thanks for help.
# 9  
Old 08-17-2012
Moving forward with this issue.

File structure
Code:
1ABC1234567890123Y          31-Jul-2012
1DEF1234567890124NMEDIUM    31-Jul-2012
193939312345889  YSMALL     31-Jul-2012
10093939312345889YSMALL     31-Jul-2012
1                YSMALL     31-Jul-2012
0CPC_ASD00000512-Apr-2012_05:20

My code to read file by position

Code:
awk '
/^0/ {H[h++]=substr($0,9,6) + 0} 
/^1/ {A[a++]=gsub(/ /,"",substr($0,2,16))} 
{P[p++]=gsub(/ /,"",substr($0,18,1))}
{S[s++]=gsub(/ /,"",substr($0,19,10))}
{D[d++]=gsub(/ /,"",substr($0,29,11))}
END{
if(a!=H[0])
{
print "Incorrect number of data rows in input file."
exit 1
}
else if (H[0]!=0)
{
printf "%s data row(s) in input file\n",H[0]
print "INSERT ALL" > Load_SQL
for(i=0;i<a;i++)
printf "into %s (val1, val2, val3, val4) values (%c%s%c,%c%s%c,%c%s%c,%c%s%c)\n",data_table,39,A[i],39,39,P[i],39,39,S[i],39,39,D[i],39 >> Load_SQL
print "SELECT * from dual;" >> Load_SQL
}
else
{
print "No data in input file."
}
}' $input_file

Result of the code

Code:
INSERT ALL
into data_table (val1, val2, val3, val4) values ('0','0','10','0')
into data_table (val1, val2, val3, val4) values ('0','0','4','0')
into data_table (val1, val2, val3, val4) values ('2','0','5','0')
into data_table (val1, val2, val3, val4) values ('0','0','5','0')
into data_table (val1, val2, val3, val4) values ('16','0','5','0')
SELECT * from dual;

Question is why gsub function below

Code:
{A[a++]=gsub(/ /,"",substr($0,2,16))}

returning count of spaces in the substring rather then text without those spaces?

How to amend this function to return text without spaces?

Thanks
# 10  
Old 08-17-2012
Because that's how gsub behaves. It returns the number of substitutions made (if any). The string parameter to gsub is modified "in place" (you could say) without gsub returning that modified string.
# 11  
Old 08-17-2012
Thanks for info.

How to return modified string then?
# 12  
Old 08-17-2012
As I said, gsub modifies the string. Just reference that string and you've got the modified string!
# 13  
Old 08-17-2012
Sorry but I don't get it.

What else I'm doing if not referencing this string by A[a++]=

and then printf A[i]
# 14  
Old 08-17-2012
modify
Code:
{A[a++]=gsub(/ /,"",substr($0,2,16))}

as
Code:
{x=substr($0,2,16);gsub(/ /,"",x);A[a++]=x}

This User Gave Thanks to raj_saini20 For This Post:
viallos
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk and substr

Hello All; I have an input file 'abc.txt' with below text: 512345977,213458,100021 512345978,213454,100031 512345979,213452,100051 512345980,213455,100061 512345981,213456,100071 512345982,213456,100091 512345983,213457,100041 512345984,213451,100011 I need to paste the first field... (10 Replies)
Discussion started by: mystition
10 Replies

2. Shell Programming and Scripting

HELP : awk substr

Hi, - In a file test.wmi Col1 | firstName | lastName 4003 | toto_titi_CT- | otot_itit - I want to have only ( colones $7,$13 and $15) with code 4003 and 4002. for colone $13 I want to have the whole name untill _CT- or _GC- 1- I used the command egrep with awk #egrep -i... (2 Replies)
Discussion started by: georg2014
2 Replies

3. Shell Programming and Scripting

awk substr

Hello life savers!! Is there any way to use substr in awk command for returning one part of a string from declared start and stop point? I mean I know we have this: substr(string, start, length) Do we have anything like possible to use in awk ? : substr(string, start, stop) ... (9 Replies)
Discussion started by: @man
9 Replies

4. Shell Programming and Scripting

Substr with awk

Hi to all, I'm here again, cause I need your help to solve another issue for me. I have some files that have this name format: date_filename.csv In my shell I must rename each file removing the date so that the file name is filename.csv To do this I use this command: fnames=`ls ${fname}|... (2 Replies)
Discussion started by: leobdj
2 Replies

5. Shell Programming and Scripting

awk substr

HI I am using awk and substr function to list out the directory names in the present working directory . I am using below code ls -l | awk '{ if ((substr($1,1,1)) -eq d) {print $9 }}' But the problem is i am getting all the files and directories listed where as the requirement i wrote... (7 Replies)
Discussion started by: prabhu_kumar
7 Replies

6. Shell Programming and Scripting

Help with awk and substr

I have the following to find lines matching "COMPLETE" and extract parts of it using substr. sed -n "/COMPLETE/p" 1.txt | awk 'BEGIN { FS = "\" } {printf"%s %s:%s \n", substr($3,17,3),substr($6,4,1), substr($7,4,1)}' | sort | uniq > temp.txt Worked fine until the numbers in 2nd & 3rd substr... (5 Replies)
Discussion started by: zpn
5 Replies

7. Shell Programming and Scripting

awk substr

Hi I have multiple files that name begins bidb_yyyymm. (yyyymm = current year month of file creation). What I want to do is look at the files and where yyyymm is older than 1 month I want to remove the file from the server. I was looking at looping through the files and getting the yyyymm... (2 Replies)
Discussion started by: colesga
2 Replies

8. UNIX for Dummies Questions & Answers

awk or substr

i have a variable 200612 the last two digits of this variable should be between 1 and 12, it should not be greater than 12 or less than 1 (for ex: 00 or 13,14,15 is not accepted) how do i check for this conditions in a unix shell script. thanks Ram (3 Replies)
Discussion started by: ramky79
3 Replies

9. Shell Programming and Scripting

How to use awk substr ?

Hi all, I have a flatfile I would like to get ext = 7950 , how do I do that ? if ($1 == "CTI-ProgramStart") { ext = substr($9,index($9,"Extension")+11,4); But why it is not working ???? Please help . Thanks (1 Reply)
Discussion started by: sabercats
1 Replies

10. Shell Programming and Scripting

awk substr?

Sorry if this has been posted before, I searched but not sure what I really want to do. I have a file with records that show who has logged into my application: 2003-03-14:I:root: Log_mesg: registered servername:userid. (more after this) I want to pull out the userid, date and time into... (2 Replies)
Discussion started by: MizzGail
2 Replies
Login or Register to Ask a Question