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

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