Shell Programming and Scripting

OK, let's tackle this step by step.

First, don't start by attacking the loop. That's just a simplification so that you don't have each and every file manually. Instead, develop the necessary steps using 1 file as example, in a way that can be applied to all other files.

The first thing you want to do with each file is remove the first 2 lines, and the last one. This can easily be done using sed:

Code:

sed -e '1,2d; $d' file

Next, you want to remove all empty lines. This can be included with the previous sed statement:

Code:

sed -e '1,2d; $d; /^[:space:]*$/d' file

Once that's done you want to surround the values with and SQL INSERT statement. For that, we need a second sed statement:

Code:

sed -e '1,2d; $d; /^[:space:]*$/d' file | \
sed -e '1s/^/INSERT INTO file VALUES (/; $!s/$/,/; $s/$/);/'

This can be read as "On the first line, replace the beginning of the line with this string. On all but the last line, replace the end of the line with a comma. On the last line, append the string to the end of the line."

Now that a single file can be processed as needed, we can start working on the loop. A simple for loop is all that is needed:

Code:

for file in /xxx/sss/*
do
    sed -e '1,2d; $d; /^[:space:]*$/d' ${file} | \
    sed -e '1s/^/INSERT INTO file VALUES (/; $!s/$/,/; $s/$/);/'
done

But wait! The table used should be the one the file is named after, so we'll have to use that somewhere too. For that we need basename, and we have to alter the second sed statement a bit (marked in green)

Code:

for file in /xxx/sss/*
do
    filename=$( basename $file )
    sed -e '1,2d; $d; /^[:space:]*$/d' ${file} | \
    sed -e '1s/^/INSERT INTO '${filename}' VALUES (/; $!s/$/,/; $s/$/);/'
done

That's all there is to it. There's no magic, arcane knowledge, or infinite UNIX wisdom needed as long as you can break down the steps as far as needed, and are not afraid of reading the man pages for the commands you think you'll need.

Hi Pludi,

when i try the same its giving syntax error.do we need to have add some thing ?

That's an error message from the shell. Can you post the part of the complete script where you're using the loop, including the surrounding lines (copy-paste please)?

Its simple actually,

I have n number .sql files under a folder each file is having columns inside the file.

and i save this your code and running on that folder

Code:

for file in /home/db2inst1/test/JAVA/DDL/*
do
filename=$( basename $file )
sed -e '1,2d; $d; /^[:space:]*$/d ${file} \
sed -e '1s/^/INSERT INTO '${filename}' VALUES(/; $!s/$/,/; $s/$/);/'
done

when i ran the file using ./column.sh cmd.its giving error.Once its get successed i will use this part of the code in ohter script.

Code:

./column.sh: line 14: syntax error near unexpected token `('
./column.sh: line 14: ` sed -e '1s/^/INSERT INTO '${filename}' VALUES(/; $!s/$/,/; $s/$/);/''

Ah, I see the error, typo on my part. On the first sed command I've left out the closing quote. The line should read

Code:

sed -e '1,2d; $d; /^[:space:]*$/d' ${file} | \

Hi pludi,

yes its working party but ouput is coming like this Even i don't want the Last line "3 records(s) selected" for each file ouput.
sed: can't read sed: No such file or directory

Code:

--------------------,
AAAAAAA,
BBBBBBB,
CCCCCCC ,
3 record(s) selected.,
sed: can't read sed: No such file or directory
--------------------,
AAAAAAA,
BBBBBBB,
CCCCCCC....
......
......
.......

21 record(s) selected.,

do we need to edit some thing else? i could not seen any INSERT statement or braces "( ) " printed.

Yes, I've missed another typo (shame on me). There should be a pipe before the backslash, again on the first sed line. I've corrected and marked it in my previous post.