Sử dụng các công cụ để nhập dữ liệu từ Excel vào SQL Server

Bài viết này trình bày chi tiết cách chuyển dữ liệu từ bảng tính Microsoft Excel vào cơ sở dữ liệu SQL Server thông qua nhiều phương pháp khác nhau, bao gồm: Integration Services, truy vấn phân tán, kết nối liên kết (linked server), và lập trình với ADO.

Các công nghệ được sử dụng

Các ví dụ trong bài áp dụng các thành phần sau:

• SQL Server Integration Services (SSIS) hoặc Data Transformation Services (DTS)
• Linked Server trong SQL Server
• Hàm OPENDATASOURCE và OPENROWSET
• ActiveX Data Objects (ADO) kết hợp với OLE DB Provider cho SQL Server (SQLOLEDB)
• ADO cùng OLE DB Provider cho Jet 4.0

Điều kiện tiên quyết

Để thực hiện các thao tác trong bài viết, cần có:

• Một phiên bản SQL Server 7.0 trở lên (bao gồm SQL Server 2000, 2005 hoặc mới hơn)
• Microsoft Visual Basic 6.0 nếu sử dụng đoạn mã ADO

Kiến thức nền tảng về SSIS/DTS, truy vấn phân tán, và lập trình ADO là cần thiết để hiểu rõ nội dung.

Nhập dữ liệu và chèn thêm

Các ví dụ sử dụng câu lệnh SELECT INTO để tạo bảng mới trong SQL Server và sao chép dữ liệu từ Excel. Cú pháp minh họa như sau:

SELECT * INTO NewTable FROM ExcelSource

Nếu muốn chèn dữ liệu vào bảng đã tồn tại thay vì tạo mới, hãy thay bằng cú pháp:

INSERT INTO ExistingTable SELECT * FROM ExcelSource

Sử dụng SQL Server Import Wizard (SSIS/DTS)

Với SQL Server Import and Export Wizard (hoặc DTS Import Wizard trên phiên bản cũ), bạn có thể dễ dàng đưa dữ liệu từ Excel vào SQL Server. Khi chọn nguồn dữ liệu Excel, lưu ý:

• Tên sheet phải có hậu tố $, ví dụ: Sheet1$
• Tên vùng dữ liệu (range) không có dấu $, ví dụ: DataRange

Cấu hình Linked Server đến Excel

Để truy vấn Excel như một cơ sở dữ liệu nội bộ, bạn có thể cấu hình nó thành linked server. Ví dụ sau tạo truy vấn đến sheet Customers$ trong workbook Excel:

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]

Để kiểm soát chính xác hơn câu lệnh truy vấn nguồn, dùng OPENQUERY:

SELECT * INTO XLImport2 
FROM OPENQUERY(EXCELLINK, 'SELECT * FROM [Customers$]')

Sử dụng truy vấn phân tán (OPENDATASOURCE/OPENROWSET)

Nếu không muốn cấu hình linked server vĩnh viễn, có thể dùng OPENDATASOURCE hoặc OPENROWSET để truy cập tạm thời vào file Excel. Ví dụ:

SELECT * INTO XLImport3 
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

Dùng ADO với SQLOLEDB Provider

Trong ứng dụng Visual Basic 6.0, bạn có thể dùng ADO để thực thi các câu lệnh phân tán qua kết nối đến SQL Server. Đoạn mã sau minh họa việc này:

Dim conn As ADODB.Connection
Dim sqlCmd As String
Dim recordsAffected As Long

Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB;Data Source=YourServer;" & _
          "Initial Catalog=YourDB;User ID=user;Password=pass"

' Sử dụng OPENDATASOURCE
sqlCmd = "SELECT * INTO XLImport6 FROM " & _
         "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
         "'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]"
conn.Execute sqlCmd, recordsAffected, adExecuteNoRecords
Debug.Print "Số bản ghi xử lý: " & recordsAffected

' Sử dụng OPENROWSET với tên đối tượng
sqlCmd = "SELECT * INTO XLImport7 FROM " & _
         "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
         "'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])"
conn.Execute sqlCmd, recordsAffected, adExecuteNoRecords

' Sử dụng OPENROWSET với truy vấn SELECT
sqlCmd = "SELECT * INTO XLImport8 FROM " & _
         "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
         "'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')"
conn.Execute sqlCmd, recordsAffected, adExecuteNoRecords

conn.Close
Set conn = Nothing

Dùng ADO với Jet OLE DB Provider

Thay vì kết nối đến SQL Server, bạn có thể kết nối trực tiếp đến file Excel bằng Jet Provider, rồi đẩy dữ liệu sang SQL Server. Jet hỗ trợ cú pháp đặc biệt để truy cập cơ sở dữ liệu ngoài, ví dụ qua ODBC:

[odbc;Driver={SQL Server};Server=server;Database=db;UID=user;PWD=pass]

Đoạn mã VB6 sau đây minh họa kỹ thuật này:

Dim conn As ADODB.Connection
Dim sqlStmt As String
Dim affectedCount As Long

Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\test\xltest.xls;" & _
          "Extended Properties=Excel 8.0"

sqlStmt = "SELECT * INTO " & _
          "[odbc;Driver={SQL Server};Server=server;Database=db;" & _
          "UID=user;PWD=pass].XLImport9 " & _
          "FROM [Customers$]"

conn.Execute sqlStmt, affectedCount, adExecuteNoRecords
Debug.Print "Đã xử lý: " & affectedCount & " bản ghi"

conn.Close
Set conn = Nothing

Phương pháp này cũng có thể dùng để xuất dữ liệu sang Access, dBASE, Paradox hoặc bất kỳ nguồn dữ liệu ODBC nào.

Khắc phục sự cố thường gặp

• Tên sheet phải có $: Nhớ rằng Sheet1$ là sheet, còn NamedRange là vùng đã đặt tên.
• Sắp xếp cột không đúng thứ tự: Khi dùng Jet Provider, đôi khi các cột trong bảng đích bị sắp xếp theo thứ tự alphabet. Đây là hành vi đã biết của engine Jet khi suy luận cấu trúc bảng.
• Dữ liệu bị NULL do xung đột kiểu: Nếu một cột trong Excel chứa cả số và chữ, Jet sẽ chọn kiểu dữ liệu dựa trên đa số mẫu (thường là Text hoặc Number), khiến các giá trị không khớp bị coi là NULL. Để giảm thiểu, nên đảm bảo cột dữ liệu đồng nhất, hoặc dùng IMEX=1 trong chuỗi kết nối để ép chế độ nhập liệu.