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

On 3/17/2008 9:00 PM, mostro713@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.

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

mostro713@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

My take (well, not very brilliant, but anyway...):

(assumes no header line)
sed "s/^\([0-9]\{1,\}\)[ \t]\{1,\}\(.*\)/update table insert keywords = '\2'
where id = \1/g" yourfile.txt

(if you have the header line)
sed -n "2,$ s/^\([0-9]\{1,\}\)[ \t]\{1,\}\(.*\)/update table insert keywords
= '\2' where id = \1/pg" file.txt

All commands must be on a single line.

--
All the commands are tested with bash and GNU tools, so they may use
nonstandard features. I try to mention when something is nonstandard (if
I'm aware of that), but I may miss something. Corrections are welcome.

On 3/18/2008 11:43 AM, mostro713@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.

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.