Cách truy vấn dữ liệu trên cùng file không mở kết nối (ADODB.Connection)

[thuyyeu99]

Khi thử với 1 triệu dòng thì tôi mới biết là cái Name Range khai báo với số dòng không được vượt quá 65.536 dòng. Khi tôi khai báo Name: =Sheet1!$A$1:$D$1000000 là Excel báo lỗi không tìm thấy name range ngay. Sau đó tôi phải đổi lại là: =Sheet1!$A:$D thì mới hết báo lỗi.




Cái hàm của bạn xử lý nhanh thật. Cái cách tiếp cận mà Delphi dùng để thao tác, xử lý dữ liệu này là gì vậy bạn?

Ngoai ra ban cũng vào pos lập trình dll với excel cua anh tuân, va doc các bài phân tích của anh Vietmimi va anh Batman1 có nhiều cái hay để nghiên cứu lắm hihih

 

[Kiều Mạnh]

Khi thử với 1 triệu dòng thì tôi mới biết là cái Name Range khai báo với số dòng không được vượt quá 65.536 dòng. Khi tôi khai báo Name: =Sheet1!$A$1:$D$1000000 là Excel báo lỗi không tìm thấy name range ngay. Sau đó tôi phải đổi lại là: =Sheet1!$A:$D thì mới hết báo lỗi.




Cái hàm của bạn xử lý nhanh thật. Cái cách tiếp cận mà Delphi dùng để thao tác, xử lý dữ liệu này là gì vậy bạn?

Trước đây Mạnh cũng đã từng tự học Delphi nhưng chỉ biết mấy cái lèo tèo thôi ... ???
https://www.giaiphapexcel.com/diendan/threads/tạo-và-sử-dụng-thư-viện-liên-kết-Động-dll-windows-api.125604/

Sau khi có thớt sau thì Mạnh mới thật sự biết nhiều về Delphi .... Giờ tự viết Hàm API ( DLL ) tạm xài ???

1/ Vô link sau tham khảo coi .... Mạnh học từ đó đấy và bắt đầu thật sự biết viết Hàm API từ đó
Trong thớt đó nếu Bạn thật sự đam mê là học được .... Có nhiều bài viết rất hay mà tìm trên mạng hình như ko có đấy ???

2/ Có mấy bài viết của @Nguyễn Duy Tuân Và Anh @batman1 mới đọc thấy ko có chi nhưng sau này nghiền Delphi rồi thì thấy kinh nghiệm viết ra mấy dòng ấy phải trên 10 Năm ???

3/ Nghiền ngẫm mấy bài về mảng của @thuyyeu99 .... mấy bài đó là xài nhiều nhất trong bất cứ cái dự án code nào hầu như code nào cũng xài mảng ... Nó gắm liền với cuộc đời và sự nghiệp của một tay Coder !!!??? khi rành mảng 1D, 2D ... thì Bạn xử lý đầy các vấn đề ra đó

https://www.giaiphapexcel.com/diendan/threads/ai-muốn-lập-trình-dll-cho-excel-và-các-loại-bằng-delphi-thì-xem-video-này-nhé.137281/

 

[thichproc]

Sắp xếp và lọc duy nhất:

...

Em sắp xếp dữ liệu để xóa trùng Mã Khách hàng nhưng bị lỗi này. Lẽ nào đối tượng Recordset của em không hỗ trợ Sort và Filter?
Bác chỉ em cách sửa với!

 

[ongke0711]

Em sắp xếp dữ liệu để xóa trùng Mã Khách hàng nhưng bị lỗi này. Lẽ nào đối tượng Recordset của em không hỗ trợ Sort và Filter?
Bác chỉ em cách sửa với!

Bạn phải thêm: rs.CurrentLocation = adUseClient (nếu khai báo muộn thì = 3)
Sort property nó yêu cầu CurrentLocation phải là adUseClient

 

[Hai Lúa Miền Tây]

Em sắp xếp dữ liệu để xóa trùng Mã Khách hàng nhưng bị lỗi này. Lẽ nào đối tượng Recordset của em không hỗ trợ Sort và Filter?
Bác chỉ em cách sửa với!

