Nhờ giúp đỡ Đọc dữ liệu từ nhiều file text có cấu trúc giống nhau và ghi dữ liệu vào bảng tính Excel

huypas115 · 22/8/24

Em chào các anh/chị trên diễn đàn GPE.
Nhờ các anh chị giúp vấn đề này với, nội dung như sau:
-Em có nhiều file text (.txt) có cấu trúc giống nhau (do xuất dữ liệu từ một phần mềm data). file xếp theo các lớp folder theo năm, tháng, ngày
-Em muốn xây dựng một tool nhỏ đọc hàng loạt các file .txt trên và ghi dữ liệu ra bảng tính excel theo mẫu đính kèm (chỉ cần 01 vài trường dữ liệu).
(đính kèm thư mục file .txt, file đọc dữ liệu.xlsb tạo sẵn, có mô tả các trường dữ liệu cần lấy)
Em cám ơn.

snow25 · 22/8/24

huypas115 đã viết:
Em chào các anh/chị trên diễn đàn GPE.
Nhờ các anh chị giúp vấn đề này với, nội dung như sau:
-Em có nhiều file text (.txt) có cấu trúc giống nhau (do xuất dữ liệu từ một phần mềm data). file xếp theo các lớp folder theo năm, tháng, ngày
-Em muốn xây dựng một tool nhỏ đọc hàng loạt các file .txt trên và ghi dữ liệu ra bảng tính excel theo mẫu đính kèm (chỉ cần 01 vài trường dữ liệu).
(đính kèm thư mục file .txt, file đọc dữ liệu.xlsb tạo sẵn, có mô tả các trường dữ liệu cần lấy)
Em cám ơn.

File excel trắng bạn à.Cái này dùng VBA để lấy dữ liệu bạn à.

ongke0711 · 22/8/24

huypas115 đã viết:
-Em có nhiều file text (.txt) có cấu trúc giống nhau (do xuất dữ liệu từ một phần mềm data). file xếp theo các lớp folder theo năm, tháng, ngày
-Em muốn xây dựng một tool nhỏ đọc hàng loạt các file .txt trên và ghi dữ liệu ra bảng tính excel theo mẫu đính kèm (chỉ cần 01 vài trường dữ liệu).

Cái đề tài này không khó nhưng cũng hay hay để kiểm tra các cách xử lý xem cách nào nhanh hơn đây.
File mẫu có 6 tháng (6 folders),1 folder tháng có 30 folder con cho ngày. Trung bình 1 tháng có tổng cộng khoảng 70.000 dòng text file.
Dùng power query cho folder tôi thấy chạy cũng miệt mài, dùng FSO đọc file thì chưa thử, tôi cũng đang thử cách dùng CMD gộp file text lại với nhau rồi xử lý xem có nhanh hơn không. Python thì khỏi nói vì không biết nó.

ptm0412 · 23/8/24

Power query 25 giây, 26,190 dòng.
5 phút đo 1 lần, => 1 ngày 288 lần, 3 tháng (91 ngày) là 26,208 lần đo. Thực tế ít hơn 1 chút.

JavaScript:

let
    FPath = Excel.CurrentWorkbook(){[Name = "FolderPath"]}[Content]{0}[Column1] ,
    Source = Folder.Files(FPath),
    Source1 = Table.SelectColumns(Source,{"Content", "Name", "Folder Path"}),
    AddData = Table.AddColumn(Source, "Data", each Csv.Document(File.Contents([Folder Path] & "\" & [Name]),
            [Delimiter="    ", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]))[Data],
    RawData = List.Transform(AddData, each
    let
        AddTitle = Table.AddColumn(_,"Title", each (if (Text.Length([Column3]) > 0) then [Column1] & " (" & [Column3] & ")" else [Column1])),
        ChooseCols = Table.SelectColumns(AddTitle,{"Title","Column2", "Column4"}),
        TransData = Table.PromoteHeaders(Table.Transpose(ChooseCols)),
        n = TransData[pH]{1},
        AddYear = Table.AddColumn(TransData, "Year", each Number.From(Text.Start(n, 4)), Int64.Type),
        AddMonth = Table.AddColumn(AddYear, "Month", each Number.From(Text.Middle(n, 4,2)), Int64.Type),
        AddDay = Table.AddColumn(AddMonth, "Day", each Number.From(Text.Middle(n, 6,2)), Int64.Type),
        AddHour = Table.AddColumn(AddDay, "Hour", each #time(Number.From(Text.Middle(n, 8,2)),
            Number.From(Text.Middle(n, 10,2)), Number.From(Text.Middle(n, 12,2))) )
    in Table.FirstN(AddHour,1)),
    Result = Table.Combine(RawData)
