sql loader

Can you post some sample data?

I haven't done it in a while, but if both sets of records are in the same file, I believe there needs to be some sort of identifier to get them to load to separate tables.  With some sample data, someone can see if they can get what  you have working.

ok let me send you the actual table structure with the actual data,
PaymentDetails.CSV
CreateCommAdjustmentTables.SQL

Not sure what the PaymentDetails.CSV file is, but it is not a csv file.

I agree, the *.CSV file that you uploaded is certainly not a plain, comma-separated ASCII text file that the ".CSV" extension usually indicates.  Where did this file come from?  Or, which application produced it?  Can you open that in Excel or a different application on your computer, then save it as a comma-separated, ASCII text file?

Your data and table create statement do not seem to match with anything like what you presented in your original question. It appeared like you wanted to load two different ables with employee information, but the data looks like it contains some kind of sales and broker information and there is only one table. Perhaps you can give us a more clear description of what it is you want to accomplish. Also, using some sample data and what you expect the table(s) to contain would be helpful.

BTW, the file can be opened in Excel and saved as a *.csv file.

You are correct, that is actually an Excel file that someone apparently renamed to have a "CSV" extension, but it is not in comma-separated ASCII format, so Oracle's SQL*Loader can't process it as is.

I'm guessing that the original question just described the problem in general terms, but when we asked for specifics, we now have the actual tables and data file.

What we don't have though is a mapping of which columns in the data file need to map to which columns in the tables.

It may be possible in this case to set up a controlfile for SQL*Loader to populate two tables based on this data file.  But, what I usually find is a simpler approach to this business problem is like this:
1. Create a work table with as many columns as the data file, with all varchar2 columns and with column names similar to the column names in the Excel file.
2. Create a simple SQL*LOader control file to quickly load the entire data file into this work table.
3. Use SQL and/or PL/SQL commands to populate the actual target tables in the database based on these work table records.

Sorry, I missed the fact there were two create statements with the text all crammed together. That aside, I agree with markgeer that we need mappings from the datafile columns to the table columns, especially since there are 19 columns in one table and 22 in the other, but only 27 columns of data in the data file. I think I figured out about 7 of them but that's all.

I second Mark's approach of using a staging table but wanted to suggest a slight deviation:
If the file can be placed on the database server itself, use external tables instead.

Then if this is an ongoing process, all you need to do is replace the file on disk and run the load script.  It skips the loading of the staging table on subsequent runs.

yes, there are lot of discrepancy from the file i could see also . thanks for verifiying  above file

but suppose i want to  to sent  below csv data to tables which has a table structure x table (empno, empname, ) and Y table as ( empno, sal, deptno)  . let know how the control file should look with two table

-----------------------------------------------------------
empno, empname, sal,       deptno
1001,      albert,       1000,      10
2001,      sam,           2000,     30

If you wanted to do this with SQL*Loader without using an intermediate table and a procedure or SQL statements, then I don't think you can.

My understanding of using delimited files in SQL*Loader and loading into multiple tables from one logical record tells me that the empno field must be repeated.  

So, if your sample data looked like this:

empno, empname, empno, sal,       deptno
1001,      albert,       1001, 1000,      10
2001,      sam,           2001, 2000,     30

Then this would work as a control file:

load data
   infile 'load.csv'
   into table x
   fields terminated by "," optionally enclosed by '"'
   trailling nullcols
   ( empno,
     empname
   )
   into table y
   fields terminated by "," optionally enclosed by '"'
   trailling nullcols
   ( empno,
     sal,
     deptno
   )

Select allOpen in new window

If your file is fixed width rather than delimited, it can be done without having to duplicate the EMPNO field in the data.

Hi,

I tried running using sqlldr

load data
   infile 'd:\ws\Book1.csv'
   into table emp
   fields terminated by "," optionally enclosed by '"'
   TRAILING nullcols
   ( empno,
     ename,
     sal,
     deptno
   )
   into table emp1
   fields terminated by "," optionally enclosed by '"'
   TRAILING nullcols
   ( empno,
     ename
     
   )

Select allOpen in new window

it is getting record in the emp table not in 2nd table emp1 table what might be the reason ??

Please post the file you used.

The fields for the 2 tables must be in the file in the order that you put them in the control file.  So the fields in Book1.csv must be:

empno,  ename,  sal,  deptno,  empno,  ename

suppose i want to replace empno  null values column with 0 , using NVL function, but it gives me error what is the mistake am doing

SQL*Loader-350: Syntax error at line 6.
Expecting "," or ")", found "(".
   ( NVL(empno,0),
        

Select allOpen in new window

load data
   infile 'd:\ws\Book1.csv'
   into table emp
   fields terminated by "," optionally enclosed by '"'
   TRAILING nullcols
   ( NVL(empno,0),
     ename,
     sal,
     deptno
   )

Select allOpen in new window

Sorry, I read your previous post incorrectly.  My previous post would put NULL in EMPNO if the filed in the file is 0.

I believe this is what you want:

load data
   infile 'd:\ws\Book1.csv'
   into table emp
   fields terminated by "," optionally enclosed by '"'
   TRAILING nullcols
   ( empno "NVL(:empno,'0')",
     ename,
     sal,
     deptno
   )

Select allOpen in new window