View attachment 214974

Đối với câu lệnh trên, bạn dùng select distinct cho lọc duy nhất và sắp xếp = order by (Bạn đã làm) là được. Lưu ý là bài mà tôi giới thiệu khác với truy vấn dữ liệu bằng cách kết nối với CSDL nhé bạn.

 

[thichproc]

Bạn phải thêm: rs.CurrentLocation = adUseClient (nếu khai báo muộn thì = 3)
Sort property nó yêu cầu CurrentLocation phải là adUseClient

Cảm ơn bác. Nhờ bác em đã giải quyết vấn đề sort và filter.
Em lại gặp vấn đề khác là khi em thêm 1 field "STT", rồi em duyệt rs, gán rs.Fields("STT").value = rs.AbsolutePosition
Nhưng lệnh trên lại bị lỗi. Bác giúp em thêm với!

Bài đã được tự động gộp: 10/4/19

Đối với câu lệnh trên, bạn dùng select distinct cho lọc duy nhất và sắp xếp = order by (Bạn đã làm) là được. Lưu ý là bài mà tôi giới thiệu khác với truy vấn dữ liệu bằng cách kết nối với CSDL nhé bạn.

Thanks Bác HLMT.
Em muốn xóa trùng trong trường hợp Khách hàng có cùng tổng số dư nhưng khác PGD. Chưa nghĩ ra câu lệnh SQL nào làm được nên em phải sử dụng duyệt recordset để xóa.
PS: Bác thông cảm vì em đã làm hỏng luồng của bác. Hic.

 

[nmhung49]

Cũng nhân đây nmhung49 cũng góp thêm các khác với dữ liệu 1tr dòng thì lấy data chưa đầy 5s

 

[VetMini]

@Bài #46:
Muốn xoá trùng thì dùng:
SELECT TrườngChính, Max(Trường 1), Max(Trường 2), ...
From ...
GROUP BY TrườngChính

 

[thichproc]

@Bài #46:
Muốn xoá trùng thì dùng:
SELECT TrườngChính, Max(Trường 1), Max(Trường 2), ...
From ...
GROUP BY TrườngChính

Dữ liệu em thế này:
MaKH TenKH Tong SD PGD
1234. Nguyen Van A. 100000. 01.
1234. Nguyen Van A. 100000. 02.
2345. Nguyen Van B. 200000. 00.
2345. Nguyen Van B. 200000. 01.

Sau khi xóa trùng, cho ra kết quả:
MaKH TenKH Tong SD PGD
1234. Nguyen Van A. 100000. 01.
2345. Nguyen Van B. 200000. 00.

Em chưa nghĩ ra câu lệnh SQL.

 

[VetMini]

Select MaKH, TenKH, [Tong SD], Min(PGD)
From
...
Group By MaKH, TenKH, [Tong SD]
Order By MaKH, TenKH, [Tong SD]

 

[thichproc]

Select MaKH, TenKH, [Tong SD], Min(PGD)
From
...
Group By MaKH, TenKH, [Tong SD]
Order By MaKH, TenKH, [Tong SD]

Chuẩn rồi bác ơi! Vậy mà em nghĩ hoài ko ra. Thanks bác.
Còn bài #46, phần đầu, về việc gán STT, bác giúp em với!

 

[ongke0711]

Select MaKH, TenKH, [Tong SD], Min(PGD)
From
...
Group By MaKH, TenKH, [Tong SD]
Order By MaKH, TenKH, [Tong SD]

Câu lệnh này của anh Vietmini đúng ra là: lọc danh sách duy nhất. Kiểu như Select DistinctRow...
Vì nếu có 1 MaKH khác xuất hiện 1 dòng thì nó cũng sẽ liệt kê ra luôn.

Câu lệnh SQL của em sẽ là: theo điều kiện trùng [MaKH], trùng [TongSD], khác [PGD]

SELECT Table1.MaKH, Table1.TenKH, Table1.TongSD,Table1.PGD, (SELECT Count(*)
     FROM Table1 As X
     WHERE X.MaKH = Table1.MaKH
         And X.TongSD=Table1.TongSD
         And X.PGD <= Table1.PGD) AS SeqNo, IIf([SeqNo] >1,"Trung","") AS Status
