Bài tập về ADO căn bản.

Hai Lúa Miền Tây · 14/12/18

AutoReply đã viết:
Ủa cái này có vấn đề gì khó hả bạn ? mình thấy hình như bạn cũng đâu có yếu khoản SQL ta

Không ai có thể biết hết mọi thứ, không biết thì hỏi có sao đâu bạn.

dhn46 đã viết:
Các anh chị cho dhn46 hỏi về lấy dữ liệu file csv:
dhn46 có file 1.csv tại D:\, có file để chứa kết quả Result.Xlsm cùng thư mục D:\
Tại sheet1 file Result, [A1:A10] chứa điều kiện cần lọc tương ứng với cột thứ 1 file 1.csv
Vậy xin hỏi code sử dụng SQL để lấy dữ liệu từ file D:\1.csv tới file D:\Result.xlsm với điều kiện tại D:\Result.xlsm_Sheet1.[A1:A10]
Xin cảm ơn GPE

Bạn có thể dùng Inner Join để lấy dữ liệu theo ý nhé.

Mã:

Sub GetValue_CSV()
    With CreateObject("ADODB.Connection")
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0"
        Sheet2.Range("A2").CopyFromRecordset .Execute("SELECT * FROM [Text;Database=" & ThisWorkbook.Path & ";HDR=YES;FMT=Delimited].[1.csv] a " & _
                                                        "INNER JOIN [Sheet1$A1:A10] b on a.[ID]=b.[ID] ")
    End With
End Sub

AutoReply · 14/12/18

Hai Lúa Miền Tây đã viết:
Không ai có thể biết hết mọi thứ, không biết thì hỏi có sao đâu bạn.

Bạn có thể dùng Inner Join để lấy dữ liệu theo ý nhé.

Mã:

Sub GetValue_CSV() With CreateObject("ADODB.Connection") .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0" Sheet2.Range("A2").CopyFromRecordset .Execute("SELECT * FROM [Text;Database=" & ThisWorkbook.Path & ";HDR=YES;FMT=Delimited].[1.csv] a " & _ "INNER JOIN [Sheet1$A1:A10] b on a.[ID]=b.[ID] ") End With End Sub

Đây là dạng kết nối dữ liệu từ 2 database khác nhau, cách viết cũng gần giống như kết nối 2 file excel khác nhau.
Mình thấy bạn đó tham gia nhiều chủ đề ADO chắc có biết kỹ thuật này chứ. Vui thôi mà.

Hai Lúa Miền Tây · 14/12/18

AutoReply đã viết:
Đây là dạng kết nối dữ liệu từ 2 database khác nhau, cách viết cũng gần giống như kết nối 2 file excel khác nhau.
Mình thấy bạn đó tham gia nhiều chủ đề ADO chắc có biết kỹ thuật này chứ. Vui thôi mà.

Việc kết nối dữ liệu với nhiều kiểu dữ liệu nguồn khác nhau cũng ít gặp trên diễn đàn này. Nên người ta chưa biết thì người ta hỏi. Nếu mình biết và thích trả lời thì mình trả lời giúp họ, đừng nên vặn vẹo chi nhé.

dhn46 · 14/12/18

Hai Lúa Miền Tây đã viết:
Việc kết nối dữ liệu với nhiều kiểu dữ liệu nguồn khác nhau cũng ít gặp trên diễn đàn này. Nên người ta chưa biết thì người ta hỏi. Nếu mình biết và thích trả lời thì mình trả lời giúp họ, đừng nên vặn vẹo chi nhé.

Cảm ơn anh HLMT và bạn AutoReply nhiều.

Dhn46 cũng đã thử nhưng chưa làm được ^^.

Với lượng dữ liệu nhiều thì kết nối ADO tới file csv này nhanh hơn việc mở file nên dhn46 muốn thử cho trường hợp này (đã test với "Select * from 1.csv")

Không biết còn phương pháp nào mà tốc độ lấy dữ liệu cao hơn không với file *.csv lên tới hàng GB? Nếu có nhờ các anh chị chỉ giúp (không xét các trường hợp sử dụng Server)

Hai Lúa Miền Tây · 14/12/18

dhn46 đã viết:
Cảm ơn anh HLMT và bạn AutoReply nhiều.

Dhn46 cũng đã thử nhưng chưa làm được ^^.

Với lượng dữ liệu nhiều thì kết nối ADO tới file csv này nhanh hơn việc mở file nên dhn46 muốn thử cho trường hợp này (đã test với "Select * from 1.csv")

