Sunday, May 30, 2010

Insert bulk data into oracle using SQLLoader in C#




Please visit my new Web Site WWW.Codedisplay.com



Sometimes we need to insert a large volume of data into the Oracle database. Sometimes data comes from comma seperated csv file or from a comma seperated text file. By reading this article you can insert data from both source to oracle. There are mainly two ways to insert bulk data into Oracle:

1. Using Bindarray
2. Using SqlLoader

Here in this article i will explain how one can use to insert bulk data from text file or csv file into oracle
using SQLLoader.

To do that please follow the below steps:

1. Create a folder in your c: drive and named it WorkingFolder.
2. Create a controller file in the WorkingFolder using below code:
LOAD DATA
INFILE DataOut.txt
BADFILE dataFile.bad
APPEND INTO TABLE SASN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
(CDR_Type,Rec_Type)
And name this file by "Ctrl.txt".
3. Now create a text file which contins data like below:
1,2
3,4
5,6
And name this file "DataOut.txt".
4. Now create a sample table in oracle like below:

Oracle Table

5. Now from your c# project run the below sample code:
Process proc = new Process();
            string myCommand = @"CMD.EXE";
            proc.StartInfo = new ProcessStartInfo(myCommand);
            
            //Set up arguments for CMD.EXE
            proc.StartInfo.Arguments = @"/c SQLLDR User/Password@Schema CONTROL=C:\WorkingFolder\Ctrl.txt";

            proc.StartInfo.RedirectStandardOutput = true;

            proc.StartInfo.RedirectStandardError = true;

            proc.StartInfo.UseShellExecute = false;

            proc.StartInfo.WorkingDirectory = @"c:\WorkingFolder\";
            proc.Start();
            proc.WaitForExit();

            if (proc.ExitCode == 0) // Successfully completed
                MessageBox.Show("Successfully Inserted");
            
            else
                MessageBox.Show(proc.StandardError.ReadToEnd());

6. Now run the above code & hope you will get below output:

SQLLoader Output

2 comments:

Anonymous said...

This helped me a lot

Thanks

Gayana Chandrasekara said...

nice post

Want to say something?
I WOULD BE DELIGHTED TO HEAR FROM YOU

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash