Có một số cách để Xuất/Nhập dữ liệu Máy chủ SQL sang tệp Excel. Trong bài viết này, một số cách để nhập và xuất dữ liệu SQL Server sang tệp Excel sẽ được giải thích
- Xuất dữ liệu SQL Server sang tệp Excel bằng cách sử dụng
- Trình hướng dẫn Nhập và Xuất Máy chủ SQL
- mã T-SQL
- Xuất sang Excel từ ApexSQL Complete
- Nhập dữ liệu Máy chủ SQL vào Excel bằng Trình hướng dẫn Kết nối Dữ liệu
Xuất dữ liệu SQL Server sang tệp Excel bằng Trình hướng dẫn Nhập và Xuất SQL Server
Trong SQL Server Management Studio [SSMS], có một tính năng cung cấp khả năng xuất dữ liệu từ nguồn dữ liệu này sang nguồn dữ liệu khác;
Để bắt đầu sử dụng tính năng này, hãy vào Object Explorer, nhấp chuột phải vào bất kỳ cơ sở dữ liệu nào [e. g. AdventureworksDW2016CTP3], trong Nhiệm vụ, chọn lệnh Xuất dữ liệu
Thao tác này sẽ mở cửa sổ Trình hướng dẫn Nhập và Xuất SQL Server
Để tiến hành xuất dữ liệu SQL Server ra file Excel, nhấn nút Next. Thao tác này sẽ mở cửa sổ Chọn nguồn dữ liệu. Trên cửa sổ này, bạn có thể chọn nguồn mà bạn muốn sao chép dữ liệu
Từ hộp thả xuống Nguồn dữ liệu, chọn SQL Server Native Client 11. 0 mặt hàng. Trong hộp thả xuống Tên máy chủ, chọn phiên bản Máy chủ SQL mà dữ liệu cần được sao chép từ đó. Trong phần Xác thực, chọn chế độ xác thực cho kết nối nguồn dữ liệu và trong hộp thả xuống Cơ sở dữ liệu, chọn cơ sở dữ liệu mà dữ liệu sẽ được sao chép từ đó. Sau khi tất cả các tùy chọn này được đặt, hãy nhấp vào nút Tiếp theo để tiếp tục
Thao tác này sẽ mở cửa sổ Chọn điểm đến trong đó có thể chỉ định nơi sao chép dữ liệu từ nguồn dữ liệu
Trong hộp thả xuống Đích, chọn mục Microsoft Excel. Trong đường dẫn tệp Excel, chọn đích cho dữ liệu được sao chép từ nguồn dữ liệu SQL Server [Dữ liệu SQL. xlsx]. Trong hộp thả xuống Phiên bản Excel, hãy chọn phiên bản của trang tính Microsoft Excel. Sau khi đã thiết lập xong, nhấn nút Next để tiếp tục. Nhưng sau khi nhấn nút Tiếp theo, thông báo cảnh báo sau có thể xuất hiện
Không thể hoàn thành thao tác.
THÔNG TIN BỔ SUNG.
'Microsoft. ÁT CHỦ. OLEDB. 12. 0’ chưa được đăng ký trên máy cục bộ. [Hệ thống. dữ liệu]
Điều này xảy ra vì SSMS là một ứng dụng 32 bit và khi bạn khởi chạy Trình hướng dẫn Xuất qua SSMS, nó sẽ khởi chạy phiên bản 32 bit của Trình hướng dẫn Xuất. Mặt khác, máy [Hệ điều hành] mà SSMS được cài đặt là phiên bản 64 bit và SQL Server đã cài đặt là phiên bản 64 bit
Để giải quyết vấn đề này, hãy đóng SSMS, chuyển đến menu Bắt đầu và tìm phiên bản 64-bit của Nhập và xuất dữ liệu SQL Server
Sau khi khởi chạy Trình hướng dẫn Nhập và Xuất SQL Server này, nó sẽ được nhắc với cùng một cửa sổ được sử dụng qua SSMS. Sau khi thiết lập tất cả các tùy chọn trước đó, nhấp vào nút Tiếp theo. Cửa sổ Specify Table Copy or Query sẽ xuất hiện
Trên cửa sổ này, có thể chỉ định sao chép dữ liệu từ một hoặc nhiều bảng và dạng xem hoặc sao chép kết quả của một truy vấn
Chọn nút radio Sao chép dữ liệu từ một hoặc nhiều bảng hoặc chế độ xem và nhấp vào nút Tiếp theo. Cửa sổ Select Source Table and Views cho phép chọn một hoặc nhiều bảng và dạng xem mà bạn muốn xuất dữ liệu SQL Server sang tệp Excel bằng cách nhấp vào hộp kiểm bên cạnh tên của bảng/dạng xem
Như có thể nhận thấy, ngay khi hộp kiểm bên cạnh bảng/khung nhìn được chọn, tên của bảng/khung nhìn sẽ được sao chép dưới cột Đích. Tên này đại diện cho tên của trang tính sẽ đặt dữ liệu từ bảng, tên này có thể được thay đổi thành bất kỳ tên nào bạn thích, nhưng bây giờ, nó sẽ được giữ nguyên
Để xem trước dữ liệu nào sẽ được tạo thành tệp Excel, hãy nhấp vào nút Xem trước
Sau khi chọn dữ liệu bảng/dạng xem nào sẽ được sao chép sang tệp Excel, nhấp vào nút Tiếp theo
Trên cửa sổ Save as Run Package, đánh dấu vào ô Run NGAY và nhấn nút Next
Cửa sổ Complete the Wizard hiển thị tất cả các tùy chọn được chọn cho hoạt động xuất. Để kết thúc quá trình xuất dữ liệu SQL Server sang tệp Excel, hãy nhấp vào nút Kết thúc. Cửa sổ SQL Server Import and Export Wizard cuối cùng hiển thị trạng thái dữ liệu đã được xuất thành công hay xảy ra một số lỗi trong quá trình xuất dữ liệu
Trong trường hợp của chúng tôi, dữ liệu đã được tạo thành công vào Dữ liệu SQL. xlsx trong bảng DimScenario
Xuất dữ liệu SQL Server sang tệp Excel bằng mã T-SQL
OPENROWSET Transact-SQL có thể được sử dụng để xuất dữ liệu Máy chủ SQL sang tệp Excel qua SSMS. Trong một loại trình soạn thảo truy vấn và thực thi đoạn mã sau
INSERT INTO OPENROWSET['Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM [Sheet1$]']
SELECT * FROM dbo.DimScenario
Tuy nhiên, khi thực thi đoạn mã trên, lỗi sau có thể xảy ra
Msg 15281, Cấp 16, Trạng thái 1, Dòng 1
Máy chủ SQL đã chặn quyền truy cập vào TUYÊN BỐ 'OpenRowset/OpenDatasource' của thành phần 'Truy vấn phân tán đặc biệt' vì thành phần này bị tắt . Quản trị viên hệ thống có thể cho phép sử dụng 'Truy vấn phân tán đặc biệt' bằng cách sử dụng sp_configure. Để biết thêm thông tin về cách bật 'Truy vấn phân tán đặc biệt', hãy tìm kiếm 'Truy vấn phân phối đặc biệt' trong SQL Server Books Online.
Để giải quyết lỗi này, tùy chọn Ad Hoc Distributed Queries phải được bật. Điều này có thể được thực hiện bằng cách sử dụng quy trình sp_configure và thực thi mã SQL sau trong trình chỉnh sửa truy vấn
EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE
Sau khi thực thi đoạn mã trên, thông báo sau sẽ xuất hiện cho biết rằng tùy chọn hiển thị nâng cao và tùy chọn Truy vấn phân tán đặc biệt đã được bật
Tùy chọn cấu hình 'hiển thị tùy chọn nâng cao' đã thay đổi từ 0 thành 1. Chạy câu lệnh RECONFIGURE để cài đặt.
Tùy chọn cấu hình 'Truy vấn phân tán đặc biệt' đã thay đổi từ 0 thành 1. Chạy câu lệnh RECONFIGURE để cài đặt.
Bây giờ, khi một lần nữa mã được thực thi
INSERT INTO OPENROWSET['Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM [Sheet1$]']
SELECT * FROM dbo.DimScenario
Lỗi sau có thể xuất hiện
Nhà cung cấp OLE DB “Microsoft. ÁT CHỦ. OLEDB. 12. 0” cho máy chủ được liên kết “[null]” trả về thông báo “Công cụ cơ sở dữ liệu Microsoft Access không thể mở hoặc ghi vào tệp”. Nó đã được mở độc quyền bởi người dùng khác hoặc bạn cần có quyền để xem và ghi dữ liệu của nó. ”.
Msg 7303, Mức 16, Trạng thái 1, Dòng 1
Không thể khởi tạo đối tượng nguồn dữ liệu của nhà cung cấp OLE DB “Microsoft. ÁT CHỦ. OLEDB. 12. 0” cho máy chủ được liên kết “[null]
Điều này thường xảy ra do không đủ quyền
Bạn có thể tìm thêm thông tin về cách giải quyết các vấn đề này trong trang Cách truy vấn dữ liệu Excel bằng cách sử dụng máy chủ được liên kết với SQL Server
Một cách để giải quyết vấn đề này là mở SSMS với tư cách quản trị viên và thực thi lại mã. Nhưng lần này, một lỗi khác có thể xuất hiện
Msg 213, Mức 16, Trạng thái 1, Dòng 1
Tên cột hoặc số lượng giá trị được cung cấp không khớp với định nghĩa bảng.
Để giải quyết vấn đề này, hãy mở tệp excel [e. g. Dữ liệu SQL. xlsx] được lên kế hoạch lưu trữ dữ liệu từ bảng SQL Server [e. g. dbo. DimScenario] và nhập tên cột đại diện cho tên cột từ bảng DimScenario
Đóng dữ liệu SQL. xlsx và một lần nữa thực thi mã
Bây giờ, thông báo sau sẽ xuất hiện
[3 hàng bị ảnh hưởng]
Cuối cùng, dữ liệu từ bảng SQL Server được sao chép vào tệp Excel
Nhập dữ liệu SQL Server vào Excel bằng hộp thoại Trình hướng dẫn Kết nối Dữ liệu
Một cách khác để sao chép dữ liệu từ bảng SQL Server sang tệp Excel là sử dụng hộp thoại Trình hướng dẫn kết nối dữ liệu từ Excel
Để đạt được điều đó, hãy mở một tệp Excel [e. g. Dữ liệu SQL. xlsx] mà bạn muốn nhập dữ liệu. Trong tab Dữ liệu, bên dưới menu phụ Từ nguồn khác, chọn lệnh Từ máy chủ SQL
Thao tác này sẽ mở hộp thoại Trình hướng dẫn kết nối dữ liệu. Trong Tên máy chủ, nhập tên của phiên bản Máy chủ SQL mà dữ liệu cần được sao chép từ đó. Trong phần Đăng nhập thông tin đăng nhập, chọn chế độ xác thực cho kết nối nguồn dữ liệu và nhấp vào nút Tiếp theo
Từ hộp thả xuống Chọn cơ sở dữ liệu chứa dữ liệu bạn muốn, chọn cơ sở dữ liệu mà dữ liệu sẽ được sao chép từ đó. Trong lưới, tất cả các bảng và dạng xem có sẵn sẽ được liệt kê. Chọn một bảng/khung nhìn mong muốn [e. g. DimScenario] và nhấp vào nút Tiếp theo để tiếp tục
Trên cửa sổ này, để nguyên mọi thứ và nhấn nút Kết thúc
Trong hộp thoại Nhập dữ liệu, chọn nút radio Bảng, bên dưới mục Bạn muốn đặt dữ liệu ở đâu?
Sau khi nhấn nút OK, dữ liệu từ bảng DimScenario sẽ được sao chép vào Dữ liệu SQL. tập tin xlsx
Xuất dữ liệu Máy chủ SQL sang tệp Excel bằng Xuất sang Excel từ ApexSQL Complete
Sử dụng tính năng Xuất sang Excel từ ApexSQL Complete, bổ trợ cho SSMS và Visual Studio, việc xuất dữ liệu từ bảng SQL Server sang tệp Excel có thể được thực hiện bằng một cú nhấp chuột
Trong trình chỉnh sửa truy vấn, hãy nhập và thực thi đoạn mã sau
SELECT * FROM dbo.DimScenario ds
Từ lưới Kết quả, chọn dữ liệu bạn muốn xuất sang tệp Excel, nhấp chuột phải vào lưới Kết quả và từ menu ngữ cảnh, chọn tùy chọn Xuất sang Excel
Thao tác này sẽ mở một sổ làm việc Excel mới và dữ liệu đã chọn từ lưới Kết quả sẽ được sao chép trong trang tính đầu tiên