I have a pipe delimited file I need to add a sequence number to in the third field. The record fields will be variable length, so I have to parse for the second pipe. Another requirement is that the sequence number must be unique to all records in the file and subsequent files created, so the sequence numbers will be maintained by an Oracle sequence.

I'm thinking basically select nextval into a variable and insert it, but I'm not that certain on the best approach to doing so with a ksh. Any ideas on how to insert this sequence in a ksh? I'm not very familiar with sed/awk.

TIA

before:

rec1field1sdss|rec1field2||rec1field4|rec1field5
rec2field1ff|rec2field2sds||rec2field4sdsds|rec2field5
rec3field1qwe|rec3field2wsxd||rec3field4sds|rec3field5

after:

rec1field1sdss|rec1field2|1|rec1field4|rec1field5
rec2field1ff|rec2field2sds|2|rec2field4sdsds|rec2field5
rec3field1qwe|rec3field2wsxd|3|rec3field4sds|rec3field5

Do you only want to use ksh? Try doing it like this:

i=1
while read line; do
echo ${line%\|\|*}\|$i\|${line#*\|\|}
i=$(($i+1))
done < filename

Using awk :

awk -v FS='|' -v OFS='|' '{$3=NR ; print}' filename

Thanks for the help. I would love to use the ksh only method, but I have to maintain the sequence number across multiple files.

ex:

file1:

field1|field2|1|field3...
field1|field2|2|field3...


file2, which will be generated on the following day:

field1|field2|3|field3...
field1|field2|4|field3...

These sequence numbers must remain unique to the records across files so we can keep track of them. They will be sent to a vendor and they will send them back at a later time. The boss wants to use an Oracle sequence to maintain uniqueness across all records sent out. Any ideas?


TIA

If you must use oracle sequence to put sequence number in file then You may need to use UTL_FILE package of oracle.
Other method is create some temp table in oracle load these files into that table with sql loader utility with third field as oracle sequence.Now select all records from that table concatenated with | and spool that file.

Not enough time to read up on UTL_FILE, I will most likely have to use the second option.

Thanks for all your help, you have saved me a bunch of time spinning my wheels...