On Mar 17, 10:08 pm, Ed Morton <mor...@lsupcaemnt.com> wrote:
> On 3/17/2008 9:00 PM, mostro...@gmail.com wrote:
>
>
>
> > Hello,
>
> > I have a file with two columns and about 1200 rows. The first column
> > displays a number (unique ID) and the second column displays some
> > keywords.
>
> > Ex.
>
> > ID keywords
> > 7 dog || cat || bird || shark || dolphin || rabbit
> > 3454 some || more || random || data || goes || here
>
> > What I want to do is turn this text into SQL statements so I can
> > upload it to Mysql.
>
> > This is what the command would look like:
>
> > Update table insert keywords = 'dog || cat || bird || shark || dolphin
> > || rabbit ' where Id = 7
>
> > Is there a way to go from the example to the SQLstatement for every
> > row and output it into another file to be used by Mysql?
>
> > Update table insert keyword = $2 where Id = $1
>
> > I hope this makes sense.
>
> > thanks
>
> Assuming that's a tab character between the ID and the keywords:
>
> awk -F'\t' -v q=\' '{printf "Update table insert keywords = %s where Id =
> %s\n",q$2q,$1}' file
>
> If there really is a headr line you need to skip, just make it:
>
> awk -F'\t' -v q=\' 'NR>1{printf "Update table insert keywords = %s where Id =
> %s\n",q$2q,$1}' file
>
> and if the white space after the ID isn't a tab:
>
> awk -v q=\' '{id=$1; sub(/[^[:space:]]+[[:space:]]+/,""); printf "Update table
> insert keywords = %s where Id = %s\n",q$0q,id}' file
>
> Ed.

Hi Ed,

It almost works.

Two things:

[1] The newline character doesn't work as expected. I get two update
statements per line
Update table insert keywords = ' ' where Id = Update table insert
keywords = 'dog || cat || bird || shark || dolphin || rabbit ^M' where
Id = 7

Each line looks the same. The line begins with "Update table insert
keywords = '' where Id =" followed by the correct statement.

[2] There is a ^M (Ctrl-M) right before the last ' (second instance of
the variable) character in each line. This is an easy fix with the
dos2unix command


Both the first and third awk statements produce the same result but
the file is TAB separated.

Thanks