Add-Ins Cho Excel - Tổng Hợp Dữ Liệu Sheet Và File

[Kiều Mạnh]

Đính kèm là link CSV file với 3.6 triệu dòng mà hôm bữa tôi test.
Link: CSV files 3.6 mil rows

Mạnh thử File sau chưa gán lên Sheet đang thử load lấy dòng cuối + cột thì hết khoãng 11 giây

 

[ongke0711]

Mạnh thử File sau chưa gán lên Sheet đang thử load lấy dòng cuối + cột thì hết khoãng 11 giây

Lần trước tôi dùng PQ gộp dữ liệu 6 files đó lại rồi gán thêm cột dữ liệu, xoá dữ liệu dư thừa hết khoảng 86 giây đó bác.

 

[tranhungdao12a3]

Máy các bác chạy nhanh quá nhỉ:
Tôi test thử mà hình như Tool của bác chủ nó không cho chạy quá dòng excel, nhờ bác chỉ dùm làm sao nạp quá số dòng excel
Tôi thử PQ của excel:
1. với 10955487 dòng x 29 cột mất 8 phút 35 giây file xlsx

2. Tôi test thử với 8846494 dòng x 35 cột mất 5 phút 14 giây 84 file Csv

3. Tôi thử với PQ của Power bi: Vẫn với 8846494 x 35 cột file Csv mất 4 phút 11 giây 56

Data của tôi tương đối là nhiều chữ:

Cấu hình máy tôi:

Máy các bác có 8gb ram sao nó nạp chạy nhanh vậy nhỉ???

 

[tranhungdao12a3]

Tôi cũng đang test vụ Power Query với ADO về việc tổng hợp báo cáo từ nhiều file trong cùng một folder nên test cái tool của bạn luôn nhưng tool bạn chưa hỗ trợ lấy nhiều file dữ liệu CSV (Các file dữ liệu lớn trên triệu dòng họ thường lưu dạng CSV).
- Trong video là tôi test trên dữ liệu mẫu, mỗi file 500k dòng 5 cột và tôi copy ra thành 48 cột để test. Tổng hợp các file thành 3.6 triệu dòng.
- Gộp files và tổng hợp ra báo cáo theo từng bộ phận, từng tháng, năm.
--> Dùng PQ thì vừa gộp file và xử lý hết 1 phút 26 giây. Sau đó thì tùy chọn các báo cáo không mất thời gian nữa.

* Máy tính tôi là: code i5 1.4 và 8G RAM. Đang chạy song song 2 hệ điều hành Mac Os + Windows 11 nên tài nguyên bị chia ra. Máy khác chắc chạy nhanh hơn nhiều.

--> Tôi thấy đây chính là điểm mạnh, ứng dụng của PQ với ngôn ngữ truy vấn M (MCode) mà các tool, ứng dụng khác chưa làm được (các tool bên Google Sheet thì tôi không biết).

Em cũng test dữ liệu 3.6tr dòng của bác mà vẫn mất 3 phút 04 giây, sao máy bác chạy nhanh vậy nhỉ?Trong khi ram chỉ 8gb

 

[Kiều Mạnh]

Tôi đang vọc các kiểu xem sao thấy như sau

1/ trong Delphi nếu cho vào TFDMemTable = nhanh nhất chỉ mất trên 11 giây ... nhưng kẹt là tôi chưa biết xuất nó ra Range trên Excel

2/ Gán lên StringGrid mất 26 giây

3/ nhanh hay chậm phụ thuộc vào chính cấu hình máy ai đó .... chứ ko thể so sánh 11 giây trên máy cấu hình Mạnh với 60 giây trên máy RAM 1 G

4/ công bằng chạy 3 lần của từng loại trên cùng 1 máy là biết hết thôi



Hình sau là sử dụng ADODB của Ms trên Delphi ... xem ra phá banh xác hết các kiểu ra xong sẻ lấy 1 kiểu

Mất 2 phút 1 giây

 

[ongke0711]

Em cũng test dữ liệu 3.6tr dòng của bác mà vẫn mất 3 phút 04 giây..

Tôi đang vọc các kiểu xem sao thấy như sau
1/ trong Delphi nếu cho vào TFDMemTable = nhanh nhất chỉ mất trên 11 giây ... nhưng kẹt là tôi chưa biết xuất nó ra Range trên Excel
2/ Gán lên StringGrid mất 26 giây

