Lọc dữ liệu từ file text

quangdiepctmbk · 9/8/12

Mình có file text dạng(cột mã vạch lúc nào cũng chỉ có 10 ký tự)
Ngày,Lớp,Mã vạch
20120809,A,123DEH3456
20120810,B,123456FG3E
20120810,B,78904FG3EV
Mình muốn lập một file Excel sẽ đọc dữ liệu từ file text này và hiện lên tất cả dữ liệu của ngày 20120810 nhưng cột mã vạch sẽ tách làm 3 : phần 1 là từ ký tự thứ 1 đến 3, phần 2 là từ 4 đến 7 và phần 3 là còn lại, có dạng như dưới.
Ngày Lớp Phần 1 Phần 2 Phần 3
20120810 B 123 456F G3E
20120810 B 789 04FG 3EV
Cảm ơn mọi người nhiều nhé!

ndu96081631 · 10/8/12

quangdiepctmbk đã viết:
Mình có file text dạng(cột mã vạch lúc nào cũng chỉ có 10 ký tự)
Ngày,Lớp,Mã vạch
20120809,A,123DEH3456
20120810,B,123456FG3E
20120810,B,78904FG3EV
Mình muốn lập một file Excel sẽ đọc dữ liệu từ file text này và hiện lên tất cả dữ liệu của ngày 20120810 nhưng cột mã vạch sẽ tách làm 3 : phần 1 là từ ký tự thứ 1 đến 3, phần 2 là từ 4 đến 7 và phần 3 là còn lại, có dạng như dưới.
Ngày Lớp Phần 1 Phần 2 Phần 3
20120810 B 123 456F G3E
20120810 B 789 04FG 3EV
Cảm ơn mọi người nhiều nhé!

Nói chung là làm được tuốt! Nhưng ít nhất bạn phải cho 2 file lên đây: File Text + file Excel
Thế nhé!

quangdiepctmbk · 10/8/12

Bạn làm giúp mình nhé!

ndu96081631 · 10/8/12

quangdiepctmbk đã viết:
Bạn làm giúp mình nhé!

Các bạn ai đang nghiên cứu VBA thử làm bài này xem!
Không khó lắm, cứ dùng Scripting.FileSystemObject là xong hết

tintam7251 · 10/8/12

Bạn xem thử đã được chưa. File text phải nằm cùng thư mục với file chương trình.
(Bạn mở cửa sổ code ( bấm Alt+F11) vào Tools - References chọn Microsoft Scripting Runtime)

siwtom · 11/8/12

quangdiepctmbk đã viết:
Bạn làm giúp mình nhé!

Góp vui tí.

Mã:

Sub ReadTextFile()
Dim objRE As Object, colMatches As Object, Match As Object, fso As Object, text As String
Dim r As Long, c As Long, ngay As String, lop As String, Arr, t As Double
    t = GetTickCount
    
    On Error Resume Next
    Range("A5:E65536").ClearContents
    Set objRE = CreateObject("VBScript.RegExp")
    Set fso = CreateObject("Scripting.FileSystemObject")
    text = fso.OpenTextFile(ThisWorkbook.Path & "\log.txt").ReadAll
    ngay = Cells(2, 2)
    lop = Cells(3, 2)
    With objRE
        .Global = True
        .Pattern = ngay & "," & lop & "," & "(\w{3})(\w{4})(\w{3})"
        Set colMatches = .Execute(text)
        ReDim Arr(1 To colMatches.Count, 1 To 5)
        For r = 1 To UBound(Arr)
            Set Match = colMatches.Item(r - 1)
            Arr(r, 1) = ngay: Arr(r, 2) = lop
            For c = 3 To 5
                Arr(r, c) = Match.SubMatches(c - 3)
            Next c
        Next r
    End With
    Range("A5").Resize(UBound(Arr), 5).Value = Arr
 
    Set fso = Nothing
    Set Match = Nothing
    Set colMatches = Nothing
    Set objRE = Nothing
    
    Debug.Print (GetTickCount - t) / 1000
End Sub

Nói tóm tắt thì dùng Pattern như trên thì ta có ngay các đoạn cần tách (3 đoạn cuối thôi vì 2 đoạn đầu có trong đk lọc rồi) trong SubMatches, không có chuyện dùng 5 MID ở đây. Và tôi đọc luôn 1 lèo toàn bộ tập tin vào biến text (tôi đã thử cho tập tin 150.000 dòng) chứ không đọc từng dòng một.

