匯入 CSV 檔案到 SQL Server 的完整步驟 (使用 BULK INSERT)

本文链接：https://blog.csdn.net/m0_56816903/article/details/146395295

前言

不曉得為啥一直沒辦法直接使用匯入精靈直接匯入.csv到我的資料庫裏面在資料表正常顯示出來資料的我，再爬了許多文章以後改用BULK INSERT才終於成功，故藉此紀錄一下，以防下次又忘記。

步驟 1：確保 CSV 檔案格式正確

1. CSV 檔案必須使用 UTF-8 編碼，以避免中文亂碼。

２. 每一列的欄位數必須與 SQL 資料表一致（不能多也不能少）。

3. 使用 逗號 (,) 作為欄位分隔符。

4. 第一行是欄位名稱（可選，若有則需在 BULK INSERT 中設定 FIRSTROW 跳過）。

範例:

步驟 2：確保 SQL Server 有權限讀取檔案

1.建議將 .CSV 檔案放置在 *C:\temp* 資料夾，這是較安全的路徑(自己在C槽創一個暫存資料夾來放)

2.按下 Win + S 輸入 cmd，在「命令提示字元」上按右鍵，選擇「以系統管理員身分執行」：

icacls C:\temp /grant Everyone:F
icacls C:\temp /grant "NT SERVICE\MSSQLSERVER":F

確保 SQL Server 服務帳戶有權限存取檔案

步驟 3：確保 SQL Server 允許 BULK INSERT

1.在 SSMS 中執行以下指令，啟用 BULK INSERT 功能：

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

步驟 4：建立 SQL Server 資料表

1. 確保 StudentData 表格結構正確，欄位類型需與 CSV 資料匹配。

2. 以下為範例:

CREATE TABLE StudentData (
    AcademicYear INT,                    -- 學年度 (數字)
    SchoolCode INT,                      -- 學校代碼 (數字)
    SchoolName NVARCHAR(100),            -- 學校名稱 (中文/英文)
    DepartmentCode NVARCHAR(50),         -- 科系代碼 (可能含字母，設為字串)
    DepartmentName NVARCHAR(255),        -- 科系名稱 (中文/英文，長度加大)
    DayOrEvening NVARCHAR(10),           -- 日間/進修別 (中文)
    DegreeLevel NVARCHAR(20),            -- 等級別 (中文/英文)
    TotalStudents INT,                   -- 總計 (數字)
    MaleStudents INT,                    -- 男生計 (數字)
    FemaleStudents INT,                  -- 女生計 (數字)
    ChinesePrepMale INT,                 -- 華語先修生男生 (數字)
    ChinesePrepFemale INT,               -- 華語先修生女生 (數字)
    FirstYearMale INT,                   -- 一年級男生 (數字)
    FirstYearFemale INT,                 -- 一年級女生 (數字)
    SecondYearMale INT,                  -- 二年級男生 (數字)
    SecondYearFemale INT,                -- 二年級女生 (數字)
    ThirdYearMale INT,                   -- 三年級男生 (數字)
    ThirdYearFemale INT,                 -- 三年級女生 (數字)
    FourthYearMale INT,                  -- 四年級男生 (數字)
    FourthYearFemale INT,                -- 四年級女生 (數字)
    FifthYearMale INT,                   -- 五年級男生 (數字)
    FifthYearFemale INT,                 -- 五年級女生 (數字)
    SixthYearMale INT,                   -- 六年級男生 (數字)
    SixthYearFemale INT,                 -- 六年級女生 (數字)
    SeventhYearMale INT,                 -- 七年級男生 (數字)
    SeventhYearFemale INT,               -- 七年級女生 (數字)
    ExtendedStudyMale INT,               -- 延修生男生 (數字)
    ExtendedStudyFemale INT,             -- 延修生女生 (數字)
    CountyName NVARCHAR(255),            -- 縣市名稱 (中文)
    SystemType NVARCHAR(255)             -- 體系別 (中文/英文)
);

*如果不確定資料最長有多少字數，可以替換成max

步驟 5：執行 BULK INSERT 匯入 CSV

