sqlldr - include infile filename as data for field in db 1

[landolakes]

I am attempting to load many files using multiple INFILE parameters in a single SQL*Loader control file. I want to include in one of the data fields, the name of the current .dat file being processed (see "DOC_NAME" below). Where '090005.DAT' is hard-coded on that line, the current "INFILE" filename text should be inserted. I can't seem to find in any documentation how I can do this. Any thoughts?


sample.ctl:

LOAD DATA 
INFILE '090005.DAT' "fix 13"
INFILE '090014A.DAT' "fix 13"
INFILE '090026.DAT' "fix 13"
INFILE '090057W.DAT' "fix 13"
INFILE 'ZW90062.DAT' "fix 13"
INFILE 'ZW90072.DAT' "fix 13"

APPEND
INTO TABLE "DOC_TEXT"

  (CODE POSITION(1:1) CHAR
, 
   TEXT POSITION(2:13) CHAR
,
   "QID" "QID_SEQ.NEXTVAL"
, 
   "TYPE" CONSTANT "A"
, 
   "PROCESS_DATE" SYSDATE
, 
   "POST_DATE"
, 
   "DOC_NAME" char(15) "SUBSTR('090005.DAT', 1, length('090005.DAT')-4)"

)

sample data:
090005.DAT
----------
3ABCDEFGHIJKL
2ABC123ABC
3DEF456DEF2


090014A.DAT
-----------
134SDF45D
2AAAAAAAAAAAA
3BBBBBBBBBBBB


090026.DAT
----------
3ZZZZZ
2XYXYXYXY
1LKJHIGFEDCBA

 

[Dagon]

Among the following options, there should a solution:

a) put the input file on the command line instead of the text e.g. sqlldr datafile=yyyy. You could then control the name of the file from the script calling sqlldr.

b) assuming your OS is UNIX, use a shell variable. The following seems to work on Solaris:

export MYFILE=090005.DAT
...
INFILE "$MYFILE"
...

c) generate the control file i.e. get the shell script to write it out at run time. That way you can insert whatever you want for the infile parameters.

 

[landolakes]

I am using Solaris, I am generating the list of 'infile' lines using 'find', then running those results through sed to format it as a valid infile line, then concating that with a pre-built 'append into table...' section to create a single ctl similar to my example file with all infile names available at the time of the run, then I execute that sqlldr ctl file. I have also tried putting the names on command line, and have tried things with parameter files instead. None of those solutions seem to allow me to put the actual name of the file being processed as text input into a db field.

In first 2 examples, the data after load would be something like:

code    text            qid  type  process_date  post_date  doc_name
3       ABCDEFGHIJKL    1     A    10-oct-06     NULL       090005
2       ABC123ABC       2     A    10-oct-06     NULL       090005
3       3DEF456DEF2     3     A    10-oct-06     NULL       090005
1       34SDF45D        4     A    10-oct-06     NULL       090014A
2       AAAAAAAAAAAA    5     A    10-oct-06     NULL       090014A
3       BBBBBBBBBBBB    6     A    10-oct-06     NULL       090014A

I can't seem to get the data for "doc_name" to be dynamically assigned based upon the name of the current file being processed.

 

[Dagon]

Just write out the .ctl file for each input file, substituting the physical file name in the two places (the infile and the doc_name), then just call the thing iteratively in a loop.

 

[Dagon]

Here's an example script:

set -A FILENAME doc.dat doc1.dat doc2.dat

i=0
while [ ! -z ${FILENAME[$i]} ]
do
echo ${FILENAME[$i]}
cat << !! > load_doc.ctl
LOAD DATA
INFILE ${FILENAME[$i]}

APPEND
INTO TABLE "DOC_TEXT"
(CODE POSITION(1:1) CHAR,
TEXT POSITION(2:13) CHAR,
"TYPE" CONSTANT "A",
"PROCESS_DATE" SYSDATE,
"POST_DATE",
"DOC_NAME" char(15) "SUBSTR('${FILENAME[$i]}', 1, length('${FILENAME[$i]}')-4)"
)
!!

sqlldr userid=xxx/xxx@xxxx control=load_doc.ctl
i=`expr $i + 1`
done

 

[landolakes]

I'll play around with that idea a bit and let you know how it goes. I've got approx. 33,000 separate .dat files, so as long as I'm creating a ctl file only long enough for the loop to process it, and then deleting it, I think this could work.

 

[landolakes]

I used your advise and code sample, and the load works like a charm! Thank-you very much. I made a slight modification so I have included that here. This script was able to load about 33,000 individual files.

# load_doc_text.sh
for i in `cat srcpath_sample.dat`
do
cat << !! > load_doc.ctl
LOAD DATA
INFILE '$i' "fix 13"

APPEND
INTO TABLE "DOC_TEXT"

  (CODE POSITION(1:1) CHAR ,
   LABEL POSITION(2:13) CHAR ,
   "QID" "QID_SEQ.NEXTVAL" ,
   "TYPE" CONSTANT "A" ,
   "PROCESS_DATE" SYSDATE ,
   "POST_DATE" ,
   "DOC_NAME" char(15) "SUBSTR('$i',INSTR('$i', '/', -1)+1, LENGTH(SUBSTR('$i',INSTR('$i', '/', -1)+1))-4)"
  )
!!

echo $i
sqlldr userid=user/password control=load_doc.ctl silent=(header,feedback)
done

The source file was simply a "find" done in UNIX to locate all of the files I needed to load.

 srcpath_sample.dat:

./090005.DAT
./090014.DAT
./090026.DAT
./090057.DAT
./090062.DAT
./090071.DAT
./090074A.DAT
./090075C.DAT
./090076A.DAT
./090084A.DAT
./090085A.DAT
./090088.DAT
./090093.DAT
./090094.DAT
./090099.DAT

One thing to note, my "DOC_NAME" sql*loader line can very easily become too long. I didn't have time to fix that yet. It seems an additional vbl in the script containing just the last 15 or so chars of the input would do the trick.