Bạn tintam7251 "đổ" kết quả trong vòng lặp xuống sheet thế thì chỉ dùng cho tập tin vài dòng thôi. Nếu dữ liệu nhiều thì chết.
Tôi thử sửa lại qua code của bạn tintam7251 để nhập kết quả vào mảng, xong xuôi rồi mới "đập" xuống sheet. Tôi đã test qua loa cho tập tin có 65536 dòng thì có kết quả (tính bằng giây):
1. code của bạn tintam7251
- 219,225
- 218,885
2. code của bạn tintam7251 sau khi sửa để dùng Array (các câu lệnh khác giữ nguyên)
- 14,511
- 15,022
- 15,302
3. code của tôi như trên
- 5,568
- 5,659
- 5,658
-----------------
Ai tò mò thì toàn bộ 3 code trong tập tin RAR

nmhung49 · 11/8/12

Đối với file này em nghĩ dùng TextToColumn rồi kết hợp với Filter là được dùng chi FSO, REG thấy hơi ghê mà em nghĩ tốc độ thì cũng không chậm cho lắm

ndu96081631 · 11/8/12

siwtom đã viết:
Nói tóm tắt thì dùng Pattern như trên thì ta có ngay các đoạn cần tách (3 đoạn cuối thôi vì 2 đoạn đầu có trong đk lọc rồi) trong SubMatches, không có chuyện dùng 5 MID ở đây. Và tôi đọc luôn 1 lèo toàn bộ tập tin vào biến text (tôi đã thử cho tập tin 150.000 dòng) chứ không đọc từng dòng một.

Bạn tintam7251 "đổ" kết quả trong vòng lặp xuống sheet thế thì chỉ dùng cho tập tin vài dòng thôi. Nếu dữ liệu nhiều thì chết.
Tôi thử sửa lại qua code của bạn tintam7251 để nhập kết quả vào mảng, xong xuôi rồi mới "đập" xuống sheet. Tôi đã test qua loa cho tập tin có 65536 dòng thì có kết quả (tính bằng giây):
1. code của bạn tintam7251
- 219,225
- 218,885
2. code của bạn tintam7251 sau khi sửa để dùng Array (các câu lệnh khác giữ nguyên)
- 14,511
- 15,022
- 15,302
3. code của tôi như trên
- 5,568
- 5,659
- 5,658
-----------------
Ai tò mò thì toàn bộ 3 code trong tập tin RAR

Mình thì sẽ làm theo hướng này:
- Đọc text vào 1 Array (1)
- Dùng hàm Filter2DArray để lọc theo điều kiện (2)
- Riêng phần tách cột cuối thành 3 cột ta làm riêng (3)
Tuy dài dòng nhưng các hàm trong code (1) và (2) còn có thể dùng vào việc khác được!
(Mình thử nghiệm sơ qua theo hướng trên, tốc độ chạy code khoảng dưới 3 giây)

tintam7251 · 11/8/12

Cám ơn bạn siwtom. Quả thật xét về tốc độ thì đúng như bạn nói. Code của bạn ( dùng RegExp) chạy nhanh nhất trong 3 cách viết trên. Tuy nhiên, trên máy mình mức độ chênh lệch không quá lớn: Chạy với 65356 dòng trên máy mình:
-Code đầu tiên chưa tối ưu: 8,158 giây
-Code dùng Array:2,403 giây
-Code dùng RegExp: 1,95 giây
Dù thế nào chỉ cần hơn 1/1000 giây thì cũng khác nhau về đẳng cấp rồi.
Code đầu tiên mình viết chỉ mới giải quyết được vấn đề thôi. Chưa quan tâm tốc độ đối với dữ liệu lớn và tối ưu.

ndu96081631 · 11/8/12

