Import data to mysql database from .txt files using "Sed"

Review the post :http://sanjaybdalal.wordpress.com/2009/05/08/export-mysql-tables-in-txt-files/  . In this post we have export the mysql database data into the .txt files. 

Now we are import the data from .txt files to mysql database. For that we have to reformate the .txt files data which will support in the sql format.

#cat example.txt

1,SANJAY,AHMEDABAD,SYSTEM ADMIN

1,PRIYA,PUNE,PERL DEVELOPER

1,AKSHAY,GONDAL,PERL DEVELOPER

1,MIHIR,MUMBAI,PERL DEVELOPER

Now using "sed" command we will format this file to support sql.

 

sed -e 's/,/","/g' -e 's/^/insert into example values("/g' -e 's/$/");/g' example.txt

#sed -e 's/,/","/g' -e 's/^/insert into example values("/g' -e 's/$/");/g' example.txt > example.txt.new

where ^ represent start to the line and $ represent end of the line.

Output :

#cat example.txt.new

 insert into example values("1","SANJAY","AHMEDABAD","SYSTEM ADMIN");

insert into example values("1","PRIYA","PUNE","PERL DEVELOPER");

insert into example values("1","AKSHAY","GONDAL","PERL DEVELOPER");

insert into example values("1","MIHIR","MUMBAI","PERL DEVELOPER");