Loading data through SQLLDR (SQL* Loader) from C# .Net
Problem :
Load comma seperated data into tables.
Solution:
Best way is SQL Loader "SQLLDR"
DOS/Oracle Part:
How to use SQL Loader from Command line:
SQLLDR UserName/Password@TNS_DBName CTLFILE=anyFile BADFILE=anyOtherName
Problem :
Load comma seperated data into tables.
Solution:
Best way is SQL Loader "SQLLDR"
DOS/Oracle Part:
How to use SQL Loader from Command line:
SQLLDR UserName/Password@TNS_DBName CTLFILE=anyFile BADFILE=anyOtherName
- SQLLDR = SQL Loader
- User Name = User Name of Oracle Db.
- Password = Password of Oracle Db
- TNS_DBName = TNS Entry of DB instance
- CTLFILE => CtlFile is file where you will write actual load process
- BADFILE => BadFile will be generated by process, for records which are not updated.
Following is an example of CTLFILE
LOAD DATA
INFILE dataFile.csv
BADFILE dataFile.bad
APPEND INTO TABLE myTableName
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(id,name,email)
Where INFILE dataFile.csv
BADFILE dataFile.bad
APPEND INTO TABLE myTableName
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(id,name,email)
- dataFile.csv : is data file, you can replace it with yours.
- FIELDS TERMINATED BY ',' : You are telling process that fields are terminated by comma (your format)
- (id,name,email) : column names, seperated by comma.
C# calling part:
To write a fancy code:
- First write this CTL file dynamically (using FileStream class)
- then to call SQLLoader use following code: (code has comments so it is pretty much self explanatory)
using System.Diagnostics;
Function is :
//public function ExecuteLoader() (of DataLoader class---Names given by me :) )
Process p = new Process();
string myCommand = @"CMD.EXE";
p.StartInfo = new ProcessStartInfo(myCommand);
//Provide arguments to CMD.EXE
p.StartInfo.Arguments = @"/c SQLLDR SALMAN/SALMAN@CODE4CODER CONTROL=C:\CODE4CODER\TestFile.txt";
//To read output of process (before error)
p.StartInfo.RedirectStandardOutput = true;
//To read output of process(after error)
p.StartInfo.RedirectStandardError = true;
p.StartInfo.UseShellExecute = false;
p.StartInfo.WorkingDirectory = @"c:\WorkingFolder\";
p.Start();
p.WaitForExit();
if (p.ExitCode == 0){ //Success
string standardOutputString = p.StandardOutput.ReadToEnd();
//Show standard output (before error)
MessageBox.Show(standardOutputString);
}
else
{
string errorString = p.StandardError.ReadToEnd();
//Show output if there is any error
MessageBox.Show(errorString);
}
PS: This is totally my own code, if you want to copy you are free, but if you posting it to your blog, please refer http://code4coder.com as source.
