Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

script to parse text file into sql commands

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting script to parse text file into sql commands
# 1  
Old 08-26-2011
script to parse text file into sql commands
Hello all, I tried searching for something similiar before posting but couldn't come up with anything that fit what I need.

In Linux, I'm trying to parse through a number of files and take the info in them and put it into mysql. My file is a dump from an expect script:

---filename 17da------
Code:
spawn ssh Sys@17da list -l 2 ^M
password:^M
^M^M
system> list -l 2 ^M^M
system^M^M
        blade[1]  H27995BLD^M^M
        blade[2]  H27995TST^M^M
        blade[3]  H2LABBUSMED01^M^M
        blade[4]  H2LABBUSMED02^M^M
        blade[5]  QARWDK^M^M
        blade[6]  QARWFK^M^M
        blade[7]  QARWFW^M^M
        blade[8]  QARWDP^M^M
        blade[9]  QARWDD^M^M
        blade[10]  QARWDN^M^M
        blade[11]  H2ABLE01^M^M
        blade[12]  H2ABLE02^M^M
        blade[13]  H2ABLE03^M^M
        blade[14]  H2ABLE04^M^M
        blower[1]^M^M
        blower[2]^M^M
        power[1]^M^M
        power[2]^M^M
        power[3]^M^M
        power[4]^M^M
        mm[1]     primary^M^M
        switch[1]^M^M
        switch[2]^M^M
        mt[1]^M^M

I need to get the blade info and put it into mysql. I need the output for all blade lines to be in the folloring format:

Code:
 
insert into blades (chassis,blade,hostname) values ('17da','1','H27995BLD')

The chassis name is the name of the file, and also in the spawn line. Currently, I'm just getting the filename and using that for anything I'm trying.


Thanks in advance for any help.
# 2  
Old 08-26-2011
parse the output and then use MySQL :: MySQL 5.1 Reference Manual :: 12.2.6 LOAD DATA INFILE Syntax
# 3  
Old 08-26-2011
Quote:
Originally Posted by hamanjam
...My file is a dump from an expect script:
---filename 17da------
Code:
spawn ssh Sys@17da list -l 2 ^M
password:^M
^M^M
system> list -l 2 ^M^M
system^M^M
        blade[1]  H27995BLD^M^M
        blade[2]  H27995TST^M^M
        blade[3]  H2LABBUSMED01^M^M
        blade[4]  H2LABBUSMED02^M^M
        blade[5]  QARWDK^M^M
        blade[6]  QARWFK^M^M
        blade[7]  QARWFW^M^M
        blade[8]  QARWDP^M^M
        blade[9]  QARWDD^M^M
        blade[10]  QARWDN^M^M
        blade[11]  H2ABLE01^M^M
        blade[12]  H2ABLE02^M^M
        blade[13]  H2ABLE03^M^M
        blade[14]  H2ABLE04^M^M
        blower[1]^M^M
        blower[2]^M^M
        power[1]^M^M
        power[2]^M^M
        power[3]^M^M
        power[4]^M^M
        mm[1]     primary^M^M
        switch[1]^M^M
        switch[2]^M^M
        mt[1]^M^M

I need to get the blade info and put it into mysql. I need the output for all blade lines to be in the folloring format:

Code:
 
insert into blades (chassis,blade,hostname) values ('17da','1','H27995BLD')

The chassis name is the name of the file, and also in the spawn line.
...
Not sure why you have so many "^M" characters over there.
Are they present in your text file "17da", or did they show up when you copied your text from your file to your posted question ?

In any case, you may want to fix your Expect script or ftp process so that the "^M" characters are absent from your text file. (Try "dos2unix" or its equivalent command on your system to fix those characters.)

So if your text file looks like the following:

Code:
$
$ cat 17da
spawn ssh Sys@17da list -l 2
password:

system> list -l 2
system
        blade[1]  H27995BLD
        blade[2]  H27995TST
        blade[3]  H2LABBUSMED01
        blade[4]  H2LABBUSMED02
        blade[5]  QARWDK
        blade[6]  QARWFK
        blade[7]  QARWFW
        blade[8]  QARWDP
        blade[9]  QARWDD
        blade[10]  QARWDN
        blade[11]  H2ABLE01
        blade[12]  H2ABLE02
        blade[13]  H2ABLE03
        blade[14]  H2ABLE04
        blower[1]
        blower[2]
        power[1]
        power[2]
        power[3]
        power[4]
        mm[1]     primary
        switch[1]
        switch[2]
        mt[1]
$
$

then you could run a Perl one-liner to generate the INSERT statements, like this:

