代码执行批量Excel数据导入Oracle数据库

由于基于Oracle数据库上做开发,因此常常会需要把大量的Excel数据导入到Oracle数据库中,其实如果从事SqlServer数据库的开发,那么思路也是一样的,本文主要介绍如何导入Excel数据进入Oracle数据库的内容。

方法一:

1.准备数据:在excel中构造出需要的数据
2.将excel中的数据另存为文本文件(有制表符分隔的)
3.将新保存到文本文件中的数据导入到pl*sql中
在pl*sql中选择tools--text importer,在出现的窗口中选择Data from Textfile,然后再选择Open data file,
在弹出的文件选择框中选中保存有数据的文本文件,此时将会看到data from textfile中显示将要导入的数据
4.在configuration中进行如下配置
注:如果不将Name in header勾选上会导致字段名也当做记录被导入到数据库中,从而导致数据错误
5.点击data to oracle,选择将要导入数据的表,并在fields中将文本中的字段与表中的字段进行关联
6.点击import按钮进行导入
7.查看导入的数据
OK,至此数据导入成功。
 
方法二:

一般我们拿到的Excel数据,都会有一个表头说明,然后下面是一连串的数据内容,如下图所示:

 

而Oracle中数据库一般为英文名称,中文名称就需要转义,为了方便导入,我把中文名称对照数据库的字段,把表头修改为对应的字段名称,如果没有数据库对应的字段,那么删除Excel的无用列即可,如下所示。

 

首先我们在导入Excel的例子中加载显示要导入的数据,一个是为了直观,第二个也是为了检查数据的有效性,避免出错,界面如下所示:

 

在介绍导入操作前,我们先要分析下数据,否则就很容易出现错误的语句,一般日期的格式、数字的格式就要特别注意,文本格式一般看是否超出字段的长度,一般成功导入前都会发生好多次的错误问题,解决了这些格式的问题,基本上就OK了。如下面日期和数字的格式问题,就必须注意转换为对应的内容格式:

 

 

下面介绍具体的显示数据和导入数据的操作代码:

 显示Excel数据的代码如下所示:

 代码

        private string connectionStringFormat = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = ‘{0}‘;Extended Properties=Excel 8.0";         private DataSet myDs = new DataSet();
        private void btnViewData_Click(object sender, EventArgs e)         {             if (this.txtFilePath.Text == "")             {                 MessageUtil.ShowTips("请选择指定的Excel文件");                 return;             }
            string connectString = string.Format(connectionStringFormat, this.txtFilePath.Text);             try             {                 myDs.Tables.Clear();                 myDs.Clear();                 OleDbConnection cnnxls = new OleDbConnection(connectString);                 OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);                 myDa.Fill(myDs, "c");
                dataGrid1.DataSource = myDs.Tables[0];             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }         }

 

 