tintam7251 đã viết:
Cám ơn bạn siwtom. Quả thật xét về tốc độ thì đúng như bạn nói. Code của bạn ( dùng RegExp) chạy nhanh nhất trong 3 cách viết trên. Tuy nhiên, trên máy mình mức độ chênh lệch không quá lớn: Chạy với 65356 dòng trên máy mình:
-Code đầu tiên chưa tối ưu: 8,158 giây
-Code dùng Array:2,403 giây
-Code dùng RegExp: 1,95 giây
Dù thế nào chỉ cần hơn 1/1000 giây thì cũng khác nhau về đẳng cấp rồi.
Code đầu tiên mình viết chỉ mới giải quyết được vấn đề thôi. Chưa quan tâm tốc độ đối với dữ liệu lớn và tối ưu.

Vấn đề đặt biến và tính toán thông qua các biến cũng ảnh hưởng rất nhiều đến tốc độ
Giả sử bạn sửa code của bạn thành vầy:

Mã:

Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub ReadFromTextFile()
  Dim ff As String, aLine, Arr() as String, t As Double
  Dim lR As Long, lC As Long
  Dim Crit1 As String, Crit2 As String
  Dim tmp1 As String, tmp2 As String, tmp3 As String
  t = GetTickCount
  Range("A5:E65536").ClearContents
  ReDim Arr(1 To 65536, 1 To 5)
  Crit1 = Range("B2").Text
  Crit2 = Range("B3").Text
  With CreateObject("Scripting.FileSystemObject")
    With .OpenTextFile(ThisWorkbook.Path & "\log.txt", 1, , -2)
      While Not .AtEndOfStream
        ff = .ReadLine
        aLine = Split(ff, ",")
        tmp1 = Trim(aLine(0))
        tmp2 = Trim(aLine(1))
        tmp3 = Trim(aLine(2))
        If CStr(tmp1) Like Crit1 Then
          If CStr(tmp2) Like Crit2 Then
            lR = lR + 1
            Arr(lR, 1) = tmp1
            Arr(lR, 2) = tmp2
            Arr(lR, 3) = Left(tmp3, 3)
            Arr(lR, 4) = Mid(tmp3, 4, 4)
            Arr(lR, 5) = Right(tmp3, 3)
          End If
        End If
      Wend
      .Close
    End With
  End With
  If lR Then Range("A5:E5").Resize(lR).Value = Arr
  MsgBox (GetTickCount - t) / 1000
End Sub

Thì khi test thử trên máy bạn nó cho tốc độ bao nhiêu?

tintam7251 · 11/8/12

ndu96081631 đã viết:

Vấn đề đặt biến và tính toán thông qua các biến cũng ảnh hưởng rất nhiều đến tốc độ
Giả sử bạn sửa code của bạn thành vầy:

Mã:

Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub ReadFromTextFile()
  Dim ff As String, aLine, Arr() as String, t As Double
  Dim lR As Long, lC As Long
  Dim Crit1 As String, Crit2 As String
  Dim tmp1 As String, tmp2 As String, tmp3 As String
  t = GetTickCount
  Range("A5:E65536").ClearContents
  ReDim Arr(1 To 65536, 1 To 5)
  Crit1 = Range("B2").Text
  Crit2 = Range("B3").Text
  With CreateObject("Scripting.FileSystemObject")
    With .OpenTextFile(ThisWorkbook.Path & "\log.txt", 1, , -2)
      While Not .AtEndOfStream
        ff = .ReadLine
        aLine = Split(ff, ",")
        tmp1 = Trim(aLine(0))
        tmp2 = Trim(aLine(1))
        tmp3 = Trim(aLine(2))
        If CStr(tmp1) Like Crit1 Then
          If CStr(tmp2) Like Crit2 Then
            lR = lR + 1
            Arr(lR, 1) = tmp1
            Arr(lR, 2) = tmp2
            Arr(lR, 3) = Left(tmp3, 3)
            Arr(lR, 4) = Mid(tmp3, 4, 4)
            Arr(lR, 5) = Right(tmp3, 3)
          End If
        End If
      Wend
      .Close
    End With
  End With
  If lR Then Range("A5:E5").Resize(lR).Value = Arr
  MsgBox (GetTickCount - t) / 1000
End Sub

Thì khi test thử trên máy bạn nó cho tốc độ bao nhiêu?

Rất tuyệt. Test trên máy mình, với 65536 dòng chỉ mất 0,656 giây.
Mình chưa rõ những yếu tố nào trong code giúp cải thiện tốc độ so với đoạn code cùng thuật toán (xử lý dữ liệu trong mảng )ở trên. Nhờ bạn phân tích thêm.

