Efficient excel in C # methods for importing Oracle

How to efficiently import Excel into Oracle? And the first two days of sqlbulkcopy import into SQL Server correspondence, Oracle has its own approach, just slightly more complicated.

That is using Oracle's Sql*loader feature, and SQLLDR only supports data that resembles CSV format, so you have to transform Excel yourself.

Implementation steps:

Read excel-Save as CSV format with COM component-handle null in last field and header-build table based on Excel structure-generate SQLLDR control file-Import data with sqlldr command

This performance is not as fast as SQL BCP, but it is quite impressive, more than 10,000 data in my machine less than 4 seconds, and the import process code is relatively simple, there is no loop splicing SQL insert so difficult to maintain.

Here's the question: can you optimize the processing of a CSV file with a table header and the last field null? In addition to the examples in my code, I can't think of any other way.

using System;


using System.Data;


using System.Text;


using System.Windows.Forms;


using Microsoft.Office.Interop.Excel;


using System.Data.OleDb;


//reference-com-microsoft Excel objects 11.0


namespace WindowsApplication5


{


public partial class Form1:form


　　{


public Form1 ()


　　　　{


InitializeComponent ();


　　　　}





<SUMMARY>


///Excel import to Oracle


///</SUMMARY>


///<param name= "excelfile" > FileName </PARAM>


///<param name= "sheetname" >sheet name </PARAM>


///<param name= "sqlplusstring" >oracle command sqlplus connection string </PARAM>


public void TransferData (string excelfile, String sheetname, string sqlplusstring)


　　　　{


string strtempdir = System.IO.Path.GetDirectoryName (excelfile);


String strFileName = System.IO.Path.GetFileNameWithoutExtension (excelfile);


string strcsvpath = Strtempdir + "\" +strfilename + ". csv";


string strctlpath = Strtempdir + "\" + strFileName + ". Ctl ";


string strsqlpath = Strtempdir + "\" + strFileName + ". SQL ";


if (System.IO.File.Exists (Strcsvpath))


System.IO.File.Delete (Strcsvpath);





//Get Excel Object


Microsoft.Office.Interop.Excel.Application objexcel = new Microsoft.Office.Interop.Excel.Application ();





Microsoft.Office.Interop.Excel.Workbook Objworkbook;





Microsoft.Office.Interop.Excel.Worksheet objworksheet = null;





Objworkbook = ObjExcel.Workbooks.Open (Excelfile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type. Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);





foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in objworkbook.sheets)


　　　　　　{


if (sheet. Name.tolower () = = Sheetname.tolower ())


　　　　　　　　{


Objworksheet = sheet;


break;


}


　　　　　　}


if (Objworksheet = null) throw new Exception (string. Format ("{0} not found!!", sheetname));





//Save as CSV temp file


Objworksheet.saveas (Strcsvpath, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing , False, False, False, Type.Missing, Type.Missing, false);


Objworkbook.close (False, Type.Missing, Type.Missing);


Objexcel.quit ();





//Read the CSV file, you need to remove the header, and the last column of NULL is treated as NULL, otherwise Oracle will not recognize, this step has a good alternative?


System.IO.StreamReader reader = new System.IO.StreamReader (strcsvpath,encoding.getencoding ("gb2312"));


string strall = reader. ReadToEnd ();


Reader. Close ();


string strdata = Strall.substring (Strall.indexof ("\ r \ n") + 2). Replace (", \ r \ n", ", Null");





byte[] bytes = System.Text.Encoding.Default.GetBytes (strdata);


System.IO.Stream ms = System.IO.File.Create (Strcsvpath);


Ms. Write (bytes, 0, bytes. Length);


Ms. Close ();





//Get Excel Table Structure


string strconn = "provider=microsoft.jet.oledb.4.0;" + "Data source=" + Excelfile + ";" + "Extended Properties =excel 8.0; ";


OleDbConnection conn = new OleDbConnection (strconn);


Conn. Open ();


System.Data.DataTable table = conn. GetOleDbSchemaTable (System.Data.OleDb.OleDbSchemaGuid.Columns,


new object[] {null, NULL, sheetname+ "$", null});





//Generate SQLLDR used control file, file structure reference Sql*loader function, this example has comma-separated CSV, the data is enclosed in quotes with commas.


string strcontrol = "Load Data\r\ninfile ' {0}' \r\nappend into Table {1}\r\n "+


"FIELDS terminated by ',' Optionally enclosed by '\ "'\r\n (";


Strcontrol = string. Format (Strcontrol, strcsvpath,sheetname);


foreach (System.Data.DataRow drowcolumns in table). Select ("1=1", "ordinal_position")


　　　　　　{


Strcontrol + = drowcolumns["column_name"]. ToString () + ",";


　　　　　　}





Strcontrol = strcontrol.substring (0, strcontrol.length-1) + ")";


bytes=system.text.encoding.default.getbytes (Strcontrol);


ms= System.IO.File.Create (Strctlpath);





Ms. Write (bytes, 0, bytes. Length);


Ms. Close ();





//Generate initialization of the Oracle table structure file


string strSQL = @ "drop table {0};"


CREATE TABLE {0}


　　　　　　　　　　(";


strSQL = string. Format (strSQL, sheetname);


foreach (System.Data.DataRow drowcolumns in table). Select ("1=1", "ordinal_position")


　　　　　　{


strSQL + = drowcolumns["column_name"]. ToString () + "VARCHAR2 (255),";


　　　　　　}


strSQL = strsql.substring (0, strsql.length-1) + "); \r\nexit;";


bytes = System.Text.Encoding.Default.GetBytes (strSQL);


ms = System.IO.File.Create (Strsqlpath);





Ms. Write (bytes, 0, bytes. Length);


Ms. Close ();





//Run Sqlplus, initialize table


System.Diagnostics.Process p = new System.Diagnostics.Process ();


p.startinfo = new System.Diagnostics.ProcessStartInfo ();


p.startinfo.filename = "Sqlplus";


p.startinfo.arguments = string. Format ("{0} @{1}", sqlplusstring, Strsqlpath);


P.startinfo.windowstyle = System.Diagnostics.ProcessWindowStyle.Hidden;


P.startinfo.useshellexecute = false;


P.startinfo.createnowindow = true;


P.start ();


p.WaitForExit ();





//Run SQLLDR, import data


p = new System.Diagnostics.Process ();


p.startinfo = new System.Diagnostics.ProcessStartInfo ();


p.startinfo.filename = "Sqlldr";


p.startinfo.arguments = string. Format ("{0} {1}", sqlplusstring, Strctlpath);


P.startinfo.windowstyle = System.Diagnostics.ProcessWindowStyle.Hidden;


P.startinfo.redirectstandardoutput = true;


P.startinfo.useshellexecute = false;


P.startinfo.createnowindow = true;


P.start ();


System.IO.StreamReader r = p.standardoutput;//Intercept output stream


String line = R.readline ();//read one row at a time


TextBox3.Text + + "\ r \ n";


while (!r.endofstream)


　　　　　　{


line = R.readline ();


TextBox3.Text + + "\ r \ n";


textbox3.update ();


　　　　　　}


p.WaitForExit ();





//can resolve temporary files Csv,ctl and SQL, code omitted


　　　　}





private void Button1_Click (object sender, EventArgs e)


　　　　{


TransferData (@ "D:\test.xls", "Sheet1", "username/password@servicename");


　　　　}





　　}


}