-
I am trying to load the content of a datafile to a database table. The problem is that my datafile has more columns than the database table. Here is an example,
datafile in .csv format, the first row in the .csv file contains the column headers that describes the column.
-------------------------------------------------------
interest rate, loan number, current balance,coupon , date,
6.4, 1000, 34455.00, 9.3, 20100101
7.8, 1001, 34343.43, 8.9, 20100530
and suppose my database table 'LOAN' has only 4 columns
-------------------------------------------------------------------
{
loan_number,
interest_rate,
cur_bal,
date
}
From the above, you can see that I have to skip the first row, and also the fourth column "coupon" in the .csv file. Besides that, I will also need to make proper mapping between the datafile column and the database column since the order they appear in the datafile is different from .
ie,
"loan number" column in .csv TO 'loan_num' column in database
"current balance" column in .csv TO 'cur_bal' column in database, etc...
Does anyone know how to skip columns in the datafile? Any help will be greatly appreciated.
Thanks.
thg.
-
Since this is a .csv file, I believe you can open it in MS-Excel and then select the entire column you would delete, and then delete it.
-
that won't solve the problem because the .csv file is uploaded through the network by clients and has to load into the database immediately. Once the data is loaded into the database, the .csv file will be deleted.
No human interventions.
-
check the parameter SKIP in SQL*Loader control file reference (I guess, SKIP=1 will skip the first record, in your case the column title).
Also check the FILLER clause of SQL*Loader manual for skipping certain columns
-
If your are using unix o/s you can use the follwing method
1. cut -d"," -f2,1,3,5 csv_file > input_file
2. Using the input_file as the input file for loading in SQL*Loader you can specify skip 1 in the control file to load the data in the database.
