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

VetMini · 9/9/19

Có 2 cách:
1. vào Registry chỉnh TypeGuessRows từ 8 thành 0 (Excel sẽ không dùng 8 rows đầu để đoán)
2. chèn thêm 1 dòng giả, với số ký tự cỡ 1000.

snow25 · 9/9/19

VetMini đã viết:
Có 2 cách:
1. vào Registry chỉnh TypeGuessRows từ 8 thành 0 (Excel sẽ không dùng 8 rows đầu để đoán)
2. chèn thêm 1 dòng giả, với số ký tự cỡ 1000.

Cảm ơn bác.Bác cho cháu 1 ví dụ ở cách 2 với ạ.

VetMini · 9/9/19

Mở file.
Copy dữ liệu sang sheet khác.
Insert Row đầu tiên. Vào cột cần làm, gõ 1000 ký tự. Save.

Cách gõ 1000 ký tự:
Mở word hoặc Notepad.
Lựa một trang web nào đó có các câu cú dài dài (ví dụ https:// libguides.navitas.com/literature-review/examples)
Chọn một đoạn (pargraph) dài dài và không có chứa gì đặc biệt (như link...)
Gõ Enter 3 lần (lần thứ nhất là chọn vị trí, lần thứ nhì bôi đen từ, lần thứ ba bôi đen cả đoạn)
Ctrl+C, đem qua văn bản Word hay Notepad Ctrl+V vài lần là sẽ đủ 1000 ký tự.
Ctrl+A, Ctrl+C
Trong clipboard sẽ có 1000 ký tự.

Kiều Mạnh · 9/9/19

thấy mô tả thế chạy mất dép ... vào Registry mà sửa cho nó nhanh -0-0-0-

LYSM · 9/9/19

kieu manh đã viết:
thấy mô tả thế chạy mất dép ... vào Registry mà sửa cho nó nhanh
View attachment 223944

Trước hết em xin cám ơn các anh chị, em xin giải thích là code này bác Snow25 làm giúp em ạ, Khổ cái máy công ty không cho chỉnh sửa Redistry, còn cách của bác Vietmini em làm theo nhưng chắc vẫn sai ở đâu đó nên không được.

VetMini · 9/9/19

kieu manh đã viết:
thấy mô tả thế chạy mất dép ... vào Registry mà sửa cho nó nhanh
...

1. Không phải máy nào cũng được phép sửa registry.
2. Có lẽ quý vị quá quen với hai từ "tự động" cho nên nghĩ là chêm dòng là công việc rắc rối. Thực ra rất nhiều người già dặn kinh nghiệm về CSDL sử dụng cách chêm dòng khi cần import dữ liệu Excel vào các CSDL khác.

LYSM đã viết:
Trước hết em xin cám ơn các anh chị, em xin giải thích là code này bác Snow25 làm giúp em ạ, Khổ cái máy công ty không cho chỉnh sửa Redistry, còn cách của bác Vietmini em làm theo nhưng chắc vẫn sai ở đâu đó nên không được.

Thử chêm 10 dòng?

thuyyeu99 · 9/9/19

snow25 đã viết:

Các anh cho em hỏi là em viết code lấy dữ liệu vào mảng bằng ADO nhưng nó chỉ lấy được 255 ký tự vào mảng.Có cách nào lấy được toàn bộ dữ liệu không nhỉ.

Mã:

Sub tonghop()
     Application.ScreenUpdating = False
     Application.AskToUpdateLinks = False
     Application.DisplayAlerts = False
     Dim cn As Object, sqlStr As String, i As Long, lr As Long, k, rst As Object, Pro As String, ext As String, arr(1 To 1000, 1 To 14), a As Long
     Dim sarr, j As Long, b As Long
     Set cn = CreateObject("ADODB.Connection")
     Set rst = CreateObject("ADODB.recordset")
     With Application.FileDialog(msoFileDialogFilePicker)
         .AllowMultiSelect = True
    If Not .Show = -1 Then Exit Sub
    For Each k In .SelectedItems
       Pro = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
       ext = ";Extended Properties=""Excel 12.0;HDR=yes;IMEX= 1"";"
       cn.Open (Pro & k & ext)
       sqlStr = "Select * From [sheet1$a1:e30]"
       sarr = cn.Execute(sqlStr).GetRows
       a = a + 1
       b = Len(sarr(1, 24))
       arr(a, 1) = a
       arr(a, 2) = sarr(1, 3)
       arr(a, 3) = sarr(1, 0)
       arr(a, 4) = sarr(1, 2)
       arr(a, 5) = sarr(1, 1)
       arr(a, 9) = sarr(4, 7)
       arr(a, 10) = sarr(4, 8)
       arr(a, 11) = sarr(4, 9)
       arr(a, 12) = sarr(4, 10)
       arr(a, 13) = sarr(4, 11)
       arr(a, 14) = sarr(1, 24)
       For j = 25 To 28
          If sarr(1, j) <> Empty Then arr(a, 14) = arr(a, 14) & Chr(10) & sarr(1, j)
       Next j
       cn.Close
    Next
    End With
    With Sheets("sheet1")
         lr = .Range("A" & Rows.Count).End(xlUp).Row
         If lr > 12 Then .Range("A13:N" & lr).ClearContents
         If a Then .Range("A13:N13").Resize(a).Value = arr
     End With
End Sub

anh thử kiểm tra chuỗi nào = 255 thì cắt tới 255 rồi nối chuỗi "&" 256 trở đi thử xem được không

LYSM · 9/9/19

VetMini đã viết:
1. Không phải máy nào cũng được phép sửa registry.
2. Có lẽ quý vị quá quen với hai từ "tự động" cho nên nghĩ là chêm dòng là công việc rắc rối. Thực ra rất nhiều người già dặn kinh nghiệm về CSDL sử dụng cách chêm dòng khi cần import dữ liệu Excel vào các CSDL khác.

Thử chêm 10 dòng?

Dạ đúng rồi bác, máy công ty nên không chỉnh được redistry ạ, nhờ bác qua thead này xem giúp em với ạ, em cám ơn!
www.giaiphapexcel.com/diendan/threads/xin-giúp-đỡ-code-tổng-hợp-dữ-liệu-từ-nhiều-file-excel-vào-1-file.142213/

ongke0711 · 9/9/19

VetMini đã viết:
Insert Row đầu tiên. Vào cột cần làm, gõ 1000 ký tự. Save.
Lựa một trang web nào đó có các câu cú dài dài (ví dụ https:// libguides.navitas.com/literature-review/examples)

Vụ vượt qua lỗi chỉ lấy 255 ký tự vào mảng, anh Vetmini hướng dẫn vụ này chính xác đó bạn.
Cái này là do lỗi của Jet OLEDB Provider của ADO chứ không phải do mảng. Nó sẽ tham chiếu 8 dòng đầu trong Excel spreadsheet để xác định kiểu dữ liệu, nếu số ký tự nhỏ hơn 255 thì nó cho là Text nên sẽ giới hạn ký tự. Giải pháp đơn giản là như anh Vietmini có nói là: chèn thêm 1 dòng giả với số ký tự lớn hơn 255. Bạn cứ thử chèn (vào 1 trong 8 dòng đầu của Range mà ADO truy xuất) một đoạn text dài 2.000 ký tự xem, nó cũng trả kết quả như yêu cầu.

Tôi thì hay vô trang: https://www.lipsum.com/ để lấy các đoạn text mẫu với số lượng ký tự mong muốn.

Còn việc tuỳ chỉnh Registry thì chẳng lẻ copy ứng dụng qua máy khác lại phải yêu cầu người dùng thiết lập lại? chưa kể là không được phân quyền như LYSM đã nói.

LYSM · 9/9/19

ongke0711 đã viết:
Vụ vượt qua lỗi chỉ lấy 255 ký tự vào mảng, anh Vetmini hướng dẫn vụ này chính xác đó bạn.
Cái này là do lỗi của Jet OLEDB Provider của ADO chứ không phải do mảng. Nó sẽ tham chiếu 8 dòng đầu trong Excel spreadsheet để xác định kiểu dữ liệu, nếu số ký tự nhỏ hơn 255 thì nó cho là Text nên sẽ giới hạn ký tự. Giải pháp đơn giản là như anh Vietmini có nói là: chèn thêm 1 dòng giả với số ký tự lớn hơn 255. Bạn cứ thử chèn (vào 1 trong 8 dòng đầu của Range mà ADO truy xuất) một đoạn text dài 2.000 ký tự xem, nó cũng trả kết quả như yêu cầu.

Tôi thì hay vô trang: https://www.lipsum.com/ để lấy các đoạn text mẫu với số lượng ký tự mong muốn.

Còn việc tuỳ chỉnh Registry thì chẳng lẻ copy ứng dụng qua máy khác lại phải yêu cầu người dùng thiết lập lại? chưa kể là không được phân quyền như LYSM đã nói.

Vâng, bác giải thích dễ hiểu quá, nhưng em hỏi chút tại sao phải làm nhiều bước như bác VetMini hướng dẫn ạ? Em đánh toàn dấu Space vào dòng đầu tiên và cũng ra kết quả như ý? Có cách nào không phải “lừa” hệ thống như vậy không ạ? Chẳng hạn như 1 Sub phụ để xác định kiểu dữ liệu cho nó luôn không?

ongke0711 · 9/9/19

LYSM đã viết:
Vâng, bác giải thích dễ hiểu quá, nhưng em hỏi chút tại sao phải làm nhiều bước như bác VetMini hướng dẫn ạ? Em đánh toàn dấu Space vào dòng đầu tiên và cũng ra kết quả như ý? Có cách nào không phải “lừa” hệ thống như vậy không ạ? Chẳng hạn như 1 Sub phụ để xác định kiểu dữ liệu cho nó luôn không?

Uh. Tôi cũng quên nói bạn là dùng space cũng được.
vd: text = Space(256)

Còn đoạn mà anh Vetmini hướng dẫn là hình như dùng để copy đoạn văn bản trong trang web của link đính kèm thôi.
Chủ yếu là cái dòng này:

Insert Row đầu tiên. Vào cột cần làm, gõ 1000 ký tự. Save

Còn về Sub phụ để gán kiểu dữ liệu cho Field trong Excel thì có một cách khác đó là tự tạo Table ảo ADO Recordset trên bộ nhớ rồi gán trị từng dòng của Table đó từ mảng. Cách này thì bạn muốn tạo Field kiểu dữ liệu gì cũng được. ADO có kiểu "adLongVarChar" tương đương kiểu Memo (Long Text) trong Access - hình như chứa được 64.000 ký tự.

Bạn đọc bài của bác HLMT có ví dụ vè việc tạo in-memorry ADO Recordset đó.
Link: https://www.giaiphapexcel.com/diendan/threads/cách-truy-vấn-dữ-liệu-trên-cùng-file-không-mở-kết-nối-adodb-connection.141551/

À nhưng mà cách tạo Table ảo ADO thì lại không phù hợp với trường hợp xử lý dữ liệu của bạn vì là truy cập File excel khác thì lấy đâu ra mảng mà gán vào Table

.

befaint · 9/9/19

ongke0711 đã viết:
Uh. Tôi cũng quên nói bạn là dùng space cũng được.
vd: text = Space(256)

Trên bảng tính có hàm Rept() mà anh. @@

ongke0711 · 9/9/19

befaint đã viết:
Trên bảng tính có hàm Rept() mà anh. @@

Hehe..Tôi còn chưa biết cái hàm REPT() này nữa mà.
Chỉ biết tạo chuỗi khoảng trắng bằng code VBA là dùng hàm Space() đó thôi.

LYSM · 9/9/19

ongke0711 đã viết:
Uh. Tôi cũng quên nói bạn là dùng space cũng được.
vd: text = Space(256)

Còn đoạn mà anh Vetmini hướng dẫn là hình như dùng để copy đoạn văn bản trong trang web của link đính kèm thôi.
Chủ yếu là cái dòng này:

Còn về Sub phụ để gán kiểu dữ liệu cho Field trong Excel thì có một cách khác đó là tự tạo Table ảo ADO Recordset trên bộ nhớ rồi gán trị từng dòng của Table đó từ mảng. Cách này thì bạn muốn tạo Field kiểu dữ liệu gì cũng được. ADO có kiểu "adLongVarChar" tương đương kiểu Memo (Long Text) trong Access - hình như chứa được 64.000 ký tự.

Bạn đọc bài của bác HLMT có ví dụ vè việc tạo in-memorry ADO Recordset đó.
Link: https://www.giaiphapexcel.com/diendan/threads/cách-truy-vấn-dữ-liệu-trên-cùng-file-không-mở-kết-nối-adodb-connection.141551/

À nhưng mà cách tạo Table ảo ADO thì lại không phù hợp với trường hợp xử lý dữ liệu của bạn vì là truy cập File excel khác thì lấy đâu ra mảng mà gán vào Table .

Hic, ADO em mù tịt ạ, nếu bác viết giúp em sub khắc phục được nhược điểm 255 ký tự thì tốt quá ạ.

ongke0711 · 9/9/19

LYSM đã viết:
Hic, ADO em mù tịt ạ, nếu bác viết giúp em sub khắc phục được nhược điểm 255 ký tự thì tốt quá ạ.

Bạn dùng code hiện tại của bạn snow25 là tối ưu hơn rồi. Dùng kiểu tạo table nó sẽ đi lòng vòng và không dùng được cho trường hợp của bạn.

LYSM · 10/9/19

ongke0711 đã viết:
Bạn dùng code hiện tại của bạn snow25 là tối ưu hơn rồi. Dùng kiểu tạo table nó sẽ đi lòng vòng và không dùng được cho trường hợp của bạn.

Vâng, em đã khắc phục được rồi ạ, em cám ơn!

Kiều Mạnh · 22/3/20

Mình đang sử dụng code sau để lấy dữ liệu từ File *.CSV lên Excel mà nó lỗi ...
1/ Tiêu đề và Lỗi Font Tiếng việt
2/ xử lý ngay trong ADO khi lấy lên cột ( Format Number ): số tiền nạp ... để cộng ,trừ nhân chia ngay trên Excel ok
3/ Mình có Chỉnh HDR=Yes Or No rồi mà nó lấy lên báo lỗi font tiếng việt và thiếu

( File SIM BANKING!.csv là file xuất ra từ Web của người ta và họ cũng làm khó chút là thêm cái ký tự ! vào tên File)

Xin cảm ơn

Mã:

Rem ==========
Function GetShortName(ByVal FileName_Path As String)
    With CreateObject("Scripting.FileSystemObject")
        If .FileExists(FileName_Path) Then
            GetShortName = .GetFile(FileName_Path).ShortName
        End If
    End With
End Function
Rem ==========
Sub Import_CSV()
    Dim Conn As ADODB.Connection
    Dim Rst As ADODB.Recordset
    Dim FileName As String
    Dim sPath As String
    Dim rSQL As String
    Rem ======
    sPath = ThisWorkbook.Path & "\"
    FileName = GetShortName(sPath & "\SIM BANKING!.csv") ''Xu ly loi ADO ko lay duoc Filename ky tu dac biet!
    Rem ======
    Set Conn = New ADODB.Connection
    Conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Mode=Read;Extended Properties=""Text;HDR=Yes;FMT=Delimited(,)"";"
    Conn.Open
    Rem ======
    Set Rst = Conn.Execute("SELECT * FROM " & "[" & FileName & "]")
    Cells.Clear
    Range("A2").CopyFromRecordset Rst

    Rst.Close: Set Rst = Nothing
    Conn.Close: Set Conn = Nothing
End Sub
Rem ==========

batman1 · 22/3/20

kieu manh đã viết:
Mình đang sử dụng code sau để lấy dữ liệu từ File *.CSV lên Excel mà nó lỗi ...
1/ Tiêu đề và Lỗi Font Tiếng việt
2/ xử lý ngay trong ADO khi lấy lên cột ( Format Number ): số tiền nạp ... để cộng ,trừ nhân chia ngay trên Excel ok
3/ Mình có Chỉnh HDR=Yes Or No rồi mà nó lấy lên báo lỗi font tiếng việt và thiếu

Tập tin CSV đang mã bằng utf-8. Tôi e rằng không dùng được utf-8 đâu. Dùng unicode thì được.

ongke0711 · 22/3/20

kieu manh đã viết:
Mình đang sử dụng code sau để lấy dữ liệu từ File *.CSV lên Excel mà nó lỗi ...
1/ Tiêu đề và Lỗi Font Tiếng việt
2/ xử lý ngay trong ADO khi lấy lên cột ( Format Number ): số tiền nạp ... để cộng ,trừ nhân chia ngay trên Excel ok
3/ Mình có Chỉnh HDR=Yes Or No rồi mà nó lấy lên báo lỗi font tiếng việt và thiếu

( File SIM BANKING!.csv là file xuất ra từ Web của người ta và họ cũng làm khó chút là thêm cái ký tự ! vào tên File)

Như bác batman1 nói tập tin có định dạng UTF-8 (từ web) nên bạn Mạnh chỉ có thể dùng ADODB. Stream để đọc và ghi nó được thôi. ADODB Stream có thể đọc tên file có dấu chấm than (!) luôn nhé.
Ví dụ:

Mã:

sFilePath = ThisWorkbook.Path & "\SIM BANKING!.CSV"
Dim oStream As Object
   Set oStream = CreateObject("ADODB.Stream")
   With oStream
        .Open
        .Type = 1  ' adTypeBinary
        .LoadFromFile sFilePath
        .Type = 2  ' adTypeText
        .Charset = "utf-8"
        strText = .ReadText(-1)  ' adReadAll
    End With
    ...
    'Code đưa strText vào Sheet'

batman1 · 23/3/20

ongke0711 đã viết:
Như bác batman1 nói tập tin có định dạng UTF-8 (từ web) nên bạn Mạnh chỉ có thể dùng ADODB. Stream để đọc và ghi nó được thôi.

Ý tôi là nếu dùng ADO (code trong tập tin) thì không dùng được utf-8. Nếu muốn dùng ADO thì phải mở và lưu lại CSV với mã unicode.

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

Ăn cùng góc phố

Thành viên gạo cội

Ăn cùng góc phố

I don't program, I beat code into submission!!!

Thành viên thường trực

Ăn cùng góc phố

Trùm Nhiều Chuyện

Thành viên thường trực

Thành viên gắn bó

Thành viên thường trực

Thành viên gắn bó

|||||||||||||

Thành viên gắn bó

Thành viên thường trực

Thành viên gắn bó

Thành viên thường trực

I don't program, I beat code into submission!!!

File đính kèm

Thành viên gạo cội

Thành viên gắn bó

Thành viên gạo cội

Đếm ngược thời gian