Truy vấn sql excel

Thủ thuật Excel – Chúng ta thường lưu trữ và xử lý dữ liệu trên cơ sở dữ liệu nhưng báo cáo lại thường được thực hiện, trình bày và gửi đi bên dưới các định dạng tệp Excel. Đối với các báo cáo định kỳ như báo cáo ngày, báo cáo tuần hay báo cáo tháng, việc xử lý dữ liệu và xuất dữ liệu ra Excel thường được thực hiện giống nhau và lặp lại. Vì thế việc tạo kết nối giữa Excel với các cơ sở dữ liệu giúp chúng ta có thể lấy dữ liệu tự động, nhanh chóng, dễ dàng. Bên cạnh đó, trong một số trường hợp, dữ liệu trên cơ sở dữ liệu quá lớn, việc sao chép ra Excel có thể bị mất dữ liệu, kết nối Excel với cơ sở dữ liệu là một giải pháp hữu hiệu giúp chúng tôi xử lý vấn đề này

Bài viết sau đây thủ thuật excel sẽ hướng dẫn bạn cách tạo kết nối giữa Excel với cơ sở dữ liệu, cụ thể là SQL. Các bạn có thể thực hiện theo các bước sau

1. In Tab Data trên thanh Ribbon của Excel, bạn vào tìm mục Get Data. Tại đây, bạn có thể chọn From SQL Server Data to connect with SQL to get data. Nếu bạn không nhìn thấy nút Nhận dữ liệu , hãy nhấn > Truy vấn mới > Từ cơ sở dữ liệu > Từ cơ sở dữ liệu máy chủ SQL

1. Trong hộp thoại Microsoft SQL Database , hãy chỉ định SQL Server để kết nối với hộp Server name. Tùy ý, bạn có thể chỉ định tên Cơ sở dữ liệu
2. Nếu bạn muốn nhập dữ liệu bằng cách sử dụng truy vấn cơ sở dữ liệu gốc, hãy xác định truy vấn của bạn trong hộp Câu lệnh SQL

4. Bấm OK

5. Chọn chế độ xác thực để kết nối với cơ sở dữ liệu SQL Server

• các cửa sổ. đây là lựa chọn mặc định. Chọn tùy chọn này nếu bạn muốn kết nối bằng cách sử dụng xác thực Windows
• cơ sở dữ liệu. Chọn tùy chọn này nếu bạn muốn kết nối bằng xác thực SQL Server. Sau khi bạn chọn mục này, hãy chỉ định tên người dùng và mật khẩu để kết nối với phiên bản SQL Server của bạn

1. Theo mặc định, hộp kiểm tra Mã hóa kết nối được chọn để biểu thị rằng Power query kết nối với cơ sở dữ liệu của bạn bằng kết nối được mã hóa. Nếu bạn không muốn kết nối bằng cách sử dụng kết nối đã được mã hóa, hãy xóa hộp kiểm này, rồi nhấn vào Kết nối. Nếu kết nối với SQL Server của bạn không được thiết lập bằng kết nối được mã hóa, Power query sẽ nhắc bạn kết nối bằng một kết nối không được mã hóa. Bấm OK trong thư để kết nối bằng cách sử dụng kết nối không được mã hóa

Ví dụ về công thức

Bạn cũng có thể sử dụng Query Editor để viết công thức cho Power query

= Sql.Databases(".")
= Sql.Database(".","Digitalskillworks")

Chỉ với các bước đơn giản trên là bạn đã có thể kết nối với SQL Server để lấy dữ liệu vào Excel một cách nhanh chóng và chính xác rồi. Giờ đây, mỗi lần nguồn dữ liệu trong SQL Server thay đổi, bạn chỉ cần Refresh lại trong Excel, ngay lập tức những gì thay đổi sẽ được cập nhật cho bạn. Thủ thuật Excel hy vọng sẽ giúp bạn có thêm những kiến ​​thức bổ trợ phục vụ cho công việc của mình

Mình thử mà không được ?

 

hoahuongduong1986 đã viết

Mình thử mà không được ?

Click chuột vào đây để mở rộng

Doạn phụ này của bạn đã sửa thành thế này

Mã. Sao chép.

Sub sql_1()

'Khai bao bien
Dim cn As Object, sql As String
Dim FPath As String, FName As String
'FPath và FName là du?ng d?n d?y d? và tên c?a file d? li?u ngu?n, dùng cho câu .ConnectionString bên du?i
FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
sql = "SELECT * FROM RangeName WHERE Reporter='Albania'"

Set rs = cn.Execute(sql)
Sheet4.Range("A2").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban
End Sub

 

vanacx đã viết

Doạn phụ này của bạn đã sửa thành thế này

Mã. Sao chép.

Sub sql_1()

'Khai bao bien
Dim cn As Object, sql As String
Dim FPath As String, FName As String
'FPath và FName là du?ng d?n d?y d? và tên c?a file d? li?u ngu?n, dùng cho câu .ConnectionString bên du?i
FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
sql = "SELECT * FROM RangeName WHERE Reporter='Albania'"

Set rs = cn.Execute(sql)
Sheet4.Range("A2").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban
End Sub

Click chuột vào đây để mở rộng