in
    Result

ongke0711 · 23/8/24

ptm0412 đã viết:
Power query 25 giây, 26,190 dòng.
5 phút đo 1 lần, => 1 ngày 288 lần, 3 tháng (91 ngày) là 26,208 lần đo. Thực tế ít hơn 1 chút.

Bác cho xin cái file. Tôi copy code của bác vô mà sao nó cứ chạy hoài, không biết làm sai đoạn nào.

ptm0412 · 23/8/24

ongke0711 đã viết:
Bác cho xin cái file. Tôi copy code của bác vô mà sao nó cứ chạy hoài, không biết làm sai đoạn nào.

Tôi đang xài Excel 365, và nó lấy được xuống tới thư mục con cấp nhỏ nhất. Máy cũng tương đối mạnh.
Sửa đường dẫn ở ô A1 rồi refresh. Tôi mới test lại: vừa mở file thì refresh 40 giây, sau đó chỉ 25 - 30 giây

ongke0711 · 23/8/24

ptm0412 đã viết:
Tôi đang xài Excel 365, và nó lấy được xuống tới thư mục con cấp nhỏ nhất. Máy cũng tương đối mạnh.
Sửa đường dẫn ở ô A1 rồi refresh. Tôi mới test lại: vừa mở file thì refresh 40 giây, sau đó chỉ 25 - 30 giây

Máy bác quá mạnh. Tôi quay phim thời gian thực chạy trên máy tôi mất 1s nó mớ đọc xong 1 file

.
Để rảnh dùng FSO xử lý rồi nhờ bác chạy thử xem như thế nào (cho nó cùng cấu hình máy).

ptm0412 · 23/8/24

ongke0711 đã viết:
Máy bác quá mạnh. Tôi quay phim thời gian thực chạy trên máy tôi mất 1s nó mớ đọc xong 1 file .

Tôi làm Power query theo cách chân phương nghĩa là sử dụng logic đơn giản, code dùng hàm đơn giản. Rất ít khi tôi lồng nhiều hàm trong 1 câu lệnh trừ khi hàm chỉ 1 đối số.
Cũng vì vậy nên code tôi chưa chắc đã tối ưu, không biết có ai viết tốt hơn không.

BuiQuangThuan · 24/8/24

ongke0711 đã viết:
Để rảnh dùng FSO xử lý rồi nhờ bác chạy thử xem như thế nào (cho nó cùng cấu hình máy).

Viết bằng fso chạy cũng mất khoảng 25s anh ạ

ongke0711 · 24/8/24

BuiQuangThuan đã viết:
Viết bằng fso chạy cũng mất khoảng 25s anh ạ

Bạn đưa bác @ptm0412 chạy thử trên máy bác xem mới so sánh được, nhiều khi máy bạn còn mạnh hơn máy bác ấy thì sao

.

ongke0711 · 24/8/24

BuiQuangThuan đã viết:
Viết bằng fso chạy cũng mất khoảng 25s anh ạ

Không biết bạn xử lý bằng FSO đọc từng file rồi lưu xuống Excel sheet từng file hay đọc hết text file rồi lưu xuống 1 lần vào Excel sheet?.
Tôi cũng thử một cách khác là dùng PowerShell gộp file text lại thành file tổng rồi xử lý dựa trên file tổng đó, mà máy chạy chậm quá

.

JavaScript:

Get-ChildItem -path "C:\Temp\2024\" -file -filter "*.txt" -recurse |
    % { Out-File -filepath "C:\Temp\2024\final.txt" -inputobject (get-content $_.fullname) -Append}