ndu96081631 · 11/8/12

tintam7251 đã viết:
Rất tuyệt. Test trên máy mình, với 65536 dòng chỉ mất 0,656 giây.
Mình chưa rõ những yếu tố nào trong code giúp cải thiện tốc độ so với đoạn code cùng thuật toán (xử lý dữ liệu trong mảng )ở trên. Nhờ bạn phân tích thêm.

Chỉ là đặt biến tạm thôi mà. Cái nào thường xài nhiều ta sẽ cho vào biến tạm để truy xuất cho nhanh
Ví dụ: Bạn thử bỏ 2 biến Crit1 và Crit2 rồi cho chúng trực tiếp vào code xem
Thay vì

Mã:

If CStr(tmp1) Like Crit1 Then
  If CStr(tmp2) Like Crit2 Then

Bạn đưa trực tiếp vào thế này:

Mã:

If CStr(tmp1) Like Range("B2").Text Then
  If CStr(tmp2) Like Range("B2").Text Then

rồi thử lại xem tốc độ bao nhiêu

tintam7251 · 11/8/12

Mã:

Sub ReadFromTextFileArray()
Dim fs As Scripting.FileSystemObject, f As Scripting.TextStream, ff As String
Dim l As Long, t As Double, Arr
  t = GetTickCount
  Set fs = New FileSystemObject
 Set f = fs.OpenTextFile(ThisWorkbook.Path & "\log.txt", _
                            ForReading, False)
    ReDim Arr(1 To 65536, 1 To 5)
    Range([A5], [E65536].End(3)).ClearContents
    With f
        l = 1
        Crit1 = Cells(2, 2)
        Crit2 = Cells(3, 2)
        While Not .AtEndOfStream
            ff = .ReadLine
        If Mid(ff, 1, 8) = Crit1 And Mid(ff, 10, 1) = Crit2 Then
            Arr(l, 1) = Crit1
            Arr(l, 2) = Crit2
            Arr(l, 3) = Mid(ff, 12, 3)
            Arr(l, 4) = Mid(ff, 15, 4)
            Arr(l, 5) = Mid(ff, 19, 3)
            l = l + 1
        End If
        Wend
        .Close
    End With
    Range("A5").Resize(UBound(Arr), 5).Value = Arr
    Set f = Nothing
    Set fs = Nothing
    MsgBox (GetTickCount - t) / 1000
End Sub

Đoạn này sau khi dùng biến tạm, tốc độ được cải thiện thấy rõ: 1,888 giây ( thay vì 2,403). Tuy nhiên vẫn chưa bằng code của bạn. Phải chăng dùng Mid chậm hơn Left, Right?

ndu96081631 · 11/8/12

tintam7251 đã viết:
Đoạn này sau khi dùng biến tạm, tốc độ được cải thiện thấy rõ: 1,888 giây ( thay vì 2,403). Tuy nhiên vẫn chưa bằng code của bạn. Phải chăng dùng Mid chậm hơn Left, Right?

Tôi chẳng biết nữa nhưng... có cái này chắc còn nhanh hơn nữa nè:

Mã:

Sub ReadFromTextFile3()
  Dim aLine, tmpArr, Arr() As String, t As Double
  Dim lR As Long, lC As Long, i As Long, lCount As Long
  Dim Crit1 As String, Crit2 As String, Text As String
  Dim tmp1 As String, tmp2 As String, tmp3 As String, sLine As String
  t = GetTickCount
  Range("A5:E65536").ClearContents
  Crit1 = Range("B2").Text
  Crit2 = Range("B3").Text
  With CreateObject("Scripting.FileSystemObject")
    With .OpenTextFile(ThisWorkbook.Path & "\log.txt", 1, , -2)
      Text = .ReadAll: lCount = .Line
      ReDim Arr(1 To lCount, 1 To 5)
      tmpArr = Split(Text, vbCrLf)
      For i = 1 To lCount
        sLine = tmpArr(i - 1)
        aLine = Split(sLine, ",")
        tmp1 = Trim(aLine(0))
        tmp2 = Trim(aLine(1))
        tmp3 = Trim(aLine(2))
        If CStr(tmp1) Like Crit1 Then
          If CStr(tmp2) Like Crit2 Then
            lR = lR + 1
            Arr(lR, 1) = tmp1
            Arr(lR, 2) = tmp2
            Arr(lR, 3) = Left(tmp3, 3)
            Arr(lR, 4) = Mid(tmp3, 4, 4)
            Arr(lR, 5) = Right(tmp3, 3)
          End If
        End If
      Next
      .Close
    End With
  End With
  If lR Then Range("A5:E5").Resize(lR).Value = Arr
  MsgBox (GetTickCount - t) / 1000
