SQL Loader uploading hundreds of files .

From: nullpointer <null_pointer_at_rediffmail.com>
Date: 18 Apr 2003 02:30:11 -0700
Message-ID: <c0728f9d.0304180130.7d8f901d_at_posting.google.com>

[Quoted] Hi Tom,

        I have to load an oracle table with lots of employee data. The problem is that this data comes to me in several CSV (Comma separated values) files. Now Each filename is an identifier telling me the category of the employees (Billable, Associate, Float ... and many more), the location (M, C , T) and the dept they belongs to (Sales, Marketing, Finance).

        So a file name is typically like :

        BILLABLE_M_S.csv = BILLABLES for MUMBAI location and SALES dept

	I get around 324 (36 categories, 3 locations and 3 departments). 
	These files differ in the number of fields that they capture. Some

capture 15 fields while others may capture only 10. But the files for each category are in the same format.

        In My table I have 4 columns more than the files these are CATEGORY, LOCATION, DEPARTMENT, AS_ON_DATE. Now while loading these files I have to load the appropriate values in the table depending on the filename. Is this possible in SQL loader ? I can load the SYSDATE easily but how do I go around identifying the file and loading the appropriate values ? Or do I have to write 324 control files which I really want to avoid. Can it be done using 36 one for each category control files which is still manageable.         

        As far as I could figure out from the documentation there is no variable in the control file syntax which can be passed from the command line. Neither is there a variable for the filesname which I can manipulate.         

        Any help will be appreciated.         

        Thanks Dev Received on Fri Apr 18 2003 - 11:30:11 CEST