BuiQuangThuan · 24/8/24

ongke0711 đã viết:
Không biết bạn xử lý bằng FSO đọc từng file rồi lưu xuống Excel sheet từng file hay đọc hết text file rồi lưu xuống 1 lần vào Excel sheet?.
Tôi cũng thử một cách khác là dùng PowerShell gộp file text lại thành file tổng rồi xử lý dựa trên file tổng đó, mà máy chạy chậm quá .

JavaScript:

Get-ChildItem -path "C:\Temp\2024\" -file -filter "*.txt" -recurse | % { Out-File -filepath "C:\Temp\2024\final.txt" -inputobject (get-content $_.fullname) -Append}

Em có lấy đường dẫn từng file 1 bằng FSO. xong mở từng file 1 lấy dữ liệu vào mảng. Trên máy của em nó chạy cũng tầm khoảng 25s gì đó

Mã:

Sub TongHop()
    Dim folderPath As String, fileList As Collection, fileName As Variant, s, k&, data()
    Dim startTime As Double, fd As FileDialog, txtFile As TextStream, sText As String
    Dim endTime As Double, FSo As Scripting.FileSystemObject
    Dim elapsedTime As Double, fileNumber
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    If fd.Show = -1 Then
        folderPath = fd.SelectedItems(1)
    Else
        MsgBox "Ban chua chon thu muc nao"
    End If
    startTime = Timer
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set fileList = New Collection
    Set FSo = New Scripting.FileSystemObject
    Call GetFilesInFolder(folderPath, fileList)
    ReDim data(1 To fileList.Count, 1 To 12)
    For Each fileName In fileList
        fileNumber = FreeFile
        Open fileName For Input As #fileNumber
            sText = Input$(LOF(fileNumber), fileNumber)
        Close #fileNumber
        s = Split(sText, vbCrLf)
        If UBound(s) > 5 Then
            k = k + 1
            data(k, 1) = k
            data(k, 2) = Left(Split(s(0), vbTab)(3), 4)
            data(k, 3) = Mid(Split(s(0), vbTab)(3), 5, 2)
            data(k, 4) = Mid(Split(s(0), vbTab)(3), 7, 2)
            data(k, 5) = Mid(Split(s(0), vbTab)(3), 9, 2) & ":" & Mid(Split(s(0), vbTab)(3), 11, 2) & ":" & Mid(Split(s(0), vbTab)(3), 13, 2)
            data(k, 6) = Split(s(0), vbTab)(1)
            data(k, 7) = Split(s(1), vbTab)(1)
            data(k, 8) = Split(s(2), vbTab)(1)
            data(k, 9) = Split(s(3), vbTab)(1)
            data(k, 10) = Split(s(4), vbTab)(1)
            data(k, 11) = Split(s(5), vbTab)(1)
            data(k, 12) = Split(s(6), vbTab)(1)
        End If
    Next
    If k > 0 Then
        With Sheets("Sheet1")
            .Range("A3").Resize(65000, 12).ClearContents
            .Range("B3:E3").Resize(k).NumberFormat = "@"
            .Range("A3").Resize(k, 12).Value = data
        End With
    End If
    endTime = Timer
    elapsedTime = endTime - startTime
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Hoàn Thành :" & elapsedTime & "s"
    End Sub
Sub GetFilesInFolder(ByVal folderPath As String, ByRef fileList As Collection)
    Dim file As String, subFolder As Variant, folder As Object, FSo As Object
    Set FSo = CreateObject("Scripting.FileSystemObject")
    Set folder = FSo.GetFolder(folderPath)
    file = Dir(folderPath & "\*.txt")
    Do While file <> ""
        fileList.Add folderPath & "\" & file
        file = Dir
    Loop
    For Each subFolder In folder.SubFolders
        Call GetFilesInFolder(subFolder.Path, fileList)
    Next subFolder
End Sub

Kiều Mạnh · 24/8/24

BuiQuangThuan đã viết:

