Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

sql dump file - how to get certain values using sed

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sql dump file - how to get certain values using sed
# 1  
Old 05-10-2012
sql dump file - how to get certain values using sed
Hi,

I have a dumpfile.sql -
========Start of file=================
Code:
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
Code:
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.

Moderator's Comments:
Mod Comment How to use code tags
Last edited by Franklin52; 05-10-2012 at 04:52 AM.. Reason: Please use code tags
# 2  
Old 05-10-2012
Code:
$ 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

https://www.unix.com/how-post-unix-li...code-tags.html
# 3  
Old 05-10-2012
How about this ?
Code:
 awk '/CREATE TABLE/{gsub("`","",$3);split($3,a,"_");printf a[1]","$3","}/AUTO_INCREMENT/{print $2}' filename

# 4  
Old 05-10-2012
That was very quick Smilie 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}

Code:
   
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,

Code:
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  
Old 05-10-2012
Code:
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

This User Gave Thanks to pravin27 For This Post:
ashokvpp
# 6  
Old 05-10-2012
Thank you pravin - works now.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Links replacement on sql dump

Hi All I have dump of mysql DB (Joomla), and I found out that there is absolute links on the DB not with the domain name but the IP address ex: ,(115205,'http://54.5.17.148//index.php/restoflink... (12 Replies)
Discussion started by: molwiko
12 Replies

2. Programming

create a spool file based on values passed from korn shell to sql script

this is my issue. 4 parameters are passed from korn shell to sql script. parameter_1= varchar2 datatype or no value entered my user. parameter_2= number datatype or no value entered my user. parameter_3= number datatype or no value entered my user. parameter_4= number datatype or no... (5 Replies)
Discussion started by: megha2525
5 Replies

3. Shell Programming and Scripting

sed parse a lign into a new sql file

Hi everyone, another question while using sed. my sed statement should parse every line in a file and store all "i" variable item a a new file. any wrong arguments here? Thanks a million. task_name => name, object_type => 'TABLE', attr1 => 'TestR3', attr2 => '$i', for i... (4 Replies)
Discussion started by: sundaygeek
4 Replies

4. Shell Programming and Scripting

formating sql file using awk or sed

Hi, I have a file where I would like to add a prompt type object_name statement before every create commnad create or replace force view test_view_01 ( col1 col2 col3 ) as (select a,b,c from sometable ); create or replace view test_view_02 ( col4 col5 col6 ) as (5 Replies)
Discussion started by: jville
5 Replies

5. Shell Programming and Scripting

How do i use sed to replace string with values from a dictionary file

I have file 1 with one million rows. one of the fields is "FIRSTNAME" (the string) I have a second file with about 20 first names. JUDE DAVID HOMER CANE ABEL MARTY CARL SONNY STEVE BERT OSCAR MICKY JAMES JOHN GLENN DOUG (3 Replies)
Discussion started by: yoyolandre
3 Replies

6. Shell Programming and Scripting

Reading values from a file using DB2 SQL

I have some alphbetical codes in that (1 Reply)
Discussion started by: kavithakuttyk
1 Replies

7. Shell Programming and Scripting

how to break mysql dump sql file

Hi folks I have mysql dump which having insert queries, i want to break that file when 10 complete "INSERTS" lines so extract that line and store in 1.sql and 2.sql and for next 10 insert lines. pls guide me how can i do that. Regards, Bash (2 Replies)
Discussion started by: learnbash
2 Replies

8. Shell Programming and Scripting

SED + Regex + SQL Input file

Here's the problem... I have a mysqldump file and I need to put single quotes around the date/time timestamp. So for example I have a line like: INSERT INTO attachments VALUES (1,182,2004-08-06 09:24:04,'description'... and I need it to become INSERT INTO attachments VALUES... (10 Replies)
Discussion started by: primal
10 Replies

9. Shell Programming and Scripting

use SQL loader to dump a fixed length file in to DB

consider a fixed length file 12345abcd8901 12345abcd7777 12345njdu8888 12345hdku8388 i would like to dump it in to oracle DB using sql loader 12345 in to first coloumn abcd in to second coloumn 8901 in to 3rd coloumn (2 Replies)
Discussion started by: anumkoshy
2 Replies

10. UNIX for Dummies Questions & Answers

Aggregate values in a file & compare with sql output

Hi, I have a file containing the following data: junk123junk723itemcode001qty01price10total10junkjunk junk123junk723itemcode002qty02price10total20junkjunk .. .. .. could be 5000+ lines I have an algo and need a code to implement this: 1. Linecount = wc -l (should give 5000) 2. For i... (1 Reply)
Discussion started by: shiroh_1982
1 Replies
Login or Register to Ask a Question