Các bạn đọc lại bài của tôi nhé. Tôi không có đề cập tới việc đọ tốc độ load file giữa PQ và tool Delphi vì so sánh vậy nó khập khiễng quá.
Tôi chỉ đề cập việc giải một bài toán mà theo tôi công cụ Power Query mới phát huy tính năng của nó chứ không phải thuần túy là chỉ làm công việc gộp file, tải file không thôi. Kết quả của việc gộp file là gì? là để tổng hợp ra các báo cáo mong muốn một cách thuận tiện, nhanh gọn...
Tôi diễn tả bài toán khá rõ là tổng hợp 3,6 tr dòng dữ liệu (không quan tâm tới giới hạn dòng của 1 sheet Excel), tổng hợp chi phí theo năm tháng, bộ phận, nhân viên. Với PQ, nó mất thời gian tổng hợp ban đầu khá nhiều nhưng sau đó các báo cáo chỉ cần click là chạy, là cập nhật kết quả theo các tùy chọn mà không cần phải mất thời xử lý, code gì thêm nữa.
Do vậy nếu so sánh các công cụ (tool) thì nên so sánh cái kết quả báo cáo cuối cùng thôi chứ không phải tách từng giai đoạn ra so sánh. PQ với ngôn ngữ truy vấn M chính là để ra báo cáo dữ liệu mong muốn. Làm việc với dữ liệu lớn (tất nhiên khi đã lớn thì nó sẽ chuẩn hóa hơn) thì nên học hỏi các công cụ chuyên dụng cho nó. Tôi cũng chỉ mới ngâm cứu PQ (chưa sạch nước cản) và thấy nó hay trong cái khoản tổng hợp báo cáo này.

Nếu chỉ muốn đề cập về việc tải 3,6 tr dòng x 48 cột, mà không xử lý gì cả thì PQ máy tôi chạy mất 7,8 phút (chạy song song 2 hệ điều hành --> có thể mất khoảng 4 phút nếu máy chạy Windows không thôi).
Còn kết quả trong video là tôi đã xử lý bỏ các cột không cần thiết (vì tổng hợp báo cáo thì chỉ cần lấy các cột dữ liệu liên quan, chứ ai đâu đi lấy toàn bộ cho lãng phí tài nguyên).

 

[ptm0412]

Em cũng test dữ liệu 3.6tr dòng của bác mà vẫn mất 3 phút 04 giây, sao máy bác chạy nhanh vậy nhỉ?Trong khi ram chỉ 8gb

Bạn test thử code Power Query này xem, trên máy tôi i5-11 (6 cores 12 threads), 16 Gb Ram chạy excel dưới 2 phút, Power BI nhanh hơn 1 chút.
Xin cám ơn.

let
   FolderName ="D:\OneDrive\MyBooks\MCode-PowerQuery\CSV Test files",
    FileNameList= Folder.Files(FolderName)[Name],
    FileNum={0..List.Count(FileNameList)-1},
    DataF= List.Transform(FileNum, (f) =>
    let
        FFullName =FolderName & "\" & FileNameList{f},
        Source1 = Csv.Document(File.Contents(FFullName),[Delimiter=",", Columns=48, Encoding=65001, QuoteStyle=QuoteStyle.None]),
        SourceData=Table.PromoteHeaders(Source1)
    in SourceData),
    List2 =Table.FromList(DataF,Splitter.SplitByNothing()),
    ListColumns2 = Table.ColumnNames(DataF{0}),
    Ketqua = Table.ExpandTableColumn(List2, "Column1", ListColumns2)
  
in Ketqua

 

[Kiều Mạnh]

Các bạn đọc lại bài của tôi nhé. Tôi không có đề cập tới việc đọ tốc độ load file giữa PQ và tool Delphi vì so sánh vậy nó khập khiễng quá.
Tôi chỉ đề cập việc giải một bài toán mà theo tôi công cụ Power Query mới phát huy tính năng của nó chứ không phải thuần túy là chỉ làm công việc gộp file, tải file không thôi. Kết quả của việc gộp file là gì? là để tổng hợp ra các báo cáo mong muốn một cách thuận tiện, nhanh gọn...
Tôi diễn tả bài toán khá rõ là tổng hợp 3,6 tr dòng dữ liệu (không quan tâm tới giới hạn dòng của 1 sheet Excel), tổng hợp chi phí theo năm tháng, bộ phận, nhân viên. Với PQ, nó mất thời gian tổng hợp ban đầu khá nhiều nhưng sau đó các báo cáo chỉ cần click là chạy, là cập nhật kết quả theo các tùy chọn mà không cần phải mất thời xử lý, code gì thêm nữa.
Do vậy nếu so sánh các công cụ (tool) thì nên so sánh cái kết quả báo cáo cuối cùng thôi chứ không phải tách từng giai đoạn ra so sánh. PQ với ngôn ngữ truy vấn M chính là để ra báo cáo dữ liệu mong muốn. Làm việc với dữ liệu lớn (tất nhiên khi đã lớn thì nó sẽ chuẩn hóa hơn) thì nên học hỏi các công cụ chuyên dụng cho nó. Tôi cũng chỉ mới ngâm cứu PQ (chưa sạch nước cản) và thấy nó hay trong cái khoản tổng hợp báo cáo này.

