Solved

Linux RedHat,  Inserting lines into file with a list from other file.

Posted on 2014-11-21
9
184 Views
Last Modified: 2014-11-25
Hi experts, I need to do an Insert of many lines into a file called test.ctl, before APPEND line:
LOAD DATA
----> here should be new lines <-----
APPEND
INTO TABLE "INT_OPERATIONS"
FIELDS TERMINATED BY '!'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
SCADA_ID CHAR,
SCADA_SISTEMA CHAR,
SCADA_OPERATION_CLASS CHAR,
SCADA_OPERATION_SUBCLASS CHAR,
SCADA_OPERATION_DATE ,
SCADA_DATE_OFFSET ,
SCADA_OPERATION_TIMESTAMP "TO_TIMESTAMP(RPAD(SUBSTR(:SCADA_OPERATION_TIMESTAMP,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_PROCESS_STATUS ,
CERTA_PROCESS_DATE "TO_TIMESTAMP(RPAD(SUBSTR(:CERTA_PROCESS_DATE,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_OPERATION_ID ,
CERTA_NOTES CHAR,
INT_ID
 )

Open in new window

But the lines must be obtained from a file called list.lst.
He has lines like:
Transfer_data.20141118194103
Transfer_data.20141118194132

Open in new window

concatenated with string INFILE. then it should look like this:
LOAD DATA
INFILE Transfer_data.20141118194103
INFILE Transfer_data.20141118194132  
APPEND
INTO TABLE "INT_OPERATIONS"
FIELDS TERMINATED BY '!'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
SCADA_ID CHAR,
SCADA_SISTEMA CHAR,
SCADA_OPERATION_CLASS CHAR,
SCADA_OPERATION_SUBCLASS CHAR,
SCADA_OPERATION_DATE ,
SCADA_DATE_OFFSET ,
SCADA_OPERATION_TIMESTAMP "TO_TIMESTAMP(RPAD(SUBSTR(:SCADA_OPERATION_TIMESTAMP,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_PROCESS_STATUS ,
CERTA_PROCESS_DATE "TO_TIMESTAMP(RPAD(SUBSTR(:CERTA_PROCESS_DATE,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_OPERATION_ID ,
CERTA_NOTES CHAR,
INT_ID
 )

Open in new window

I tried with a script ksh:
ls -1 Transfer_data* > lista.lst
line=$(cat test.ctl | grep -n 'APPEND' | grep -o '^[0-9]*')
line=$((line -1))
for f in `cat  lista.lst`
   do
       sed -i ${line}' i\INFILE "$f"' 'test.ctl'
   done

Open in new window

But the lines inserted are in any place, and with wrong sintaxis:
INFILE "$f"
INFILE "$f"
LOAD DATA
APPEND
INTO TABLE "INT_OPERATIONS"
FIELDS TERMINATED BY '!'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
etc...

Open in new window

Note ' INFILE "$f" 'on first and second place.

Can you help me find an alternative?
Thanks and regards
0
Comment
Question by:carlino70
  • 3
  • 3
  • 3
9 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40458317
Try this:

sed -i '/LOAD DATA/ r list.lst' test.ctl

This will add the lines in list.lst into test.ctl after the LOAD DATA line.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40458328
awk '/APPEND/ {l=$0; while ((getline < "list.lst") > 0) {print "INFILE",$0}; print l; next} {print}' test.ctl
0
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
ID: 40458358
Since awk cannot change files "in place" add ">testnew.ctl" to write the changed content to a new file:

awk '/APPEND/ {l=$0; while ((getline < "list.lst") > 0) {print "INFILE",$0}; print l; next} {print}' test.ctl > testnew.ctl

