How to import an efficient Excel file to Oracle in C #

How can I efficiently import an Excel file to Oracle? Compared with SQL bulkcopy imported to SQL Server two days ago, Oracle also has its own method, which is a little more complicated.
That is, the SQL * loader function of Oracle is used, while sqlldr only supports data similar to CSV format, so you need to convert the Excel file yourself.
Steps:
Use the COM component to read the Excel file and save it as a CSV file. process the case where the last field is null and the header. Create a table based on the Excel structure. Generate a sqlldr control file. Use the sqlldr command to import data.
Although this performance is not as fast as the SQL BCP, it is still quite impressive. In my machine, more than 10 thousand pieces of data are less than 4 seconds, and the import process code is relatively simple, it is also difficult to maintain the SQL insert statement without loop splicing.

The following question is also raised: Can the header and the last field of the CSV file be optimized if it is null? Except for examples in my code, I can't find any other method.

 

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 ();} ///// import data to Oracle in Excel //////File Name ///Sheet Name ///Oracle command sqlplus connection string 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); // obtain the 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. m Issing, 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. forma T ("{0} Not Found !! ", Sheetname); // save it as a CSV temporary file objworksheet. saveas (strcsvpath, Microsoft. office. interOP. excel. xlfileformat. xlcsv, type. missing, type. missing, 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 process the null field in the last column as the displayed null. Otherwise, Oracle will not recognize it, is there a good replacement method for this step? 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 (); // obtain the 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}); // generates the control file used by sqlldr. For the file structure, see SQL * loader function. This example For example, CSV files are separated by commas (,), and the data is enclosed by commas (,) in quotation marks. String strcontrol = "load data/R/ninfile '{0}'/R/nappend into table {1}/R/N" + "fields terminated ', '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. le Ngth-1) + ")"; bytes = system. text. encoding. default. getbytes (strcontrol); MS = system. io. file. create (strctlpath); Ms. write (bytes, 0, bytes. length); Ms. close (); // generate the 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 to initialize the 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 and import data P = new system. diagnostics. process (); p. startinfo = new system. diagnostics. processstartinfo (); p. startinfo. filename = "s Qlldr "; 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; // capture the output stream string line = R. readline (); // read a row of textbox3.text + = line + "/R/n "; While (! R. endofstream) {line = R. readline (); textbox3.text + = line + "/R/N"; textbox3.update ();} p. waitforexit (); // you can remove the temporary files CSV, CTL, and SQL by yourself, and the code is omitted} private void button#click (Object sender, eventargs e) {transferdata (@ "D: /test.xls "," sheet1 "," username/password @ servicename ");}}}