Incrementing in a script

[rmueller58]

I have another flat file output result from Informix DB delimited with a pipe '|'


5995|0|1|JobApplicantCenterIssues.doc|5995_0_1.doc|
5996|2|1|test.doc|5996_2_1.doc|
177|0|2|Capacity Standards.doc|177_0_2.doc|
177|0|3|Capacity Standards.doc|177_0_3.doc|
177|0|1|Mentor Luncheon Conversations - Nov 07.doc|177_0_1.doc|
5996|0|1|Job Postings.doc|5996_0_1.doc|
177|0|4|Capacity Standards.doc|177_0_4.doc|
5997|0|1|test.txt|5997_0_1.txt|
177|0|5|AGENDA%20January%2019,%202009.pdf|177_0_5.pdf|
177|0|6|Empl_Verification_ssn.doc|177_0_6.doc|
177|0|7|Email Payroll Vouchers Custom.doc|177_0_7.doc|

COLUMN1 = appID
COLUMN2 = IterID (ALWAYS '0' Zero)
COLUMN3 = docID (may have multiples per appID)
COLUMN4 = Descriptive File Name(string)
COLUMN5 = stored File name

Column 5 = appID_IterID_docID.ext

The School is staging documents in a "staging area" that will be in the following Format:
EXAMPLE: Transcript1__0_.ext
EXAMPLE: LOR1__0_.ext
EXAMPLE: LOR2__0_.ext
EXAMPLE: Review2009_< appid>_0_.ext

For each document loaded to the staging location I need to check the docID in the SQL output and increment the docID beginning at 6 up to 100 so each record re-inserted will be unique.

I am looking for a way to define a counter for this and create an entry into a flat file like the one above, and copy the file and rename with the incremented docID.

I am using the "original file name" to base the "storename"

I am using an awk -F_ '{print $#}' to define each piece of the file name as a variable..

I wanted to pick brains again on this incrementing.

Any ideas appreciated

[OldSchool]

I'm afraid I'm not following how you want to transform this...ie: i don't see any relation from whats in the flat file and what in the EXAMPLE columns (they appear to be disjoint, at least to me).

Might help if you posted the awk you've got to date as well....

[rmueller58]

OLDSCHOOL,
Sorry for the confusion..

The table contains information about Documents..
||||

The unique identifier is docID.

I need to get information from the table for each and beginning at number 6 increment the by +1

[rmueller58]

There will likely be multiples for each appID, the only uniq identifier for each is the docID.
From the process I need to begin incrementing at number 6, if number 6 is taken, on to number 7 and so on.

[OldSchool]

Given:
EXAMPLE: Transcript1__0_.ext
EXAMPLE: LOR1__0_.ext
EXAMPLE: LOR2__0_.ext
EXAMPLE: Review2009_< appid>_0_.ext


Where in the flat file illustrated do you find LOR1 TRANSCRIPT...et all....I still see no connection between the data illustrated and the examples given

[rmueller58]

5995|0|1|JobApplicantCenterIssues_5995_0_1.doc|5995_0_1.doc|
5996|2|1|test_5996_2_1.doc|5996_2_1.doc|
177|0|2|Capacity Standards_177_0_2.doc|177_0_2.doc|
177|0|3|Capacity Standards_177_0_3.doc|177_0_3.doc|
177|0|1|Mentor Luncheon Conversations - Nov 07_|177_0_1.doc|
5996|0|1|Job Postings_5996_0_1.doc|5996_0_1.doc|
177|0|4|Capacity Standards_177_0_4.doc|177_0_4.doc|

[rmueller58]

OldSchool,

The String for "Descriptive Name" may vary, this is something that will fill a database field.

[OldSchool]

The String for "Descriptive Name" may vary, this is something that will fill a database field.

right..i got that...i got all the other stuff...but note of the EXAMPLES correspond to the DATA???? I know YOU know what/where DESCRIPTIVE NAME comes from....but I don't see it

[rmueller58]

IN the file name:

docID for 177 is 2 and 3 so far, there are two sources for this, slots 1-5 are loaded by a web client but 6-99 will be staged by the client through a scanning process.


177|0|2|Capacity Standards_177_0_2.doc|177_0_2.doc
177|0|3|Capacity Standards_177_0_3.doc|177_0_3.doc

