今天 User 給我一個 excel，要求我不但將實體檔案做更名，連同資料庫紀錄的路徑也一起更新。
此時就必須做到 excel 檔案與實體資料表的查詢。
我利用 OPENROWSET 語法將 excel 資料轉成資料表，例如以下程式碼：
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=檔案位置;HDR=YES;IMEX=1','SELECT * FROM [Sheet1$]') AS O
INNER JOIN Product P ON P.ID = O.ID
利用實體檔案 ( excel ) 與檔案資料表 ( *.xls ) 的關聯欄位做合併查詢 ( INNER JOIN )，然後就出現以下錯誤訊息：
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Chinese_Taiwan_Stroke_CI_AS" in the equal to operation.
後來 google 一下才知道這是定序的問題。
最後只需要在欄位後面加上 collate Chinese_Taiwan_Stroke_CI_AS，對應到另一個資料表的定序即可。
2012/06/18 AM 10:28 KaiYai 補充：
下面KaiYai也來提供一下最近遇到的excel與資料庫問題
問題：從excel篩選出所需資料，做一些運算後Insert到資料庫
解法：
首先先做篩選與運算
我們將excel當成一個資料庫來使用(產生ConnectionString)
對其下SQL語法篩選資料
以下提供一個class
public class ReadExcel
{
/// <summary>
/// 讀取Excel資料
/// </summary>
/// <param name="File">Excel完整路徑</param>
/// <param name="strCommand">SQL語法</param>
public DataTable ReadExcelFile(string File, string strCommand)
{
DataTable dt = new DataTable();
string sConnectionString = "";
if (File.Substring(File.Length - 4, 4) == "xlsx")
{
sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + File + ";Persist Security Info=False;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
}
if (File.Substring(File.Length - 3, 3) == "xls")
{
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + File + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
}
if (sConnectionString != "")
{
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand(strCommand, objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
try
{
objAdapter1.Fill(objDataset1, "myExcel");
dt = objDataset1.Tables["myExcel"];
}
catch
{
}
finally
{
objConn.Close();
}
}
return dt;
}
}
接著要將資料傳到SQL SERVER
這邊我們使用預存類型與預存程序
預存類型：
USE [DataBase]
GO
CREATE TYPE [teb] AS TABLE(
[No] [int],
[Name] [nvarchar](20),
[Address] [nvarchar](max),
[Age] [int]
)
GO
預存程序：
USE [DataBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [DataTableUpload]
@table teb READONLY --宣告一個teb，後面一定要有READONLY
AS
insert into Member([No],[Name],[Address],[Age])
select [No],[Name],[Address],[Age]
from @table
GO
在使用上面，我是習慣用SQLObject
SQLObject obj = new SQLObject(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
SqlParameter[] pm = new SqlParameter[1];
pm[0] = new SqlParameter("@table", dt);
obj.ExeProcNon("DataTableUpload", pm);
這樣就大功告成了
回sql目錄
回首頁
