怎样将Excel一列数据写到oracle数据库多行中一个字段里呢

用PL/SQL Developer

第1步 用sql语句查询出你需要的记录，如select t from [表名] t

第2步 在t后面增加,trowid，组成语句select t,trowid from [表名] t，再次查询

第3步 点击编辑数据按钮后，就可以直接将Excel里的数据直接拷进去了

数据量大的话，建议将Excel里的数据建成临时表，再与要更新的表做关联，update就可以了

using System;

using SystemCollectionsGeneric;

using SystemLinq;

using SystemWeb;

using SystemWebUI;

using SystemWebUIWebControls;

using SystemIO;

using SystemDataOleDb;

using SystemData;

using SystemWebServices;

public partial class admin_Att_ExcelImporting : SystemWebUIPage

{

static string puth = "";

protected void Page_Load(object sender, EventArgs e)

{

}

[WebMethod]

public static List<string> getExcelSheetNames()

{

OleDbConnection conn = null;

try

{

string strConn = "Provider=MicrosoftJetOLEDB40;" + "Data Source=" + puth + ";" + "Extended Properties=Excel 80;";

conn = new OleDbConnection(strConn);

connOpen();

DataTable dt = connGetOleDbSchemaTable(OleDbSchemaGuidTables, new object[] { null, null, null, "TABLE" });

List<string> strli = new List<string>();

foreach (DataRow dr in dtRows)

{

strliAdd((String)dr["TABLE_NAME"]);

}

connClose();

return strli;

}

catch (Exception)

{

if (conn != null)

connClose();

return null;

}

}

public DataSet ExcelToDS(string sheetName)

{

OleDbConnection conn = null;

try

{

string strConn = "Provider=MicrosoftJetOLEDB40;" + "Data Source=" + puth + ";" + "Extended Properties=Excel 80;";

conn = new OleDbConnection(strConn);

connOpen();

string strExcel = "";

OleDbDataAdapter myCommand = null;

DataSet ds = null;

strExcel = "select from ["+sheetName+"]";

myCommand = new OleDbDataAdapter(strExcel, strConn);

ds = new DataSet();

myCommandFill(ds, "sheetName");

return ds;

}

catch (Exception ex)

{

if (conn != null)

{

connClose();

}

ToolLogWriteWriteLog(ex);

return null;

}

}

protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkitAsyncFileUploadEventArgs e)

{

if (SystemIOPathGetExtension(eFileName) == "xls")

{

AjaxControlToolkitAsyncFileUpload au = sender as AjaxControlToolkitAsyncFileUpload;

string nowputh = ServerMapPath("");

DirectoryInfo dir = new DirectoryInfo(nowputh);

string upputh = dirParentFullName + "\\TemporaryExcel\\";

if (!DirectoryExists(upputh))

DirectoryCreateDirectory(upputh);

Random random = new Random();

string randomNum = randomNext(100000, 999999)ToString();

upputh += randomNumToString();

upputh += SystemIOPathGetExtension(eFileName);

puth = upputh;

if (au != null && auHasFile)

auSaveAs(upputh);

}

else

{

}

}

[WebMethod]

public static string Emp_ExcelImporting(string sheet,string type)