Không biết còn phương pháp nào mà tốc độ lấy dữ liệu cao hơn không với file *.csv lên tới hàng GB? Nếu có nhờ các anh chị chỉ giúp (không xét các trường hợp sử dụng Server)

Chưa làm được là do dữ liệu nhiều hay là do chỗ nào vậy bạn?

dhn46 · 14/12/18

Hai Lúa Miền Tây đã viết:
Chưa làm được là do dữ liệu nhiều hay là do chỗ nào vậy bạn?

Đây là sub lấy toàn bộ dữ liệu của em

Mã:

Sub Test1()
    Set cnn = CreateObject("ADODB.Connection")
    Set rcst = CreateObject("ADODB.Recordset")
    pathFolder = ThisWorkbook.Path
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathFolder & _
                           ";Mode=Read;Extended Properties=""Text;HDR=NO;FMT=Delimited(,)"";"
    cnn.Open
            strQuery = "Select * from 1.csv"
            rcst.Open strQuery, cnn
    Range("A1").CopyFromRecordset rcst
End Sub

Đây là hướng em lấy 1 phần dữ liệu.
Anh xem giúp em hướng này sai tại đâu nhé
Cảm ơn anh!

Mã:

Sub Test()
    Set cnn = CreateObject("ADODB.Connection")
    Set rcst = CreateObject("ADODB.Recordset")
    pathFolder = ThisWorkbook.Path
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathFolder & _
                           ";Mode=Read;Extended Properties=""Text;HDR=NO;FMT=Delimited(,)"";"
    cnn.Open
            strQuery = "Select * from 1.csv where F1 in [excel 8.0;database=" & ThisWorkbook.FullName & ";HDR=No].[Sheet1$A1:A10].F1"
            rcst.Open strQuery, cnn
    Range("A1").CopyFromRecordset rcst
End Sub

Hai Lúa Miền Tây · 14/12/18

dhn46 đã viết:

Đây là sub lấy toàn bộ dữ liệu của em

Mã:

Sub Test1()
    Set cnn = CreateObject("ADODB.Connection")
    Set rcst = CreateObject("ADODB.Recordset")
    pathFolder = ThisWorkbook.Path
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathFolder & _
                           ";Mode=Read;Extended Properties=""Text;HDR=NO;FMT=Delimited(,)"";"
    cnn.Open
            strQuery = "Select * from 1.csv"
            rcst.Open strQuery, cnn
    Range("A1").CopyFromRecordset rcst
End Sub

Đây là hướng em lấy 1 phần dữ liệu.
Anh xem giúp em hướng này sai tại đâu nhé
Cảm ơn anh!

Mã:

Sub Test()
    Set cnn = CreateObject("ADODB.Connection")
    Set rcst = CreateObject("ADODB.Recordset")
    pathFolder = ThisWorkbook.Path
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathFolder & _
                           ";Mode=Read;Extended Properties=""Text;HDR=NO;FMT=Delimited(,)"";"
    cnn.Open
            strQuery = "Select * from 1.csv where F1 in [excel 8.0;database=" & ThisWorkbook.FullName & ";HDR=No].[Sheet1$A1:A10].F1"
            rcst.Open strQuery, cnn
    Range("A1").CopyFromRecordset rcst
End Sub

Đổi lại code như bài của tôi thử nhé.

Hai Lúa Miền Tây · 15/12/18

dhn46 đã viết:

Đây là sub lấy toàn bộ dữ liệu của em

Mã:

Sub Test1()
    Set cnn = CreateObject("ADODB.Connection")
    Set rcst = CreateObject("ADODB.Recordset")
    pathFolder = ThisWorkbook.Path
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathFolder & _
                           ";Mode=Read;Extended Properties=""Text;HDR=NO;FMT=Delimited(,)"";"
    cnn.Open
            strQuery = "Select * from 1.csv"
            rcst.Open strQuery, cnn
    Range("A1").CopyFromRecordset rcst
End Sub

Đây là hướng em lấy 1 phần dữ liệu.
Anh xem giúp em hướng này sai tại đâu nhé
Cảm ơn anh!

Mã:

Sub Test()
    Set cnn = CreateObject("ADODB.Connection")
    Set rcst = CreateObject("ADODB.Recordset")
    pathFolder = ThisWorkbook.Path
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathFolder & _
                           ";Mode=Read;Extended Properties=""Text;HDR=NO;FMT=Delimited(,)"";"
    cnn.Open
            strQuery = "Select * from 1.csv where F1 in [excel 8.0;database=" & ThisWorkbook.FullName & ";HDR=No].[Sheet1$A1:A10].F1"
            rcst.Open strQuery, cnn
    Range("A1").CopyFromRecordset rcst