FROM Table1;

- Cái trường phụ: SeqNo chỉ để đánh dấu số thứ tự nếu có dòng trùng các điều kiện trên xuất hiện, xuất hiện mấy dòng sẽ đánh số tăng dần theo 2, 3 ,4... Sau đó trường [Status] dùng để hiện text cho dễ hiểu dòng trùng nếu [SegNo>1).

 

[ongke0711]

Em lại gặp vấn đề khác là khi em thêm 1 field "STT", rồi em duyệt rs, gán rs.Fields("STT").value = rs.AbsolutePosition
Nhưng lệnh trên lại bị lỗi. Bác giúp em thêm với!

- Cái AbsolutePosition của ADO recordset nó chỉ là tương đối tuỳ theo câu lệnh sql, trường chọn sắp xếp (sort) của bạn thôi, bạn gán vô CSDL làm gì.
- Nếu chỉ muốn hiển thị lên Form cho người dùng dễ nhìn thì lúc đó lồng thêm câu lệnh Sql để lấy số thứ tự xong rồi thôi, khỏi ghi lên database cho phí.

 

[thuyyeu99]

Dữ liệu em thế này:
MaKH TenKH Tong SD PGD
1234. Nguyen Van A. 100000. 01.
1234. Nguyen Van A. 100000. 02.
2345. Nguyen Van B. 200000. 00.
2345. Nguyen Van B. 200000. 01.

Sau khi xóa trùng, cho ra kết quả:
MaKH TenKH Tong SD PGD
1234. Nguyen Van A. 100000. 01.
2345. Nguyen Van B. 200000. 00.

Em chưa nghĩ ra câu lệnh SQL.

Do không biết dữ liệu chính xác của bạn làm sao, nên đón mò Ta có thể kết hợp Group và Having là ra thôi

 

[Huyyeu99999]

Cái bài này nghiệm ra có nhiều cái hay thật, nó có cách không dùng vòng lập đưa thẳng vào Recoset đó anh @Hai Lúa Miền Tây

 

[Hai Lúa Miền Tây]

Cái bài này nghiệm ra có nhiều cái hay thật, nó có cách không dùng vòng lập đưa thẳng vào Recoset đó anh @Hai Lúa Miền Tây

Chia sẻ đi bạn.

 

[Huyyeu99999]

Hihi đơn giản lắm anh đừng nghĩ cao quá, bản thân excel nó có đó anh

 

[Hai Lúa Miền Tây]

Hihi đơn giản lắm anh đừng nghĩ cao quá, bản thân excel nó có đó anh

Nếu biết cách nào hay hơn thì bạn cứ chia sẻ để mọi người cùng học tập. Có thể đối với bạn nó đơn giản, nhưng chưa chắc đối với ngưới khác giống bạn.

 

[Huyyeu99999]

Nếu biết cách nào hay hơn thì bạn cứ chia sẻ để mọi người cùng học tập. Có thể đối với bạn nó đơn giản, nhưng chưa chắc đối với ngưới khác giống bạn.

Chìa khóa XlRangeValueDataType
xlRangeValueXMLS Lansheet

Bài đã được tự động gộp: 16/1/20

Sub Copy()
Dim xml As string
xml = Application.ActiveSheet.Range("A1:D20").Value(XlRangeValueDataType.xlRangeValueXMLSpreadsheet)
Application.ActiveSheet.Range("G1:j20").Value(XlRangeValueDataType.xlRangeValueXMLSpreadsheet) = xml
End Sub

Có xml này anh đưa vào record

 

[HeSanbi]

Thông thường ta truy vấn dữ liệu bằng ADO trên cùng file thì ta vẫn phải mở kết nối đến chính file thực thi đó. Điều này làm giảm đi tốc độ khi chạy code.
Hôm nay mình xin chia sẻ với các bạn cách truy vấn dữ liệu mà không cần mở kết nối trên cùng file thực thi.

