課程名稱： 資料庫系統

9. 資料匯入

Properties3
description—
tagsmssql
date2026-06-28

閲覽人數 -- 次

1. 前言

1.1 專案開發說明

在專案開發過程中可能需要經過 招標過程 及 專案開發 過程，其中有一個環結是 舊系統資料轉檔 及 新系統開帳 過程，在此可能需要進行資料匯入的程序，因此學會資料匯入的技術是有必要的。

1.2 專案招標流程

專案招標流程

image

一、招標準備階段（業主端）

步驟項目內容項目說明
1預算編列與核定各政府機關需先依計畫或業務需要提報預算，經審查後由主管機關核定。
2撰寫招標文件（RFP）包括 工作範疇（SOW）、技術需求、交付項目、時程、付款方式 等。也會納入投標須知與契約草案。
3決定招標方式常見有： - 公開招標 - 限制性招標 - 選擇性招標 - 單一來源招標（特殊情況）
4發布招標公告上傳至 政府電子採購網 並公布政府公報或相關媒體。

二、投標階段（廠商端）

步驟項目內容項目說明
1資料下載與評估廠商可下載 RFP 文件與相關附件，分析是否符合條件與有投標意願。
2投標文件準備包含： - 技術建議書 - 商業報價書 - 公司資格證明文件（登記、資本額、財務報表等） - 履約經驗與人員配置
3提交投標文件須於公告截止日前送達政府機關（多採用線上與紙本並行）。
4投標保證金繳納視案量而定，一般為投標總金額的 1%~5%，可為保證支票或銀行保函。

三、開標與資格審查

步驟項目內容項目說明
1開標依公告時間開標，公開開啟所有投標文件，紀錄投標廠商與報價。
2資格審查確認是否符合投標資格，包括： - 公司登記 - 財務狀況 - 是否列入拒絕往來廠商名單
3文件初審與技術評選評選委員會審查 技術提案、履約能力、 人員資格 等。（如為最有利標，會按技術+價格加權）

四、決標階段

步驟項目內容項目說明
1評選與評分- 最低標：價低者得標 - 最有利標：依評分標準綜合比較後由評選委員會決定得標者
2決標公告通常會公告得標廠商名稱、金額、標案名稱與評選結果。
3繳交履約保證金得標廠商須於期限內繳納（通常為契約金額 3%~10%）
4簽署合約得標廠商與客戶簽署合約。

1.3 專案開發流程

專案招標流程

image

2. 資料匯入

2.1 資料匯入說明

image

在數據工程的實務環境中，我們經常面臨一個核心挑戰：使用者提供的原始資料與資料庫所要求的嚴謹結構化定義（Schema）之間存在巨大鴻溝。為了確保系統穩定，資料整理 成為了串接前端輸入與後端儲存不可或缺的橋樑。雖然專業開發者可以使用 Python 或 ETL 工具處理，但對於大多數情境而言，使用 Excel 進行初步的清洗與格式化，是確保資料能夠 順利匯入 資料庫最直覺且低成本的高效方式。

image

透過 Excel，我們能在進入資料庫前完成資料校閱，大幅降低因格式錯誤導致的匯入失敗。接下來，我將帶領各位從最基礎的高效選取技巧開始，逐步建立起專業的資料處理工作流。

2.2 資料匯入方法

image

2.2.1 小量資料 匯入使用 剪貼簿

當資料量小於 1 萬筆以內，可以使用剪貼簿方式由 Excel 匯入至資料庫中。

2.2.2 大量資料 匯入使用 T-SQL BULK INSERT 指令

當資料量大於 1 萬筆以上，可以考慮使用 T-SQL BULK INSERT 指令匯入至資料庫中。

2.3 匯入範例檔案

Zip32_11208.zip 為郵政總局下載的郵遞區號原始壓縮檔

範例檔案下載連結

Zip32_11208.zip null https://drive.google.com/file/d/1GfkXeyLf2lokSQuHAHgFRKPfKjTbWFA0/view?usp=sharing

執行結果

image

2.4 表格結構

資料表格結構

image

2.5 SQL 語法

SQL 語法

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
DROP TABLE IF EXISTS [dbo].[ZipArea]
DROP TABLE IF EXISTS [dbo].[ZipCity]
DROP TABLE IF EXISTS [dbo].[ZipRoad]
GO
 
CREATE TABLE [dbo].[ZipArea](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CityNo] [nvarchar](50) NULL,
	[AreaNo] [nvarchar](50) NULL,
	[AreaName] [nvarchar](50) NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_ZipArea] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[ZipCity](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CityNo] [nvarchar](50) NULL,
	[CityName] [nvarchar](50) NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_ZipCity] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[ZipRoad](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CityNo] [nvarchar](50) NULL,
	[AreaNo] [nvarchar](50) NULL,
	[RoadName] [nvarchar](50) NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_ZipRoad] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

2.6 BULK INSERT 指令

image

SQL 語法

BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE =
      { 'char' | 'native' | 'widechar' | 'widenative' } ]
   [ [ , ] DATA_SOURCE = 'data_source_name' ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]
   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
   [ [ , ] LASTROW = last_row ]
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] TABLOCK ]
 
   -- input file format options
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
    )]

2.7 範例指令

image

image

SQL 語法

--先刪除所有的資料,TRUNCATE 後 Id 會由 1 開始編
TRUNCATE TABLE [dbo].[ZipRoad]
GO
 