{

int rowcount = 0;

int rowerr = 0;

string message = "";

//TRBLLOrg_PositionBll orgpbll = new TRBLLOrg_PositionBll();

//TRBLLOrg_OrganizationBll orgbll=new TRBLLOrg_OrganizationBll();

TRBLLEmp_EmployeesBasicBLL empbll=new TRBLLEmp_EmployeesBasicBLL();

TRBLLSys_TypeBll sysbll=new TRBLLSys_TypeBll();

try

{

admin_Att_ExcelImporting ff = new admin_Att_ExcelImporting();

DataSet ds = ffExcelToDS(sheet);

if (ds != null && dsTablesCount > 0)

{

DataTable dt = dsTables[0];

if (type=="2")

{

foreach (DataRow r in dtRows)

{

try

{

rowcount++;

int empid = empbllGetEmpIdByJobNumber(r[0]ToString());

int systypeId = sysbllGetTypeIdByTypeName(r[2]ToString());

if (empid == 0)

{

message += "插入第" + rowcount + "条数据时，社番号不正确，插入失败，已跳过！</br>";

rowerr++;

}

else if (systypeId == 0)

{

message += "插入第" + rowcount + "条数据时，检测到'" + r[2] + "'请假类型不存在，插入失败，已跳过！</br>";

rowerr++;

}

else

{

Object obj = ToolSQLHelperExecuteScalar(CommandTypeText, "select id from Att_Leave where empId=" + empid + " and typeId="+systypeId+" and DATEDIFF(day,'" + DateTimeNowToString("yyyy-MM-dd") + "',addTime)=0", null);

string str = "";

if (obj==null)

{

str = "INSERT INTO [dbo][Att_Leave]([empId],[typeId],[leaveTime1],[leaveTime2],[days],[leaveReason],[isEnd],[added],[status])VALUES(" + empid + " ," + systypeId + ",'" + r[3] + "','" + r[4] + "'," + r[5] + ",'" + r[6] + "',1,'" + ffSession["User_Nickname"] + "',1)";

}

else

{

str = "update Att_Leave set typeId=" + systypeId + ",leaveTime1='"+r[3]+"',leaveTime2='"+r[4]+"',days="+r[5]+",leaveReason='"+r[6]+"',isEnd=1,added='"+ffSession["User_Nickname"]+"',status=1 where id="+ConvertToInt32(obj);

}

ToolSQLHelperExecuteNonQuery(CommandTypeText, str, null);

}

}

catch (Exception)

{

message += "插入第" + rowcount + "条数据时发生错误（可能是数据格式存在问题），已跳过！</br>";

rowerr++;

}

}

}

else

{

foreach (DataRow r in dtRows)

{

try

{

rowcount++;

int empid = empbllGetEmpIdByJobNumber(r[0]ToString());

int systypeId = sysbllGetTypeIdByTypeName(r[2]ToString());

if (empid == 0)

{

message += "插入第" + rowcount + "条数据时，社番号不正确，插入失败，已跳过！</br>";

rowerr++;

}

else if (systypeId == 0)

{

message += "插入第" + rowcount + "条数据时，检测到'" + r[2] + "'加班类型不存在，插入失败，已跳过！</br>";

rowerr++;

}

else

{

Object obj =ToolSQLHelperExecuteScalar(CommandTypeText,"select id from Att_Overtime where empId="+empid+" and typeId="+systypeId+" and datediff(day,'"+DateTimeNow+"',addTimes)=0",null);

string str = "";

if (obj == null)

{

str = "INSERT INTO [dbo][Att_Overtime]([empId],[typeId],[overtime],[addTime],[added],[status]) VALUES (" + empid + "," + systypeId + "," + r[3] + ",'" + r[4] + "','" + ffSession["User_Nickname"] + "',1)";

}

else

{

str = "update Att_Overtime set [empId]=" + empid + ",[typeId]=" + systypeId + ",[overtime]='" + r[3] + "',[addTime]='" + r[4] + "',[added]='" + ffSession["User_Nickname"] + "',[status]=1 where id="+ConvertToInt32(obj);

}

ToolSQLHelperExecuteNonQuery(CommandTypeText, str, null);

}

}

catch (Exception)

{

message += "插入第" + rowcount + "条数据时发生错误（可能是数据格式存在问题），已跳过！</br>";

rowerr++;

}

}

}

if (rowerr == 0)

{

return "执行全部成功！共执行" + rowcount + "行，成功" + rowcount + "行</br>";

}

else

{

return (message + "执行完成！共执行" + rowcount + "行，成功" + (rowcount - rowerr) + "行，失败" + rowerr + "行</br>");

}

}

else

{

return "Excel数据为空！</br>";

}

}

catch (Exception ex)

{

return "执行过程中发生异常！"+exMessage+"</br>";

}

}

}

在查询分析器里，直接写 SQL语句：

如果是导入数据到现有表，则采用形式

INSERT INTO 表 SELECT FROM OPENROWSET('MICROSOFTJETOLEDB40' ,'Excel 50;HDR=YES;DATABASE=c:\testxls',sheet1$)

如果是导入数据并新增表，则采用形式

SELECT INTO 表 FROM OPENROWSET('MICROSOFTJETOLEDB40' ,'Excel 50;HDR=YES;DATABASE=c:\testxls',sheet1$)

SQL2005中直接可以实现导入功能 SQL2008不知道可不可以。 *** 作过程如下：

第一步：登录到 SQL Server Management Studio

第二步：在 “对象资源管理器 ”中右键单击 “管理 ”，在d出列表中单击 “导入数据 ”

第三步：在 “导入向导 ”对话框中单击 “下一步 ”，进入到 “选择数据源 ”对话框，在 “数据源 ”列表中选择 “Microsoft Excel ”，同时选择相应的 Excel 文档，完成后单击 “下一步 ”（一定要勾选该对话框中的 “首行包含列名称 ”，因此它是将 Excel文档中的列标题为数据库表中的列项标题）

第四步：指定目标数据库服务，依次单击 “下一步 ”。。。。至到 “完成 ”

第五步：重新打到 SQL Server Management Studio，进入到导入的数据库表，可以发现所导入的 Excel文档数据。

以上就是关于怎样将Excel一列数据写到oracle数据库多行中一个字段里呢全部的内容，包括:怎样将Excel一列数据写到oracle数据库多行中一个字段里呢、.net怎么讲excel表的一列导入到数据库某张表的一列、请教如何将excle上面的数据批量导入至sql2008数据库等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！