Nếu chỉ muốn đề cập về việc tải 3,6 tr dòng x 48 cột, mà không xử lý gì cả thì PQ máy tôi chạy mất 7,8 phút (chạy song song 2 hệ điều hành --> có thể mất khoảng 4 phút nếu máy chạy Windows không thôi).
Còn kết quả trong video là tôi đã xử lý bỏ các cột không cần thiết (vì tổng hợp báo cáo thì chỉ cần lấy các cột dữ liệu liên quan, chứ ai đâu đi lấy toàn bộ cho lãng phí tài nguyên).

View attachment 284516

Tôi chỉ đang thử phá banh xác các kiểu ra xem nó ra cái gì thôi .... xong xuôi đâu đó hiểu nó xong mới tiến hành viết code theo mục đích chủ đề này

Mà tôi xem nhiều File CSV thấy nó khác nhau cái đấu phân cách ... ta thì vậy còn tây nó nhiều kiểu khác

nên đang suy nghĩ cái Mục Optional cho cái phân cách đó như thế nào

đừng có hiểu lộn và quá nhạy cảm câu từ với tôi ... với cá tính tôi nếu so sánh tôi nói thẳng toẹt ra A thua B ngay và luôn

mà nói lại là tôi đang phá các kiểu xem sao thôi và có sao nói vậy ........... Vui lòng ko nhắc lại nữa

Ai lạc đề vui lòng lặp thớt mới bà Tám he

rất lấy làm tiếc phải nói thẳng toẹt ra vậy

 

[Kiều Mạnh]

Nhắc lại phần tiếp theo tôi đang viết tiếp lấy dữ liệu file *.csv lên Sheet ( bao gồm cả gộp File ) .... Còn xử lý như thế nào sau khi lấy lên Sheet ko thuộc phạm vi bàn ở đây ai muốn bàn vui lòng lặp thớt mới mà bàn

Xin cảm ơn

 

[ongke0711]

nên đang suy nghĩ cái Mục Optional cho cái phân cách đó như thế nào
đừng có hiểu lộn và quá nhạy cảm câu từ với tôi ... với cá tính tôi nếu so sánh tôi nói thẳng toẹt ra A thua B ngay và luôn

Bạn lại quá nhạy cảm và suy nghĩ quá rồi..

 

[tranhungdao12a3]

Bạn test thử code Power Query này xem, trên máy tôi i5-11 (6 cores 12 threads), 16 Gb Ram chạy excel dưới 2 phút, Power BI nhanh hơn 1 chút.
Xin cám ơn.

let
   FolderName ="D:\OneDrive\MyBooks\MCode-PowerQuery\CSV Test files",
    FileNameList= Folder.Files(FolderName)[Name],
    FileNum={0..List.Count(FileNameList)-1},
    DataF= List.Transform(FileNum, (f) =>
    let
        FFullName =FolderName & "\" & FileNameList{f},
        Source1 = Csv.Document(File.Contents(FFullName),[Delimiter=",", Columns=48, Encoding=65001, QuoteStyle=QuoteStyle.None]),
        SourceData=Table.PromoteHeaders(Source1)
    in SourceData),
    List2 =Table.FromList(DataF,Splitter.SplitByNothing()),
    ListColumns2 = Table.ColumnNames(DataF{0}),
    Ketqua = Table.ExpandTableColumn(List2, "Column1", ListColumns2)
 
in Ketqua

Đúng là code này nhanh hơn hẳn nạp PQ bình thường, em test mất 1'49 giây. Em bấm chậm mất 1s khi nó kết thúc. So với 3 phút 05 thì nhanh hơn tương đối nhiều! Nạp với dữ liệu lớn hơn hi vọng cũng đạt được tỷ lệ nhanh như vậy!

 

[ptm0412]

