我想将数据从Excel源提取到Sqlserver数据库 [英] i want pull the data from Excel source to Sqlserver database

问题描述

Hai！



这是madhu。

实际上我创建了一个包，用于将数据从Excel源提取到Sqlserver数据库。

但执行包后数据无法加载到我的目标数据库中。

并且在此我执行包时没有得到任何警告和错误。

这里数据流任务和Excel源只有红色而Oledb目的地没有任何颜色。

在这里我从特定文件夹中获取了一个Excel（2007）文件。

在包中只使用了一个Excel源和Oledb目的地。

And我也想在这里采取Excel 2003文件而不是Excel 2007文件的差异



你能给我简单的答案.......？

解决方案

我没有得到你想要的，但我想你想要将数据从excel保存到sql。在这种情况下试试这个：





protected void btnupload_Click（object sender，EventArgs e）

{

string cont_name1 =;

cont_name1 = fucupl.FileName;

string path1 = Server.MapPath（excel /）;

fucupl.SaveAs（path1 + cont_name1）;

string FullPath1 = path1 + cont_name1;

foreach（GetExcelSheetNames（FullPath1）中的字符串表）

{

试试

{

if（Sheet.Contains（

））

{

OleDbConnection oconn = new OleDbConnection（@Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ FullPath1 +; Extended Properties = Excel 8.0;）;

OleDbCommand ocmd = new OleDbCommand（SELECT * from [+ Sheet +]，oconn）;

oconn.Open（） ;

OleDbDataReader odr = ocmd.ExecuteReader（）;





string client_id =;

string client_name = ;

string client_address =;

string client_gender =;

string client_image_url =;

string client_qual =;

string phone_no =;

string email =;

string phone1 = ;

string email1 =;

string added_by =;







while（odr.Read（））

{



// Sr_no =有效（ odr，0）; //这里我们调用有效的方法

client_id =有效（odr，0）;

client_name =有效（odr，1）;

client_address =有效（odr，2）;

client_gender =有效（odr，3）;

client_image_url =有效（odr，4）;

client_qual =有效（odr，5）;

phone_no =有效（odr，6）;

email =有效（odr，7）;

phone1 =有效（odr，8）;

email1 =有效（odr，9）;

added_by =有效（odr，10）;



SerchNRemoveDouble（client_id，client_name，client_address ，client_gender，client_image_url，client_qual，phone_no，email，phone1，email1，added_by）;

}

}

}

catch

{



}

}

}



private void SerchNRemoveDouble（string client_id，string client_name，string client_address，string client_gender，string client_image_url，string client_qual，string phone_no，string email，string phone1，string email1，string added_by）

{

string constr1 = ConfigurationManager.ConnectionStrings [connstr]。ToString（）;

SqlConnection con1 = new SqlConnection（constr1）;

string sql = 插入客户端值（''+ client_id +''，''+ client_name +''，''+ client_address +''，''+ client_gender +''，''+ client_image_url +''，''+ client_qual +''，''+ phone_no +''，''+ email +''，''+ phone1 +''，''+ email1 +''，''+ added_by +''，''+ DateTime.Now.ToString（）+''）;

SqlCommand cmd1 = new Sq l命令（sql，con1）;

con1.Open（）;

cmd1.ExecuteNonQuery（）;



Response.Write（< script> alert（''Client Saved Successfully。''）< / script>）;

}

受保护的字符串有效（ OleDbDataReader myreader，int stval）//如果有任何列是

//找到null然后它们被替换为零

{

object val = myreader [stval];

if（val！= DBNull.Value）

返回val.ToString（）;

else

返回Convert.ToString（0）;

}

private String [] GetExcelSheetNames（string excelFile）

{



OleDbConnection oconn = new OleDbConnection（@Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ excelFile +; Extended Properties = Excel 8.0;）;

// OleDbConnection objConn = null;

System.Data.DataTa ble dt = null;



尝试

{

//连接字符串。将excel文件更改为

//将搜索的文件。



//使用前面的连接字符串创建连接对象。

// objConn = new OleDbConnection（oconn）;

//打开与数据库的连接。

oconn.Open（）;

//获取包含模式guid的数据表。

dt = oconn.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，null）;



if（dt == null）

{

返回null;

}



String [] excelSheets = new String [dt.Rows.Count];

int i = 0;



/ /将工作表名称添加到字符串数组。

foreach（dt.Rows中的DataRow行）

{

excelSheets [i] = row [TABLE_NAME]。ToString（）;

i ++;

}



//如果你也需要遍历所有表格...

// for（int j = 0; j< excelSheets.Length; j ++）

// {

// //查询每个excel表。

//}



返回excelSheets;

}

catch（例外情况）

{

返回null;

}

终于

{

//清理。

if（oconn！= null）

{

oconn.Close（）;

oconn.Dispose（）;

}

if（dt！= null）

{

dt.Dispose（）;

}

}

}