--大量資料匯入使用 BULK INSERT 指令
--FIELDTERMINATOR = 指定欄位間的間隔方式
--ROWTERMINATOR = 指定記錄間的間隔方式, \n 為 Enter 斷行
--FIRSTROW = 指令由第幾筆記錄開始匯入
BULK INSERT [dbo].[ZipRoad] 
FROM N'd:\road.csv' 
WITH (
	FIELDTERMINATOR = N',',
	ROWTERMINATOR = '\n',
	FIRSTROW = 2
)

3. 大量資料使用指令方式匯入注意事項

3.1 匯入準備：欄位規則與檔案編碼

image

將 Excel 另存為 CSV 準備匯入前，請務必完成以下「最後一哩路」的查核，這能解決 90% 以上的匯入報錯。

萬事俱備後，我們將根據資料規模選擇最合適的匯入管道。

3.2 匯入的欄位需為所有欄位

📌 重點提示

  • 小量資料匯入：不需要準備 Id 自動編唬欄位的資料
  • 大量資料匯入：需要準備 Id 自動編唬欄位的資料
    匯入的欄位必須包含 Id 即自動編號欄位的 所有欄位 ，且欄位順序與表格建立欄位時的 順序一致。

範例資料

image

3.3 CSV 檔案編碼必須為 ANSI

若匯入時出現「資料被截斷」或亂碼，主因是編碼衝突。

📌 重點提示

請使用「記事本」開啟 CSV 檔案，點選「另存新檔」，將編碼格式選擇為 ANSI。這能確保舊式匯入引擎或 SQL 指令在處理多位元組文字時不發生解析錯誤。

操作方式

image

4. Excel 資料整理

4.1 移除重覆值

image

面對動輒數萬行的資料集，操作效率決定了你的專業深度。身為數據工程師，我們必須掌握精準的框選與資料清洗基礎。

操作技巧對照表

操作目標具體動作
快速區塊框選先選取欲選取範圍最頂端的一個儲存格，再按下 Ctrl + Shift + Down(向下鍵)
移除重複值進入功能區「資料」分頁，點選「移除重複項」

移除重複值標準步驟

  1. 完整框選欲處理的資料區域（或使用 Ctrl + A）。
  2. 點擊功能選單中的 資料 > 移除重複項。
  3. 在彈出的視窗中，務必確認勾選 「我的資料有標題」，這能確保標題列不被視為重複內容而遭誤刪。

在確保資料的唯一性與純淨度後，下一步我們需要為這些資料建立符合資料庫規範的識別碼與結構化欄位。

操作方式

4.2 區塊框選

image

操作方式

4.3 產生文字類型的流水號

資料庫在匯入時對格式極其敏感。例如「01」這類編碼，若不刻意處理，Excel 常會自動將其轉為數字「1」，導致資料與原始意義不符。

產生「文字類型」流水號

為了防止 Excel 誤判資料類型（如：刪除前導零或將長數字轉為科學記號），請遵循以下輸入規則：

'01

資深架構師筆記：在數字前輸入單引號 ’ 會強制將其定義為文字格式，這對於保存如身分證字號、行政區碼等資料至關重要。

文字相加技巧

image

若需將分散欄位合併（例如將「縣市」與「路名」合併），請使用 & 連結符號：

=A2 & B2

💡 自動填滿的高效操作小撇步

當你設定好第一個儲存格的公式或編號後，無需辛苦拖曳滑鼠。只需將滑鼠移至儲存格右下角的「黑十字」填滿把手，快速點擊左鍵兩下，Excel 就會自動偵測左側欄位的資料長度，精準填滿整欄。

當基礎欄位準備就緒，接下來我們要運用 Excel 最強大的關聯功能，模擬資料庫中的 Join 邏輯。

操作方式

4.4 文字相加

文字相加用 & 符號

操作方式

4.5 使用 VLOOKUP 函數

在資料預處理階段，VLOOKUP 是實現資料關聯的靈魂工具。例如：你擁有一份「路名資料」，但資料庫需要對應的「縣市代碼」，此時就必須透過 VLOOKUP 進行對應。

當資料內容經由公式補齊後，最後一步是確保匯出格式符合資料庫的匯入規範。

操作方式

image

⚠️ 注意事項

查詢範圍的邏輯安排  在使用此函數時，「要查詢的文字」 必須位在來源範圍的 最左欄。
例如：你想用「縣市名稱」查「縣市代號」，則來源表中「縣市名稱」必須在左側，「代號」在右側。
要參照的區域需要設定成 要查詢的文字為左欄 要取得的文字為右欄，如要用縣市名稱查縣市代號，則縣市名稱需左方，縣市代號放右方，

ℹ️ VLOOKUP 四大參數拆解

  • Lookup_value (查詢對象)：你想比對的原始資料（例如：台北市）。
  • Table_array (查詢範圍)：查找資料的來源範圍。
  • Col_index_num (取值欄位)：找到對應資料後，要取回該範圍中的第幾欄？
  • Range_lookup (尋找類型)：請務必輸入 FALSE（或 0），代表執行「完全一致」的精確搜尋。

操作方式

image

📌 重點提示

絕對參照與 F4 快捷鍵  為防止公式在下拉填滿時範圍發生位移，必須在 Table_array 使用 CB$25。你可以直接選取範圍後按下 F4 鍵，Excel 會自動幫你加上所有固定符號。

關係圖譜