Hướng dẫn how do you insert data from an excel sheet into a database table? - làm thế nào để bạn chèn dữ liệu từ một trang tính excel vào một bảng cơ sở dữ liệu?

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

SQL Server [all supported versions] Azure SQL Database

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:

Xuất văn bản đầu tiên [SQL Server và cơ sở dữ liệu SQL]Trực tiếp từ excel [chỉ trên máy chủ SQL]

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.

1. 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.

2. Mở rộng cơ sở dữ liệu.Databases.

3. Nhấp chuột phải vào cơ sở dữ liệu.

4. Chỉ vào các nhiệm vụ.Tasks.

5. 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:

• Khởi động Trình hướng dẫn nhập và xuất SQL Server
• Dịch vụ tích hợp [SSIS]

Nếu bạn quen thuộc với SQL Server Integration Servication [SSIS] và không muốn chạy trình hướng dẫn nhập và xuất nhập SQL Server, hãy tạo gói SSIS sử dụng nguồn Excel và đích SQL Server trong luồng dữ liệu.

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ú

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

5, như được hiển thị trong ví dụ sau. Để biết thêm thông tin, hãy xem tùy chọn cấu hình máy chủ truy vấn phân tán ad hoc.

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

3 để nhập dữ liệu từ bảng tính Excel

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO

7 vào bảng cơ sở dữ liệu mớ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

Đâ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

4.

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

9 thay vì 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

0 được sử dụng trong các ví dụ trước.

Để 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

1 tiêu chuẩn.

Để 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

2 trên máy chủ liên kết Excel hiện tại

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

3 vào bảng cơ sở dữ liệu SQL Server mới có tê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

4.

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

5, như được hiển thị trong ví dụ sau.

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

6 là một lệnh Transact-SQL mà bạn có thể chạy từ SQL Server Management Studio. Ví dụ sau tải dữ liệu từ tệp được phân phối bằng dấu phẩy

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

7 vào bảng cơ sở dữ liệu hiện có.

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

9 hiện có.

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ề 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
• 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.

Lỗi đầy đủ là:

Msg 7403, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

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:

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

Lỗi đầy đủ là:

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]".

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:

Lỗi đầy đủ là:

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO

0

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:

Nhà cung cấp Ole DB 32 bit "Microsoft.ace.oledb.12.0" không thể được tải trong quá trình xử lý trên máy chủ SQL 64 bit

Điều này xảy ra khi phiên bản 32 bit của nhà cung cấp DB cũ được cài đặt với máy chủ SQL 64 bit. Để giải quyết vấn đề này, hãy gỡ cài đặt phiên bản 32 bit và cài đặt phiên bản 64 bit của nhà cung cấp OLE DB thay thế.

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO

1

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET['Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]];
GO

2

Nhà cung cấp OLE DB "Microsoft.ace.oledb.12.0" cho máy chủ được liên kết "[NULL]" đã báo cáo lỗi.

• Không thể khởi tạo đối tượng nguồn dữ liệu của nhà cung cấp OLE DB "Microsoft.ace.oledb.12.0" cho máy chủ được liên kết "[NULL]"
• Cả hai lỗi này thường chỉ ra sự cố quyền giữa quy trình SQL Server và tệp. Đảm bảo rằng tài khoản đang chạy dịch vụ SQL Server có quyền truy cập đầy đủ vào tệp. Chúng tôi khuyên bạn nên cố gắng nhập tệp từ máy tính để bàn.
• Các lỗi đầy đủ là:
• Di chuyển dữ liệu bằng cách sử dụng hoạt động sao chép

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

Làm cách nào để chuyển dữ liệu từ excel sang cơ sở dữ liệu?

Làm cách nào để chuyển dữ liệu từ bảng tính sang cơ sở dữ liệu?