Re: Writing shell script to add/update/remove data from flat file
>I wrote a shell script with help from this list to add data from ACII file.
>This works like a charm. I used the ISQL, LOAD, and INSERT command to do this.
>My question is that how would I update or remove the data using the flat file?
>In ascii file, first character designates whether I want to remove or update.
>
>Please help me if this is possible using shell script and ISQL comands.
>Some suggested that I could write C and do this using 4gl. But the problem
>is that I don't know C but if you have sample example, I would really
>appreciate. I need to do this right away.
You don't need to write any C code for this. It can be done entirely from
ISQL.
Given the following flat file:
----- Begin zip.dat -----
A00000191920La Mesa CA
U00000291945Lemon Grove CA
D00000333433Some Whereville AZ
A00000492104San Diego CA
A00000592101San Diego CA
----- End zip.dat -----
Oh yes, the record layout of the above file is:
Action ([A]dd, [U]pdate, [D]elete) 1-1
Key 2-7
5 digit zipcode 8-12
City 13-31
State 32-33
Also, ISQL needs to have a '|' at the end of each field in a load file, so
we need to run the following sed command:
sed -e 's/$/|/' <zip.dat >delimzip.dat
The following script will take the approptiate actions (based on what is
in the action field):
----- Begin loadzip.sql -----
create temp table tzip (
tdata char(40)
);
load from "delimzip.dat"
insert into tzip;
delete from zipcodes
where key
in (select tdata[2,7] from tzip where tdata[1] = "D");
update zipcodes
set (zip, city, state)
= ((select tdata[8,12], tdata[13,31], tdata[32,33]
from tzip
where tdata[2,7] = key))
where key
in (select tdata[2,7] from tzip where tdata[1] = "U");
insert into zipcodes(key, city, state, zip)
select tdata[2,7], tdata[13,31], tdata[32,33], tdata[8,12]
from tzip
where tdata[1] = "A";
----- End loadzip.sql -----
Hope this helps,
jeffl
----
Jeffrey F. Lawhorn Information Systems Group
Programming Manager Unix, C, and Database Consulting
jeffl@isg.com 450 B Street 16th Floor
sdsu!isg100!jeffl San Diego, CA 92101 (619) 234-3405 x274