awk or perl to place 'around word'

[Ratzie]

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'

[Jeff_Traigle · Accepted solution]

Neither awk nor perl, but will work:

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

[Raj D.]

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.

[A. Clay Stephenson]

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

[James R. Ferguson]

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...

[Jeff_Traigle]

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

[john korterman]

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.

[H.Merijn Brand (procura]

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 ]

[Sandman!]

How about the following aw construct:

# tr "\t" " "
cheers!

[Sandman!]

How about the following awk construct...

# tr "\t" " "
cheers!

[Muthukumar_5]

You can try as,

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

hth.

[Muthukumar_5]

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

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

hth.

[Muthukumar_5]

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.

[Ratzie]

thanks