Sub LocDL_HLMT()
    Dim rst As Object, vArray As Variant, i As Long, thoigian As Long 'Khai bao bien
    Set rst = CreateObject("ADODB.Recordset") ' Khoi tao Recordset
    vArray = Sheet1.Range("A2:D10001").Value ' Du lieu nguon vao mang
    thoigian = Timer()
    With rst
        'Khai bao va mo Recorset
        .Fields.Append "ID", 3
        .Fields.Append "MatID", 200, 10
        .Fields.Append "Balance", 3
        .Open
        'Dua du lieu nguon tu mang da set vao Recordset
        For i = LBound(vArray) To UBound(vArray)
            .AddNew
            .Fields("ID").Value = vArray(i, LBound(vArray))
            .Fields("MatID").Value = vArray(i, LBound(vArray) + 1)
            .Fields("Balance").Value = vArray(i, LBound(vArray) + 3)
            .Update
        Next
        'Loc du lieu trong Recordset
        .Filter = "Balance " & Sheet2.Range("B1")
    End With
    Sheet2.Range("A2:D11000").ClearContents 'Xoa vung du lieu
    Sheet2.Range("A4").CopyFromRecordset rst 'Do du lieu tu Recordset da loc xuong sheet
    MsgBox Timer - thoigian
    rst.Close
    Set rst = Nothing
End Sub

Bài viết của anh @Hai Lúa Miền Tây hoàn toàn có vấn đề. Anh đọc qua bài viết dưới đây để hiểu vì sao như vậy.


----------------------
Không biết mắt có đọc nhằm không. Đọc 2-3 lần
Đọc bài viết và đọc code là "MatID", khi vào đọc trong Data thì là "MaID"


Sao anh @Hai Lúa Miền Tây lại để LBound của Hàng sang Cột được, hơi khó hiểu.

Đoạn code phải là:

     Dim LB%, LB2%
     LB= LBound(vArray): LB2 = LBound(vArray, 2)
        .Fields.Append vArray(LB, LB2), 3
        .Fields.Append vArray(LB, LB2 + 1), 200, 10
        .Fields.Append vArray(LB, LB2 + 3), 3
     '......

     .Fields(vArray(LB, LB2)).Value = vArray(i, LB2)
     .Fields(vArray(LB, LB2 + 1)).Value = vArray(i, LB2 + 1)
     .Fields(vArray(LB, LB2 + 3)).Value = vArray(i, LB2 + 3)

----------------------

Đến bài #47: cũng là Code "MatID" và trong Data thì là "MaID".

Cũng nhân đây nmhung49 cũng góp thêm các khác với dữ liệu 1tr dòng thì lấy data chưa đầy 5s

----------------------


Và không hiểu vì sao anh không Thực hiện truy vấn dữ liệu file đang mở vào Recordset bằng cách dưới đây. Mà phải thông qua vòng lặp (vô lý kia).
Tham số Data Source, ADO Sẽ thực hiện tìm xem File có được mở cùng một Process hay không, có thì truy vấn trực tiếp không thì phải mở File sau đó thực hiện kết nối. Ở đây ADO được khởi tạo cùng tiến trình với VBA và cùng tiến trình với Excel hiện tại. Nên File sẽ được kết nối ngay lập tức.

*Hiểu về Process: là Tiến trình chính - tiến trình Excel Application đang hoạt động trong Bộ nhớ máy tính.
----------------------

Sub testRecordset()
  Dim T#: T = Timer
  'Lưu ý: File phải được Save lại với định dạng chứa Macro để phù hợp đối số "Excel 12.0 Xml" mới test được Code này'
  With CreateObject("ADODB.Recordset")
    .Open "SELECT * FROM `" & ThisWorkbook.Sheets(1).Name & "$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml"""
    .Close
  End With
  Debug.Print Round(Timer - T, 5)
End Sub

----------------------------
Phương pháp trên của anh thực ra chỉ nhờ vào Tốc độ xử lý và thuật toán trong ADODB, và nó được viết bằng C++, chứ không phải một phương pháp để cải thiện tốc độ khi truy vấn một File Excel đang mở.

Đúng hơn thì không phải là phương pháp.

Bài viết của anh đã vô tình làm người đọc hiểu nhầm là ADO luôn phải mở mới truy vấn kể cả File cùng một Process.