cảm ơn vân. Vân cho mình hỏi là mình muốn linh hoạt trong câu lệnh SQL nên muốn cho nó đoạn lên SQL vào ô A1 để tiện. Mình đã sửa thành File mà không được. Vân xem giúp mình với nhé

 

hoahuongduong1986 đã viết

cảm ơn vân. Vân cho mình hỏi là mình muốn linh hoạt trong câu lệnh SQL nên muốn cho nó đoạn lên SQL vào ô A1 để tiện. Mình đã sửa thành File mà không được. Vân xem giúp mình với nhé

Click chuột vào đây để mở rộng

Bạn thử xem bằng sub này

Mã. Sao chép.

Sub sql_1()

'Khai bao bien
Dim cn As Object, sql As String
Dim FPath As String, FName As String
'FPath và FName là du?ng d?n d?y d? và tên c?a file d? li?u ngu?n, dùng cho câu .ConnectionString bên du?i
FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
'sql = "SELECT * FROM RangeName WHERE Reporter='Albania' and Partner='World'"
sql = Sheet4.Range("A1").Value

Set rs = cn.Execute(sql)
Sheet4.Range("A2").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban
End Sub

 

vanacx đã viết

Bạn thử xem bằng sub này

Mã. Sao chép.

Sub sql_1()

'Khai bao bien
Dim cn As Object, sql As String
Dim FPath As String, FName As String
'FPath và FName là du?ng d?n d?y d? và tên c?a file d? li?u ngu?n, dùng cho câu .ConnectionString bên du?i
FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
'sql = "SELECT * FROM RangeName WHERE Reporter='Albania' and Partner='World'"
sql = Sheet4.Range("A1").Value

Set rs = cn.Execute(sql)
Sheet4.Range("A2").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban
End Sub

Click chuột vào đây để mở rộng

Cho mình hỏi chút với Vân, mình chạy thử SQL các kiểu thì ổn rồi, nhưng làm sao để lấy tiêu đề của nó nhỉ, tiêu đề cho vào dòng 2 ấy

 

hoahuongduong1986 đã viết

Em chào Anh. Nhờ Anh xem giúp em vấn đề Code SQL theo File với ạ.
- At A1 (Em tạo 2 mã SQL). Mã 1 chạy nhưng không lấy được tiêu đề làm sao để có tiêu đề ạ; .

Click chuột vào đây để mở rộng

- Tiêu đề phải sao chép từ Dữ liệu sang hoặc điền tay. Đáng lẽ phải khai báo ConnectionString có HDR=YES thì phải có Header nhưng hiện tại không thấy chạy
- Mã 2 có tính tổng nhưng thiếu Group By những trường không tính toán. Edit as after.
SELECT Distinct [Partner],SUM([Trade Value (US$)]) As [Tongcong] FROM RangeName Nhóm theo [Partner]

Nếu lấy 3 cột thì phải Group By 2 cột không tính toán:

SELECT Distinct [Partner],[Commodity],SUM([Trade Value (US$)]) As [Tongcong] FROM RangeName Group By [Partner],[Commodity]

 

ongke0711 đã viết

Để lưu dòng tiêu đề vô Trang tính thì bạn phải sử dụng mã được gán riêng, còn sử dụng CopyFromRecordset chỉ là lấy Recordset (tức thì các bản ghi thôi chứ không có Trường)
Thêm dòng mã này .

Mã. Sao chép.

Dim iCol As Integer
For iCol = 1 To rs.Fields.Count
    Sheet4.Cells(2, iCol).Value = rs.Fields(iCol - 1).Name
Next

Click chuột vào đây để mở rộng

Code chạy đúng rồi ạ. Em cảm ơn các Anh đã giúp đỡ em.

Bài đã tự động khai thác. 13/6/20

ptm0412 đã viết

- Tiêu đề phải sao chép từ Dữ liệu sang hoặc điền tay. Đáng lẽ phải khai báo ConnectionString có HDR=YES thì phải có Header nhưng hiện tại không thấy chạy
- Mã 2 có tính tổng nhưng thiếu Group By những trường không tính toán. Edit as after.
SELECT Distinct [Partner],SUM([Trade Value (US$)]) As [Tongcong] FROM RangeName Nhóm theo [Partner]

Nếu lấy 3 cột thì phải Group By 2 cột không tính toán:

SELECT Distinct [Partner],[Commodity],SUM([Trade Value (US$)]) As [Tongcong] FROM RangeName Group By [Partner],[Commodity]

Click chuột vào đây để mở rộng

Em cảm ơn ạ.

Bài đã tự động khai thác. 13/6/20

ongke0711 đã viết

Để lưu dòng tiêu đề vô Trang tính thì bạn phải sử dụng mã được gán riêng, còn sử dụng CopyFromRecordset chỉ là lấy Recordset (tức thì các bản ghi thôi chứ không có Trường)
Thêm dòng mã này .

Mã. Sao chép.

Dim iCol As Integer
For iCol = 1 To rs.Fields.Count
    Sheet4.Cells(2, iCol).Value = rs.Fields(iCol - 1).Name
Next

Click chuột vào đây để mở rộng

Em hỏi note anh cái này ạ. Em muốn thêm số thứ tự vào Code SQL em làm thế này mà không chạy được thì làm sao ạ

SELECT ROW_NUMBER() OVER (ORDER BY [Classification]) AS [Số thứ tự],[