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
# 1  
Old 08-13-2012
awk loop - substr and sub query
Hi there

Looking for help with formating text output.

I have following file

Code:
1ABC1234567890123Y          31-Jul-2012
1DEF1234567890124NMEDIUM    31-Jul-2012
193939312345889  YSMALL     31-Jul-2012
10093939312345889YSMALL     31-Jul-2012
1                YSMALL     31-Jul-2012

I'm reading lines that start with '1' and then read 16 characters.

Code:
awk '/^1/ {A[a++]=substr($0,2,16)} END {for(i=0;i<a;i++) printf "values (%c%s%c)\n",39,A[i],39}' file_name

I'm getting following

Code:
values ('ABC1234567890123')
values ('DEF1234567890124')
values ('93939312345889  ')
values ('0093939312345889')
values ('                ')

but would like to remove all spaces and get

Quote:
values ('ABC1234567890123')
values ('DEF1234567890124')
values ('93939312345889')
values ('0093939312345889')
values ('')
Assume have to use sub function. So far tried following

Code:
awk '/^1/ {A[a++]=substr($0,2,16) sub(/ */,"")} END {for(i=0;i<a;i++) printf "values (%c%s%c)\n",39,A[i],39}' file_name

and

Code:
awk '/^1/ {A[a++]=sub(/ */,"",substr($0,2,16))} END {for(i=0;i<a;i++) printf "values (%c%s%c)\n",39,A[i],39}' file_name

with no joy.

Any ideas.
Thanks
Last edited by viallos; 08-13-2012 at 06:20 AM..
# 2  
Old 08-13-2012
Line 5 of your input starts with a 1 but is not in the wanted output. What's the criteria? Missing alpha numeric characters between 1 and YSMALL?
# 3  
Old 08-13-2012
Looks like the spaces in the file has been replaced when posted this.

In the file
Line 3 - Between 9 and Y there are 2 spaces
Line 5 - Between 1 and Y there are 16 spaces

Hope this looks better:
Code:
1ABC1234567890123Y          31-Jul-2012
1DEF1234567890124NMEDIUM    31-Jul-2012
193939312345889  YSMALL     31-Jul-2012
10093939312345889YSMALL     31-Jul-2012
1                YSMALL     31-Jul-2012

# 4  
Old 08-13-2012
Hi


Code:
$ awk '/^1/{x=substr($0,2,16);sub(/ *$/,"",x);print "values(" q x q")";}' q="'" file
values('ABC1234567890123')
values('DEF1234567890124')
values('93939312345889')
values('0093939312345889')
values('')


Guru.
# 5  
Old 08-13-2012
Yes, use code tags. quote tags will not help to preserve such things.

Code:
$ awk '/^1/ {a=substr($0,2,16); gsub(/ /,"",a); print "values(" b a b ")"}' b="'" infile
values('ABC1234567890123')
values('DEF1234567890124')
values('93939312345889')
values('0093939312345889')
values('')

# 6  
Old 08-13-2012
Thank you both it is working fine but this is not exactly what I was looking for.

This code was only part of my program and I have to use loop rather then proposed code.

I will try to explain better - I'm building sql statement based on the file data. file contain also footer with row count - line starting with '0'

The whole code is

Code:
File_read()
{
     awk -v Batch_SQL=$Batch_SQL -v Load_SQL=$Load_SQL -v data_table=$data_table -v log_table=$log_table '
          /^0/ {H[h++]=substr($0,9,6) + 0} 
          /^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)}
     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
               print "INSERT ALL" > Batch_SQL
               printf "into %s values (1,%cASD%c,%s,%cRows from file processed%c,SYSDATE)\n",log_table,39,39,H[0],39,39 >> Batch_SQL
          }
          else
          {
               print "No data in input file."
          }
     }' $input_file
}

I don't think I can use proposed solution and when I try to replace a in porposed code to A[a] it doesn't work.

Thanks and sorry for not being more detailed in first place.

---------- Post updated at 01:49 PM ---------- Previous update was at 05:35 AM ----------

Anyone will help with this further?

As a quick workaround I have created trigger on DB table that trim values before input but would love to get it done via unix script.

Thanks
Last edited by zaxxon; 08-14-2012 at 05:15 AM.. Reason: indention is quite helpful
# 7  
Old 08-14-2012
Sorry I am not up for analyzing your script. I indented your code as it was no fun to read. Please tell us where your particular problem is. If you think you need a loop, use a loop. You have a for-loop there in the code somewhere.
Please be more precise where the problem is, thanks.
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