SQL excel 檔案查詢以及定序問題的解決

今天 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目錄
回首頁