carlino70
asked on
Linux RedHat, Inserting lines into file with a list from other file.
Hi experts, I need to do an Insert of many lines into a file called test.ctl, before APPEND line:
He has lines like:
Can you help me find an alternative?
Thanks and regards
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
)
But the lines must be obtained from a file called list.lst. He has lines like:
Transfer_data.20141118194103
Transfer_data.20141118194132
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
)
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
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...
Note ' INFILE "$f" 'on first and second place.Can you help me find an alternative?
Thanks and regards
awk '/APPEND/ {l=$0; while ((getline < "list.lst") > 0) {print "INFILE",$0}; print l; next} {print}' test.ctl
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
woolmilkporc, your suggestion is ok, with a modification:
see lista.ctl
Thanks
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
But when test.ctl is filled, the order is distinct that lista.lst:see lista.ctl
Transfer_data.20141118194103
Transfer_data.20141118194132
and see the order inside test.ctlINFILE Transfer_data.20141118194132
INFILE Transfer_data.20141118194103
How I can reverse the order?Thanks
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!
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!
The 'sed' suggestion I made does not reverse order and is much shorter as well ;) So why don't you use that?
ASKER
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.
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.
ASKER
Gerwin Jansen, your suggestion not insert lines in test.ctl
Thanks anyway
Thanks anyway
>> Gerwin Jansen, your suggestion not insert lines in test.ctl
Sure it did, did you try my suggestion at all?
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 ~]$
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.