在Oracle数据库中导入excle文件 [英] import excle file in oracle database

问题描述

嘿，
我想制作一个c#实用程序，将其导入oracle数据库中的excle文件.

hey,
i want to make a c# utility which i import excle file in oracle database

推荐答案

您可以使用Microsoft Integration Services并用它批量加载文件

另一种方法是将excel工作表转换为cvs并通过Oracle Loader加载它们.

You can use Microsoft Integration Services and bulkload the files with it

Another way is to convert the excel sheets into cvs and load them via Oracle Loader

受保护的void excle()
{
试试
{
字符串strCon1 ="Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + textBox1.Text +;扩展属性= \" Excel 8.0; HDR = YES; IMEX = 1 \";
OleDbConnection CON =新的OleDbConnection(strCon1);
DataTable dt = null;
int ii;
CON.Open();
字符串str =";
dt = CON.GetOleDbSchemaTable(OleDbSchemaGuid.Tables，null);
String [] excelsheets = new String [dt.Rows.Count];
int i = 0;
foreach(在dt.rows中的DataRow dr)
{
excelsheets [i] = dr ["TABLE_NAME"].ToString();
i ++;
}
DataSet ds = new DataSet();
foreach(excelsheets中的字符串温度)
{
字符串查询=从[" + temp +]中选择*"；
OleDbDataAdapter adp =新的OleDbDataAdapter(query，CON);
adp.Fill(ds，"ASDD");
}
//返回ds;
cond.connse.Open();
for(i = 0; i< ds.Tables [0] .Rows.Count; i ++)
{
if((ds.Tables [0] .Rows [0] [0] .ToString()！=")&&(ds.Tables [0] .Rows [0] [1] .ToString()！=" ))
{
DataSet ds1 = new DataSet();
str =从sysadm.CLIENT_MASTER中选择*，其中CODE ="" + ds.Tables [0] .Rows [i] [0] .ToString()+''";
OracleDataAdapter da1 =新的OracleDataAdapter(str，cond.connse);
da1.Fill(ds1，"aa");
if(ds1.Tables [0] .Rows.Count> 0)
{
str ="update sysadm.client_master set name =""+ ds.Tables [0] .Rows [i] [1] .ToString()+"''，add1 =''" + ds.Tables [0].行[i] [2] .ToString()+''，add2 =''""+ ds.Tables [0].行[i] [3] .ToString()+"''，City ='' + ds.Tables [0] .Rows [i] [4] .ToString()+''，state =''" + ds.Tables [0] .Rows [i] [5] .ToString()+"''，Country =''"+ ds.Tables [0] .Rows [i] [6] .ToString()+"''，mobileno =''"+ ds.Tables [0] .Rows [i] [ 7] .ToString()+''，GROUPclient ="''"+ ds.Tables [0] .Rows [i] [8] .ToString()+"''，EMAILID =''" + ds.Tables [ 0] .Rows [i] [9] .ToString()+''WHERE CODE ="''"+ ds.Tables [0] .Rows [i] [0] .ToString()+"'';
}
其他
{
str =插入sysadm.client_master(代码，名称，add1，add2，城市，州，国家/地区，移动电话，client_enable，GROUPclient，EMAILID)values(""+ ds.Tables [0] .Rows [i] [0 ] .ToString()+''，''" + ds.Tables [0] .Rows [i] [1] .ToString()+''，''" + ds.Tables [0] .Rows [ i] [2] .ToString()+''，''" + ds.Tables [0] .Rows [i] [3] .ToString()+''，''" + ds.Tables [0 ] .Rows [i] [4] .ToString()+''，''" + ds.Tables [0] .Rows [i] [5] .ToString()+''，''" + ds .Tables [0] .Rows [i] [6] +''，''" + ds.Tables [0] .Rows [i] [7] .ToString()+''，``Enable'' ，''"+ ds.Tables [0] .Rows [i] [8] .ToString()+"''，''"+ ds.Tables [0] .Rows [i] [9] .ToString() +'')";
}
OracleCommand cmd =新的OracleCommand(str，cond.connse);
ii = cmd.ExecuteNonQuery();
textBox2.Text = Convert.ToString(i + 1);
}
}
cond.connse.Close();
CON.Close();
MessageBox.Show(导入成功已满..."，消息");
}
catch {cond.connse.Close();//CON.Close();
}
}

protected void excle()
{
try
{
string strCon1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
OleDbConnection CON = new OleDbConnection(strCon1);
DataTable dt = null;
int ii;
CON.Open();
string str = "";
dt = CON.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelsheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
excelsheets[i] = dr["TABLE_NAME"].ToString();
i++;
}
DataSet ds = new DataSet();
foreach (string temp in excelsheets)
{
string query = "select * from [" + temp + "]";
OleDbDataAdapter adp = new OleDbDataAdapter(query, CON );
adp.Fill(ds, "ASDD");
}
// return ds;
cond.connse.Open();
for (i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if ((ds.Tables[0].Rows[0][0].ToString()!="")&& (ds.Tables[0].Rows[0][1].ToString()!=""))
{
DataSet ds1=new DataSet();
str = "select * from sysadm.CLIENT_MASTER where CODE=''" + ds.Tables[0].Rows[i][0].ToString() + "''";
OracleDataAdapter da1 = new OracleDataAdapter(str, cond.connse);
da1.Fill(ds1, "aa");
if (ds1.Tables[0].Rows.Count > 0)
{
str = "update sysadm.client_master set name=''" + ds.Tables[0].Rows[i][1].ToString() + "'',add1=''" + ds.Tables[0].Rows[i][2].ToString() + "'',add2=''" + ds.Tables[0].Rows[i][3].ToString() + "'',City=''" + ds.Tables[0].Rows[i][4].ToString() + "'',state=''" + ds.Tables[0].Rows[i][5].ToString() + "'',Country =''" + ds.Tables[0].Rows[i][6].ToString() + "'', mobileno=''" + ds.Tables[0].Rows[i][7].ToString() + "'', GROUPclient=''" + ds.Tables[0].Rows[i][8].ToString() + "'',EMAILID=''" + ds.Tables[0].Rows[i][9].ToString() + "'' WHERE CODE=''" + ds.Tables[0].Rows[i][0].ToString() + "''";
}
else
{
str = "insert into sysadm.client_master(code,name,add1,add2,city,state,country,mobileno,client_enable,GROUPclient,EMAILID)values(''" + ds.Tables[0].Rows[i][0].ToString() + "'',''" + ds.Tables[0].Rows[i][1].ToString() + "'',''" + ds.Tables[0].Rows[i][2].ToString() + "'',''" + ds.Tables[0].Rows[i][3].ToString() + "'',''" + ds.Tables[0].Rows[i][4].ToString() + "'',''" + ds.Tables[0].Rows[i][5].ToString() + "'',''" + ds.Tables[0].Rows[i][6] + "'',''" + ds.Tables[0].Rows[i][7].ToString() + "'',''Enable'',''" + ds.Tables[0].Rows[i][8].ToString() + "'',''" + ds.Tables[0].Rows[i][9].ToString() + "'')";
}
OracleCommand cmd = new OracleCommand(str, cond.connse);
ii = cmd.ExecuteNonQuery();
textBox2.Text = Convert.ToString(i + 1);
}
}
cond.connse.Close();
CON.Close();
MessageBox.Show("Import Success Full...","Message");
}
catch { cond.connse.Close();// CON.Close();
}
}

这篇关于在Oracle数据库中导入excle文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！