End Sub

Lưu ý: Tôi nghĩ dùng Like sẽ hay hơn so sánh bằng. Ví dụ Cell B2 tôi gõ 20120810, cell B3 tôi gõ dấu * thì có nghĩa là lọc Ngày = 20120810 còn Lớp thì.. sao cũng được
----------------
Mà nè! Bạn phải dựa vào dấu phân cách mà tách chứ, sao lại Mid tùm lum thế kia? Nếu độ dài chuổi của mỗi dòng khác nhau thì bạn tính sao?

tintam7251 · 11/8/12

Đây là một kinh nghiệm quý. Dùng Mid vì nó trực tiếp xử lý chuỗi, không cần qua công đoạn Split. Split cũng cho ra chuỗi. Sau đó không Mid thì cũng Left, Right. Không ngờ thêm Split mà tốc độ lại nhanh hơn. Nếu độ dài chuỗi mỗi dòng thay đổi thì số ký tự trích xuất từ mỗi dòng cũng thay đổi và như thế viết cách nào cũng cần chỉnh lại code.

ndu96081631 · 11/8/12

tintam7251 đã viết:
Đây là một kinh nghiệm quý. Dùng Mid vì nó trực tiếp xử lý chuỗi, không cần qua công đoạn Split. Split cũng cho ra chuỗi. Sau đó không Mid thì cũng Left, Right. Không ngờ thêm Split mà tốc độ lại nhanh hơn. Nếu độ dài chuỗi mỗi dòng thay đổi thì số ký tự trích xuất từ mỗi dòng cũng thay đổi và như thế viết cách nào cũng cần chỉnh lại code.

Đâu có chứ
Nếu ta đã quyết định Import file text vào Excel thì cho dù dùng công cụ có sẵn nó cũng bắt ta phải xác định ký tự phân cách (mới biết để phân ra các cột chứ)
Vậy nên dù là nhập liệu theo cách gì, dù là độ dài chuổi mỗi dòng có khác nhau thì cũng phải có 1 dấu hiệu nhận biết, đó là DẤU PHÂN CÁCH
Dựa vào DẤU PHÂN CÁCH ấy, ta cứ việc tách ra các cột mà không cần phải quan tâm đến độ dài chuổi từng dòng (dài bi nhiêu thây kệ nó)
Bạn cứ việc thử đi. Ví dụ cột Lớp là ký tự "BC" (thay vì là "B") cũng chẳng cần phải sửa code tí nào cả

tintam7251 · 11/8/12

Đối với cột ngày và lớp thì đúng như thế. Dù có tăng bao nhiêu ký tự, khi split ta vẫn lấy được nguyên cột. Với cột mã vạch thì khác. Hiện nay cột này gồm 12 ký tự - 2 dấu phân cách = 10 kt, chia làm 3: 3 - 4 - 3. Nếu phần này thêm ký tự thì phần chia cũng sẽ tăng thêm ký tự và như thế phải chỉnh lại code.

Mã:

Arr(lR, 3) = Left(tmp3, [B]3[/B])
 Arr(lR, 4) = Mid(tmp3, 4, [B]4[/B])
Arr(lR, 5) = Right(tmp3, [B]3[/B])

Mấy con số in đậm có thể phải chỉnh khi phần này tăng thêm ký tự.

Quang_Hải · 11/8/12

Góp vui tí với code đơn giản để giải quyết bài toán này

PHP:

