Shell Programming and Scripting

Here's the problem...

I have a mysqldump file and I need to put single quotes around the date/time timestamp. So for example I have a line like:

Code:

INSERT INTO attachments VALUES (1,182,2004-08-06 09:24:04,'description'...

and I need it to become

Code:

INSERT INTO attachments VALUES (1,182,'2004-08-06 09:24:04','description'...

I tried

Code:

cat backup.sql | sed s/\,200/,\'200/ | sed s/..:..:../\&\'/ | less

and it did what I wanted, however it would also add a single quote infront of the first occurance of 200 so eventually it would insert

Code:

('2005,20041,2003-05-06 10:13:12',...

So then I attempted

Code:

cat backup.sql | sed s/.*\,.*\,/\&\'/ | sed s/..:..:../\&\'/ | less

but it seems to only add the single quote after the time. I can't figure out how to get the single quote before the date as well.

Anyone able to provide assistance?

Thanks!
Primal

Try this:

Code:

awk -F, '/INSERT INTO attachments VALUES/{$3="\047"$3"\047"}1' backup.sql

Regards

Try:

Code:

sed "s/,\(200[0-9]-..-.. ..:..:..\)/,'\1'/" mysqldump

@unilover
When I run your command it returns:

Code:

\1 not defined in the RE

@Franklin52
When I run your command it places the single quotes around the dates but it removed the commas

Code:

...VALUES (1 182 '2004-...

I was able to get it with this

Code:

sed s/VALUES\ \([0-9]*\,[0-9]*\,/\&\'/ | sed s/..:..:../\&\'/

but then I realized that the timestamp also appears in other tables (different fields and more than once)

This is where I am right now

Code:

sed s/....-..-..\ ..:..:../\'&\'/g

The only problem with that is some timestamps already have single quotes around them and now after this line, some timestamps get 2 singles quotes surrounding them ''2004-08-06 09:24:04''

Is there anyway to do it so that it skips any timestamps that already have quotes?

I can do

Code:

s/,....-..-..\ ..:..:../\'&\'/g

but that would place the single quote ',2004-08-06 09:24:04' Is it possible to include the comma in the search and then place the single quote right after it?

Maybe if you append the following to your command-line:

Code:

|sed -e "s/''2/'2/g" -e "s/'',/',/g"

Code:

awk 'BEGIN{FS=OFS=","}/INSERT INTO attachmentsVALUES/{$3="\047"$3"\047"}1' backup.sql

Regards

@Franklin52
Can you explain your awk command? What does the 1 at the end mean?