导入操作的代码如下所示(由于数据格式需要验证,以及需要判断数据库是否存在指定关键字的记录,如果存在,那么更新,否则插入新的记录,如果仅仅是第一次导入,操作代码可以更为精简一些):

 代码

        private void btnSaveData_Click(object sender, EventArgs e)         {             if (this.txtFilePath.Text == "")             {                 MessageUtil.ShowTips("请选择指定的Excel文件");                 return;             }
            if (MessageUtil.ShowYesNoAndWarning("该操作将把数据导入到系统的用户数据库中,您确定是否继续?"== DialogResult.Yes)             {                 InsertData();             }         }
        private bool CheckIsDate(string columnName)         {             string str = ",PREPARE_DATE,COPY_DATE,COPY_VALIDITY,BUSINESS_VALIDITY,OPENING_APPROVAL_DATE,OPENING_DATE,EDITTIME,LICENSE_DATE,LICENSE_VALIDITY,TEMP_OPENING_DATE,LICENSE_START_DATE,ADDTIME,EDITTIME,";             return str.Contains("," + columnName.ToUpper() + ",");         }
        private bool CheckIsNumeric(string columnName)         {             string str = ",FIXED_CAPITAL,REG_CAPITAL,MARGIN,PARK_AREA,PARK_SPACE_NUMBER,";             return str.Contains("," + columnName.ToUpper() + ",");         }
        private void InsertData()         {             int intOk = 0;             int intFail = 0;
            if (myDs != null && myDs.Tables[0].Rows.Count > 0)             {                 string accessConnectString = config.GetConnectionString("DataAccess");                 OracleConnection conn = new OracleConnection(accessConnectString);                 conn.Open();                 OracleCommand com = null;
                #region 组装字段列表                 string insertColumnString = "ID,";                 DataTable dt = myDs.Tables[0];                 int k = 0;                 foreach (DataColumn col in dt.Columns)                 {                     insertColumnString += string.Format("{0},", col.ColumnName);                 }                 insertColumnString = insertColumnString.Trim(,);
                #endregion
                try                 {                     foreach (DataRow dr in dt.Rows)                     {                         if (dr[0].ToString() == "")                         {                             continue;                         }
                        #region 组装Sql语句                         string insertValueString = "SEQ_TBPARK_ENTERPRISE.Nextval,";                         string updateValueString = "";                         string COMPANY_CODE = dr["COMPANY_CODE"].ToString().Replace("<空>""");
                        #region 拼接Sql字符串
                        for(int i = 0; i < dt.Columns.Count; i++)                         {                             string originalValue = dr[i].ToString().Replace("<空>""");                             //if (!CheckIsDate(dt.Rows[0][i].ToString()))                             if (!CheckIsDate(dt.Columns[i].ColumnName))                             {                                 if (!string.IsNullOrEmpty(originalValue))                                 {                                     if (CheckIsNumeric(dt.Columns[i].ColumnName))                                     {                                         insertValueString += string.Format("‘{0}‘,", Convert.ToDecimal(originalValue));                                         updateValueString += string.Format("{0}=‘{1}‘,", dt.Columns[i].ColumnName, Convert.ToDecimal(originalValue));                                     }                                     else                                     {                                         insertValueString += string.Format("‘{0}‘,", originalValue);                                         updateValueString += string.Format("{0}=‘{1}‘,", dt.Columns[i].ColumnName, originalValue);                                     }                                 }                                 else                                 {                                     insertValueString += string.Format("NULL,");                                     updateValueString += string.Format("{0}=NULL,", dt.Columns[i].ColumnName);                                 }                             }                             else                             {                                 if (!string.IsNullOrEmpty(originalValue))                                 {                                     insertValueString += string.Format("to_date(‘{0}‘,‘yyyy-mm-dd‘),", Convert.ToDateTime(originalValue).ToString("yyyy-MM-dd"));                                     updateValueString += string.Format("{0}=to_date(‘{1}‘,‘yyyy-mm-dd‘),", dt.Columns[i].ColumnName, Convert.ToDateTime(originalValue).ToString("yyyy-MM-dd"));                                 }                                 else                                 {                                     insertValueString += string.Format("NULL,");                                     updateValueString += string.Format("{0}=NULL,", dt.Columns[i].ColumnName);                                 }                             }                         }                         insertValueString = insertValueString.Trim(,);                         updateValueString = updateValueString.Trim(,);                          #endregion
                        string insertSql = string.Format(@"INSERT INTO tbpark_enterprise ({0}) VALUES({1})", insertColumnString, insertValueString);                         string updateSql = string.Format("Update tbpark_enterprise set {0} Where COMPANY_CODE=‘{1}‘ ", updateValueString, COMPANY_CODE);                         string checkExistSql = string.Format("Select count(*) from tbpark_enterprise where COMPANY_CODE=‘{0}‘ ", COMPANY_CODE);                         #endregion
                        #region 写入数据                         try                         {                             com = new OracleCommand();                             com.Connection = conn;                             com.CommandText = checkExistSql;                             object objCount = com.ExecuteScalar();
                            bool succeed = false;                             bool exist = Convert.ToInt32(objCount) > 0;                             if (exist)                             {                                 //需要更新                                 //WriteString(updateSql);                                 com.CommandText = updateSql;                                 succeed = com.ExecuteNonQuery() > 0;                             }                             else                             {                                 //需要插入                                 //WriteString2(insertSql);                                 com.CommandText = insertSql;                                 succeed = com.ExecuteNonQuery() > 0;                             }
                            if (succeed)                             {                                 intOk++;                             }                             else                             {                                 intFail++;                             }                         }                         catch (Exception ex)                         {                             intFail++;                             WriteString(com.CommandText);                             LogHelper.Error(ex);                             break;                         }
                        #endregion                     }
                    #region 关闭                     if (conn != null && conn.State != ConnectionState.Closed)                     {                         conn.Close();                     }                     if (com != null)                     {                         com.Dispose();                     }                     #endregion                 }                 catch (Exception ex)                 {                     LogHelper.Error(ex);                     MessageUtil.ShowError(ex.ToString());                 }
                if (intOk > 0 || intFail > 0)                 {                     string tips = string.Format("数据导入成功:{0}个,失败:{1}个", intOk, intFail);                     MessageUtil.ShowTips(tips);                 }             }         }

 

以上代码,为了方便,使用了输出脚本的方式进行验证对比,一般情况下也是用得着的。

代码执行批量Excel数据导入Oracle数据库,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。