sql dump file - how to get certain values using sed

Shell Programming and Scripting
1

Hi,

I have a dumpfile.sql -
========Start of file=================

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;

CREATE TABLE  `test_table1` (
  `id` int 
) AUTO_INCREMENT=12
CREATE TABLE  `test_table2` (
  `id` int 
) AUTO_INCREMENT=120
CREATE TABLE  `test_table3` (
  `id` int 
) AUTO_INCREMENT=1200

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sample` /*!40100 DEFAULT CHARACTER SET latin1 */;

 CREATE TABLE  `sample_table1` (
  `id` int 
) AUTO_INCREMENT=12
CREATE TABLE  `sample_table2` (
   `id` int 
 ) AUTO_INCREMENT=120
CREATE TABLE  `sample_table3` (
   `id` int 
 ) AUTO_INCREMENT=1200

==============eof======================

I need the output as: i,e

test,test_table1,AUTO_INCREMENT=12
test,test_table2,AUTO_INCREMENT=120
test,test_table3,AUTO_INCREMENT=1200
sample,sam_table1,AUTO_INCREMENT=12
sample,sam_table2,AUTO_INCREMENT=120
sample,sam_table3,AUTO_INCREMENT=1200

I tried with basic sed - given in this forum but still unable to get the desired output.

I am not sure if I need to use awk as well.

Please post the best way to achieve this.

Thank you.

2 
$ nawk '/CREATE DATABASE/ {gsub("`","",$7);db=$7}/CREATE TABLE/{gsub("`","",$3);t=$3}/AUTO_INCREMENT/{incr=$2}{if(db && t && incr){print db","t","incr;t=incr=""}}' input.txt
mysql,test_table1,AUTO_INCREMENT=12
mysql,test_table2,AUTO_INCREMENT=120
mysql,test_table3,AUTO_INCREMENT=1200
sample,sample_table1,AUTO_INCREMENT=12
sample,sample_table2,AUTO_INCREMENT=120
sample,sample_table3,AUTO_INCREMENT=1200

---------- Post updated at 01:26 PM ---------- Previous update was at 01:26 PM ----------

please use code tag, while posting the sample data and scripts

3

How about this ?

 awk '/CREATE TABLE/{gsub("`","",$3);split($3,a,"_");printf a[1]","$3","}/AUTO_INCREMENT/{print $2}' filename
4

That was very quick :slight_smile: Thank you very much itkamaraj.

I did not have nawk - I used just awk and it works!

awk '/CREATE DATABASE/ {gsub("`","",$7);db=$7}/CREATE TABLE/{gsub("`","",$3);t=$3}/AUTO_INCREMENT/{incr=$3}{if(db && t && incr){print db","t","incr;t=incr=""}}' dump.schema

Did a slight change : AUTO_INCREMENT/{incr=$2} to AUTO_INCREMENT/{incr=$3}

   
CREATE TABLE  `sample_table1`  ( 
  `id` int  ) 
AUTO_INCREMENT=12
 CREATE TABLE  `sample_table2` (  
  `id` int   ) AUTO_INCREMENT=120 
CREATE TABLE  `sample_table3` (   
 `id` int   )ENGINE=InnoDB AUTO_INCREMENT=1200

It might help someone.

Good day.

---------- Post updated at 06:25 AM ---------- Previous update was at 03:24 AM ----------

Hello Again,

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sample` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE  `sample_table1`  ( 
`Time_zone_id` int(10) unsigned NOT NULL auto_increment
)ENGINE=InnoDB AUTO_INCREMENT=1200
CREATE TABLE  `sample_table2`  ( 
`Time_zone_id` int(10) NOT NULL auto_increment
)ENGINE=InnoDB AUTO_INCREMENT=120

In the above case, how can I grab the below data.

sample,sample_table1,Time_zone_id int(10) unsigned, AUTO_INCREMENT=1200

I tried modifying the awk command but no luck - I will reading through - catonmat dot net/blog/awk-one-liners-explained-part-one/

Appreciate for your help.

Thanks

5 
awk '/CREATE TABLE/{gsub("`","",$3);split($3,a,"_");printf a[1]","$3","}/Time_zone_id/{gsub("`","",$1);printf $1" "$2","}/AUTO_INCREMENT/{print $2}' filename
6

Thank you pravin - works now.