By the way, a few modifications applied (mostly " instead of ' and the subtraction removed) your script will work, too:

ls -1 Transfer_data* > lista.lst
line=$(grep -n 'APPEND' test.ctl | grep -o '^[0-9]*')
for f in $(cat  lista.lst)
   do
       sed  -i "${line} i\INFILE $f" test.ctl
   done
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:carlino70
ID: 40464468
woolmilkporc, your suggestion is ok, with a modification:
ls -1 Transfer_data* > lista.lst
line=$(cat test.ctl | grep -n 'APPEND' | grep -o '^[0-9]*')
line=$((line -0))                                                                   #here I do a change
     for f in $(cat  lista.lst)
        do
           sed  -i ${line}" i\INFILE $f" test.ctl
          # sqlldr epe/epe@xa21 control=test.ctl log=sqlldr.log
        done

Open in new window

But when test.ctl is filled, the order is distinct that lista.lst:
see lista.ctl
Transfer_data.20141118194103
Transfer_data.20141118194132

Open in new window

and see the order inside test.ctl
INFILE Transfer_data.20141118194132
INFILE Transfer_data.20141118194103

Open in new window

How I can reverse the order?
Thanks
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40464515
It's quite normal that the order gets reversed - we read the list file line by line and insert that respective line just above "APPEND", i.e we read top-down and insert bottom-up.

We can use the "tac" utiity (on Linux only) instead of "cat" to reverse the order of the lines as they're presented to "for".
Line #4 of the last version you posted should then read:

 for f in $(tac  lista.lst)

Another option: Reverse the order in lista.lst by instructing "ls" to display the newest file first: "ls -t1 ...".

Obviously you should use just one of the above suggestions at a time!

By the way, why would you want to subtract "0" from "line"? The variable $line should be numeric ab ovo, so no need for an arithmetic NOP.

And how about my "awk" solution? Here the file is inserted as is above "APPEND", so the order will be kept automatically. Is it because "awk" cannot change "in place"?

Finally, the "sqlldr" command should not appear inside the "for" loop, because test.ctl will not contain all "INFILE" lines from lista.lst before the last iteration has completed!
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40464541
The 'sed' suggestion I made does not reverse order and is much shorter as well ;) So why don't you use that?
0
 

Author Comment

by:carlino70
ID: 40464637
woolmilkporc,

I appreciate your attention and suggestions. Finally, use the option "ls -t1", and so far it works.

Also, I took the loop sentence "sqlldr ...." was my mistake.

Thanks again.
0
 

Author Comment

by:carlino70
ID: 40464642
Gerwin Jansen, your suggestion not insert lines in test.ctl

Thanks anyway
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40464667
>> Gerwin Jansen, your suggestion not insert lines in test.ctl
Sure it did, did you try my suggestion at all?

[gerwin@localhost ~]$ cat test.ctl
LOAD DATA
APPEND
INTO TABLE "INT_OPERATIONS"
FIELDS TERMINATED BY '!'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
SCADA_ID CHAR,
SCADA_SISTEMA CHAR,
SCADA_OPERATION_CLASS CHAR,
SCADA_OPERATION_SUBCLASS CHAR,
SCADA_OPERATION_DATE ,
SCADA_DATE_OFFSET ,
SCADA_OPERATION_TIMESTAMP "TO_TIMESTAMP(RPAD(SUBSTR(:SCADA_OPERATION_TIMESTAMP,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_PROCESS_STATUS ,
CERTA_PROCESS_DATE "TO_TIMESTAMP(RPAD(SUBSTR(:CERTA_PROCESS_DATE,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_OPERATION_ID ,
CERTA_NOTES CHAR,
INT_ID
 )
[gerwin@localhost ~]$ cat list.lst
Transfer_data.20141118194103
Transfer_data.20141118194132
[gerwin@localhost ~]$ sed -i '/LOAD DATA/ r list.lst' test.ctl
[gerwin@localhost ~]$ cat test.ctl
LOAD DATA
Transfer_data.20141118194103
Transfer_data.20141118194132
APPEND
INTO TABLE "INT_OPERATIONS"
FIELDS TERMINATED BY '!'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
SCADA_ID CHAR,
SCADA_SISTEMA CHAR,
SCADA_OPERATION_CLASS CHAR,
SCADA_OPERATION_SUBCLASS CHAR,
SCADA_OPERATION_DATE ,
SCADA_DATE_OFFSET ,
SCADA_OPERATION_TIMESTAMP "TO_TIMESTAMP(RPAD(SUBSTR(:SCADA_OPERATION_TIMESTAMP,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_PROCESS_STATUS ,
CERTA_PROCESS_DATE "TO_TIMESTAMP(RPAD(SUBSTR(:CERTA_PROCESS_DATE,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_OPERATION_ID ,
CERTA_NOTES CHAR,
INT_ID
 )
[gerwin@localhost ~]$

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Linux Mailserver setup & redundancy 2 75
CENTOS DHCP Server / PXE/TFTP 14 202
maybe no no httpd.conf 6 77
Fuzzy search functionality DB2 UDB 3 30
Daily system administration tasks often require administrators to connect remote systems. But allowing these remote systems to accept passwords makes these systems vulnerable to the risk of brute-force password guessing attacks. Furthermore there ar…
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question