Bỏ qua nội dung chính
Trình duyệt này không còn được hỗ trợ.
Nâng cấp lên Microsoft Edge để tận dụng các tính năng mới nhất, cập nhật bảo mật và hỗ trợ kỹ thuật.
Nhập dữ liệu từ Excel vào SQL Server hoặc Azure SQL cơ sở dữ liệu
- Bài báo
- 16/16/2022
- 9 phút để đọc
Trong bài viết này
Áp dụng cho: SQL Server [tất cả các phiên bản được hỗ trợ] Cơ sở dữ liệu Azure SQL
Có một số cách để nhập dữ liệu từ các tệp Excel vào SQL Server hoặc vào cơ sở dữ liệu Azure SQL. Một số phương thức cho phép bạn nhập dữ liệu trong một bước trực tiếp từ các tệp Excel; Các phương thức khác yêu cầu bạn xuất dữ liệu Excel của mình dưới dạng văn bản [tệp CSV] trước khi bạn có thể nhập nó.
Bài viết này tóm tắt các phương thức được sử dụng thường xuyên và cung cấp các liên kết để biết thêm thông tin chi tiết. Một mô tả đầy đủ về các công cụ và dịch vụ phức tạp như SSIS hoặc Azure Data Factory nằm ngoài phạm vi của bài viết này. Để tìm hiểu thêm về giải pháp mà bạn quan tâm, hãy làm theo các liên kết được cung cấp.
Danh sách các phương pháp
Có một số phương pháp để nhập dữ liệu từ Excel. Bạn có thể cần cài đặt SQL Server Management Studio [SSMS] để sử dụng một số công cụ này.
Bạn có thể sử dụng các công cụ sau để nhập dữ liệu từ Excel:
| Nhập trình hướng dẫn tệp phẳng | Trình hướng dẫn nhập và xuất SQL Server |
| Tuyên bố chèn số lượng lớn | Dịch vụ tích hợp SQL Server [SSIS] |
| BCP | Hàm OpenRowset |
| Sao chép Wizard [Nhà máy dữ liệu Azure] | |
| Nhà máy dữ liệu Azure |
Nếu bạn muốn nhập nhiều bảng tính từ một sổ làm việc Excel, bạn thường phải chạy bất kỳ công cụ nào trong số này một lần cho mỗi tờ.
Trình hướng dẫn nhập và xuất khẩu
Nhập dữ liệu trực tiếp từ các tệp Excel bằng cách sử dụng trình hướng dẫn nhập và xuất nhập SQL Server. Bạn cũng có tùy chọn để lưu cài đặt dưới dạng gói Dịch vụ tích hợp SQL Server [SSIS] mà bạn có thể tùy chỉnh và sử dụng lại sau.
Trong SQL Server Management Studio, kết nối với một thể hiện của công cụ cơ sở dữ liệu SQL Server.
Mở rộng cơ sở dữ liệu.Databases.
Nhấp chuột phải vào cơ sở dữ liệu.
Chỉ vào các nhiệm vụ.Tasks.
Chọn nhập dữ liệu hoặc xuất dữ liệu:Import Data or Export Data:
Điều này ra mắt trình hướng dẫn:
Để tìm hiểu thêm, hãy xem lại:
- Khởi động Trình hướng dẫn nhập và xuất SQL Server
- Bắt đầu với ví dụ đơn giản này về trình hướng dẫn nhập và xuất khẩu
Nhập dữ liệu từ Excel hoặc Xuất dữ liệu sang Excel với Dịch vụ tích hợp SQL Server [SSIS]
Tiện ích BCP
Để tìm hiểu thêm, hãy xem lại:
- Nguồn Excel
- Điểm đến SQL Server
Để bắt đầu học cách xây dựng các gói SSIS, hãy xem hướng dẫn cách tạo gói ETL.
OpenRowset và máy chủ được liên kết
Ghi chú
Nhà cung cấp ACE [trước đây là nhà cung cấp máy bay phản lực] kết nối với các nguồn dữ liệu Excel được dành cho việc sử dụng phía máy khách tương tác. Nếu bạn sử dụng nhà cung cấp ACE trên SQL Server, đặc biệt là trong các quy trình hoặc quy trình tự động chạy song song, bạn có thể thấy kết quả không mong muốn.
Truy vấn phân phối
Nhập dữ liệu trực tiếp vào SQL Server từ các tệp Excel bằng cách sử dụng hàm Transact-SQL
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
Trước khi bạn có thể chạy truy vấn phân tán, bạn phải bật tùy chọn cấu hình máy chủ
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Mẫu mã sau sử dụng
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
Đây là ví dụ tương tự với
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE['Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0']...[Sheet1$];
GO
Để nối dữ liệu đã nhập vào bảng hiện có thay vì tạo một bảng mới, hãy sử dụng cú pháp
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE['Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0']...[Sheet1$];
GO
Để truy vấn dữ liệu Excel mà không cần nhập nó, chỉ cần sử dụng cú pháp
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE['Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0']...[Sheet1$];
GO
Để biết thêm thông tin về các truy vấn phân tán, hãy xem các chủ đề sau:
- Các truy vấn phân tán [các truy vấn phân tán vẫn được hỗ trợ trong SQL Server 2019, nhưng tài liệu cho tính năng này chưa được cập nhật.]
- OpenRowset
- OpenDataSource
Máy chủ liên kết
Bạn cũng có thể định cấu hình kết nối liên tục từ SQL Server đến tệp Excel dưới dạng máy chủ được liên kết. Ví dụ sau đây nhập dữ liệu từ bảng tính
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE['Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0']...[Sheet1$];
GO
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE['Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0']...[Sheet1$];
GO
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE['Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0']...[Sheet1$];
GO
USE ImportFromExcel;
GO
SELECT * INTO Data_ls FROM EXCELLINK...[Data$];
GO
Bạn có thể tạo một máy chủ được liên kết từ SQL Server Management Studio hoặc bằng cách chạy quy trình được lưu trữ hệ thống
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE['Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0']...[Sheet1$];
GO
DECLARE @RC int
DECLARE @server nvarchar[128]
DECLARE @srvproduct nvarchar[128]
DECLARE @provider nvarchar[128]
DECLARE @datasrc nvarchar[4000]
DECLARE @location nvarchar[4000]
DECLARE @provstr nvarchar[4000]
DECLARE @catalog nvarchar[128]
-- Set parameter values
SET @server = 'EXCELLINK'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.ACE.OLEDB.12.0'
SET @datasrc = 'C:\Temp\Data.xlsx'
SET @provstr = 'Excel 12.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog
Để biết thêm thông tin về các máy chủ được liên kết, hãy xem các chủ đề sau:
- Tạo máy chủ được liên kết
- OpenQuery
Để biết thêm các ví dụ và thông tin về cả máy chủ được liên kết và truy vấn phân tán, hãy xem các chủ đề sau:
- Cách sử dụng Excel với các máy chủ liên kết SQL Server và các truy vấn phân tán
- Cách nhập dữ liệu từ Excel vào SQL Server
Điều kiện tiên quyết - Lưu dữ liệu Excel dưới dạng văn bản
Để sử dụng phần còn lại của các phương thức được mô tả trên trang này - câu lệnh chèn số lượng lớn, công cụ BCP hoặc nhà máy dữ liệu Azure - trước tiên bạn phải xuất dữ liệu Excel của mình sang tệp văn bản.
Trong Excel, chọn Tệp | Lưu dưới dạng và sau đó chọn Text [Tab-Delimited] [*.txt] hoặc CSV [dấu phẩy-Delimited] [*.csv] làm loại tệp đích.File | Save As and then select Text [Tab-delimited] [*.txt] or CSV [Comma-delimited] [*.csv] as the destination file type.
Nếu bạn muốn xuất nhiều bảng tính từ sổ làm việc, hãy chọn từng tờ và sau đó lặp lại quy trình này. Lệnh lưu chỉ xuất xuất bảng hoạt động.Save as command exports only the active sheet.
Mẹo
Để có kết quả tốt nhất với các công cụ nhập dữ liệu, hãy lưu các bảng chỉ chứa các tiêu đề cột và các hàng dữ liệu. Nếu dữ liệu đã lưu chứa các tiêu đề trang, các dòng trống, ghi chú, v.v., bạn có thể thấy kết quả bất ngờ sau khi bạn nhập dữ liệu.
Nhập trình hướng dẫn tệp phẳng
Nhập dữ liệu được lưu dưới dạng tệp văn bản bằng cách bước qua các trang của Trình hướng dẫn tệp phẳng nhập.
Như được mô tả trước đây trong phần Điều kiện tiên quyết, bạn phải xuất dữ liệu Excel của mình dưới dạng văn bản trước khi bạn có thể sử dụng Trình hướng dẫn tệp Flat Interpress để nhập nó.
Để biết thêm thông tin về Trình hướng dẫn tệp phẳng nhập, hãy xem Nhập tệp phẳng vào SQL Wizard.
Lệnh chèn số lượng lớn
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE['Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0']...[Sheet1$];
GO
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE['Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0']...[Sheet1$];
GO
Như được mô tả trước đây trong phần Điều kiện tiên quyết, bạn phải xuất dữ liệu Excel của mình dưới dạng văn bản trước khi bạn có thể sử dụng Chèn số lượng lớn để nhập nó. Chèn số lượng lớn không thể đọc trực tiếp các tệp excel. Với lệnh chèn số lượng lớn, bạn có thể nhập một tệp CSV được lưu trữ cục bộ hoặc trong bộ nhớ Azure Blob.
USE ImportFromExcel;
GO
BULK INSERT Data_bi FROM 'C:\Temp\data.csv'
WITH [
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
];
GO
Để biết thêm thông tin và ví dụ cho cơ sở dữ liệu SQL Server và SQL, hãy xem các chủ đề sau:
- Nhập dữ liệu số lượng lớn bằng cách sử dụng Chèn số lượng lớn hoặc OpenRowset [số lượng lớn ...]
- Chèn số lượng lớn
Công cụ BCP
BCP là một chương trình mà bạn chạy từ dấu nhắc lệnh. Ví dụ sau tải dữ liệu từ tệp được phân phối bằng dấu phẩy vào bảng cơ sở dữ liệu
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE['Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0']...[Sheet1$];
GO
Như được mô tả trước đây trong phần Điều kiện tiên quyết, bạn phải xuất dữ liệu Excel của mình dưới dạng văn bản trước khi bạn có thể sử dụng BCP để nhập nó. BCP không thể đọc trực tiếp các tệp excel. Sử dụng để nhập vào cơ sở dữ liệu SQL Server hoặc SQL từ tệp thử nghiệm [CSV] được lưu vào bộ lưu trữ cục bộ.
Quan trọng
Đối với tệp văn bản [CSV] được lưu trữ trong bộ lưu trữ Azure Blob, sử dụng Chèn số lượng lớn hoặc OpenRowset. Để biết ví dụ, xem ví dụ.
bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,
Để biết thêm thông tin về BCP, hãy xem các chủ đề sau:
- Nhập và xuất dữ liệu hàng loạt bằng cách sử dụng tiện ích BCP
- Tiện ích BCP
- Chuẩn bị dữ liệu để xuất hoặc nhập khẩu số lượng lớn
Sao chép Wizard [ADF]
Nhập dữ liệu được lưu dưới dạng tệp văn bản bằng cách bước qua các trang của trình hướng dẫn sao chép Azure Data Factory [ADF].
Như được mô tả trước đây trong phần Điều kiện tiên quyết, bạn phải xuất dữ liệu Excel của mình dưới dạng văn bản trước khi bạn có thể sử dụng Nhà máy dữ liệu Azure để nhập nó. Nhà máy dữ liệu không thể đọc trực tiếp các tệp excel.
Để biết thêm thông tin về trình hướng dẫn bản sao, hãy xem các chủ đề sau:
- Trình hướng dẫn bản sao nhà máy dữ liệu
- Hướng dẫn: Tạo một đường ống với hoạt động sao chép bằng cách sử dụng Wizard sao chép Data Factory.
Nhà máy dữ liệu Azure
Nếu bạn quen thuộc với Azure Data Factory và không muốn chạy trình hướng dẫn sao chép, hãy tạo một đường ống với hoạt động sao chép sao chép từ tệp văn bản sang SQL Server hoặc đến cơ sở dữ liệu Azure SQL.
Như được mô tả trước đây trong phần Điều kiện tiên quyết, bạn phải xuất dữ liệu Excel của mình dưới dạng văn bản trước khi bạn có thể sử dụng Nhà máy dữ liệu Azure để nhập nó. Nhà máy dữ liệu không thể đọc trực tiếp các tệp excel.
Để biết thêm thông tin về trình hướng dẫn bản sao, hãy xem các chủ đề sau:
- Trình hướng dẫn bản sao nhà máy dữ liệu
- Hướng dẫn: Tạo một đường ống với hoạt động sao chép bằng cách sử dụng Wizard sao chép Data Factory.
- Nhà máy dữ liệu Azure
Nếu bạn quen thuộc với Azure Data Factory và không muốn chạy trình hướng dẫn sao chép, hãy tạo một đường ống với hoạt động sao chép sao chép từ tệp văn bản sang SQL Server hoặc đến cơ sở dữ liệu Azure SQL.
- Để biết thêm thông tin về việc sử dụng các nguồn và bồn rửa dữ liệu này, hãy xem các chủ đề sau:
- Hệ thống tập tin
Máy chủ SQL
Cơ sở dữ liệu Azure SQL
Để bắt đầu học cách sao chép dữ liệu với nhà máy dữ liệu Azure, hãy xem các chủ đề sau:
Di chuyển dữ liệu bằng cách sử dụng hoạt động sao chép
Msg 7403, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
Hướng dẫn: Tạo một đường ống với hoạt động sao chép bằng cổng thông tin Azure
Lỗi thông thường
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
Di chuyển dữ liệu bằng cách sử dụng hoạt động sao chép
Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[null]".
Hướng dẫn: Tạo một đường ống với hoạt động sao chép bằng cổng thông tin Azure
Lỗi thông thường
Di chuyển dữ liệu bằng cách sử dụng hoạt động sao chép
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
Hướng dẫn: Tạo một đường ống với hoạt động sao chép bằng cổng thông tin Azure
Lỗi thông thường
Microsoft.ace.oledb.12.0 "chưa được đăng ký
Lỗi này xảy ra vì nhà cung cấp OLEDB không được cài đặt. Cài đặt nó từ Microsoft Access DataBase Engine 2010 Phân phối lại. Hãy chắc chắn cài đặt phiên bản 64 bit nếu Windows và SQL Server đều là 64 bit.
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO
Lỗi đầy đủ là:
- Không thể tạo một thể hiện của nhà cung cấp OLE DB "Microsoft.ace.oledb.12.0" cho máy chủ được liên kết "[NULL]"
- Điều này chỉ ra rằng Microsoft OLEDB chưa được cấu hình đúng. Chạy mã Transact-SQL sau để giải quyết điều này:
- Tiện ích BCP
- Để biết thêm thông tin về việc sử dụng các nguồn và bồn rửa dữ liệu này, hãy xem các chủ đề sau:
Hệ thống tập tin
Máy chủ SQL