Oracle's SQL*Plus application spools fields with spaces even if you try to trim the fields, because SQL*Plus is "doing you a favor" by making the fields line up. One way to work around this is to concatenate all fields into one big field so that SQL*Plus spools one big field. However, when I tried this with some very large fields, I got an error implying that I was concatenating too many characters. So how to work around this?
Here's what I did: I let SQL*Plus spool a tab delimited file with leading and trailing spaces (trailing blanks for all fields except for a field which uses STRAGG and had one leading space). Then I created an awk script to remove the leading and trailing spaces with code similar to the following:
#!/bin/awk -f
BEGIN { # Initialize section
FS="\t" # Input field separator
OFS="\t" # Output field separator
}
{
for (i=1; i<=NF; i++ ){ # For all fields
sub(/^ +/, "", $i) # Delete leading spaces
sub(/ +$/, "", $i) # Delete trailing spaces
}
print $0 # Print all fields to output
}
Based on what I saw on other web pages on the Internet, I thought that, instead of using the two sub statements, I could just use $i=$i to remove leading and trailing spaces, but that didn't work for me. Maybe my version of awk doesn't support that.
To call this awk script, I just needed to add one line to my shell script which calls the awk script; the line looks similar to this:
awk -f awkscript oldfile.txt > newfile.txt
1 comment:
Thanks Rick.
I was struggling to get rid of trailing spaces from extracted files. This script saved my time and working fine.
Post a Comment