嗨......

类型在mysql cmd提示符下面。这是还有一个用于将excel表数据插入数据库。

看到这可能对你有帮助。



>>加载数据本地infile' filefulpath '进入table database.tabalename 以'，'括起来的字段'''以'\\ n'结尾的行包围;



谢谢你。

Hai!

This is madhu.
actually i created one package for pull the data from Excel source to Sqlserver database.
but after executing the package data could not loaded into my target database.
and in this i did not get any warnings and Errors while executing the package .
here dataflow task and Excel source got red color only and Oledb destination didn''t get any color.
In this i took one Excel(2007) file from particularly folder.
In the package took one Excel source and Oledb destination only.
And also i want here difference taking Excel 2003 file instead of Excel 2007 file

can u please give me brief answers .......?

解决方案

I did not get exactly what u want, but i guess u want to save data from excel to sql. In that case try this:


protected void btnupload_Click(object sender, EventArgs e)
{
string cont_name1 = "";
cont_name1 = fucupl.FileName;
string path1 = Server.MapPath("excel/");
fucupl.SaveAs(path1 + cont_name1);
string FullPath1 = path1 + cont_name1;
foreach (string Sheet in GetExcelSheetNames(FullPath1))
{
try
{
if (Sheet.Contains("

"))
{
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FullPath1 + ";Extended Properties=Excel 8.0;");
OleDbCommand ocmd = new OleDbCommand("SELECT * from [" + Sheet + "]", oconn);
oconn.Open();
OleDbDataReader odr = ocmd.ExecuteReader();


string client_id = "";
string client_name = "";
string client_address = "";
string client_gender = "";
string client_image_url = "";
string client_qual = "";
string phone_no = "";
string email = "";
string phone1 = "";
string email1 = "";
string added_by = "";



while (odr.Read())
{

//Sr_no = valid(odr, 0);//Here we are calling the valid method
client_id = valid(odr, 0);
client_name = valid(odr, 1);
client_address = valid(odr, 2);
client_gender = valid(odr, 3);
client_image_url = valid(odr, 4);
client_qual = valid(odr, 5);
phone_no = valid(odr, 6);
email = valid(odr, 7);
phone1 = valid(odr, 8);
email1 = valid(odr, 9);
added_by = valid(odr, 10);

SerchNRemoveDouble(client_id, client_name, client_address, client_gender, client_image_url, client_qual, phone_no, email, phone1, email1, added_by);
}
}
}
catch
{

}
}
}

private void SerchNRemoveDouble(string client_id,string client_name,string client_address,string client_gender,string client_image_url,string client_qual,string phone_no,string email,string phone1,string email1,string added_by)
{
string constr1 = ConfigurationManager.ConnectionStrings["connstr"].ToString();
SqlConnection con1 = new SqlConnection(constr1);
string sql = "insert into client values(''" + client_id + "'',''" + client_name + "'',''" + client_address + "'',''" + client_gender + "'',''" + client_image_url + "'',''" + client_qual + "'',''" + phone_no + "'',''" + email + "'',''" + phone1 + "'',''"+email1+"'',''" + added_by + "'',''" + DateTime.Now.ToString()+ "'') ";
SqlCommand cmd1 = new SqlCommand(sql, con1);
con1.Open();
cmd1.ExecuteNonQuery();

Response.Write("<script>alert(''Client Saved Successfully.'')</script>");
}
protected string valid(OleDbDataReader myreader, int stval)//if any columns are
//found null then they are replaced by zero
{
object val = myreader[stval];
if (val != DBNull.Value)
return val.ToString();
else
return Convert.ToString(0);
}
private String[] GetExcelSheetNames(string excelFile)
{

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;");
//OleDbConnection objConn = null;
System.Data.DataTable dt = null;

try
{
// Connection String. Change the excel file to the file you
// will search.

// Create connection object by using the preceding connection string.
//objConn = new OleDbConnection(oconn);
// Open connection with the database.
oconn.Open();
// Get the data table containg the schema guid.
dt = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt == null)
{
return null;
}

String[] excelSheets = new String[dt.Rows.Count];
int i = 0;

// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}

// Loop through all of the sheets if you want too...
//for (int j = 0; j < excelSheets.Length; j++)
//{
// // Query each excel sheet.
//}

return excelSheets;
}
catch (Exception ex)
{
return null;
}
finally
{
// Clean up.
if (oconn != null)
{
oconn.Close();
oconn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}

Hi...
type below in mysql cmd prompt.this is also one for inserting excel sheet data into databse.
see this may helpful to u.

>>Load data local infile ‘filefulpath’ into table database.tabalename fields terminated by ‘,’ enclosed by ‘"’ lines terminated by ‘\n’;

thank u.

这篇关于我想将数据从Excel源提取到Sqlserver数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！