End Sub

Không thấy bạn nói thêm gì, thôi thì tôi chỉnh lại theo miêu tả của bạn bên dưới.

Mã:

Sub GetValue_CSV()
    With CreateObject("ADODB.Connection")
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No"""
        Sheet2.Range("A2").CopyFromRecordset .Execute("SELECT a.* FROM [Text;Database=" & ThisWorkbook.Path & ";HDR=NO;FMT=Delimited].[1.csv] a " & _
                                                        "INNER JOIN [Sheet1$A1:A10] b on a.[F1]=b.[F1] ")
    End With
End Sub

dhn46 · 17/12/18

Hai Lúa Miền Tây đã viết:
Đổi lại code như bài của tôi thử nhé.

Cảm ơn anh HLMT, em đã áp dụng hướng dẫn của anh và sử dụng được cho công việc của mình.
có 1 vấn đề phát sinh là tên File có dấu chấm thì Code báo lỗi.
Vậy có cách nào khắc phục vấn đề này không anh?
Nhờ anh và các anh chị tư vấn và cho giải pháp.

Mã:

Sub CsvToExcel(FilterSN As Boolean, cTarget As Range)
Dim LinkFile As String, LinkFolder As String, FileName As String, strSQL As String
Dim Cnn, Rst
Set Cnn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
With Application.FileDialog(msoFileDialogFilePicker)
    If .Show = -1 Then
        .AllowMultiSelect = False
        LinkFile = .SelectedItems(1)
        LinkFolder = Left(LinkFile, InStrRev(LinkFile, "\"))
        FileName = Right(LinkFile, Len(LinkFile) - InStrRev(LinkFile, "\"))
    End If
If .SelectedItems.Count > 0 Then
Cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & LinkFolder & _
                           ";Mode=Read;Extended Properties=""Text;HDR=No;FMT=Delimited(,)"";"
                           
If FilterSN = True Then
    strSQL = "SELECT * FROM [Text;Database=" & ThisWorkbook.Path & ";HDR=No;FMT=Delimited].[" & FileName & "] a " & _
                                                        "INNER JOIN [FilterSN$B4:B1000] b on a.[ID]=b.[ID] "
Else
    strSQL = "SELECT * FROM [Text;Database=" & LinkFolder & ";HDR=No;FMT=Delimited].[" & FileName & "] a"
End If
Cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0"
Rst.Open strSQL, Cnn, 1, 3
cTarget.CopyFromRecordset Rst

Cao Mạnh Sơn · 20/4/19

Tôi đã đọc loạt bài viết về ADO của mọi người trong chuyên đề này, tôi đã viết 1 đoạn code test thử cho mục đích tổng hợp bảng chấm công như trong file đính kèm. kết quả là dữ liệu tập hợp bị thiếu rất nhiều kể cả trên 1 dòng hay trên nhiều cột, thiếu cả dữ liệu dạng text, cả dữ liệu dạng số, cả dữ liệu là time....
File dữ liệu nguồn là bảng chấm công cũng đã test tiêu đề cột dạng text, dạng Number đủ cả, vùng dữ liệu cũng test các kiểu: cả bảng tính hoặc chỉ vùng chấm công ... đều không được. kết quả khi mở file nguồn và khi đóng file nguồn là khác nhau...
Tôi đưa file đính kèm hy vọng mọi người vào xem và giải quyết giúp vấn đề.
Trân trọng

ongke0711 · 20/4/19

cao mạnh sơn đã viết:
Tôi đã đọc loạt bài viết về ADO của mọi người trong chuyên đề này, tôi đã viết 1 đoạn code test thử cho mục đích tổng hợp bảng chấm công như trong file đính kèm. kết quả là dữ liệu tập hợp bị thiếu rất nhiều kể cả trên 1 dòng hay trên nhiều cột, thiếu cả dữ liệu dạng text, cả dữ liệu dạng số, cả dữ liệu là time....
File dữ liệu nguồn là bảng chấm công cũng đã test tiêu đề cột dạng text, dạng Number đủ cả, vùng dữ liệu cũng test các kiểu: cả bảng tính hoặc chỉ vùng chấm công ... đều không được. kết quả khi mở file nguồn và khi đóng file nguồn là khác nhau...
Tôi đưa file đính kèm hy vọng mọi người vào xem và giải quyết giúp vấn đề.
Trân trọng

Bạn giải thích giùm câu lệnh SQL mà bạn muốn lấy dữ liệu nhé: thực sự tôi cũng chưa hiểu lắm

Mã:

"SELECT *,'ChamcongGioLamViecTo1.xlsm' as [From File] ,(SELECT *  FROM [T1$D6:D6]) FROM [T1$B12:EM] WHERE F1 is not Null"

ADO rất khó tính trong việc đọc dữ liệu, bạn cứ thử nếu tiêu đề dạng Text thì dữ liệu bên dứoi bạn cũng đổi về text đối chiếu xem ADO nó convert đúng không.

Cao Mạnh Sơn · 20/4/19

ongke0711 đã viết:
Bạn giải thích giùm câu lệnh SQL mà bạn muốn lấy dữ liệu nhé: thực sự tôi cũng chưa hiểu lắm

Mã:

"SELECT *,'ChamcongGioLamViecTo1.xlsm' as [From File] ,(SELECT * FROM [T1$D6:D6]) FROM [T1$B12:EM] WHERE F1 is not Null"

Trong câu lệnh trên ngoài việc lấy dữ liệu tất cả các cột của vùng B12:EM của sheet T1 thì chèn thêm cột tên file lấy từ tên file nguồn, cột tháng lấy từ D6 của sheet T1, loại bỏ tất cả các dòng rỗng của cột B trong dữ liệu nguồn. Đại ý là như vậy bạn nhé.

ongke0711 · 20/4/19

Nhìn trong file bạn tôi thấy nó không thiết dữ liệu mà chỉ không hiển thị dữ liệu là: 12:00:00 AM. Hình như cái kiểu này của Excel, phải chọn "Show a zero in cells that have zero value" nó mới hiển thị.

Cao Mạnh Sơn · 20/4/19

ongke0711 đã viết:
Nhìn trong file bạn tôi thấy nó không thiết dữ liệu mà chỉ không hiển thị dữ liệu là: 12:00:00 AM. Hình như cái kiểu này của Excel, phải chọn "Show a zero in cells that have zero value" nó mới hiển thị.

Phần định dạng mình có thể đưa code xử lý, không cần bàn nội dung này. Vấn đề là code hiện tại sửa thế nào để có thể lấy đủ dữ liệu từ file nguồn theo mong muốn của mình. Ở đây vấn đề đáng nói là dữ liệu kiểu text cũng thiếu, kiểu number cũng thiếu, kiểu time cũng thiếu, file nguồn nếu mở cho kết quả khác, nếu đóng cho kết quả khác. Anh ADO này khó nhằn thật.

Cao Mạnh Sơn · 22/4/19

cao mạnh sơn đã viết:
Tôi đã đọc loạt bài viết về ADO của mọi người trong chuyên đề này, tôi đã viết 1 đoạn code test thử cho mục đích tổng hợp bảng chấm công như trong file đính kèm. kết quả là dữ liệu tập hợp bị thiếu rất nhiều kể cả trên 1 dòng hay trên nhiều cột, thiếu cả dữ liệu dạng text, cả dữ liệu dạng số, cả dữ liệu là time....
File dữ liệu nguồn là bảng chấm công cũng đã test tiêu đề cột dạng text, dạng Number đủ cả, vùng dữ liệu cũng test các kiểu: cả bảng tính hoặc chỉ vùng chấm công ... đều không được. kết quả khi mở file nguồn và khi đóng file nguồn là khác nhau...
Tôi đưa file đính kèm hy vọng mọi người vào xem và giải quyết giúp vấn đề.
Trân trọng

Tôi vẫn ngóng kết quả. hy vọng có bạn giúp giải quyết vấn đề

tam888 · 22/4/19

cao mạnh sơn đã viết:
Phần định dạng mình có thể đưa code xử lý, không cần bàn nội dung này. Vấn đề là code hiện tại sửa thế nào để có thể lấy đủ dữ liệu từ file nguồn theo mong muốn của mình. Ở đây vấn đề đáng nói là dữ liệu kiểu text cũng thiếu, kiểu number cũng thiếu, kiểu time cũng thiếu, file nguồn nếu mở cho kết quả khác, nếu đóng cho kết quả khác. Anh ADO này khó nhằn thật.

Không khó nhằn đâu, vì dữ liệu gốc của bạn nó bị lộ cộ tiêu đề tùm lum nên thế
Thử thay thành $B11:EM (thay vì cũ $B12:EM) xem có khá hơn chút không
Nhưng giải phải trị gốc là cơ sở dữ liệu raw (gốc) phải có tiêu đề rõ ràng

Cao Mạnh Sơn · 23/4/19

tam888 đã viết:
Không khó nhằn đâu, vì dữ liệu gốc của bạn nó bị lộ cộ tiêu đề tùm lum nên thế
Thử thay thành $B11:EM (thay vì cũ $B12:EM) xem có khá hơn chút không
Nhưng giải phải trị gốc là cơ sở dữ liệu raw (gốc) phải có tiêu đề rõ ràng

Toàn bộ dòng 1 đến dòng 11 mình đã bỏ qua không duyệt đến nó vì đúng như bạn nêu, từ dòng 12 là vùng dữ liệu mình quan tâm, coi như nó là dòng tiêu đề, dòng này có cột dạng text, cột dạng number.
vấn đề cùng dạng number nhưng có cột lấy được dữ liệu, có cột mất.... thực sự là nguyên nhân ở chỗ nào mình đang muốn biết. tìm đọc không thấy. Test các vùng khác nhau nhiều rồi ko có kết quả đúng trừ khi file nguồn được mở

tam888 · 23/4/19

cao mạnh sơn đã viết:
Toàn bộ dòng 1 đến dòng 11 mình đã bỏ qua không duyệt đến nó vì đúng như bạn nêu, từ dòng 12 là vùng dữ liệu mình quan tâm, coi như nó là dòng tiêu đề, dòng này có cột dạng text, cột dạng number.
vấn đề cùng dạng number nhưng có cột lấy được dữ liệu, có cột mất.... thực sự là nguyên nhân ở chỗ nào mình đang muốn biết. tìm đọc không thấy. Test các vùng khác nhau nhiều rồi ko có kết quả đúng trừ khi file nguồn được mở

Nguyên nhân là ADO là ứng với kiểu xử lý database thường ưa thích là database chuẩn có tiêu đề, dữ liệu cùng 1 cột phải cùng kiểu dữ liệu
Trường hợp vấn đề ở đây là chương trình sẽ dò tìm 8 dòng đầu để dịnh kiểu (datatype) cho field (column) tương ứng, vì dữ liệu trống nên nó sẽ CÓ THỂ định kiểu sai, dẫn đến hiểu lầm dữ liệu time sau... nên bỏ qua

Nếu dữ liệu vẫn lộ cộ thế, thì bạn nên thử đọc và xét sâu vấn đề
Rst.Open strSQL, Cnn, 1, 3
---> tham số 1 , 3 đằng sau cho hợp lý
Và
giá trị IMEX trong chuỗi connectstring cũng ảnh hưởng việc định kiểu,
Thử thay đổi chúng nó xem sao

Cao Mạnh Sơn · 23/4/19

tam888 đã viết:
Nguyên nhân là ADO là ứng với kiểu xử lý database thường ưa thích là database chuẩn có tiêu đề, dữ liệu cùng 1 cột phải cùng kiểu dữ liệu
Trường hợp vấn đề ở đây là chương trình sẽ dò tìm 8 dòng đầu để dịnh kiểu (datatype) cho field (column) tương ứng, vì dữ liệu trống nên nó sẽ CÓ THỂ định kiểu sai, dẫn đến hiểu lầm dữ liệu time sau... nên bỏ qua

Nếu dữ liệu vẫn lộ cộ thế, thì bạn nên thử đọc và xét sâu vấn đề
Rst.Open strSQL, Cnn, 1, 3
---> tham số 1 , 3 đằng sau cho hợp lý
Và
giá trị IMEX trong chuỗi connectstring cũng ảnh hưởng việc định kiểu,
Thử thay đổi chúng nó xem sao

Sau một hồi Test và KQ = Không đạt.
Bạn có thể sửa trực tiếp vào File hộ mình được không ???

tam888 · 24/4/19

cao mạnh sơn đã viết:
Sau một hồi Test và KQ = Không đạt.
Bạn có thể sửa trực tiếp vào File hộ mình được không ???

File của bạn nhiều dữ liệu quá, tiêu đề lại loằng ngoằng, tôi bị tắc ... không thử được
Vậy phải tự làm thôi

Bài tập về ADO căn bản.

❆❆❆❆❆❆❆❆

File đính kèm

Thành viên tiêu biểu

❆❆❆❆❆❆❆❆

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Hướng tới tương lai

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

Hướng tới tương lai

Tôi đồng ý

File đính kèm

Thành viên gắn bó

Tôi đồng ý

Thành viên gắn bó

Tôi đồng ý

Tôi đồng ý

Thành viên tích cực

Tôi đồng ý

Thành viên tích cực

Tôi đồng ý

Thành viên tích cực

Facebook

Group