Code:
$
$ perl -lne "if (/^spawn.*?\@(\w+).*$/) {\$chassis = \$1}
             elsif (/^\s+blade\[(\d+)\]\s+(.*)$/) {print \"insert into blades (chassis,blade,hostname) values ('\$chassis', '\$1', '\$2');\"}
            " 17da
insert into blades (chassis,blade,hostname) values ('17da', '1', 'H27995BLD');
insert into blades (chassis,blade,hostname) values ('17da', '2', 'H27995TST');
insert into blades (chassis,blade,hostname) values ('17da', '3', 'H2LABBUSMED01');
insert into blades (chassis,blade,hostname) values ('17da', '4', 'H2LABBUSMED02');
insert into blades (chassis,blade,hostname) values ('17da', '5', 'QARWDK');
insert into blades (chassis,blade,hostname) values ('17da', '6', 'QARWFK');
insert into blades (chassis,blade,hostname) values ('17da', '7', 'QARWFW');
insert into blades (chassis,blade,hostname) values ('17da', '8', 'QARWDP');
insert into blades (chassis,blade,hostname) values ('17da', '9', 'QARWDD');
insert into blades (chassis,blade,hostname) values ('17da', '10', 'QARWDN');
insert into blades (chassis,blade,hostname) values ('17da', '11', 'H2ABLE01');
insert into blades (chassis,blade,hostname) values ('17da', '12', 'H2ABLE02');
insert into blades (chassis,blade,hostname) values ('17da', '13', 'H2ABLE03');
insert into blades (chassis,blade,hostname) values ('17da', '14', 'H2ABLE04');
$
$

tyler_durden
# 4  
Old 08-29-2011
The ^M are in the file, but I think it has to do something with how expect outputs the data. I just run it through dos2unix and it's clean.

Thanks so much fo rthe perl, I've been trying many different ways to parse, but it kept ending up with 4 different parsing operations on each line.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk script to parse SQL from Pro*C program

Hello, I have a Pro*C program which contains SQL sentences, such as: .... Pro*C sentences .... /*SQL 1*/ EXEC SQL SELECT t1.field1, t1.field2 INTO :w_field FROM TABLE1 t1, TABLE1 t2 WHERE t1.field1 = t2.field1 AND t1.ID = :wl_id; .... Pro*C sentences .... /*SQL 1*/ EXEC... (11 Replies)
Discussion started by: mvalonso
11 Replies

2. Shell Programming and Scripting

Parse SQL text and only format first SELECT statement.

Hi Forum. Need your expertise on the following question. I have the following file which I would like to parse, find first block of SELECT statment and concatenate all input fields as 1 field (~ delimited): Old File: SELECT /*+ USE_HASH(CCOMM ICAR IMAP IAS IP IMAS IMPS IAP SPCA) */ ... (5 Replies)
Discussion started by: pchang
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

Parse and Join in a text file

I wanted to parse a text file and join in specific format. please suggest me how to get this done.. The output should be in fasta format which consists of lines starting with ID, PT, PA and Sequence. "//" the two slashes are dividing lines between two different sequences. Like... (10 Replies)
Discussion started by: empyrean
10 Replies

5. Shell Programming and Scripting

How to parse a file for text b/n double quotes?

Hi guys, I desperately need some help here... I need to parse a file similar to this: I need to read the values for MY_BANNER_SSHD and WARNING_MESSAGE. The value could be empty/single line or multi-line! # Comments . . . Some lines MY_BANNER_SSHD=""... (7 Replies)
Discussion started by: shreeda
7 Replies

6. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

7. UNIX for Dummies Questions & Answers

parse through one text file and output many

Hi, everyone The input file pattern is like below: Begin Object1 txt1 end ; Begin Object2 txt2 end ; ... (14 Replies)
Discussion started by: sophiadun
14 Replies

8. Shell Programming and Scripting

parse text file

I have a file that has a header followed by 8 columns of data. I want to toss out the header, and then write the data to another file with a different header and footer. I also need to grab the first values of the first and second column to put in the header. How do I chop off the header? ... (9 Replies)
Discussion started by: craggm
9 Replies

9. Shell Programming and Scripting

parse text file

i am attempting to parse a simple text file with multiple lines and four fields in each line, formatted as such: 12/10/2006 12:34:06 77 38 this is what i'm having problems with in my bash script: sed '1,6d' $RAWDATA > $NEWFILE #removes first 6 lines from file, which are... (3 Replies)
Discussion started by: klick81
3 Replies

10. Shell Programming and Scripting

how to sql commands in shell script

hi, plz let me know one example in using sql command in shell script thanks inadvance -bali. (1 Reply)
Discussion started by: balireddy_77
1 Replies
Login or Register to Ask a Question