如何高效的导入数据到DB中（1W笔以上）

Question

我目前遇到的问题是客户端会上传一个excel文档，文档里包含了要导入到db中的数据，我是直接把excel中的数据读取到dataset中，然后循环遍历table将数据一行一行的插入，我发现性能非常的差，我用的数据库是oracle，我想咨询一下如何高效的导入大量数据，比如plsql中有直接将excel作为数据源通过odbc导入到db中，要是可以通过在页面文件中调用plsql的那些功能性能应该会提升很多，只是不知道从什么地方着手，我页面所使用的语言是asp.net，大侠指点啊！！

Answer 1

http://www.cnblogs.com/zfanswer/archive/2009/03/29/1424237.html

Answer 2

如何高效的将excel导入到oracle?和前两天的SqlBulkCopy 导入到sqlserver对应,oracle也有自身的方法,只是稍微复杂些.
那就是使用oracle的sql*loader功能，而sqlldr只支持类似csv格式的数据，所以要自己把excel转换一下。
实现步骤：
用com组件读取excel-保存为csv格式-处理最后一个字段为null的情况和表头-根据excel结构建表-生成sqlldr的控制文件-用sqlldr命令导入数据
这个性能虽然没有sql的bcp快，但还是相当可观的，在我机器上1万多数据不到4秒，而且导入过程代码比较简单，也同样没有循环拼接sql插入那么难以维护。这里也提个问题:处理csv文件的表头和最后一个字段为null的情况是否可以优化？除了我代码中的例子，我实在想不出其他办法。using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
//引用-com-microsoft excel objects 11.0
namespace WindowsApplication5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        /// 
        /// excel导入到oracle
        /// 
        ///  文件名
        ///  sheet名
        ///  oracle命令sqlplus连接串
        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);            //获取excel对象
            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));            //保存为csv临时文件
            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();            //读取csv文件，需要将表头去掉，并且将最后一列为null的字段处理为显示的null，否则oracle不会识别，这个步骤有没有好的替换方法?
            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();            //获取excel表结构
            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 });            //生成sqlldr用到的控制文件,文件结构参考sql*loader功能，本示例已逗号分隔csv，数据带逗号的用引号括起来。   
            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();            //生成初始化oracle表结构的文件
            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();            //运行sqlplus，初始化表
            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();            //运行sqlldr，导入数据
            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;//截取输出流
            string line = r.ReadLine();//每次读取一行
            textBox3.Text += line + "\r\n";
            while (!r.EndOfStream)
            {
                line = r.ReadLine();
                textBox3.Text += line + "\r\n";
                textBox3.Update();
            }
            p.WaitForExit();            //可以自行解决掉临时文件csv，ctl和sql，代码略去
        }        private void button1_Click(object sender, EventArgs e)
        {
            TransferData(@"D:\test.xls", "Sheet1", "username/password@servicename");
        }
        
    }
}

Answer 3

1. lz没有权限用sqlloader这样的数据装载工具吗？
2. 如果没有，那么通过正常的方式插入Oracle的速度，在一般情况下，也就是1k~3k数量的记录，超过4k就算很好了。在网络上可能就更慢一点了。
3. 如果没有，估计最好的办法是写一个存储过程，然后将数据批量性的喂给存储过程，不过要达到1w还是很困难的。试试看吧。

Answer 4

using System; 
using System.Data; 
using System.Text; 
using System.Windows.Forms; 
using Microsoft.Office.Interop.Excel; 
using System.Data.OleDb; 
//引用-com-microsoft excel objects 11.0 
namespace WindowsApplication5 
{ 
    public partial class Form1 : Form 
    { 
        public Form1() 
        { 
            InitializeComponent(); 
        }         /// 
        /// excel导入到oracle 
        /// 
        ///  文件名 
        ///  sheet名 
        ///  oracle命令sqlplus连接串 
        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);             //获取excel对象 
            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));             //保存为csv临时文件 
            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();             //读取csv文件，需要将表头去掉，并且将最后一列为null的字段处理为显示的null，否则oracle不会识别，这个步骤有没有好的替换方法? 
            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();             //获取excel表结构 
            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 });             //生成sqlldr用到的控制文件,文件结构参考sql*loader功能，本示例已逗号分隔csv，数据带逗号的用引号括起来。  
            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();             //生成初始化oracle表结构的文件 
            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();             //运行sqlplus，初始化表 
            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();             //运行sqlldr，导入数据 
            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;//截取输出流 
            string line = r.ReadLine();//每次读取一行 
            textBox3.Text += line + "\r\n"; 
            while (!r.EndOfStream) 
            { 
                line = r.ReadLine(); 
                textBox3.Text += line + "\r\n"; 
                textBox3.Update(); 
            } 
            p.WaitForExit();             //可以自行解决掉临时文件csv，ctl和sql，代码略去 
        }         private void button1_Click(object sender, EventArgs e) 
        { 
            TransferData(@"D:\test.xls", "Sheet1", "username/password@servicename"); 
        } 
        
    } 
}