Đúng là code này nhanh hơn hẳn nạp PQ bình thường, em test mất 1'49 giây. Em bấm chậm mất 1s khi nó kết thúc. So với 3 phút 05 thì nhanh hơn tương đối nhiều! Nạp với dữ liệu lớn hơn hi vọng cũng đạt được tỷ lệ nhanh như vậy!

Ý chính khi tôi đưa code lên nhờ test là nếu dùng Power query (hay bất cứ phương thức nào), bản thân việc tối ưu code cũng làm tăng tốc độ lên đáng kể. Và nếu không cùng code mà chỉ là cùng phương thức, thì không so sánh được.
------
Theo tôi thì chả lẽ 3.6 tr chưa phải là nhiều? Mặc dù tôi biết có những bạn đã xử lý 26 tr, và 40 tr.

 

[viethung78]

Bạn nào chỉ giúp mình link download tool này không? Cảm ơn!

Add-Ins Cho Excel - Tổng Hợp Dữ Liệu Sheet Và File​

 

[Kiều Mạnh]

Bạn nào chỉ giúp mình link download tool này không? Cảm ơn!

Add-Ins Cho Excel - Tổng Hợp Dữ Liệu Sheet Và File​

file bài số 1 đó chi ... làm Tôi có cảm giác ko tốt lắm cứ nghĩ xấu cho ai đó áp dụng biện pháp ngăn chặn abcdcxx ... gì đó

Tôi mới thử tải ok mà

thật sự rất lấy làm tiếc phải nói ra vậy

 

[ptm0412]

file bài số 1 đó chi ... làm Tôi có cảm giác ko tốt lắm cứ nghĩ xấu cho ai đó áp dụng biện pháp ngăn chặn abcdcxx ... gì đó

Tôi cũng mới thử tải và không có ai chặn cả. Hãy nghĩ xấu cho người đọc không chịu đọc từ đầu.

 

[NghiaKieu]

Đã đăng ký rồi mà khi cài vẫn bị lỗi này thì xử lý sao ạ: (đăng ký nhầm phiên bản excel :v)

 

[Kiều Mạnh]

Đã đăng ký rồi mà khi cài vẫn bị lỗi này thì xử lý sao ạ:

Vì 1 vài lý do gì đó ko biết nói sao ... Ms chặn COM DLL lại nếu quá trình sử dụng nó làm treo thì bạn vào mục sau Enable nó là ok

Trong File bài số 1 nếu ai đó tìm kiếm trong Folder to trên 10 G ... khi nó đang chạy mà thao tác linh tinh trên đó sẽ vô tình làm treo và bị lỗi trên

Xem hình và chọn nó Enable lại là OK




xem mục sau nếu nó check thì bỏ ra là ok

Disable all ...




Tôi mới viết lại + Fix một số lỗi + tăng tốc code chạy nhanh hơn đáng kể .... sẽ loại bỏ các Menu Ribbon Excel thừa ... ok mọi cái + tổng hợp ý kiến sử dụng xong sẽ úp lại file sau

Hiện tại dùng tạm file bài số 1

 

[Kiều Mạnh]

Thêm CSDL là *.csv vào xong rồi đấy nhưng trên máy của tôi chạy tốc độ ko hài lòng lắm

500.000 dòng x 48 cột chạy hết 57.25 giây

có lẽ dùng tạm vậy để lấy khi cầm có thời gian tôi sẽ điều chỉnh lại



Còn file lớn nhất do @ongke0711 úp lên

800.000 dòng x 48 cột chạy hết 91.344 giây



Chốt lại chỉ tiện cần file nào chọn cái là xong ... có thời gian tôi sẽ xem xét dùng TableMem của Delphi rất nhanh ... mà kẹt chưa biết lấy nó ra gán lên Cells trên Delphi cho vào Stringgid thì ok còn lấy nó ra gán lên Cells = tịt

Tải File của @ongke0711 úp lên tôi chạy hết 91.344 giây ... ai rảnh thử nó với Power Query thuần của Ms tính thơi gian từ khi chọn file cho tới xong xem nó xử lý hết mấy giây ..... xin cảm ơn

 

[Kiều Mạnh]

trên máy của tôi dùng thuần Power query của Ms lấy 800.000 dòng x 48 cột chạy khoãng 4 phút cho tới khi hoàn thành lên Cels

 

[Kiều Mạnh]

Tổng hợp List File *.csv trong cùng 1 Folder cũng đã xong . Với tùy chọn lấy tiêu đề và không lấy tiêu đề

Ai có ý kiến thêm CSDL nào khác nữa không tôi xem xét nghiên cứu viết thêm nếu có thể ..........