On Mar 18, 8:57 pm, Ed Morton <mor...@lsupcaemnt.com> wrote:
> On 3/18/2008 11:43 AM, mostro...@gmail.com wrote:
>
>
>
> > 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
>
> Fix that in your input file then rerun the script.
>
>
>
> > Both the first and third awk statements produce the same result but
> > the file is TAB separated.
>
> Yes, the 3rd script will work for any white space, including tab.
>
> Ed.

Everything works.

awk -F'\t' -v q=\' '{printf "Update table insert keywords =%s where id
= %s\n", q$2q,$1}' extract.txt > newfile.sql

Thanks for your on this.