how to modify columns in a file (use this file to generate sql script)

[qingbo]

Is there anyone know how to add single quote to each column in a test file?


I have a file that has these lines

aaa qqq vvvv 99gg 123jjkk
bbb qqq vvvv dkdl 09djkd


How to add single quotes using awk or sed or whatever in UNIX?

'aaa' 'qqq' 'vvvv' '99gg' '123jjkk'

Qingbo

[pwoneill]

i would do it in perl (as i know it better than awk/sed)

just read every line of the file
split it up based on the space
print out each individual line with single quotes around it.

something like

<font face="courier">
#!/usr/bin/perl

open (FILE, "/your/file");
while (&lt;FILE&gt;) {
chomp; # gets rid of newline character
($f1,$f2,$f3,$f4,$f5) = split ' ', $_;
print "'$f1' '$f2' '$f3' '$f4' '$f5'\n";
}
</font>

that would print everything to the screen and you could redirect it to a file, or you could just open another file in the program and print it there.

that one works (or might not since it's off the top of my head) only when there are 5 fields, you could do it with a variable number of fields by using some regular expressions but i figured the above way was easiest to understand.

[qingbo]

Thanks a lot. It's very helpful

Qingbo

[pwoneill]

You could also just have generated your script right there

instead of

print "'$f1' '$f2'..."

you could say

print "insert into table columns(col1, col2, col3) values('$f1', '$f2', '$f3');\n";

or something like that. you could keep a loop counter and put commits at whatever batch size you wanted, and then one extra one outside the loop.

[qingbo]

Yes, that's what I did. Thanks again