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 (1 người xem)

[huypas115]

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]

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]

-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]

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.

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]

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]

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]

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]

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]

Để 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]

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]

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á .

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

 

[BuiQuangThuan]

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á .

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ì đó

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]

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ì đó

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]

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]

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

 

[kokano90]

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]

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.

// 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]

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 ạ

À..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

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

 

[Kiều Mạnh]

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]

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 .

 

[BuiQuangThuan]

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

Ồ. Thế có vẻ máy em vẫn cùi bắp.

 

[Hoàng Tuấn 868]

Ồ. Thế có vẻ máy em vẫn cùi bắp.

Máy mình chạy hơn 8 giây. (core i5, ram 8 gb).

 

[ongke0711]

Máy mình chạy hơn 8 giây. (core i5, ram 8 gb).

Giống cấu hình máy tôi. Nhưng của tôi là i5-Quad core 1.4GHz chứ không phải mấy con i5 bây giờ 8 nhân, 10 nhân mười mấy luồng... .
Bạn có chạy thử cái power query của bác ptm42 và hocexcel chưa? Có ra kết quả không? Không biết máy tôi bị gì mà nó cứ chạy hoài.

 

[Hoàng Tuấn 868]

Giống cấu hình máy tôi. Bạn có chạy thử cái power query của bác ptm42 và hocexcel chưa? Có ra kết quả không? Không biết máy tôi bị gì mà nó cứ chạy hoài.

Mấy cái đó em chưa chạy thử anh ạ.

 

[cantl]

Tiếc là em không biết cách chèn code M và chạy. Nếu không thì cũng đua đòi thử tốc độ.

 

[ongke0711]

Tiếc là em không biết cách chèn code M và chạy. Nếu không thì cũng đua đòi thử tốc độ.

Bạn vô menu Data - Get Data - chọn Launch Power Query Editor. Sau đó trong cửa sổ Editor - menu New Query - chọn New Source - Other Source - Blank query. Trong cửa số Query chọn Advanced Edit - dán M code vào --> close and load.

 

[ptm0412]

Code VBA FSO ở bài 12 tôi chạy 2.5 giây, nhanh gấp 10 lần PQ của tôi.

 

[ongke0711]

Có một chia sẽ thêm là về M-code: Tôi thấy file bác @ptm0412 và @hocexcel_1991 có dùng Delimiter = " " thì trên máy tôi báo lỗi (Office 365 bản quyền). (Không biết có do lỗi copy code lên diễn đàn không?)
- Nếu tôi sửa thành Delimiter=" " (1 khoảng trắng) thì không lỗi nhưng chạy không ra kết quả.
- Nếu tôi đổi thành Delimiter ="#(tab)" thì máy tôi mới chạy được.
Code của bạn @hocexcel_1991 chạy nhanh đó, bằng 1/10 của bác @ptm0412 . Chắc là đã tinh chỉnh rồi.

 

[ptm0412]

Delimiter = " "

Tôi chọn tab và nó ra câu lệnh như vậy. Có lẽ khi copy ra ngoài nó biến thành 4 khoảng trắng chăng.

Code của bạn @hocexcel_1991 chạy nhanh đó, bằng 1/10 của bác @ptm0412

Code đó lồng hàm nhiều quá tôi chưa xem hết, Còn tôi thì ít khi lồng hàm như đã nói ở trên. Phải tách hàm ra nhiều dòng mới hiểu thuật toán.

 

[tranhungdao12a3]

Có một chia sẽ thêm là về M-code: Tôi thấy file bác @ptm0412 và @hocexcel_1991 có dùng Delimiter = " " thì trên máy tôi báo lỗi (Office 365 bản quyền). (Không biết có do lỗi copy code lên diễn đàn không?)
- Nếu tôi sửa thành Delimiter=" " (1 khoảng trắng) thì không lỗi nhưng chạy không ra kết quả.
- Nếu tôi đổi thành Delimiter ="#(tab)" thì máy tôi mới chạy được.
Code của bạn @hocexcel_1991 chạy nhanh đó, bằng 1/10 của bác @ptm0412 . Chắc là đã tinh chỉnh rồi.

View attachment 303416

Code chạy nhanh chủ yếu do cái lệnh Table.buffer + load đồng thời mới Trasform đó bác, tác vụ load trên bộ nhớ tạm cho nên không phải load lại, code đó máy mạnh chắc chỉ mất 2,3 giây thôi

 

[ongke0711]

Code chạy nhanh chủ yếu do cái lệnh Table.buffer + load đồng thời mới Trasform đó bác, tác vụ load trên bộ nhớ tạm cho nên không phải load lại, code đó máy mạnh chắc chỉ mất 2,3 giây thôi

À...Phải có kỹ thuật gì đó chứ đâu thể Power query chạy chậm một cách cách biệt như vậy được .

 

[ptm0412]

