I have 1617 columns in one of my CSV files. Oracle has a limit of 1000 columns per table, so I created 2 tables.
Table 1: 900 columns Table 2: 717 columns
I have only 1 CSV file and it is 7 GB big file. I need to use sql loader to load this file on my database.
I want a control file which can do the above for me i.e. use first 900 columns and load into table 1 and next 717 columns into table 2.
I don't think you can do this with one control file. You may have to use 2 control files. The first with columns 901 to 1617 set to FILLER which loads to table 1, the other with columns 1 to 900 set to FILLER which loads to table 2. Run sqlldr twice, once with each control file pointing to the same data file. Make sure you have the primary key column(s) on both in order to join them later.
Either that or some type of pre-processing with a wrapper program to split the file as needed.
Or load via a program that can read the file row by row and insert as necessary.
Whatever you do its going to be a maintenance nightmare, dealing with that many columns. It seems some proper data normalization is needed here but I have had to deal with vendor data that I could not change so I sympathize with you if that is the case and you are loading into a staging table for further processing.
See this post for a stored procedure that generates control files. With a little tweaking for your circumstances, it should help a lot: 46 Control Files for SQL Loader