Em có lấy đường dẫn từng file 1 bằng FSO. xong mở từng file 1 lấy dữ liệu vào mảng. Trên máy của em nó chạy cũng tầm khoảng 25s gì đó

Mã:

Sub TongHop()
    Dim folderPath As String, fileList As Collection, fileName As Variant, s, k&, data()
    Dim startTime As Double, fd As FileDialog, txtFile As TextStream, sText As String
    Dim endTime As Double, FSo As Scripting.FileSystemObject
    Dim elapsedTime As Double, fileNumber
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    If fd.Show = -1 Then
        folderPath = fd.SelectedItems(1)
    Else
        MsgBox "Ban chua chon thu muc nao"
    End If
    startTime = Timer
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set fileList = New Collection
    Set FSo = New Scripting.FileSystemObject
    Call GetFilesInFolder(folderPath, fileList)
    ReDim data(1 To fileList.Count, 1 To 12)
    For Each fileName In fileList
        fileNumber = FreeFile
        Open fileName For Input As #fileNumber
            sText = Input$(LOF(fileNumber), fileNumber)
        Close #fileNumber
        s = Split(sText, vbCrLf)
        If UBound(s) > 5 Then
            k = k + 1
            data(k, 1) = k
            data(k, 2) = Left(Split(s(0), vbTab)(3), 4)
            data(k, 3) = Mid(Split(s(0), vbTab)(3), 5, 2)
            data(k, 4) = Mid(Split(s(0), vbTab)(3), 7, 2)
            data(k, 5) = Mid(Split(s(0), vbTab)(3), 9, 2) & ":" & Mid(Split(s(0), vbTab)(3), 11, 2) & ":" & Mid(Split(s(0), vbTab)(3), 13, 2)
            data(k, 6) = Split(s(0), vbTab)(1)
            data(k, 7) = Split(s(1), vbTab)(1)
            data(k, 8) = Split(s(2), vbTab)(1)
            data(k, 9) = Split(s(3), vbTab)(1)
            data(k, 10) = Split(s(4), vbTab)(1)
            data(k, 11) = Split(s(5), vbTab)(1)
            data(k, 12) = Split(s(6), vbTab)(1)
        End If
    Next
    If k > 0 Then
        With Sheets("Sheet1")
            .Range("A3").Resize(65000, 12).ClearContents
            .Range("B3:E3").Resize(k).NumberFormat = "@"
            .Range("A3").Resize(k, 12).Value = data
        End With
    End If
    endTime = Timer
    elapsedTime = endTime - startTime
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Hoàn Thành :" & elapsedTime & "s"
    End Sub
Sub GetFilesInFolder(ByVal folderPath As String, ByRef fileList As Collection)
    Dim file As String, subFolder As Variant, folder As Object, FSo As Object
    Set FSo = CreateObject("Scripting.FileSystemObject")
    Set folder = FSo.GetFolder(folderPath)
    file = Dir(folderPath & "\*.txt")
    Do While file <> ""
        fileList.Add folderPath & "\" & file
        file = Dir
    Loop
    For Each subFolder In folder.SubFolders
        Call GetFilesInFolder(subFolder.Path, fileList)
    Next subFolder
End Sub

View attachment 303383

Tò mò tôi thử code bài này chỉ copy xong để y trang chạy mã nó như sau ... Nhanh có lẻ máy Tôi sử dụng

Ram DDR5 16GB 6000MHz ... hãy thử code trên cùng một máy mới xác định nhanh và chậm

ongke0711 · 24/8/24

BuiQuangThuan đã viết:
Em có lấy đường dẫn từng file 1 bằng FSO. xong mở từng file 1 lấy dữ liệu vào mảng. Trên máy của em nó chạy cũng tầm khoảng 25s gì đó

À..bạn dùng Freefile chứ không phải FSO text stream.

ptm0412 · 24/8/24

Máy tôi thiếu TextStream và Scripting.FileSystemObject, không rõ nó là thư viện nào.

kokano90 · 24/8/24

ptm0412 đã viết:
Máy tôi thiếu TextStream và Scripting.FileSystemObject, không rõ nó là thư viện nào.