Code chạy nhanh chủ yếu do cái lệnh Table.buffer + load đồng thời mới Trasform đó bác, tác vụ load trên bộ nhớ tạm cho nên không phải load lại, code đó máy mạnh chắc chỉ mất 2,3 giây thôi

Code đó (PQ bài 17) tôi chạy mất 10 giây, khoảng 0.4 thời gian so với code của tôi.

 

[tranhungdao12a3]

Em sửa code của Thầy Mỹ một tí:

let
    StartTime = DateTime.LocalNow(),
    Source = Folder.Files("C:\Users\Admin\Desktop\GPE\nuocthai"),
    KeepContent = Table.SelectColumns(
                    Table.TransformColumns(Source, {"Content", each
                    let
                        FileContent = Csv.Document(_, [Delimiter="    ", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
                        NonEmptyTable = if Table.RowCount(FileContent) > 0 then FileContent else Table.FromRecords({[Column1="0", Column2="0", Column3="0", Column4="0"]}),
                        AddTitle = Table.AddColumn(NonEmptyTable, "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)),
                        pHValue = try TransData[pH]{1} otherwise null,
                        // Tách ngày tháng
                        AddYear = if pHValue <> null then Table.AddColumn(TransData, "Year", each Number.From(Text.Start(pHValue, 4)), Int64.Type) else TransData,
                        AddMonth = if pHValue <> null then Table.AddColumn(AddYear, "Month", each Number.From(Text.Middle(pHValue, 4, 2)), Int64.Type) else AddYear,
                        AddDay = if pHValue <> null then Table.AddColumn(AddMonth, "Day", each Number.From(Text.Middle(pHValue, 6, 2)), Int64.Type) else AddMonth,
                        AddHour = if pHValue <> null then Table.AddColumn(AddDay, "Hour", each #time(Number.From(Text.Middle(pHValue, 8, 2)), Number.From(Text.Middle(pHValue, 10, 2)), Number.From(Text.Middle(pHValue, 12, 2)))) else AddDay
                    in
                        Table.FirstN(AddHour, 1)
                }),{"Content"}),
    Expanded_Tbl = Table.ExpandTableColumn(KeepContent, "Content", {"COD (mg/L)", "TSS (mg/L)", "pH", "NH4+ (mg/L)", "Temp (oC)", "NO3- (mg/L)", "Flow out 1 (M3/h)", "Year", "Month", "Day", "Hour"}, {"COD (mg/L)", "TSS (mg/L)", "pH", "NH4+ (mg/L)", "Temp (oC)", "NO3- (mg/L)", "Flow out 1 (M3/h)", "Year", "Month", "Day", "Hour"}),
    EndTime = DateTime.LocalNow(),
    Duration = Duration.TotalSeconds(EndTime - StartTime),
    AddExecutionTime = Table.AddColumn(Expanded_Tbl, "ExecutionTimeInSeconds", each Duration, type number)
in
    AddExecutionTime

Cơ bản vẫn chậm hơn code của bạn @hocexcel_1991 một chút ở chỗ bạn tạo cột động cho ngày tháng năm

 

[HeSanbi]

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 ạ

Không phải lỗi, chỉ là trong danh sách tệp có tệp rỗng, nên không thể gọi ReadAll.


Tối ưu mã lại như sau, nếu data được xử tại memory mã sẽ nhanh hơn nữa:

Option Explicit

#If VBA7 Then
Private Declare PtrSafe Function FindClose Lib "kernel32" (ByVal hFindFile As LongPtr) As Long
Private Declare PtrSafe Function FindFirstFileW Lib "kernel32" (ByVal lpFileName As LongPtr, ByVal lpFindFileData As LongPtr) As LongPtr
Private Declare PtrSafe Function FindNextFileW Lib "kernel32" (ByVal hFindFile As LongPtr, ByVal lpFindFileData As LongPtr) As LongPtr
#Else
Private Enum LongPtr: [_]: End Enum
Private Declare Function FindClose Lib "kernel32" (ByVal hFindFile As Long) As Long
Private Declare Function FindFirstFileW Lib "kernel32" (ByVal lpFileName As Long, ByVal lpFindFileData As Long) As Long
Private Declare Function FindNextFileW Lib "kernel32" (ByVal hFindFile As Long, ByVal lpFindFileData As Long) As Long
#End If
Private Type FILETIME
  dwLowDateTime  As Long
  dwHighDateTime As Long
End Type
Const MAX_PATH  As Long = 260
Const ALTERNATE As Long = 14
Private Type WIN32_FIND_DATA
  dwFileAttributes As Long
  ftCreationTime   As FILETIME
  ftLastAccessTime As FILETIME
  ftLastWriteTime  As FILETIME
  nFileSizeHigh    As Long
  nFileSizeLow     As Long
  dwReserved0      As Long
  dwReserved1      As Long
  cFileName        As String * MAX_PATH
  cAlternate       As String * ALTERNATE
End Type
Private Const FILE_ATTRIBUTE_DIRECTORY As Long = 16 '0x10
Private Const INVALID_HANDLE_VALUE As LongPtr = -1

Sub TongHop2()
    Dim folderPath As String, fileList As Collection, fileName As Variant, s, v$, k&, j%, data
    Dim startTime As Double, fd, txtFile, sText As String
    Dim endTime As Double
    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": Exit Sub
    End If
   ' folderPath = ThisWorkbook.Path & "\nuocthai\"
    startTime = timer
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set fileList = New Collection
    Call ListAllFiles(folderPath, fileList)
    ReDim data(1 To fileList.count + 5000, 1 To 12)
    For Each fileName In fileList
      fileNumber = FreeFile
      Open fileName For Input As #fileNumber
      s = Split(Replace$(Input$(LOF(fileNumber), fileNumber), vbCrLf, vbTab), vbTab)
      Close #fileNumber
      If UBound(s) > 20 Then
        k = k + 1: v = s(3)
        data(k, 1) = k
        data(k, 2) = Left$(v, 4)
        data(k, 3) = Mid$(v, 5, 2)
        data(k, 4) = Mid$(v, 7, 2)
        data(k, 5) = Mid$(v, 9, 2) & ":" & Mid$(v, 11, 2) & ":" & Right$(v, 2)
        For j = 0 To 6
          data(k, 6 + j) = s(j * 5 + 1)
        Next
      End If
    Next
    If k > 0 Then
      With sheets("Sheet1") 'ActiveSheet '
        .Range("A3").Resize(UBound(data), 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
Public Sub ListAllFiles(ByVal folder$, ByRef fileList As Collection)
  Dim h As LongPtr, f$, fd As WIN32_FIND_DATA
  h = FindFirstFileW(StrPtr(folder & "*"), VarPtr(fd))
  If h = INVALID_HANDLE_VALUE Then Exit Sub
  Do While FindNextFileW(h, VarPtr(fd))
    f = fd.cFileName
    If f Like "[.~]*" Then
    ElseIf fd.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY Then
      Call ListAllFiles(folder & Left$(f, InStr(f, vbNullChar) - 1) & "\", fileList)
    Else
      fileList.Add folder & f
    End If
  Loop
  FindClose h
End Sub

 

[doredore1988]

Không hiểu được bao nhiêu, chỉ nhảy vô test xem cái nào nhanh hơn thôi, VBA #12 7s, PQ #33 thì 22s, VBA #34 chạy ko ra kết quả không biết do lỗi gì. Vậy nếu chỉ lấy dữ liệu thôi thì chọn VBA rồi ))

 

[HeSanbi]

Không biết lỗi gì bạn có thể chụp hình mã không.

dòng mã folderPath phải là: folderPath = fd.SelectedItems(1) & "\"
bên máy bạn có đúng vậy không

 

[doredore1988]

Không biết lỗi gì bạn có thể chụp hình mã không.

dòng mã folderPath phải là: folderPath = fd.SelectedItems(1) & "\"
bên máy bạn có đúng vậy không

đúng là thiếu khúc đó nên không ra kết quả. Code bác nhanh hơn code #12 chạy chỉ khoảng 6s

 

[tranhungdao12a3]

Không hiểu được bao nhiêu, chỉ nhảy vô test xem cái nào nhanh hơn thôi, VBA #12 7s, PQ #33 thì 22s, VBA #34 chạy ko ra kết quả không biết do lỗi gì. Vậy nếu chỉ lấy dữ liệu thôi thì chọn VBA rồi ))

Chỉ trong phạm vi bài toán này thì đương nhiên Power Query sẽ không có cửa so với VBA, vì bài toán này dữ liệu chỉ hơn 26 ngàn dòng thôi, trong phạm vi dưới 100 ngàn dòng thì VBA sẽ là lựa chọn tốt hơn so với Power Query. Dữ liệu càng lớn thì Power Query sẽ ưu việt hơn VBA, đặc biệt với dữ liệu hàng triệu dòng trở lên.

 

[doredore1988]

Chỉ trong phạm vi bài toán này thì đương nhiên Power Query sẽ không có cửa so với VBA, vì bài toán này dữ liệu chỉ hơn 26 ngàn dòng thôi, trong phạm vi dưới 100 ngàn dòng thì VBA sẽ là lựa chọn tốt hơn so với Power Query. Dữ liệu càng lớn thì Power Query sẽ ưu việt hơn VBA, đặc biệt với dữ liệu hàng triệu dòng trở lên.

Tui nghĩ đọc dữ liệu từ file lớn thì PQ mới phát huy thế mạnh chứ lấy dữ liệu từ nhiều file nhỏ thế này thì VBA vẫn hơn

 

[ptm0412]

Tui nghĩ đọc dữ liệu từ file lớn thì PQ mới phát huy thế mạnh chứ lấy dữ liệu từ nhiều file nhỏ thế này thì VBA vẫn hơn

Đó chỉ mới là lấy dữ liệu. Có dữ liệu rồi xử lý cho phù hợp để ra báo cáo thiên hình vạn trạng là chuyện khác.
Nói riêng về VBA, nếu 2s so với 20s thì còn nghĩ đến chuyện hơn, chứ 2s so với 2.5s hoặc so với 2.2s thì tôi chọn cái đơn giản. Viết thêm 1 thước code mà chỉ tiết kiệm 1 vài giây trở xuống thì tôi không cần.
Nói về PQ (Power query) thì phù hợp với dữ liệu lớn, bất kể nhiều file nhỏ (hàng ngàn file) hay vài file lớn (hàng chục file), khi mà dữ liệu vài trăm ngàn dòng, thậm chí vượt quá 1 triệu dòng. Người ta chấp nhận chạy 15, 20 phút hoặc nửa tiếng để lấy nhiều triệu dòng. Excel có chỗ chứa mấy triệu dòng cho kết quả của PQ nhưng không có chỗ chứa cho kết quả VBA.
Ngoài ra, PQ còn kết hợp với DAX trong DataModel còn làm được nhiều việc mà nếu dùng VBA phải rất nhọc công, thậm chí không làm được.

 

[Kiều Mạnh]

Chỉ trong phạm vi bài toán này thì đương nhiên Power Query sẽ không có cửa so với VBA, vì bài toán này dữ liệu chỉ hơn 26 ngàn dòng thôi, trong phạm vi dưới 100 ngàn dòng thì VBA sẽ là lựa chọn tốt hơn so với Power Query. Dữ liệu càng lớn thì Power Query sẽ ưu việt hơn VBA, đặc biệt với dữ liệu hàng triệu dòng trở lên.

Tôi mới xem cái Folder dữ liệu Txt đó mới có 794 MB vậy thử copy nhân bản nó lên 10 lần

khoãng 10 GB chi đó xong thử code VBA và Power Query xem có gì khác biệt ??????????????

Tôi mới cải tiến thử 2 hàm trên Delphi thì thấy nhanh hơn chút với dữ liệu 794 MB còn tầm 10 GB thì khả năng chạy tốt ... còn VBA thì chờ xem thôi

code thì y trang bài số 12 chỉ thay thế hai hàm trong Delphi

1/ Hàm FindFiles được thiết kế cho tìm kiếm file trong Folder trên 10 GB dữ liệu vẫn chạy tốt thay thế cho hàm GetFilesInFolder

2/ Hàm ReadTextFileAll thay thế cho Open fileName For Input As #fileNumber

còn lại các dòng mã phía dưới của bài 12 vẫn giữ nguyên không cần thay đổi ...

Hãy Copy nhân bản dữ liệu lên tầm 10 GB xong thử xem là biết ngay và luôn ??!!! khi xử lý dữ liệu lớn trên VBA

 

[HeSanbi]

VBA làm được gấp 10 lần Power Query và DataModel. Chúng chỉ được cái người ta viết mã sẵn cho chúng các thư viện và giải thuật. Và chưa chắc mã đã được tối ưu. Và chúng chỉ giới hạn ở xử lý dữ liệu. VBA thì rộng bao la.
Power Query đánh cắp RAM và CPU để thực hiện đa luồng tăng tốc tính toán, kiểu gian lận giấu tay.
Nếu trong dự án có nhiều mã Power Query chồng lấn, gây xung đột, đến lúc nó ngồi chơi xơi nước chẳng biết lý do.
Mã M Power Query được biên dịch lớp cấp cao nên chậm hơn VBA rất rất nhiều. VBA thì lại được thông dịch gần với mã máy.

Tôi đã tìm ra cách cho các luồng VBA chạy song song, giờ đây VBA có thể làm "cụ của cụ" của Power Query.
Giả sử dữ liệu ở bài viết này có 1 triệu tệp, chỉ cần cho 10 luồng VBA chạy song song, thì mỗi luồng chỉ cần xử lý 100 ngàn tệp.
Nếu máy RAM lớn CPU mạnh, có thể tăng lên 100 luồng VBA.

Lập trình mã không nhất thiết phải đưa vào Excel để tính toán, có thể tận dụng database access để xử lý hàng triệu dòng. Excel chỉ cần dùng để xem sau khi lọc tìm.
VBA có thể tạo ra giao diện ứng dụng. Power Query thì không.
VBA có thể xử lý từ bộ nhớ, chỉnh sửa chạy mã máy, Power Query thì không.
VBA có thể điều khiển Power Query, và các ứng dụng Office, ngược lại Power Query thì không.

Power Query là một dự án không trực thuộc chính thức của Microsoft thực sự. Nó chỉ là một dự án thừa kế. Chưa chắc đã tối ưu.
Trong office nói riêng, xử lý dữ liệu mạnh nhất vẫn là sự kết hợp giữa VBA và ADODB.

Khi viết và chạy Power Query nếu không chú ý điểm dưới đây. Thì thôi rồi, không ổn tí nào.



CPU có khi lên 100% các bạn nhé. Cẩn trọng khi lập trình. Cháy máy bạn thì không sao. Chỉ sợ đưa người ta dùng, cháy máy người ta thôi.

 

[ptm0412]

VBA làm được gấp 10 lần Power Query và DataModel. Chúng chỉ được cái người ta viết mã sẵn cho chúng các thư viện và giải thuật. Và chưa chắc mã đã được tối ưu. Và chúng chỉ giới hạn ở xử lý dữ liệu. VBA thì rộng bao la.

Thì ở đây đang nói về lấy dữ liệu và xử lý dữ liệu, mở rộng ra làm gì.
Còn đánh cắp hay gian lận giấu tay thì phải phê bình trực tiếp hoặc kiện MS

 

[Kiều Mạnh]

Thì ở đây đang nói về lấy dữ liệu và xử lý dữ liệu, mở rộng ra làm gì.
Còn đánh cắp hay gian lận giấu tay thì phải phê bình trực tiếp hoặc kiện MS

Tôi nghĩ đơn giản lắm ... cũng ví dụ cụ thể đó người ta xử lý hết 1 phút ... còn tây xử lý hết 15 phút

vậy không cần quan tâm cách gì hay phương pháp gì miễn sao 1 phút là ok còn 15 phút không cần thiết quan tâm chi tiết làm gì

 

[HeSanbi]

Thật là vô lý, nói ra thì có những thứ để mọi người biết thêm, bài viết tôi cũng nói về xử lý dữ liệu, chứ có nói về cái chi đâu.
Xem cái này cái kia ưu và nhược điểm ra sao để đề phòng trong quá trình phát triển ứng dụng hoặc tận dụng các trình bổ trợ.
Học thêm không hết tại sao cấm nói ra.

 

[ptm0412]

Tôi nghĩ đơn giản lắm ... cũng ví dụ cụ thể đó người ta xử lý hết 1 phút ... còn tây xử lý hết 15 phút

vậy không cần quan tâm cách gì hay phương pháp gì miễn sao 1 phút là ok còn 15 phút không thiết thiết quan tâm chi tiết làm gì

Nếu báo cáo đặc thù của ngành nghề đặc thù như bên xây dựng (bắt buộc theo chuẩn nhà nước) thì tôi vẫn dùng VBA. Cò báo cáo dạng phân tích tổng hợp thì tôi có chọn lựa, cái nào nhanh hơn thì làm. Nội trong Power query, nếu tôi làm như bài #4 mất 25 giây, nhưng cũng PQ có người làm 2 giây như bài #17, thì tôi học thêm thủ thuật của tác giả bài đó. 2 giây là thỏa mãn tôi rồi, không cần 1.5 giây.

 

[ptm0412]

Thật là vô lý, nói ra thì có những thứ để mọi người biết thêm, bài viết tôi cũng nói về xử lý dữ liệu, chứ có nói về cái chi đâu.
Xem cái này cái kia ưu và nhược điểm ra sao để đề phòng trong quá trình phát triển ứng dụng hoặc tận dụng các trình bổ trợ.
Học thêm không hết tại sao cấm nói ra.

Tôi rút lại câu 1, để lại câu 2 ở bài 43

 

[HeSanbi]

Tôi nói "đánh cắp" là phép ẩn dụ và nhân hóa, chắc Bác có học qua
Phép chơi chữ là bình thường khi giao tiếp hay viết bài. Có gì mà là vấn đề ở đây.

 

[tranhungdao12a3]

VBA làm được gấp 10 lần Power Query và DataModel. Chúng chỉ được cái người ta viết mã sẵn cho chúng các thư viện và giải thuật. Và chưa chắc mã đã được tối ưu. Và chúng chỉ giới hạn ở xử lý dữ liệu. VBA thì rộng bao la.
Power Query đánh cắp RAM và CPU để thực hiện đa luồng tăng tốc tính toán, kiểu gian lận giấu tay.
Nếu trong dự án có nhiều mã Power Query chồng lấn, gây xung đột, đến lúc nó ngồi chơi xơi nước chẳng biết lý do.
Mã M Power Query được biên dịch lớp cấp cao nên chậm hơn VBA rất rất nhiều. VBA thì lại được thông dịch gần với mã máy.

Tôi đã tìm ra cách cho các luồng VBA chạy song song, giờ đây VBA có thể làm "cụ của cụ" của Power Query.
Giả sử dữ liệu ở bài viết này có 1 triệu tệp, chỉ cần cho 10 luồng VBA chạy song song, thì mỗi luồng chỉ cần xử lý 100 ngàn tệp.
Nếu máy RAM lớn CPU mạnh, có thể tăng lên 100 luồng VBA.

Lập trình mã không nhất thiết phải đưa vào Excel để tính toán, có thể tận dụng database access để xử lý hàng triệu dòng. Excel chỉ cần dùng để xem sau khi lọc tìm.
VBA có thể tạo ra giao diện ứng dụng. Power Query thì không.
VBA có thể xử lý từ bộ nhớ, chỉnh sửa chạy mã máy, Power Query thì không.
VBA có thể điều khiển Power Query, và các ứng dụng Office, ngược lại Power Query thì không.

Power Query là một dự án không trực thuộc chính thức của Microsoft thực sự. Nó chỉ là một dự án thừa kế. Chưa chắc đã tối ưu.
Trong office nói riêng, xử lý dữ liệu mạnh nhất vẫn là sự kết hợp giữa VBA và ADODB.

Khi viết và chạy Power Query nếu không chú ý điểm dưới đây. Thì thôi rồi, không ổn tí nào.

View attachment 303428

CPU có khi lên 100% các bạn nhé. Cẩn trọng khi lập trình. Cháy máy bạn thì không sao. Chỉ sợ đưa người ta dùng, cháy máy người ta thôi.

Tôi nghĩ Bác so sánh vậy là hơi khập khiễng, vì bản chất của Power Query chỉ là ETL biến đổi dữ liệu không chuẩn về chuẩn thôi, nó cũng chỉ như ngôn ngữ truy vấn cho tiền xử lý dữ liệu. Tôi ví dụ có 100 tệp excel mỗi tệp 1 triệu dòng, bao nhiêu người có khả năng xử lý như Bác qua cái khác rồi đập report xuống excel(Nếu mà mượn cái khác thì dùng luôn cái mượn chứ dùng VBA làm chi cho mệt). Còn Power Query có thể dễ dàng làm việc này dù có thể mất một vài chục phút thì có thể chấp nhận được với những người thường xuyên chỉ dùng Excel, chứ nếu ai đó đã xài ngôn ngữ lập trình khác thì họ cũng chả dùng đến Power Query lẫn VBA.

 

[ongke0711]

Tôi đã tìm ra cách cho các luồng VBA chạy song song, giờ đây VBA có thể làm "cụ của cụ" của Power Query.
Giả sử dữ liệu ở bài viết này có 1 triệu tệp, chỉ cần cho 10 luồng VBA chạy song song, thì mỗi luồng chỉ cần xử lý 100 ngàn tệp.
Nếu máy RAM lớn CPU mạnh, có thể tăng lên 100 luồng VBA.

Hóng cái kỹ thuật này của bạn đây.

 

[tigertiger]

Thực tình ra thì khó so sánh khi đối tượng xử lý là khác nhau

Power query nhắm đến giải pháp các dữ liệu có cấu trúc rõ ràng
VBA thì linh động và tùy thuộc nhiều vào người thảo chương (viết chương trình: thuật toán và bài toán cụ thể)

Delphi hay ngôn ngữ khác thì cũng như VBA là 1 ngôn ngữ để thảo chương, VBA khác là nhắm vào vấn đề thảo chương nhanh và tận dụng sức mạnh của Application nó can thiệp và cộng thêm vào ..., trong khi Delphi hay ngôn ngữ khác thì lại là nhắm tới dịch code hoàn trình ra mã máy, thay vì thông dịch liền tay như VBA

Còn với bài toán cụ thể ở đây, thì nên thực dụng chọn giải pháp cho hợp người dùng nhất là được (có khi người ứng dụng , dùng 1 lần hoặc tần suất dùng lại tool (công cụ) đó theo đơn vị tháng năm) -- đó là lý do các Data Analysis (phân tích dữ liệu) giờ người ta hay dùng ngôn ngữ scripts đôi khi trực tiếp trên Web luôn như kiểu python với jupyter hay MatLab, R, ...vvv dùng 1 hay vài lần vứt

 

[HeSanbi]

@tranhungdao12a3

Bạn đang nói điều không liên quan gì cả. Trong bài viết này có hai kiểu xử lý dữ liệu mà mọi người đề xuất. 1 là mã VBA, 2 là Power Query. Tôi cũng chỉ đang nói chung quanh 2 cái này. Power Query là công cụ mà một người bình thường nên sử dụng ít lại, hoặc không nên dùng, người chuyên nghiệp sử dụng cẩn trọng hơn. Nó là công cụ mang trên mình cả cái tốt và cái xấu. Người có kinh nghiệm thì biết cái xấu. Để tránh, phòng, ngừa. Đề cao Power Query quá mà không cho người ta biết cái nguy hại của nó thì khổ cho người dùng.
Power Query nó có cái quyền ngốn sạch RAM và CPU của bạn. Không có giới hạn nào cho nó. Nhưng bạn không thể thấy cảnh báo ở đâu cả. Nó chạy ngầm, không một hiển thị. Sau khi ngốn sạch RAM và CPU nhưng vẫn không đủ cho nó, nó sẽ đứng tại đó cho đến khi "Tại sao chỉ chạy Excel mà máy tính bị đơ". Dùng Power Query mà thiếu kinh nghiệm, bạn như đang nuôi con báo trong nhà. Nó sẽ cắn bạn lúc nào không hay không biết tại sao.
Bạn cứ nghĩ xử lý được dữ liệu lớn là ngon. Nhưng đừng đùa giỡn với cái biết hạn hẹp.

Nhiều người thấy sử dụng Power Query ngon, nên nhồi vào một dự án vài cái cho đã. Kết quả "why?".

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

còn power query thì nó cứ chạy hoài

Anh chạy lại bài này và chụp lại RAM và CPU, để xem power query đã sử dụng bao nhiêu.

 

[tranhungdao12a3]

@tranhungdao12a3

Bạn đang nói điều không liên quan gì cả. Trong bài viết này có hai kiểu xử lý dữ liệu mà mọi người đề xuất. 1 là mã VBA, 2 là Power Query. Tôi cũng chỉ đang nói chung quanh 2 cái này. Power Query là công cụ mà một người bình thường nên sử dụng ít lại, hoặc không nên dùng, người chuyên nghiệp sử dụng cẩn trọng hơn. Nó là công cụ mang trên mình cả cái tốt và cái xấu. Người có kinh nghiệm thì biết cái xấu. Để tránh, phòng, ngừa. Đề cao Power Query quá mà không cho người ta biết cái nguy hại của nó thì khổ cho người dùng.
Power Query nó có cái quyền ngốn sạch RAM và CPU của bạn. Không có giới hạn nào cho nó. Nhưng bạn không thể thấy cảnh báo ở đâu cả. Nó chạy ngầm, không một hiển thị. Sau khi ngốn sạch RAM và CPU nhưng vẫn không đủ cho nó, nó sẽ đứng tại đó cho đến khi "Tại sao chỉ chạy Excel mà máy tính bị đơ". Dùng Power Query mà thiếu kinh nghiệm, bạn như đang nuôi con báo trong nhà. Nó sẽ cắn bạn lúc nào không hay không biết tại sao.
Bạn cứ nghĩ xử lý được dữ liệu lớn là ngon. Nhưng đừng đùa giỡn với cái biết hạn hẹp.

Nhiều người thấy sử dụng Power Query ngon, nên nhồi vào một dự án vài cái cho đã. Kết quả "why?".

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

Anh chạy lại bài này và chụp lại RAM và CPU, để xem power query đã sử dụng bao nhiêu.

Tôi chỉ muốn nhấn mạnh nếu dữ liệu lớn thì có thể dùng Power Query được đối với đa phần những người dùng excel bình thường. Chứ tôi không đề cao cả Power Query lẫn VBA. Tất cả các loại Power từ Power Query, Power Pivot, Power Bi, Power Automate, Power Apps đều ngốn rất nhiều Ram hết chứ không riêng gì Power Query. Tôi sử dụng 128gb Ram cũng chỉ mở được 2,3 cái file. Bản thân tôi cũng không dùng nhiều Power Query vì nó ngốn rất nhiều tài nguyên, công việc của nó thì Spark làm đơn giản, gọn nhẹ, tốc độ cao. Nếu ai mà chỉ dùng Excel thường xuyên, dữ liệu hàng triệu dòng trở lên mà không thành thạo SQL/ Python/R/Spark/... thì dùng Power Query là phù hợp + kết hợp với Power Pivot là dùng cơ bản được rồi(Điều kiện là phải có nhiều Ram khi dùng). Trong cái phạm vi chức năng của Power Query thì nó mới được đánh giá tốt ở phần đó. Chứ cái nó không có thì lấy gì để mà so, ngay cả cái sức mạnh chính của nó còn thua xa một số thứ khác. Power Query được đánh giá tốt vì giao diện đơn giản, người dùng có thể dùng UI tới cũng đáp ứng được 50,60% công việc, yêu cầu phức tạp mới cần dùng tới mã M. Nó đơn giản và dễ dàng tùy biến chứ nó không phải là thứ tốt nhất.

 

[ptm0412]

Nó đơn giản và dễ dàng tùy biến chứ nó không phải là thứ tốt nhất.

Tôi đồng ý chỗ này. Nói thêm: chẳng qua là chưa sử dụng hết các hàm M đã có, nếu biết thêm thì cũng cải thiện được rõ rệt. Chẳng hạn code M bài 12 chỉ 2 giây ngang ngửa VBA, còn code 25 giây của tôi là hạng xoàng, so sánh và chê là phải. (nếu nó chiếm dụng RAM thì cứ cho nó chiếm dụng 2 giây!). Bài toán lớn hơn (chục triệu dòng) là của tập đoàn, công ty lớn thì họ có phần mềm lớn, không cần Excel. Mà nếu cần báo cáo quản trị của Excel mà phần mềm không có sẵn, thì họ sẵn sàng chi mua máy mạnh. Mua 1 máy mạnh ít tốn kém hơn mua 1 phần mềm bổ trợ, kèm theo là chi phí đào tạo phần mềm đó chỉ cho 1 vài người dùng.

Trong Power query cũng có sử dụng SQL. Khi được cấp phép truy cập vào data trong server máy chủ, thì lấy và xử lý cũng nhanh, không phải lấy từ txt, excel, folder nữa.

 

[befaint]

Đọc mà thấy hài. Phần mềm không dùng phần cứng thì chạy bằng niềm tin chăng?
Chuyện sử dụng phần cứng như thế nào là ở mỗi phần mềm được thiết kế. Chứ việc gì hù doạ, khè này nọ... cười rụng rốn. =]]]
Phần cứng sinh ra là để sử dụng, không lẽ chỉ dùng xíu xíu 1-vài % cho nó mát, cho nó bền?

Ví dụ giải thích chuyện dùng phần cứng:
- Mấy game AAA, yêu cầu phần cứng cao, chạy trên máy máy tính cấu hình thấp CPU, RAM 100% cũng không nổi, VGA kém cà giật... không lẽ kêu là game đểu?
- Các chương trình AI cần chip chuyên dụng, hiệu năng rất rất cao. Không chạy nổi trên mấy chip thông thường, không lẽ phán chương trình AI lởm?
- Các chương trình giả lập giải các bài toán về y học, thời tiết, các mô hình dự báo cần hệ thống siêu máy tính chạy hết công suất cả tháng trời chưa xong, không lẽ tắt điện giữa chừng cho đỡ hỏng máy?

Sản phẩm của Microsoft mà nguy hại, nguy hiểm như thế thì sợ quá nhỉ?
Hàng triệu người dùng không ai phát hiện ra mới tài chứ.

Mình làm sai, nhưng cứ thấy chê Office lỗi này nọ các kiểu mà cứ bám riết dùng mới tài. Nếu cảm thấy đúng là lỗi của Office thì gửi báo cáo tới Microsoft (MS) nhận thưởng thôi.
Không biết gửi báo cáo thế nào thì bắt xe đò lên Quận 1, HCM tới văn phòng MS Việt Nam nói trực tiếp.

 

[UltraExcel]

Tóm lại với bài toán này thì code VBA là tối ưu nhất, đáng để thay thế Power Query, code bác HeSanbi là nhanh nhất.
Hình như các bác chưa so sánh hay thử nghiệm với file có encoding là unicode?

 

[Kiều Mạnh]

Tôi thấy trên này chưa ai viết chạy đa luồng song song trên Excel ... nên Tôi cũng thử tí xem sao trên Taskpane của Excel

Bài này trả liên quan gì chủ đề này nhưng mấy bài trước có nói thì tôi cũng nói một tí thôi chứ ko làm loãng thớt ra ... xin lỗi spam bài này

1/ khi chạy hàm trong Taskpane chạy trong một luồng riêng biệt cứ chạy khi nào xong thì nghỉ

2/ còn trên Excel ta làm gì cứ làm xong thì cũng nghỉ

3/ khi tắt Excel thì mục 1 và 2 cũng nghỉ luôn cho khoẻ

........... không biết như vậy có phải là đa luồng song song hay tôi nhầm lẫn ??????????!!!!!!!!!!!!!

Liên kết: https://youtu.be/JGyak8yUtTU

 

[VetMini]

Ở VN, con ngựa không phải là giống phổ biến ở đồng ruộng.
Bắt con ngựa kéo cày thì chỉ một tiếng là cùng. Làm sao sánh được với bò. Và bò làm sao sánh được với trâu về mức dai dẳng. (ngựa khỏe hơn bò, bò khỏe hơn trâu, nhưng sức dai thì ngược lại)

Power BI là công cụ của quản lý. Đem nó ra tổng hợp 100 files? chơi dại. Đem so sánh với cái khác? Hoặc không biết quản lý, hoặc cố tình nâng cấp cái mà mình sành hơn.

Hồi IBM tung ra CSDL LH với SQL thì thị trường đã khẳng định rằng kỹ thuật này quá chậm. Mãi đến về sau, CPU và RAM phát triển thì hai kỹ thuật trên mới phát triển theo. Hiện giờ thì mọi thứ nó đều ngốn cả. Nhưng: giao một CSDL tổ bố cho thằng không biết tối ưu hóa thì nó chạy ì ạch thôi.
Trong CSDL LH thì lệnh Join, Group By, và Sorted là ba lệnh sử dụng nhiều nhất. Vì vậy các phần mềm như Oracle, MS Server, ... họ chú trọng về độ hữu hiệu của các lệnh này. Các lệnh khác, người khéo léo có thể uốn dữ liệu để chứng mình rằng phần mềm của mình chạy nhanh hơn người khác.

Chơi với CSDL mà không lý đến "điểm mượt" của phần mềm thì chỉ là trò tranh cãi của lập trình viên. Dân quản lý người ta biết rõ phần mềm nào dùng để làm cái gì.

Chú thích: tống hợp một đống files thì cái mà tin tưởng được, ra kết quả đáng tin cậy là xài được rồi.
VBA có nhược điểm rằng người viết code ít khi chú thích rõ ràng. Người sử dụng giống như con tín đồ. Cứ nghe theo, làm theo...