?
Solved

SQL Loader

Posted on 2013-01-30
7
Medium Priority
?
570 Views
Last Modified: 2013-05-28
Hi ,

I have an input file which contains the below columns namely ,
Name, DataofJoin,emailid, address
s1,2001-03-01 00:00:00.000, s1@yahoo.com chr(10) chr(13)s2@yahoo.com,s1chr(10)3rdstreet
s2,2002-05-02 00:10:00.000,s3@yahoo.com chr(10) chr(13)s4@yahoo.com,s2chr(10)3rdstreet

iam new to using sqlloader , I need help in creating the control file for loading the above data. The problem is that, in the columns of date of join , I have a 3 Milli seconds , that I have to convert to oracle format timestamps format , columns email id and address have character 13 and character 10 in between the values for the respective columns . I need to replace them with a space.

Another problem iam facing is that the records appear in two lines because of the newline and the form feed character. Before loading I have to make then as single record and then load into Table.

Any help is much appreciated.
0
Comment
Question by:sam_2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 total points
ID: 38837708
Dates and times are pretty straight forward and examples are out there.

Check out the example at:
http://nazeemdba.wordpress.com/2012/03/23/sql-loader-conversion-of-timestamp-and-dates-datatypes/

timestampcol TIMESTAMP “dd-Mon-yy hh:mi:ss:ff9AM”

You just need the proper format to handle the format in the file.

Now for your multiple lines.

This may or may not be possible. It depends on your actual data.

Check out:  How does one load multi-line records?
http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#i1005509

If there are fixed lines per record, it is pretty simple.  Use concatenate.

If you have the possibility of more than one line in the file as a specific record, you need some delimiter to decide what is the start of the next record to use continueif.

If you can upload a sample of your data and post expected results we might be able to post a complete working example.
0
 
LVL 32

Expert Comment

by:awking00
ID: 38840450
Sample data and expected results would truly be helpful (just need a few records to test with) . A description of your target table (i.e. column names and datatypes) would also help.
0
 

Author Comment

by:sam_2012
ID: 38864940
hi ,
I have attached the data for your reference. when iam loading the data sql loader should treat each record as single record i.e the new line character after 3 rd column should not be treated as a newline character but instead column value spanning mutliple  rows.

s1,2001-03-01 00:00:00.000,"s1@yahoo.com

s2@yahoo.com","s1
3rdstreet"
S2,2002-05-02 00:10:00.000,"s3@yahoo.com

s4@yahoo.com","s2
3rdstreet"
S3,2002-05-02 00:10:00.000,"s4@yahoo.com

s5@yahoo.com","s2
3rdstreet"
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38864970
Do you have fixed lines per row or can you have a fidderent number of lines per row?

From the data you posted it looks like 5 lines in the file per row in the database.  Is this the case for ALL rows in the file?

If this is the case, look at concatenate in the link I posted.

For removing the chr(10) and chr(13), it is a simple matter of a replace function in the sql loader control file.

There are many examples out there on these options.
0
 

Author Comment

by:sam_2012
ID: 38866645
Do you have fixed lines per row or can you have a fidderent number of lines per row?

From the data you posted it looks like 5 lines in the file per row in the database.  Is this the case for ALL rows in the file?

The answer to both the above questions is that we have may fidderent number of lines per row. Also , for all the rows we  may have 5 or 3 lines or one line , if it does not contain the chr(10) and chr(13) character.


Also , Iam not supposed to remove the chr(10) and chr(13) character for the columns email id and address.  I have to load them as is in the database.

It is bit complex , any solution is much appreciated.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38868557
Since you can have a different number of lines, you will need to use CONTINUEIF (referenced in the link I posted above).

For this to work, you need a way to determine when the next record starts.  You need some way of knowing this or I don't think it will be possible.

From the sample you posted, I don't see a record delimiter so this might not be possbible with this data.
0
 

Author Closing Comment

by:sam_2012
ID: 39201361
thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

752 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