topic Re: awk or perl to place 'around word' in Operating System - HP-UX

awk or perl to place 'around word'

Ratzie — Tue, 29 Nov 2005 15:06:15 GMT

I have a tab deliminated file (SQL LOAD)
I need to surround the field by ' ', so it can be inserted into the database.
Also if I could, do a unique on the file and only pull the one instance of the file.

File
SLAMTIDSQL 345 TEST SQL LOAD
TIDDLAMSQL 678 TEST SQL LOAD
TESTIDSQL1 111 TESTING LOAD
LAURATEST1 188 HOME
TIDSLAMSQL 678 TEST SQL LOAD
LAURATEST1 188 HOME

Need to be like:
'SLAMTIDSQL','345 TEST SQL LOAD'
'TIDDLAMSQL','678 TEST SQL LOAD'
'TESTIDSQL1','111 TESTING LOAD'
'LAURATEST1','188 HOME'

Re: awk or perl to place 'around word'

Jeff_Traigle — Tue, 29 Nov 2005 15:30:32 GMT

Neither awk nor perl, but will work:

ex filename <1,\$s/^/'/
1,\$s/$/'/
1,\$s/ /','/
wq!
EOD

Re: awk or perl to place 'around word'

Raj D. — Tue, 29 Nov 2005 15:36:10 GMT

Hi LHradowy ,

To do this , you can take help of vi.

1. use vi to add ' at both end.
:%s/^/'/g
:%s/$/'/g

save the file in diff name. say aa

2. cut the first column to a diff file. aa.1
# cat aa | awk '{print $1}' > aa.1

3. vi aa.1
:%s/$/','/g

4. # cat aa | awk '{print $2 , $3 , $4 , $5}' > aa.2

5. # paste aa.1 aa.2 > aa.final

Cheers,
Raj.

Re: awk or perl to place 'around word'

A. Clay Stephenson — Tue, 29 Nov 2005 15:38:01 GMT

Create a simple awk script, my.awk
----------------------
{
printf("'%s','",$1)
i = 2
while (i < NF)
{
printf("%s ",$(i))
++i
}
printf("%s'\n",$(i))
}
----------------------------
awk -f my.awk infile > outfile

Re: awk or perl to place 'around word'

James R. Ferguson — Tue, 29 Nov 2005 15:40:51 GMT

Hi:

Given the perl script 'perl.pl':

# cat perl.pl
#!/usr/bin/perl
while (<>) {
@flds = split /\s+/;
print "'$flds[0]','@flds[1..$#flds]'\n";
}

Now, do:

./perl.pl yourfile|sort -u

Regards!

...JRF...

Re: awk or perl to place 'around word'

Jeff_Traigle — Tue, 29 Nov 2005 15:43:38 GMT

Oh yeah... you said you wanted only unique lines... easily added:

sort -u filename > newfile
ex newfile <1,\$s/^/'/
1,\$s/$/'/
1,\$s/ /','/
wq!
EOD

Re: awk or perl to place 'around word'

john korterman — Tue, 29 Nov 2005 15:45:08 GMT

Hi,

the simple and slow approach:

#!/usr/bin/sh
cat infile | while read a b
do
echo \'$a\',\'$b\' >>path_to_outfile
done

regards,
John K.

Re: awk or perl to place 'around word'

H.Merijn Brand (procura — Tue, 29 Nov 2005 17:51:52 GMT

JRF delimits on all whitespace, not on tabs

# perl -ple'chomp;$_=join",",map{"\47$_\47"}split/\t/,$_,-1' file

a5:/tmp 115 > cat file
SLAMTIDSQL 345 TEST SQL LOAD
TIDDLAMSQL 678 TEST SQL LOAD
TESTIDSQL1 111 TESTING LOAD
LAURATEST1 188 HOME
TIDSLAMSQL 678 TEST SQL LOAD
LAURATEST1 188 HOME
a5:/tmp 116 > cat -vet file SLAMTIDSQL^I345 TEST SQL LOAD$
TIDDLAMSQL^I678 TEST SQL LOAD$
TESTIDSQL1^I111 TESTING LOAD$
LAURATEST1^I188 HOME$
TIDSLAMSQL^I678 TEST SQL LOAD$
LAURATEST1^I188 HOME$
a5:/tmp 117 > perl -ple'chomp;$_=join",",map{"\47$_\47"}split/\t/,$_,-1' file
'SLAMTIDSQL','345 TEST SQL LOAD'
'TIDDLAMSQL','678 TEST SQL LOAD'
'TESTIDSQL1','111 TESTING LOAD'
'LAURATEST1','188 HOME'
'TIDSLAMSQL','678 TEST SQL LOAD'
'LAURATEST1','188 HOME'
a5:/tmp 118 >

Enjoy, Have FUN! H.Merijn [ who would use DBI & DBD::Whatever ]

Re: awk or perl to place 'around word'

Sandman! — Tue, 29 Nov 2005 19:32:37 GMT

How about the following aw construct:

# tr "\t" " "
cheers!

Re: awk or perl to place 'around word'

Sandman! — Tue, 29 Nov 2005 19:33:08 GMT

How about the following awk construct...

# tr "\t" " "
cheers!

Re: awk or perl to place 'around word'

Muthukumar_5 — Wed, 30 Nov 2005 00:51:59 GMT

You can try as,

# sed -e "s/$^[^ ]*$ $.*$/'\1','\2'/" filename > tmp.file
# mv tmp.file filename

hth.

Re: awk or perl to place 'around word'

Muthukumar_5 — Wed, 30 Nov 2005 00:53:26 GMT

You can use perl to update the change in the same file with one liner as,

# perl -pi -e "s/(^[^ ]*)(.*)/'\$1','\$2'/" filename

hth.

Re: awk or perl to place 'around word'

Muthukumar_5 — Wed, 30 Nov 2005 01:05:35 GMT

Change perl solution as,

# perl -pe "s/(^[^ ]*) (.*)/'\$1','\$2'/"

space needed.

AWK solution:

# awk -v tab="'" '{ printf tab$1tab","tab;for(i=2;i > out
# mv out

hth.

Re: awk or perl to place 'around word'

Ratzie — Wed, 18 Jan 2006 14:44:47 GMT

thanks