Awk to remove carriage return from 65th field | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

Awk to remove carriage return from 65th field

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 03-21-2012
pinnacle pinnacle is offline
Registered User
 
Join Date: Apr 2009
Last Activity: 8 May 2014, 12:25 PM EDT
Posts: 242
Thanks: 9
Thanked 1 Time in 1 Post
Awk to remove carriage return from 65th field

Hi,

I have a pipe delimited file. There are around 700 columns in the file.

The 65th column has carriage return which is causing read issue with our ETL process. I would like to replace the new line characters in 65th field with "nothing"

i have return the following code and need help to fix it.


Code:
 
nawk -F"|" 'NF=65 && NF like \n {gsub(NF,\n,"") } file

Please help me fix this.
Sponsored Links
    #2  
Old 03-21-2012
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 29 September 2014, 8:03 PM EDT
Location: Saskatchewan
Posts: 19,468
Thanks: 794
Thanked 3,282 Times in 3,077 Posts
\n is a newline, not a carriage return. Which do you mean?

If you mean an extra newline is breaking the line early, awk doesn't know the difference between the 'right' newlines and the 'wrong' ones, they're all just bytes; and it won't be able to delete a newline it hasn't read yet.

But you can count the number of fields to see if it broke early and save the partial record for next time.


Code:
awk -F"|" 'NF==65 { T=$0; next}; T { print T $0; T=""; next} 1

Sponsored Links
    #3  
Old 03-21-2012
pinnacle pinnacle is offline
Registered User
 
Join Date: Apr 2009
Last Activity: 8 May 2014, 12:25 PM EDT
Posts: 242
Thanks: 9
Thanked 1 Time in 1 Post
When i do octal dump on the line of concern, it looks like the following. It has \r and \n. This is happening at field 65. The only thing i like to do is if the new line character is found in 65th field i want to join this line with the next line.

Help is really appreciated.


Quote:
0001160 060 040 174 060 061 061 065 065 062 174 120 104 120 124 130 015
0 | 0 1 1 5 5 2 | P D P T X \r
0001200 042 012
" \n
---------- Post updated at 03:23 PM ---------- Previous update was at 03:21 PM ----------

In the 65th field we are expecting data like

"ABC DEF"

but what we are receiving is

"ABC
DEF"

we like to convert to "ABC DEF" using awk

Appreciate help.
    #4  
Old 03-21-2012
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 29 September 2014, 8:03 PM EDT
Location: Saskatchewan
Posts: 19,468
Thanks: 794
Thanked 3,282 Times in 3,077 Posts
Did my code do anything?

I'd make a slight change knowing there's actual CR's in there:

Whenever it sees a line ending in \r, it'll change \r to space, save that line, fetch the next, and print both together. Otherwise it will print lines unmodified.

Code:
awk -F"|" '/\r/ { sub(/\r/, " "); T=$0; getline; print T $0; next } 1'

Sponsored Links
    #5  
Old 03-21-2012
pinnacle pinnacle is offline
Registered User
 
Join Date: Apr 2009
Last Activity: 8 May 2014, 12:25 PM EDT
Posts: 242
Thanks: 9
Thanked 1 Time in 1 Post
Quote:
Originally Posted by Corona688 View Post
Did my code do anything?

I'd make a slight change knowing there's actual CR's in there:

Whenever it sees a line ending in \r, it'll change \r to space, save that line, fetch the next, and print both together. Otherwise it will print lines unmodified.

Code:
awk -F"|" '/\r/ { sub(/\r/, " "); T=$0; getline; print T $0; next } 1'


Thanks Corona this works but needs few changes.

These works but need 3 small changes.
Quote:
nawk -F"|" '/\r/ { sub(/\r/, " "); T=$0; getline; print T $0; next } 1'
  • The replacement should not happen for all records, should happen only when the carriage return is present in the 65th field.
  • Carriage return needs to be replaced with "" (empty) instead of " " space

  • And to replace double quotes " in 65th field with empty
Appreciate help
Sponsored Links
    #6  
Old 03-22-2012
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 29 September 2014, 8:03 PM EDT
Location: Saskatchewan
Posts: 19,468
Thanks: 794
Thanked 3,282 Times in 3,077 Posts
Why would carriage returns be wanted in any of the records, unless the entire file is delimited with \r\n ? Whether it is would be good to know.


Code:
awk -F"|" 'NF==65 { sub(/\r/, ""); T=$0; getline; print T $0; next} 1'

Sponsored Links
    #7  
Old 03-22-2012
pinnacle pinnacle is offline
Registered User
 
Join Date: Apr 2009
Last Activity: 8 May 2014, 12:25 PM EDT
Posts: 242
Thanks: 9
Thanked 1 Time in 1 Post
Quote:
Originally Posted by Corona688 View Post
Why would carriage returns be wanted in any of the records, unless the entire file is delimited with \r\n ? Whether it is would be good to know.


Code:
awk -F"|" 'NF==65 { sub(/\r/, ""); T=$0; getline; print T $0; next} 1'

Thanks Corona !!

There is a possibility that all the files at the end may have carriage return as the file is ftped to us, hence i want to look only in the 65th field.

This works
Quote:
Now my data looks in 65th field like this
|ABCTX"" |
I like it to look like this.
|ABCTX |
I modified and tried the below but does not work. I dont know where i am going wrong.


Code:
 
awk -F"|" 'NF==65 { sub(/\r/, ""); sub(/"/, " "); T=$0; getline; print T $0; next} 1' 

awk -F"|" 'NF==65 { sub(/\r/, ""); sub(/\"/, " "); T=$0; getline; print T $0; next} 1' 

awk -F"|" 'NF==65 { sub(/\r/, ""); sub(/\"\"/, " "); T=$0; getline; print T $0; next} 1'

Help is appreciated.
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Remove carriage return in a record agathaeleanor Shell Programming and Scripting 10 08-03-2011 04:29 AM
Remove ^M (carriage return) with string manipulation aquila_1 UNIX for Dummies Questions & Answers 3 05-28-2011 04:24 PM
sqlplus returning value - remove carriage return '\r' - Please help sunshine1974 Shell Programming and Scripting 10 09-18-2009 08:19 PM
To remove carriage return between the line shash UNIX for Dummies Questions & Answers 5 07-31-2007 12:12 PM
Remove a carriage return at end of variable flagship99 UNIX for Dummies Questions & Answers 5 11-17-2004 05:39 PM



All times are GMT -4. The time now is 05:48 AM.