Sub test()
Application.ScreenUpdating = False
Dim Arr, kq(), i as long
Workbooks.OpenText ThisWorkbook.Path & "\Log.txt", DataType:=xlDelimited, Comma:=True
With ActiveWorkbook.ActiveSheet
   Arr = .Range(.[a2], .[c65536].End(3)).Value
   ReDim kq(1 To UBound(Arr), 1 To 5)
   For i = 1 To UBound(Arr)
      kq(i, 1) = Arr(i, 1)
      kq(i, 2) = Arr(i, 2)
      kq(i, 3) = Left(Arr(i, 3), 3)
      kq(i, 4) = Mid(Arr(i, 3), 3, 4)
      kq(i, 5) = Right(Arr(i, 3), Len(Arr(i, 3)) - 7)
   Next
   ActiveWorkbook.Close False
   [A5].Resize(i - 1, 5) = kq
End With
Application.ScreenUpdating = True
End Sub

siwtom · 11/8/12

ndu96081631 đã viết:
Mình thì sẽ làm theo hướng này:
- Đọc text vào 1 Array (1)
- Dùng hàm Filter2DArray để lọc theo điều kiện (2)
- Riêng phần tách cột cuối thành 3 cột ta làm riêng (3)
Tuy dài dòng nhưng các hàm trong code (1) và (2) còn có thể dùng vào việc khác được!
(Mình thử nghiệm sơ qua theo hướng trên, tốc độ chạy code khoảng dưới 3 giây)

Bạn nói hoàn toàn đúng. Mỗi người lập trình đều có 1 thư viện nhỏ gồm nhiều hàm để sử dụng trong nhiều công việc. Những hàm như Filter2DArray là những hàm nằm trong thư viện "tủ" mà mỗi bạn cần có.
Riêng tôi thời gian gần đây tôi "cố tình" dùng RegExp để hướng những người quan tâm tới RegExp. Pattern rất đa dạng và nếu lựa chọn Pattern thích hợp thì ta có thể giải quyết được nhiều vấn đề. Mục đích gần đây của tôi là thế.
Tất nhiên Pattern thế nào thì tùy cơ ứng biến. vd. dữ liệu có thể có chỗ này dấu "," là dấu phân cách chỗ kia là dấu ";", " ", "-" (gần đây đã có người đưa dữ liệu như thế) và với mỗi trường hợp đó trước và sau dấu phân cách có thể có dấu " " với độ dài tùy ý. Thậm chí dữ liệu của người dùng có thể không có dấu phân cách vd. 20120809A1234FB2145. Với dữ liệu không có dấu phân cách thì dùng Filter2DArray và RegExp và MID đều được nhưng lúc đó không dùng được Split để tách các đoạn.
Nói chung càng có nhiều giải pháp thì càng hay. Ta viết trên GPE cũng là để cho nhiều người khác quan tâm đọc chứ không chỉ riêng giải quyết vấn đề cho người hỏi vì nếu như vậy thì gửi vào e-mail của họ cho rồi. Mà những người quan quan tâm có thể có dữ liệu cấu trúc hơi khác. Có nhiều giải pháp thì người đọc mỗi lần sẽ học được thêm 1, 2 điều mới mẻ.

ndu96081631 · 11/8/12

siwtom đã viết:
Riêng tôi thời gian gần đây tôi "cố tình" dùng RegExp để hướng những người quan tâm tới RegExp. Pattern rất đa dạng và nếu lựa chọn Pattern thích hợp thì ta có thể giải quyết được nhiều vấn đề. Mục đích gần đây của tôi là thế.
.

Tôi biết đến RegExp khá lâu nhưng nói thật lòng là chỉ biết sơ sơ "ngoài da" thôi (do không học trường lớp)
Thấy bạn rất hiểu về em này nên tôi hy vọng bạn sẽ viết thêm nhiều bài liên quan đến nó cho mọi người học hỏi
Cảm ơn trước
(qua code của bạn trong topic này, tôi lại được học thêm 1 chút về RegExp rồi đấy)

Lọc dữ liệu từ file text

Thành viên hoạt động

Huyền thoại GPE

Thành viên hoạt động

File đính kèm

Huyền thoại GPE

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

File đính kèm

Thành viên gắn bó

File đính kèm

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

Huyền thoại GPE

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

Huyền thoại GPE

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

Huyền thoại GPE

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

Huyền thoại GPE

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

Huyền thoại GPE

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

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

Thành viên gắn bó

Huyền thoại GPE

Facebook

Group