SO for any new documents scanned for 177 I would need to increment record as follows:
177|0|6|Capacity Standards_177_0_6.doc|177_0_6.doc

for NEXT
177|0|7|Capacity Standards_177_0_7.doc|177_0_7.doc

Hope this makes more sense.

[rmueller58]

the "DESCRIPTIVE NAME" the original file name..

The application stores the document as "storename" so it strips off the front string. God only knows why.. That is out of my control.

I need to just take what they give me increment, but a flat file to load back into informix.

[rmueller58]

My 1st example file was a POS from their test data, sorry about that..

[OldSchool]

so, within an appid, you need to take the Highest doc#, 6 being the MINIMUM, and increment it up from there?

I assume you want to add the new lines to the flat file, but what are you using to feed this as input. do you run it once for every newly found document or what?

[rmueller58]

Oldschool,

6 being the lowest value yes.
an easier thing would be to create a new load file, since the other records already exist there is no reason to load them a second time or dump them from the table.

SO in the example for appID 177
then next record into the flat load file would look like
177|0|7|Capacity Standards_177_0_7.doc|177_0_7.doc

[rmueller58]

if there are multiples,

177|0|7|Capacity Standards_177_0_7.doc|177_0_7.doc
177|0|8|Capacity Standards_177_0_8.doc|177_0_8.doc
177|0|9|Capacity Standards_177_0_9.doc|177_0_9.doc

and so on..

I check the value in the database on each run prior to creating a new load file.

This is a hack of a document management system.. This thing has had a real bad case of scope creep since it's inception 5 years ago.. I've been bandaidding things together and am revisiting the original and re-doing it and trying to do it right from the outset of the vendors new web application that can't handle scanned documents.

[OldSchool · Accepted solution]

ok, but again, what are you using for input?? You've got the existing flat file, but how are you telling the awk script to you need to add X number of docs to application Y???

[rmueller58]

Old School,
I need to rethink how I ask this question sorry.

the previous version of this package simply moved files from one location to the next, it had no database operation to index the documents.

Simply said, job applicant files are scanned, named and placed in
for each files are moved from staging to APPLICANT/ folder. via existing scripting

My script initially looks at the files in the STAGING with this script section
rm -f flatfile
for file in `find /upload_resumes/mpsplustest/ -name "*.*" -print |awk '{print substr($1,29,60)}'`

do
field1=`echo $file | cut -d"_" -f1`
field2=`echo $file | cut -d"_" -f2`
field3=`echo $file | cut -d"_" -f3 |cut -d"." -f1`
ext=`echo $file|cut -d"." -f2`
newfile=JAC2_${field1}_0_${field3}.${ext}
storename=${field1}_0_${field3}.${ext}
#echo $newfile
echo $field1"|"0"|"$field3"|"${newfile}"|"${storename}>>flatfile

done
more flatfile


the Second part builds an SQL template, executes a query, returns the value in a
"|" delimited file.

#STAGEDFILES=/mps-docs/mps-docs/mpsplustest/staging/
STAGEDFILES=/mps-docs/mps-docs/mpsplustest/applicant/
cd $STAGEDFILES
rm -f /tmp/jac.txt
touch /tmp/jac.txt

##### LOCAL FILE NAME EXTRACT #####
for fn1 in `find -type f -mmin -360000 -print`
do
export fn=`echo $fn1| awk '{print substr($1,3,22)}'`
for appid in `echo $fn |awk -F_ '{print $2}'`
do
export distID=`echo $fn |awk -F_ '{print $1}'`
export appID=`echo $fn |awk -F_ '{print $2}'`
export docID=`echo $fn |awk -F_ '{print $3}'|cut -d. -f1`
export ext=`echo $fn |awk -F_ '{print $3}'|cut -d. -f2`

export ITERID=`echo 0`