1.使用以下 BULK INSERT 指令匯入資料：

BULK INSERT StudentData
FROM '路徑+您的.csv檔案'
WITH (
    FORMAT = 'CSV',
    FIRSTROW = 2,                 -- 從第二行開始（跳過標題）
    FIELDTERMINATOR = ',',        -- 欄位分隔符號
    ROWTERMINATOR = '0x0A',       -- 換行符號 (LF，避免 CRLF 問題)
    CODEPAGE = '65001',           -- 使用 UTF-8 避免亂碼
    TABLOCK
);

步驟 6：(if 成功)檢查匯入結果

1.檢查資料是否正確

SELECT TOP 10 * FROM 您的資料表;

2.如果有問題，可以清空資料表以後重新匯入

DELETE FROM 您的資料表;

步驟6: (else )常見錯誤與解決方法

可能狀況一(常見): 大量載入失敗，資料行太長

1.CSV 檔案的欄位數量與 SQL 資料表不一致。-> 確認 CSV 檔案的欄位數量與資料表一致

2.某些欄位的資料過長，超過欄位定義的長度。-> 檢查哪邊過長，若過長則調整欄位長度

可能狀況二：OLE DB 提供者 "BULK" 取得所需的介面 ("IID_IColumnsInfo")

原因: SQL Server 未啟用 Ad Hoc Distributed Queries，導致 BULK INSERT 無法執行。

解法: 執行以下指令啟用 (記得一行一行執行，不要太懶!!!)

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

可能狀況三：SQL Server 沒有讀取 CSV 檔案的權限

原因: 你的SQL Server 服務帳戶沒有權限存取 CSV 檔案。

解法:

1.確保 CSV 檔案放置在正確路徑的資料夾。

2.在命令提示字元中執行以下指令，授予權限:

icacls 您的檔案路徑 /grant Everyone:F
icacls 您的檔案路徑 /grant "NT SERVICE\MSSQLSERVER":F

*處理完狀況1、2、3 之後，你需要重新執行 `BULK INSERT` 指令(步驟5)，因為前面發生的錯誤導致資料沒有成功匯入。

1.確認 SQL Server 服務是否有讀取 您檔案路徑 的權限

-> 如果執行過 icacls 指令，確保權限設定已成功：

icacls C:\temp /grant Everyone:F
icacls C:\temp /grant "NT SERVICE\MSSQLSERVER":F

2.確認 SQL Server 已啟用 Ad Hoc Distributed Queries

->如果你之前還沒執行，請執行：

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

3.執行 BULK INSERT 重新載入資料:

BULK INSERT StudentData
FROM '路徑+您的.csv檔案'
WITH (
    FORMAT = 'CSV',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  -- 以逗號分隔
    ROWTERMINATOR = '0x0A',  -- 避免 CRLF 問題
    CODEPAGE = '65001',  -- 確保 UTF-8 相容
    TABLOCK
);

* 如果前面有刪除舊的資料，建議先檢查資料表是否為空：

SELECT COUNT(*) FROM 資料表;

* 如果還有舊資料，可以先刪除所有資料再重新匯入：

DELETE FROM 資料表;

總結:成功匯入 CSV 的完整流程

準備 CSV 檔案：
- 確保使用 UTF-8 編碼。
- 欄位數量與 SQL 資料表一致。
確保 SQL Server 有權限讀取檔案：
- 將檔案放置在 .csv檔案存放路徑(EX: C:\temp\)，並使用 icacls 授予權限。
確保 BULK INSERT 允許執行：
- 執行 sp_configure 啟用 Ad Hoc Distributed Queries。
建立正確的資料表結構：
- 例如: 檢查 DepartmentCode 是否為 NVARCHAR(50)。->因為不一定全部都是數字
- 確保 DepartmentName、CountyName、SystemType 長度足夠（例如 NVARCHAR(255)）。->有些科系名字特別長
執行 BULK INSERT 匯入 CSV：
- 使用正確的 CODEPAGE 和 ROWTERMINATOR 設定。
檢查資料是否正確：
- 使用 SELECT TOP 10 * FROM 您的資料表; 驗證。