Hình như nó là Scripting Runtime thầy ạ.

Bài đã được tự động gộp: 24/8/24

hocexcel_1991 · 24/8/24

Các bác thử chạy M code sau có nhanh hơn không? Máy mình cấu hình thấp ram 4g.. chạy hơn 15s.

Mã:

// nuocthai
let
    Source = Folder.Files("C:\Users\Admin\Downloads\GPE\nuocthai"),
    AddCol = Table.AddColumn(Source, "T", each Csv.Document([Content],[Delimiter="    ", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None])),
    TransCol = Table.TransformColumns(AddCol, {"T", each
            let
            combinecol = Table.CombineColumns(_,{"Column1","Column3"}, each Text.Replace(Text.Combine(_,"(")&")","()",""),"NewCol"),
            buff = Table.Buffer(combinecol)
            in
            List.Accumulate({{"Year",0,4,Number.From},{"Month",4,2,Number.From},{"Day",6,2,Number.From},{"Time",8,4,Time.From}},#table(buff[NewCol],{buff[Column2]}),(x,y)=>Table.AddColumn(x,y{0}, each Function.Invoke(y{3},{Text.Middle(buff[Column4]{0},y{1},y{2})}))) }),
    SelectCol = Table.SelectColumns(TransCol,{"T"}),
    colname = Table.ColumnNames(SelectCol[T]{0}),
    Expand = Table.ExpandTableColumn(SelectCol, "T", colname, colname),
    RemoveErrors = Table.RemoveRowsWithErrors(Expand, {"Month"}),
    AddSTT = Table.AddIndexColumn(RemoveErrors, "STT", 1, 1, Int64.Type),
    Result = Table.SelectColumns(AddSTT,{"STT"}&List.LastN(colname,4)&List.FirstN(colname,7))
in
    Result

BuiQuangThuan · 24/8/24

Lúc đầu em có sử dụng fso strem. Nhưng nó lỗi 1 số file. Không hiểu là lí do ở chỗ nào. Nên đành phải dùng Freefile anh ạ

ongke0711 đã viết:
À..bạn dùng Freefile chứ không phải FSO text stream.

Chọn thư viện Microsoft scripting runtime chú ạ

Bài đã được tự động gộp: 24/8/24

ptm0412 đã viết:
Máy tôi thiếu TextStream và Scripting.FileSystemObject, không rõ nó là thư viện nào.

Kiều Mạnh · 24/8/24

1/ trên VBA áp dụng cho thớt này sử dụng Open fileName For Input As #fileNumber cho nhanh chỉ khoãng trên 2 giây với máy của Tôi

2/ Power query mất khoãng 16 giây ... dẹp đi cho khoẻ trừ khi cần các tính năng khác của Power query mà ta chưa có khả năng viết

ongke0711 · 24/8/24

BuiQuangThuan đã viết:
Em có lấy đường dẫn từng file 1 bằng FSO. xong mở từng file 1 lấy dữ liệu vào mảng. Trên máy của em nó chạy cũng tầm khoảng 25s gì đó

Tôi chạy code này mất có 7s là xong, còn power query thì nó cứ chạy hoài

.

Nhờ giúp đỡ Đọc dữ liệu từ nhiều file text có cấu trúc giống nhau và ghi dữ liệu vào bảng tính Excel

Thành viên mới

File đính kèm

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

Thành viên gắn bó

Bad Excel Member

Thành viên gắn bó

Bad Excel Member

File đính kèm

Thành viên gắn bó

Bad Excel Member

❆❆❆❆❆❆❆❆❆❆❆❆

Thành viên gắn bó

Thành viên gắn bó

❆❆❆❆❆❆❆❆❆❆❆❆

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

Ram DDR5 16GB 6000MHz ... hãy thử code trên cùng một máy mới xác định nhanh và chậm​

Thành viên gắn bó

Bad Excel Member

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

Bắt đầu học Excel

❆❆❆❆❆❆❆❆❆❆❆❆

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

Thành viên gắn bó

Ram DDR5 16GB 6000MHz ... hãy thử code trên cùng một máy mới xác định nhanh và chậm