echo $distID >> /tmp/jac.txt
done
done
##### END LOCAL FILE NAME EXTRACT #####
COUNT=`wc -l /tmp/jac.txt`
echo $COUNT
##### BEGIN TEMPLATE BUILD #####
sort /tmp/jac.txt |uniq > /tmp/jac1.txt
echo 'unload to /tmp/jacload.txt' > /tmp/TEMPLATE
echo 'select * from jac_attachment where id in ('$(sed -e "s/^\\|\$/'/g" /tmp/jac1.txt | paste -d, -s)');' >> /tmp/TEMPLATE
more /tmp/TEMPLATE
cd
##### END TEMPLATE BUILD #####

##### BEGIN INITIAL SQL EXECUTION #####
sudo -u USER scp -B /tmp/TEMPLATE user@IP:/tmp/jac.sql
sudo -u USER ssh user@IP'/usr/local/bin/jacload.sh'
sudo -u USER scp -B connect@10.3.253.6:/tmp/jacunload.txt /tmp/

What I need to do, is take the items in SECTION 1 FLATFILE compare against section two
and increment the in a flat file to return to the Informix DB and Insert with the incremented values, as the third part of this script I will copy and move files from the staged location to the permanent storage area(s).

I hope this helps..

Comparing the two flat files is what needs to occur.
Also for items inserted the records need to begin as #6 and increase.

[OldSchool]

I'm afraid I really don't understand what you are driving at....

You've got some files (thus file names) and those have the appid and docid already in them? or you need to assign names to the files incrementing the docid within the appid?

can you explain one step at a time, what you've got, where, and what needs to be checked / generated. We've got a real disconnect here, and I think part of the problem is that you're so close to the problem that you're overlooking details in the explaination (as they're obvious to you).

I've got a feeling that this won't take long to sort out once understood.

and what, if any, relationship is there to the names of the files and the data in the example flat file?

sorry if this seems pigheaded on my part

[rmueller58]

old School, Thanks for the try, the worst of this little buggar child is I don't understand my self half the time.. I am a systems guy not a programmer. But the expectations of my director and districts is I did it before I can again "raise the dead" and "walk on water"

I can scab together some rudimentary scripting, I need to take a step back close this thread and rethink a few things so when I have questions I can be coherent about it all. I apologize.

When I get to a point I can do this I will re-ask a more coherent question about a particular section of this cluster_flop.

Thanks again.. I and closing this for now and will come back later when I have is more in a group.. Sorry.

[OldSchool]

skip my previous post...i was typing while you were posting.


so... you're parsing values out of an existing filename and you have no control over the original filename? if so, does not that mean that

177|0|7|Capacity Standards_177_0_7.doc|177_0_7.doc
177|0|8|Capacity Standards_177_0_8.doc|177_0_8.doc
177|0|9|Capacity Standards_177_0_9.doc|177_0_9.doc

could also be something like:
177|0|7|Capacity Standards_177_0_5.doc|177_0_7.doc
177|0|8|Capacity Standards_177_0_9.doc|177_0_8.doc
177|0|9|Capacity Standards_177_0_11.doc|177_0_9.doc

i still don't see what, if any relationship exists between the data existing in the flat file, the data to be added to the flat file, and the name of the file being examined.

[rmueller58]

Old School,

the Control I have over the Old File name is behavioral. I it will be a "practice" to name the file as
_.

I need to compare files in staging against the contents of the table, if an entry in the table exist, increment the value of

If you look at each Flat file it contains the values
|0||FullFilename__0_.|_0_.

I need to build an insert record that looks as the sql output and if a file exist in staging to increment it..

The comparison is between what physical files are being staged with a consistent naming convention against the value in the table.

File A - Staged files
File B - SQL output

[OldSchool]

ok, i'm out of steam here, too many variables, templates, disjoint examples to follow (at least for me)

for example, I think there are files with real , assigned names, and they aren't:
_.

[rmueller58]

Me too oldschool. it too much energy to deal with on a Friday.. I have a few I ideas.. There are way too many pieces in this hack job.. the web application does nothing to serve staged items and for intent this is a document management system for job applications to hiring. It is bigger then me and it grows each day like a virus.

Sorry to bother about it. I will isolate my question when I get to that point ..

[rmueller58]

Old School, If you are still monitoring wanted to give you a heads up I spent the weekend working on this. I got past the worst of it.

I am taking one record at a time, that is what was killing me.

I defined a limiter in my SQL select statement for max(attachno) and was able to determine the value to increment from.

Not done by over a very large hump.