Solved

sql loader

Posted on 2014-09-05
17
297 Views
Last Modified: 2014-09-14
Hi,

I have a master detail two tables called as Master and detail relation   ( x and y), I am using sql loader

x (empno, empname, ) and Y as ( empno, sal, deptno)    and a data into a csv file. I need some assistance in writing the  ctl file , i  saw an example but with  one table  how to write a ctl with two table with a master detail relation
 need assistance on this

load data
 infile 'c:\data\mydata.csv'
 into table x
 fields terminated by "," optionally enclosed by '"'              
 ( empno, empname)
0
Comment
Question by:roy_sanu
  • 6
  • 4
  • 3
  • +2
17 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 40305585
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.
0
 

Author Comment

by:roy_sanu
ID: 40305649
ok let me send you the actual table structure with the actual data,
PaymentDetails.CSV
CreateCommAdjustmentTables.SQL
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40305675
Not sure what the PaymentDetails.CSV file is, but it is not a csv file.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40305758
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?
0
 
LVL 31

Expert Comment

by:awking00
ID: 40305761
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.
0
 
LVL 31

Expert Comment

by:awking00
ID: 40305767
BTW, the file can be opened in Excel and saved as a *.csv file.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40306024
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40306040
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Expert Comment

by:awking00
ID: 40306152
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40306367
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.
0
 

Author Comment

by:roy_sanu
ID: 40306660
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
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40306738
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
   )

Open 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.
0
 

Author Comment

by:roy_sanu
ID: 40307773
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
     
   )

Open in new window


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

Expert Comment

by:johnsone
ID: 40307823
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
0
 

Author Comment

by:roy_sanu
ID: 40308609
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),
        

Open 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
   )

Open in new window

0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 40308678
You would use:

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

Open in new window

0
 
LVL 34

Expert Comment

by:johnsone
ID: 40310088
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
   )

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now