escape space characters in loop from file

Shell Programming and Scripting
1

Hi Everyone!

I want to build sql inserts from a list of countries/regions saved in a file. The list looks like this:

United Kingdom
Czech Republic
...

The script I run is:

while read i; 

do 
   var=`expr $var + 1`; 
   echo "INSERT INTO calltypes VALUES($var, '$i','$i');" >> inserts.sql; 

done < countries.txt;

The problem is that this is the result I get:

INSERT INTO calltypes VALUES(2573, 'United','United');
INSERT INTO calltypes VALUES(2574, 'Kingdom','Kingdom');
INSERT INTO calltypes VALUES(2571, 'Czech','Czech');
INSERT INTO calltypes VALUES(2572, 'Republic','Republic');

How can I escape the space characters from the file?

I've seen the solutions in this forum, but they don't work for me.

2

Hi.

What should the SQL look like?

I get quite a different result to you:

$ cat file1
United Kingdom
Czech Republic

$ cat Test
var=1
while read i;
do
   echo "INSERT INTO calltypes VALUES($var, '$i','$i');"
   let var=$var+1
done < file1

$ ./Test
INSERT INTO calltypes VALUES(1, 'United Kingdom','United Kingdom');
INSERT INTO calltypes VALUES(2, 'Czech Republic','Czech Republic');
3

My output looks like scottn's. i should contain the whole line, not only what is separated by spaces.

---------- Post updated at 03:57 PM ---------- Previous update was at 03:53 PM ----------

What shell are you using?

And could you try out a simple

while read LINE; do
    echo $LINE
done < countries.txt
4

Thanks!

It seems that I if I echo it to the screen it works, but when I save it in a file (>> inserts), it splits the lines by spaces.

Does this happen to you too?

5

Hm no. Did you change IFS maybe? Does this also happen in a fresh shell with the mini script I posted before? Ie. add >> to somefile in the line with echo.

6

Thanks again!

Sorry, silly me! The first time I run the script, I did it with a for loop (giving me the bad result) and after I changed the script, I forgot to remove the inserts.sql file, so the correct solution was being appended to the file and I never saw it.

Cheers.