Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: script to parse text file into sql commands
Top Forums Shell Programming and Scripting script to parse text file into sql commands Post 302550737 by durden_tyler on Friday 26th of August 2011 10:54:28 PM
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
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
All times are GMT -4. The time now is 06:39 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy