Add-Ins Cho Excel - Tổng Hợp Dữ Liệu Sheet Và File (1 người xem)

[Kiều Mạnh]

1/ Tôi thấy nhiều Bạn có nhu cầu Tổng hợp dữ liệu Sheet và File có cấu trúc dữ liệu như nhau

2/ Trong AddIns tôi úp phía dưới là 1 bộ siêu tập SQL trong đó bao gồm một số tiện ích khác

3/ phạm vi chủ đề này tôi chỉ giới thiệu 1 tiện ích nhỏ tổng hợp dữ liệu theo Sheet và File có cấu trúc dữ liệu như nhau theo đúng mô tả của tiêu đề thớt này

4/ là bản thử nghiệm và đang phát triển ... tôi Úp lên đây thăm dò nhu cầu sử dụng của người dùng thực tế muốn gì vvv....

5/ Sau khi hoàn tất tôi sẽ xóa hết các File úp trên này và chuyển nó vào kho lưu trữ trên github.com

6/ Quá trình sử dụng ai có nhu cầu mong muốn điều chỉnh viết thêm gì + Lỗi thì cứ mô tả chi tiết càng tốt ....
Tôi sẽ xem xét nếu trong phạm vi điều chỉnh và phù hợp với cái chung nhất tôi sẽ viết lại trong khả năng của mình

7/ Quá trình sử dụng ai có mong muốn mang tính cá nhân thì liên hệ riêng ...
Phạm vi thớt này chỉ điều chỉnh phục phụ nhu cầu chung nhất mang tính đại chúng...

Tiện ích nhỏ giới thiệu cho thớt này theo hình sau



Danh mục hổ trợ + From SQL Builder:



1/ Lấy dữ liệu File *.csv và gộp dữ liệu từ nhiều File CSV khác nhau vào 1 File duy nhất gán kết quả lên Sheet
2/ Lấy dữ liệu file Excel + Tổng hợp file + Sheet với tùy chọn
3/ lấy dữ liệu file Access + tổng hợp file + Tablename với tùy chọn
4/ Lấy dữ liệu File SQLite + tổng hợp file + Tablename với tùy chọn
5/ Lấy dữ liệu qua Internet + tổng hợp file + Tablename với tùy chọn
6/ Lấy dữ liệu Ms Server + tổng hợp Tablename với tùy chọn
7/ Hổ trợ builder SQL với các CSDL nêu trên ( Thiếu CSV bổ sung sau )

8/ Hổ trợ truy xuất File trong Folder là tiếng Việt có dấu ( Unicode ) ..... trừ App Server sẽ điều chỉnh sau

9/ Thêm phím tắt tạm dừng khi tìm kiếm files đệ quy folder lớn trên 15 GB bằng phím Esc

Hổ trợ Tìm kiếm nhanh có cải thiện tốc độ nhanh hơn ... để truy xuất dữ liệu nhanh hơn khi cần tìm

1/ Chọn Folder ... sau khi chọn thành công Path Folder sẽ lưu vào CSDL Registry ... lần sau đó ko cần nữa trừ khi chọn mới
2/ Chọn mục File Type với các phần mở rộng tích hợp sẳn Or gõ trực tiếp vào đó
3/ Tùy chọn duyệt Folder đệ quy hay ko đệ quy folder
4/ Hổ trợ tìm kiếm nhanh trong Folder trên 10 GB ... lúc trước trên máy cùi của tôi duyệt Folder 7GB có 29.185 File chạy hết 35 giây thì nay còn 23.469 giây nhanh hơn khoãng 30 % .... Nếu máy cấu hình tốt chỉ mất trên 5 giây

5/ Hổ trợ Mở Folder của file cần tìm kiếm khi kích đúp chuột lên CheckListbox ....

4/ Xem hình sau




Chỉ dẫn sơ bộ sử dụng From SQL Power Query Builder Vui lòng xem bài số #35

Hướng dẫn sử dụng:
1/ Để sử dụng được COM Add-Ins thì bắt buộc các bạn phải đăng ký sử dụng nó với Windows

2/ Tùy thuộc vào Bạn đang sử dụng Office 32 hay 64 bit thì đăng ký theo DLL 32 bit hay 64 bit

3/ Nếu sau khi đăng ký mà thay đổi thư mục đăng ký thì thực hiện lại

4/ Nếu trước đó đã đăng ký mà tải DLL mới thì xóa DLL củ đi copy DLL mới thay thế vào là ok mà ko cần đăng ký nữa

5/ Đính kèm theo DLL là các File *.vbs . Tùy theo máy Bạn cài Office 32 bit hay 64 bit thì sử dụng nó cho phù hợp

6/ Để đăng ký được DLL Bạn phải có quyền Admin trên PC đó khi chạy file *.vbs trong cùng Folder chọn Yes
--------------------------------------------------------------------
dự án code tiếp tục phát triển viết thêm tiếp, điều chỉnh , bổ sung or loại bỏ chức năng thừa + Fix lỗi nếu phát hiện ra ...

 

[Kiều Mạnh]

Chức năng tùy chọn theo Sheet ... theo thời gian sẻ bổ sung từ từ bao gồm cả Tổng hợp file qua Internet thông qua SQL TCP/IP

 

[Kiều Mạnh]

sau lần bị virus mã hóa dữ liệu mất sạch làm tôi ám ảnh diết .... Vì vậy khuyên ai đó nếu tải file ở bất kỳ nơi nào về mà trình duyệt chặn lại hay trình diệt virus mặc định của Windows10 xóa đi thì cũng ko nên luyến tiếc nữa ... hạy xóa nó đi trừ khi bạn biết chắc chắn nó vô hại ... ko nên quá tin vào ai đó sẽ ám ảnh hoài như tôi

File bài số 1 đã check Virus như hình sau

VirusTotal

VirusTotal

www.virustotal.com

 

[Kiều Mạnh]

Mới viết thêm hàm tổng hợp tất cả các File trong Folder To Sheet

Thong thả chơi là chính tôi sẽ mô phỏng 1 số chức năng viết gần giống theo Power Query và Power BI của Ms

lấy dữ liệu thô chưa xử lý lên Sheet .... còn lại ai đó tùy chỉnh lại cho phù hợp với nhu cầu sử dụng

Mcode cảm giác thấy bốc mùi lắm .... khó chơi

Video 2022 11 27 192355

Image Video 2022 11 27 192355 hosted in Gifyu

gifyu.com

 

[tieutuvodanh192]

Mới viết thêm hàm tổng hợp tất cả các File trong Folder To Sheet

Thong thả chơi là chính tôi sẽ mô phỏng 1 số chức năng viết gần giống theo Power Query và Power BI của Ms

lấy dữ liệu thô chưa xử lý lên Sheet .... còn lại ai đó tùy chỉnh lại cho phù hợp với nhu cầu sử dụng

Mcode cảm giác thấy bốc mùi lắm .... khó chơi

Video 2022 11 27 192355

Image Video 2022 11 27 192355 hosted in Gifyu

gifyu.com

Em đã thử cài addins của anh, nhưng khi click vào nút Form Folder thì bị báo lỗi như vậy:

 

[Kiều Mạnh]

Em đã thử cài addins của anh, nhưng khi click vào nút Form Folder thì bị báo lỗi như vậy:

View attachment 283966

đây he ... File bài này khác bài số 1 mới bổ sung thêm nó

1/ Chon Folder bất kỳ nó sẽ lấy hết dữ liệu trong Folder đó lên Sheet ... Lưu ý nó duyệt Folder đệ Quy sẽ lấy hết dữ Liệu File Excel + Access trong Folder chọn
2/ đang test thử sau khi hoàn thiện sẽ thêm tùy chọn duyệt Folder đệ quy hay không đệ quy
3/ kế hoạch tiếp theo sẽ cho lên Form tùy chọn File và tùy chon Sheet cần lấy

VD: trong File có 10 Sheet nhưng ta chỉ cần lấy 3 Sheet thôi vvv...

Xem hình duyệt File + tổng hợp như sau



Demos ... Lưu ý toàn bộ DLL úp trên này là thử nghiệm ... sau khi hoàn thiện cho lên kho Lưu trữ và xóa hết File trên thớt này

 

[tieutuvodanh192]

Em đã test thử với dữ liệu lớn khoảng 250k dòng và 40 cột thì tốc độ nhỉnh hơn Power Query xíu, mất 18s, PQ mất khoảng 25s.

 

[Kiều Mạnh]

Em đã test thử với dữ liệu lớn khoảng 250k dòng và 40 cột thì tốc độ nhỉnh hơn Power Query xíu, mất 18s, PQ mất khoảng 25s.

chắc chắn sẽ nhanh hơn và viết các tùy chọn thân thiện khác .... mà Power Query chưa hổ trợ tự động hóa ( nếu có ta phải mò viết code )

VD: tùy chọn lấy vài Sheet trong 1 File = checkBox = rất nhanh với kích chuột

Hãy thử kéo với Số dòng lên 1048570 test thử mới thấy hết .... Còn vài ngàn dòng ko tính

chạy code xong chờ uống cafe

 

[Mr.hieudoanxd]

Tên anh ở GiiHub là gì ạ. Để em tìm, xem lượm thêm được cái gì không anh Kiều Mạnh

 

[Kiều Mạnh]

Tên anh ở GiiHub là gì ạ. Để em tìm, xem lượm thêm được cái gì không anh Kiều Mạnh

bấm vào chữ ký đó

 

[tieutuvodanh192]

Em đã test kỹ hơn thì thấy rằng file tổng hợp của anh không yêu cầu tiêu đề phải đồng nhất giữa các file con 100%, chỉ copy dữ liệu từ các file con và nối vào nhau trong file tổng theo thứ tự các cột. Điều này khá tốt so với PQ.

Tuy nhiên sau khi tổng hợp số liệu bằng Addins này xong thì không thể edit các file con lại trong khi vẫn đang mở file Tổng, với PQ thì hoàn toàn có thể Edit file con và Refresh lại data ngay ở file tổng đang mở.
Tiêu đề cột nếu có dấu chấm "." khi tổng hợp file bằng Addins thì sẽ bị đổi thành dấu "#".

 

[Kiều Mạnh]

Em đã test kỹ hơn thì thấy rằng file tổng hợp của anh không yêu cầu tiêu đề phải đồng nhất giữa các file con 100%, chỉ copy dữ liệu từ các file con và nối vào nhau trong file tổng theo thứ tự các cột. Điều này khá tốt so với PQ.

Tuy nhiên sau khi tổng hợp số liệu bằng Addins này xong thì không thể edit các file con lại trong khi vẫn đang mở file Tổng, với PQ thì hoàn toàn có thể Edit file con và Refresh lại data ngay ở file tổng đang mở.
Tiêu đề cột nếu có dấu chấm "." khi tổng hợp file bằng Addins thì sẽ bị đổi thành dấu "#".

1/ cái đó ko khó lắm sẽ thêm tùy chon lấy tiêu đề hoặc ko lấy tiêu đề sau ... đang test quan trọng có chạy ra kết quả hay ko cái đã

2/ sau khi chạy xong code Close mọi kết nối là xong tha hồ Edit file con

3/ Thích sẽ lưu cái Path vào registry xong chỉ cần Refresh lại data là nó lấy mặc định cái File Or Folder đã lưu trước đó

4/ hãy làm thử 3 file mỗi File có 3 Sheet ... tổng số dòng của 3 File đó cộng lại = 1048570 dòng xong chạy code thử xem sao

5/ Hoặc chọn 1 File có 3 Sheet với tổng số dòng của 3 Sheet + lại = 1048570 x 50 cột thử chạy code

hóng kết quả ???

 

[Kiều Mạnh]

Sáng tới giờ có ai rảnh thử với Power Query số dòng = 1048570 x 50 cột chưa vậy .... xong thử chạy AddIns của tôi viết xem sao ???

Phạm vi Excel nên thử tải mọi cái với Số dòng hiện có xem sao = lòi hết ra thôi .... còn vài trăm ngàn dòng ko tính

Nhanh hay siêu của siêu nhanh cũng lòi ra hết

 

[Kiều Mạnh]

tieutuvodanh192​

Tôi vừa Fix lại theo yêu cầu và úp lại File bài số 1

Sau khi chạy code xong thì đóng mọi kết nối cho phép Edit các File con và mở lên chỉnh sửa file con

Tạm tới đó ... mai mốt tính tiếp

Phải công nhận là có tư duy và khả năng test code rất tốt ... Tôi lại thích điều đó

Chứ ko như ai đó có ai đó soi ra cái điểm yếu của code là mặt sưng lên như cái thớt và sau đó là ồn ào

Cảm ơn rất nhiều

 

[huhumalu]

Có cách nào không cần đăng ký mà vẫn dùng được như add-in thông thường không anh ?
Vì việc chạy đăng ký bằng file *.vbs yêu cầu phải full control như tài khoản Administrator.

 

[Kiều Mạnh]

Có cách nào không cần đăng ký mà vẫn dùng được như add-in thông thường không anh ?
Vì việc chạy đăng ký bằng file *.vbs yêu cầu phải full control như tài khoản Administrator.

Viết *.xll mà add nó vào là xong ... còn DLL kiểu bài 1 liên quan XML thì khó chưa mò ra

Hoặc dùng hàm API của tôi viết xong khai báo API trong File *.xlam mà dùng là ko cần đăng ký nữa

Còn RegFreeCOM thì úp bên thớt khác có rồi

 

[huhumalu]

Để em xem lại mấy cái RegFreeCOM của anh.

 

[Kiều Mạnh]

ai rảnh thử với Porver Query + BI với dữ liệu 1048570 dòng x 50 cột xem .... chắc chắn là xì khói ....

Còn lèo tèo vài trăm ngàn dòng ko tính

 

[tieutuvodanh192]

ai rảnh thử với Porver Query + BI với dữ liệu 1048570 dòng x 50 cột xem .... chắc chắn là xì khói ....

Còn lèo tèo vài trăm ngàn dòng ko tính

Em vừa chạy thử tổng hợp 50 file con, mỗi file 40 cột, tổng số dòng gộp lại vượt 1 triệu và đã đạt giới hạn của 1 sheet.
Thời gian của PQ hết 140 s, còn Addins của anh em chạy hết 122 s anh nha.

 

[huhumalu]

Em vừa chạy thử tổng hợp 50 file con, mỗi file 40 cột, tổng số dòng gộp lại vượt 1 triệu và đã đạt giới hạn của 1 sheet.
Thời gian của PQ hết 140 s, còn Addins của anh em chạy hết 122 s anh nha.

View attachment 284059

Cho xin bộ file mẫu thử nghiệm và PQ với

 

[Kiều Mạnh]

Em vừa chạy thử tổng hợp 50 file con, mỗi file 40 cột, tổng số dòng gộp lại vượt 1 triệu và đã đạt giới hạn của 1 sheet.
Thời gian của PQ hết 140 s, còn Addins của anh em chạy hết 122 s anh nha.

View attachment 284059

xem sẽ bỏ tiêu đề đi nếu duyệt 50 file sẽ = 50 vòng For duyệt tiêu đề sẽ tăng tốc thêm nữa

chưa hình dung ra cách tính thời gian cho PQ từ khi thao tác ban đầu là File mới tới khi thực hiện xong....

 

[tieutuvodanh192]

Cho xin bộ file mẫu thử nghiệm và PQ với

Em đã tạo 1 thư mục chứa mẫu test, tạo thêm mấy file con, sau đó nhân bản các file con đó lên sao cho tổng số dòng chạm mốc 1 triệu hàng và chạy code bên dưới (Code PQ này của thầy Mỹ)

let
FolderName =" Điền địa chỉ thư mục chữa các file con vào đây ",
FileNameList= Folder.Files(FolderName)[Name],
FileNum={0..List.Count(FileNameList)-1},
DataF= List.Transform(FileNum, (f) =>
let
FFullName =FolderName & "\" & FileNameList{f},
Source1 = Table.SelectRows(Excel.Workbook(File.Contents(FFullName), null, true),each ([Kind] = "Sheet")),
SourceData=Source1[Data],
SheetName=Source1[Name],
SheetNum={0..List.Count(SheetName)-1},
DataN= List.Transform(SheetNum, (i) =>
let
Data0 = (SourceData{i}),
DataFName = Table.AddColumn(Table.PromoteHeaders(Data0), "File", each FileNameList{f}),
Datai = Table.AddColumn(DataFName, "Sheet", each SheetName{i})
in Datai),
List1 = Table.FromList(DataN, Splitter.SplitByNothing()),
ListColumns = Table.ColumnNames(List1{0}[Column1]),
Ketqua1 = Table.ExpandTableColumn(List1,"Column1",ListColumns)
in Ketqua1),
List2 =Table.FromList(DataF,Splitter.SplitByNothing()),
ListColumns2 = Table.ColumnNames(DataF{0}),
Ketqua = Table.ExpandTableColumn(List2, "Column1", ListColumns2)

in Ketqua

chưa hình dung ra cách tính thời gian cho PQ từ khi thao tác ban đầu là File mới tới khi thực hiện xong....

Em nhìn đồng hồ trên tay thôi anh ạ, tính từ lúc nhấn Refesh Data cho tới lúc thanh trạng thái bên dưới báo Ready và có thể thao tác được trên file.

 

[Kiều Mạnh]

code thì có sẳn hết rồi chỉ thêm vào thôi

đang thiết kế cái menu Ribbon Excel làm sao cho nó dễ nhìn và gọn đẹp 1 tí

Sơ bộ vậy mọi người thấy sao ... lúc nào rảnh làm 1 tí thôi ... mai mốt làm tiếp

 

[Kiều Mạnh]

I/ Giai đoạn 1: Cơ bản đã hoàn thiện

1/ Tùy chọn lấy tiêu đề và không lấy tiêu đề dữ liệu

2/ Tùy cho Refresh All Data đã lấy và lưu trước đó

3/ thay From Excel thành Form Files ( Vì hổ trợ tổng hợp 3 loại File vào 1 hàm duy nhất là: Excel, Access và SQLite +++... nếu thích thêm vào nữa)

II/ Giai đoạn 2: Phát triển Form Power Query với các tùy chọn

1/ Tùy chọn lấy vài File trong rất nhiều File trong 1 Folder

2/ Tùy chọn lấy vài Sheet trong 1 File có rất nhiều Sheet

III/ Giai đoạn 3: Tương tự như Giai đoạn 2 là viết cho Ms Server

IV/ Giai đoạn 4: tương tự như 3 mà viết cho SQL TCP/IP = Tổng hợp dữ liệu qua Internet
...............................................................
Quậy diết mới bất chợt nhận ra trong các hạng mục trên nó gần như giống nhau ... các hàm kế thừa và dùng lại cho nhau OK hết

xem hình mô tả xong giai đoạn 1 .... còn 2 chắc trên 2 tuần là xong thôi vì code có hết rồi chỉ vẽ cái Form làm sao cho nó thân thiện nhất và đơn giản dễ sử dụng nhất với người sử dụng nó



xong ai xin cũng cho ... ai mua cũng bán ... mà không xin ko mua thì ta gieo tú cầu ai thích thì lấy mà ko thì thui vậy

Ai muốn ghi Tên họ vào mục About thì liên hệ riêng = OK sạch

 

[Kiều Mạnh]

Theo mô tả lộ trình tuần tự hóa thực hiện bài số 24 ... thì hôm nay tôi giới thiệu với các bạn Form Power Query bản Demos

Tùy chọn Refresh All Data đã lấy và lưu trước đó = tạm dừng vì có nhiều phát sinh và chưa hoàn thiện

Tuần tự sử dụng Form Power Query như sau:


1/ chọn Folder và tùy chọn lọc File theo hình



Select a Folder nó sẽ lấy hết các Files trong Folder gán vào listbox phía dưới
File Type thực hiện sau đó tùy chọn lọc theo File mà Folder đã chọn trước đó

2/ Tùy chọn tổng hợp list TableName theo Files ... tích chọn list TableName cần lấy




3/ Tùy chọn tổng hơp List Files ttheo checkBox đã check




4/ Chức năng tìm File như sau nháy đúp chuột vào CheckBox nó sẽ mở path File ra chức năng này chỉ tiện tay cho thêm vào hổ trợ tìm kiếm file nhanh



5/ Ai có mong muốn điều chỉnh thêm bớt gì cứ mô tả ... tôi xem xét trong khả năng nếu phù hợp sẽ điều chỉnh lại

6/ Thong thả theo thời gian tôi sẽ gom hết nhưng cái nhu cầu phổ biến và chung nhất từng nêu trên GPE này vào ExcelTools trong phạm vi và khả năng có thể .... và đây là bản gieo tú cầu như mô tả bài trước = Free không giới hạn

Hổ trợ tổng hợp File Excel + Access + SQlite ++++ nếu thích Tôi thêm nữa vào trong 1 Hàm duy nhất

 

[Kiều Mạnh]

Có lẽ thiết kế cái Form cho bài số 25 như sau

1/ Giao diện Form Power Query sẽ bao gồm cả SQL và tổng hợp theo tùy chọn cột

2/ Tùy chọn tìm kiếm file đệ quy folder hoặc Ko

 

[tieutuvodanh192]

Có lẽ thiết kế cái Form cho bài số 25 như sau

1/ Giao diện Form Power Query sẽ bao gồm cả SQL và tổng hợp theo tùy chọn cột

2/ Tùy chọn tìm kiếm file đệ quy folder hoặc Ko

View attachment 284245

Form như vậy em thấy trực quan và dễ sử dụng hơn đấy anh.

 

[ongke0711]

chưa hình dung ra cách tính thời gian cho PQ từ khi thao tác ban đầu là File mới tới khi thực hiện xong....

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

 

[Kiều Mạnh]

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

Thong thả sẽ thêm File CSV, Text +++++++ sau thôi

đang tập chung làm từng cái 1 cho xong cái đã

Vừa mò vừa viết và + vừa thăm dò ý kiến quần chúng tham gia cho nó hoàn thiên hơn

( đừng hiểu sai vặn vẹo từ quần chúng ở đây he ... ko lại ồn ào ... từ đó có lẽ ai đó đã từng trong xx sẽ hiểu nó ....Miễn bàn ở đây .... ai đó bà tám dòng này vui lòng lập thớt mà bàn )

 

[Kiều Mạnh]

Tôi vừa xóa hết các file và Úp lại File bài Số 1 có giao diện như sau

Thong thả tôi sẽ thêm loại File CSV + text +++++ vào sau

1/ Với tùy chọn duyệt File đệ quy Folder và không ... trước mặc định là duyệt đệ quy

tiết kiệm thời gian nếu ko cần thiết duyệt đệ quy Folder

2/ chức năng Run SQL To Range Hổ trợ Excel + Access + SQLite builder SQL

3/ Mục list Files path trong ComboBox sẽ loại bỏ sau thêm vào đó sẽ là tùy chọn tổng hợp File theo SQL với các tùy chọn cột




Ai có mong muốn gì cứ nêu + báo lỗi tôi sẽ điều chỉnh lại

Xin cảm ơn

 

[Kiều Mạnh]

Tôi đang thiết kế cái Form cho SQL TCP/IP giao diện như sau



Form Ms Server tạm sơ bộ như sau



Trong đó bao gồm:

1/ như thường lệ đã úp nhiều thớt khác là lấy dữ liệu qua Internet = Run SQL To Range

2/ Tổng hợp list TableName = tùy chọn trong CheckListBox ( Mới )

3/ Tổng hợp list File = tùy chọn trong CheckListBox ( Mới )

4/ Tổng hợp list File + Table với SQL tùy chọn các Cột của nó ( Mới )

5/ quá trình thực hiện nếu xét thấy chức năng nào đó ko phù hợp có thể xem xét điều chỉnh lại
........................

File bài số 1 mới úp lại điều chỉnh tìm kiếm File nhanh hơn rất nhiều khi chọn File Type và checkBox In sub Folder

Xong hết như kế hoạch định trước sẽ có 1 khoãng thời gian nhất định Fix code và lắng nghe người dùng phản hồi xong => điều chỉnh lại cho phù hợp

và viết thêm các tiện ích nhỏ kèm theo cho tăng thêm phần lung linh của nó

 

[Kiều Mạnh]

File bài số 1 Hổ trợ tìm kiếm File nhanh mặc định với Folder đã chọn trước đó

Trong đó bao gồm nhiều chức năng đã mô tả loạt bài trước

 

[dinhduy]

Khi nào anh hoàn thiện, anh có thể làm video hướng dẫn thì tuyệt vời quá. Em chưa đủ trình nên lắng nghe mọi người thôi. Rất cám ơn sản phẩm của anh vì cộng đồng.

 

[Kiều Mạnh]

Khi nào anh hoàn thiện, anh có thể làm video hướng dẫn thì tuyệt vời quá. Em chưa đủ trình nên lắng nghe mọi người thôi. Rất cám ơn sản phẩm của anh vì cộng đồng.

Bạn thử tải file bài số 1 ấy xong bấm chuột vài cái là ra thôi ... mọi cái nó tự động hóa hết rồi ... phần còn lại của Bạn là Select hay Check = Tích chọn nó thôi

Xong hết tôi sẽ làm 1 Video theo yêu cầu của Bạn ... hiện đang code tổng hợp File qua Internet - SQL TCP/IP

Mọi cái khá đơn giản là kế thừa các hàm trước viết thêm các hàm trung gian là xong thôi

 

[Kiều Mạnh]

Cơ bản Tôi đã thực hiện xong ... Mô tả sơ bộ qua hình ảnh sau

1/ Nếu bạn tích chọn khoanh tròn màu xanh và chạy Buton màu xanh nó sẽ tổng hợp các File đã chọn bạn bao gồm tất cả các Sheet trong File

2/ Nếu bạn tích chọn khoanh màu đỏ thì nó sẽ tổng hợp các Tablename đó cho bạn

3/ Nếu bạn tích chọn Khoanh tròn màu tím .... như trên

5/ kèm theo các tiện ích tìm kiếm vvv

6/ Áp dụng cho SQL TCP/IP = như nhau

7/ Áp dụng cho Ms Server chỉ có tổng hợp TableName .... Ms Server ko có File ??!!!

8/ File úp lại bài số 1

9/ quá trình sử dụng ai có mong muốn gì cứ nêu + mô tả lỗi ... nếu phù hợp và khả năng cho phép tôi sẽ điều chỉnh lại

10/ Tôi sẽ viết lại cái AppServer sau vì mấy bản trước úp trên này có nhiều lỗi phát sinh ngoài ý muốn

 

[tieutuvodanh192]

Cơ bản Tôi đã thực hiện xong ... Mô tả sơ bộ qua hình ảnh sau

1/ Nếu bạn tích chọn khoanh tròn màu xanh và chạy Buton màu xanh nó sẽ tổng hợp các File đã chọn bạn bao gồm tất cả các Sheet trong File

2/ Nếu bạn tích chọn khoanh màu đỏ thì nó sẽ tổng hợp các Tablename đó cho bạn

3/ Nếu bạn tích chọn Khoanh tròn màu tím .... như trên

5/ kèm theo các tiện ích tìm kiếm vvv

6/ Áp dụng cho SQL TCP = như nhau

7/ Áp dụng cho Ms Server chỉ có tổng hợp TableName .... Ms Server ko có File ??!!!

8/ File úp lại bài số 1

9/ quá trình sử dụng ai có mong muốn gì cứ nêu + mô ta lỗi ... nếu phù hợp và khả năng cho phép tôi sẽ điều chỉnh lại

10/ Tôi sẽ viết lại cái AppServer sau vì mấy bản trước úp trên này có nhiều lỗi phát sinh ngoài ý muốn


View attachment 284333

Tuyệt vời.
Với những người không thạo Power Query thì Addins của anh là một công cụ bổ sung rất tuyệt vời, đợt này khi có thời gian em sẽ giành thời gian để test addins này.
Cảm ơn anh.

 

[Kiều Mạnh]

Tôi quay 1 đoạn Video ngắn Tổng hợp dữ liệu qua Internet thông qua SQL TCP/IP

Áp dụng gần giống như tổng hợp dữ liệu trên PC

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

 

[Kiều Mạnh]

Tôi mới thử nếu Tổng hợp lấy hết TableName của 1 File Access có Tổng số dòng là 216 ngàn dòng x 12 cột

1/ Lấy trên PC chạy hết trên 4 giây

2/ lấy qua Internet chạy hết trên 10 giây ( Phụ thuộc 1 phần vào tốc độ mạng của nhà ai đó nhanh hay chậm )

3/ Trong file sau có trên 40 TableName trong đó có nhiều Tablename chỉ có trên 5 Cột

 

[Kiều Mạnh]

@ongke0711
Làm phiền bạn chút

Rảnh tôi đang xem cấu trúc file CSV trên Delphi và vài file trên GPE này mà thấy nó rối cả lên chưa hình dung ra đâu là cấu trúc hay sử dụng nhất

File tôi úp phía dưới là các file mẫu đo Delphi tạo ra ... còn trên GPE này lại khác

để load Data từ file csv trên Delphi họ cho vào TFDMemTable chạy khá nhanh

Phiền Bạn có file csv nào chuẩn chung nhất úp cho xin 1 file test code

 

[ongke0711]

@ongke0711
Phiền Bạn có file csv nào chuẩn chung nhất úp cho xin 1 file test code

Đí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

 

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

 

[ptm0412]

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

Từ lúc chọn file đến khi đưaa xong xuống sheet: 59 giây, file 2020 02 Data, 800 ngàn dòng (Power query thuần)



Refresh 52 giây.

 

[Kiều Mạnh]

Từ lúc chọn file đến khi đưaa xong xuống sheet: 59 giây, file 2020 02 Data, 800 ngàn dòng (Power query thuần)

View attachment 284704

Refresh 52 giây.

chắc máy đó cấu hình mạnh hơn ... nói chung tùy máy

Em mới thử lại trên Máy của em vẫn là tầm 4 phút


Chờ cài hình sau tầm 2 phút

 

[ptm0412]

chắc máy đó cấu hình mạnh hơn ... nói chung tùy máy

Đúng rồi. Core i5 đời 11 (6 cores 12 threads), 2.60GHz, 16 Gb Ram.
Cũng file 800 ngàn dòng, nhưng không load xuống sheet mà chỉ create connection & load to Data Model: 40 giây. Refresh 36 giây



Tôi không có ý tranh đua tốc độ vì có máy mạnh máy yếu, chỉ muốn so sánh các phương thức của cùng 1 công cụ (cụ thể là power query):
- Load xuống sheet/ chỉ connect và Data model
- Nếu là folder: Lấy theo cách cơ bản của PQ và code M tự viết (ở bài #47)

 

[Kiều Mạnh]

Đúng rồi. Core i5 đời 11 (6 cores 12 threads), 2.60GHz, 16 Gb Ram.
Cũng file 800 ngàn dòng, nhưng không load xuống sheet mà chỉ create connection & load to Data Model: 40 giây. Refresh 36 giây

View attachment 284708

Tôi không có ý tranh đua tốc độ vì có máy mạnh máy yếu, chỉ muốn so sánh các phương thức của cùng 1 công cụ (cụ thể là power query):
- Load xuống sheet/ chỉ connect và Data model
- Nếu là folder: Lấy theo cách cơ bản của PQ và code M tự viết (ở bài #47)

1/ Phạm vi chủ đề này em lại ko quan tâm chuyện đó ... như em đã nói trước chỉ chọn file lấy dữ liệu gán lên sheet là xong ... còn sau đó như thế nào ... nó lại qua một chủ đề khác ta bàn tiếp .... với em tới đó xem như hoàn thành mục tiêu đề ra ( Áp dụng cho 1 File csv )

2/ Áp dụng cho List File trong Folder cũng như trên = hoàn thành mục tiêu đề ra .... còn lại sau đó em ko bàn nữa

Như mô tả bài số 1 chỉ phục vụ cái chung nhất mang tính đại chúng

Cảm ơn Anh

 

[befaint]

Cũng file 800 ngàn dòng, nhưng không load xuống sheet mà chỉ create connection & load to Data Model: 40 giây. Refresh 36 giây

(Em chỉ góp hình vui thôi anh nhé).

Python trên máy chip i7 đời 2015.

 

[ptm0412]

hoàn thành mục tiêu đề ra ....

Như mô tả bài số 1 chỉ phục vụ cái chung nhất mang tính đại chúng

Tôi tưởng mục tiêu đề ra là bao gồm cải thiện nhiều thứ, trong đó có tốc độ.

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

File json đang hót á.

 

[Kiều Mạnh]

File json đang hót á.

Anh úp cho Em xin 1 file mẫu đơn giản nhất kiểu như 1 +1 = 2 thôi làm = PQ ... rảnh thong thả xem tiếp

Vì trên Delphi nó hổ trợ json rất tốt .... quan trọng là ta có biết viết hay ko thôi

còn tốc độ thực dụng code em viết nhanh gần gấp 3 lần rồi ... còn sau đó người dùng xử lý sao là quyền của họ tùy chỉnh tiếp = em ko quan tâm

 

[ptm0412]

(Em chỉ góp hình vui thôi anh nhé).

Xin lỗi chủ đề, chen ngang 1 cái:
Với PQ từ lúc chọn file cho đến khi nhìn thấy hình dưới đây chưa tới 1 giây, thời gian load to mới nhiều.

Bài đã được tự động gộp: 16/12/22

Anh úp cho Em xin 1 file mẫu đơn giản nhất kiểu như 1 +1 = 2 thôi làm = PQ ... rảnh thong thả xem tiếp

Có sẵn luôn nè:
Bài viết: Lấy dữ liệu từ json

Link tải file json file

 

[ongke0711]

(Em chỉ góp hình vui thôi anh nhé).

Python trên máy chip i7 đời 2015.

Đang tính đưa cái Python lên thì befaint góp vui trước rồi...hehe..

 

[befaint]

Với PQ từ lúc chọn file cho đến khi nhìn thấy hình dưới đây chưa tới 1 giây, thời gian load to mới nhiều.

Đây anh nhé .
.

 

[Kiều Mạnh]

chắc khi nào rảnh làm cái trình xem trước trên Delphi 800.000 x 48 cột chạy trên máy cùi bắp chỉ có 24.421 giây thôi còn máy ngon chắc trên 5 giây à

mấy ngày qua phá nát trên Form Delphi rùi

 

[ptm0412]

View attachment 284716

Đây anh nhé .

Đọc kỹ nhé: "Data Type base on 200 rows", không phải là review 200 rows. Mà thôi, chen ngang chủ đề người ta hoài

 

[befaint]

Đọc kỹ nhé: "Data Type base on 200 rows", không phải là review 200 rows. Mà thôi, chen ngang chủ đề người ta hoài

Em chụp hình phải căn chỉnh rất kỹ luôn đó anh.


Và anh nhìn kỹ thanh cuộn đứng (dọc) nhé.

 

[Kiều Mạnh]

ai code python xong úp cho Mạnh vọc chút xem tình hình sao he

 

[kelacloi]

Em chụp hình phải căn chỉnh rất kỹ luôn đó anh.
View attachment 284722

Và anh nhìn kỹ thanh cuộn đứng (dọc) nhé.

Nó chỉ là phần dùng để đoán định dạng, lấy 200 dòng làm mẫu thôi mà anh?
Anh cuộn xuống đọc dòng cuối cùng chưa anh?

 

[Kiều Mạnh]

Trong PQ cái hình sau cảm giác như cái trình xem trước CSDL với 2 cái COMbox tùy chỉnh chứ làm trò gì ta
à quên còn combox ngôn ngữ nữa

 

[Kiều Mạnh]

JSON trên Delphi hổ trợ quá tốt ... các đơn vị hàm dựng sẳn còn ta chỉ khai báo và sử dụng
Mò chút cơ bản nó cũng tạm Show ra

có điều cũng ko có gì phổ biến và nóng bỏng ở đây cả .... nên tới đó tạm dừng làm việc khác tốt hơn

1/ Xem Video sẽ thấy trên Tmeno là nó lấy các Cells trên File Excel tạo thành JSON
2/ Còn cái Msgbox là duyệt từng dòng JSON ............ vvv
.....
Viết thêm hàm lấy cái mục số 2 gán xuống Sheet là xong................. làm tới đó thôi vì trên này có ai dùng nó đâu mà làm

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

 

[Kiều Mạnh]

đã cập nhật hổ trợ lấy File *.csv úp lại File bài số 1



Dự án tiếp tục Fix lỗi và viết tiếp ... rất mong mọi ý kiến tham gia trong phạm vi mô tả bài số 1 ...

 

[Kiều Mạnh]

thong thả tôi sẽ viết thêm trình Builder SQL cho File *.CSV vào nữa ... vì trên thực tế có cột ko cần lấy mà cố lấy lên làm chi phí mất thời gian và góp phần tốn kém vô tích sự

 

[Kiều Mạnh]

cập nhật mới chi tiết mô tả + File úp bài số 1

 

[ptm0412]

Trong PQ cái hình sau cảm giác như cái trình xem trước CSDL với 2 cái COMbox tùy chỉnh chứ làm trò gì ta
à quên còn combox ngôn ngữ nữa

Ý tôi nói là khi đã có ra được cái hình tức là phần chạy ngầm đã lấy ra được dữ liệu (có sao lấy vậy), lưu trong bộ nhớ rồi (0 chấm mấy giây gì đó). Sau đó khi nhấn nút load/ load to mới là lúc đưa xuống và tốn thời gian.

JSON trên Delphi hổ trợ quá tốt ... các đơn vị hàm dựng sẳn còn ta chỉ khai báo và sử dụng

có điều cũng ko có gì phổ biến và nóng bỏng ở đây cả ....
Viết thêm hàm lấy cái mục số 2 gán xuống Sheet là xong................. làm tới đó thôi vì trên này có ai dùng nó đâu mà làm

Tôi được biết 1 trường hợp, và người ta nói là hiện nay nếu có bộ cơ sở dữ liệu lớn (nhiều bảng, rất nhiều dòng) thì người ta sẽ lưu dưới dạng json, khi cần truy xuất sẽ rất nhanh, họ nói nhanh hơn dùng java truy xuất bằng SQL luôn (họ nói vậy). Vì nghỉ làm rồi nên ít có cơ hội tiếp xúc với các đối tac có CSDL, nghe vậy thì biết vậy thôi.
Trường hợp khác (thấy trên GPE) đó là dữ liệu dạng json lưu trữ dữ liệu của 1 số lớn trang web.

 

[Kiều Mạnh]

Cập nhật mới khi Bạn duyệt Folder trên 10 GB mà chọn tất cả các file với tùy chọn *.* ... thì nó vẫn chạy cho tới khi

xong thì thôi và không lối thoát ... trừ khi xx
Thêm phím tắt Esc ... khi bạn nhấn nó sẽ thoát ra ko cần thiết chờ nó tìm kiếm nữa .... File úp lại bài số 1 ... bất chợt tôi nhớ nút pause ai đó nói
và sáng nay tôi cảm giác thấy nó .... khi test Folder 15 GB

Hoặc Hình sau

 

[Kiều Mạnh]

Chủ đề này sẽ tạm dừng lại một thời gian dài vì tôi đang tập chung viết CustomTaskPane vào nữa và điều chỉnh lại thiết kế cho phù hợp và thân thiện nhất với người sử dụng nó

1/ sẽ thêm Explorer Folder vào tìm kiếm và truy xuất dữ liệu kiểu cây thư mục
2/ truy xuất và xử lý dữ liệu qua Taskpane ++++....................
....
Vậy là có Ribbon Menu + Taskpane hổ trợ nữa sẻ tăng thêm phần sinh động và trực quan hơn

Ổn định mọi cái tôi sẽ xuất 1 OCX riêng cho CustomTaskpane và ko cá nhân hóa bất cứ cái gì cho ai đó thích thì sử dụng nó

TaskPane cách đây khoãng 3 năm trước tôi mò trên VB6 khoãng 2 tuần viết xong ... xong cố chuyển vào Delphi khoãng 3 tháng gì đó không xong
ghét bỏ từ ngày đó ... mới xem lại khoãng 5 ngày mò ra

sơ bộ hình ảnh TaskPane hình thành trong Delphi

 

[Haem]

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

View attachment 284700

Nó nằm ở đâu vậy

 

[ptm0412]

Nó nằm ở đâu vậy

Tùy thuộc vào nó là ai, hay nó là cái gì.

 

[hamhochoi66]

Mới viết thêm hàm tổng hợp tất cả các File trong Folder To Sheet

Thong thả chơi là chính tôi sẽ mô phỏng 1 số chức năng viết gần giống theo Power Query và Power BI của Ms

lấy dữ liệu thô chưa xử lý lên Sheet .... còn lại ai đó tùy chỉnh lại cho phù hợp với nhu cầu sử dụng

Mcode cảm giác thấy bốc mùi lắm .... khó chơi

Video 2022 11 27 192355

Image Video 2022 11 27 192355 hosted in Gifyu

gifyu.com

Em chào thầy Mạnh!
Em có sử dụng tool này phần Join Dât Multiple của thầy nhưng không biết em thao tác bị sai hay tool em tải bản cũ mà ra kết quả không đúng ạ.
Em có 2 file A, B kết cấu y chang nhau (19 cột). File A là dữ liệu là 505 dòng, B là 495 dòng. 2 file này bỏ vào 1 thư mục C. Sau đó em mở file excel mới chọn phần From excel thì xuất hiện form yêu cầu dẫn đến thư mục C.
Kết quả là tool tổng hợp dữ liệu bị double lên ạ. File A 505 dòng thì file tổng hợp 1.010 (dữ liệu file A) và 990 (dữ liệu file B).
Mong thầy phản hồi ạ! Cám ơn thầy!

 

[Kiều Mạnh]

Khi mà ốm đau hay bệnh tật đi viện khám bác sĩ xong ngồi xa cả 100 mét keo em đau lắm ... đau ở đây nè ... xong không cho ai lại gần cả thì không có tay bác sĩ nào biết đó là gì cả

Khi trên Excel cứ la lỗi xong keo ai đó giúp mình mà không có gì làm căn cứ ( hay cơ sở ) thì fix lỗi = niềm tin hay sao ???

cái chức năng đó Tôi thiết kế chung nhất là nó sẽ tổng hợp hết tất cả các Sheet có trong một File xong gán dữ liệu lên Sheet nối xuống dòng

nếu trên Sheet có nhiều sheet thì nó sẻ lấy hết lên ... nếu muốn chỉ lấy 1 hay vài sheet chỉ định trong 1 file thì có thể viết lại nó

 

[hamhochoi66]

Khi mà ốm đau hay bệnh tật đi viện khám bác sĩ xong ngồi xa cả 100 mét keo em đau lắm ... đau ở đây nè ... xong không cho ai lại gần cả thì không có tay bác sĩ nào biết đó là gì cả

Khi trên Excel cứ la lỗi xong keo ai đó giúp mình mà không có gì làm căn cứ ( hay cơ sở ) thì fix lỗi = niềm tin hay sao ???

cái chức năng đó Tôi thiết kế chung nhất là nó sẽ tổng hợp hết tất cả các Sheet có trong một File xong gán dữ liệu lên Sheet nối xuống dòng

nếu trên Sheet có nhiều sheet thì nó sẻ lấy hết lên ... nếu muốn chỉ lấy 1 hay vài sheet chỉ định trong 1 file thì có thể viết lại nó

Dạ em gửi file ạ, File TH là tổng hợp sau khi chọn thư mục chưa 2 file còn lại. Thầy xem giúp em nhé!

 

[Kiều Mạnh]

Dạ em gửi file ạ, File TH là tổng hợp sau khi chọn thư mục chưa 2 file còn lại. Thầy xem giúp em nhé!

1/ dừng ngay cách xưng hô đó lại ... nó vô duyên và lố bịt vì không đúng lúc đúng chỗ ... và tôi cũng không phải là thầy bà gì cả

2/ Tôi dò 1 lúc file của bạn nếu nhìn mắt thường thì thấy có 1 Sheet

3/ Nhưng qua SQL của tôi thì thấy nó có 2 Sheet có dữ liệu như nhau ... còn tại sao thì tôi lại chưa biết

xem hình



Vì nó có 2 Sheet có dữ liệu như nhau nên khi lấy lên dữ liệu sẻ x 2 nên bạn sẻ thấy dữ liệu gấp đôi là đúng

hãy tự xử và tìm hiểu tại sao nó có 1 Sheet Ẩn và mắt thường khi nhìn trong VBE thì chỉ có 1 Sheet duy nhất xong = cách gì đó xóa cái Sheet không nhìn thấy kia đi là hết



Xem trong đó thấy chỉ có 1 Sheet nhưng thực tế có 2 Sheet

 

[Kiều Mạnh]

SQL trong Sheet Ẩn chi tiết như sau ... tự xử lý = cách gì đó là tùy

SELECT [SALE], [TUYEN], [MÃ KH TRÊN FBO], [20'], [40'], [40' Nhẹ], [CONTNO#], [ĐỊA CHỈ ĐÓNG HÀNG], [ĐỊA CHỈ TRẢ HÀNG], [DOANH THU GOM VAT], [PHAI THU KHAC], [THU LUU CONT], [DT CHUA VAT (T)], [DOANH THU CHUA VAT] FROM ['HCM-HPH$'_xlnm#_FilterDatabase]

Rút gọn lại như sau

select * from ['HCM-HPH$'_xlnm#_FilterDatabase]

 

[Kiều Mạnh]

Hết trò ẩn Module nay lại xuất hiện Ẩn Sheet = cách chỉnh sửa File .xml

Rồi làm trò gì tiếp đây ???!!!!!!!!!!!!!!!!!!!!!!

 

[ptm0412]

Hết trò ẩn Module nay lại xuất hiện Ẩn Sheet = cách chỉnh sửa File .xml

Dùng power query cũng thấy những cái này, theo tôi không phải sheet ẩn mà là dữ liệu đã lọc và lưu trong trạng thái lọc, tên của nó có đuôi filter database. Một cái nữa PQ cũng thường thấy đó là PrintArea của 1 sheet nào đó. Vì vậy khi làm PQ tôi phải lọc loại trừ ra.

 

[Kiều Mạnh]

Tôi lại chỉ quan tâm một chút

1/ nó tạo ra Sheet ẩn đó bằng cách gì ??!! ( bằng code hay chỉnh sửa file .xml thủ công hay cách nào khác ...)

2/ Cách hiện nó ra là gì ??!! ( bằng code hay chỉnh sửa file .xml thủ công hay cách nào khác ...)

3/ cách đó cũng tạm ổn khi lưu trữ dữ liệu trong Sheet Ẩn và cần khi tái sử dụng nó ... nên chỉ quan tâm chút về nó thế thôi

còn không có gì mật mỡ ở đây cả khi dấu như vậy thì cũng lấy ra hết mà chỉ phòng hờ dữ liệu ai đó táy máy lỡ tay xóa mất đi thôi

 

[ptm0412]

1/ nó tạo ra Sheet ẩn đó bằng cách gì ??!! .... phòng hờ dữ liệu ai đó táy máy lỡ tay xóa mất đi thôi

Theo tôi suy đoán thì nó tự tạo ra và xóa cũng chẳng sao (mặc dù tôi chưa thử xóa), có điều tôi cần dữ liệu gốc chứ không cần dữ liệu phái sinh.

 

[Kiều Mạnh]

Theo tôi suy đoán thì nó tự tạo ra và xóa cũng chẳng sao (mặc dù tôi chưa thử xóa), có điều tôi cần dữ liệu gốc chứ không cần dữ liệu phái sinh.

1/ Vấn đề giải đáp thắc mắc bài số 86 tại sao "dữ liệu bị double" đã sáng tỏ như ban ngày = xong vì nó có liên quan bài số 1 của chủ đề này

2/ 1 chút có liên quan + tò mò chút là Sheet Ẩn thì đã nói ở bài số 93 ( thay vì lập thớt mới không làm loãng chủ đề này ) nhưng tôi vẫn cố ý nêu ra 1 chút

cho ai đó có đi theo hướng mật mỡ đó thì quan tâm thêm

3/ Mọi cái giải đáp thỏa mãn bài số 86 đã xong ... xem như khép lại ở đây vì có bàn thêm cũng không đi đâu về đâu cả... và quyền xóa hay không xóa Sheet ẩn do bài số 86 tự xử lý

 

[hamhochoi66]

1/ dừng ngay cách xưng hô đó lại ... nó vô duyên và lố bịt vì không đúng lúc đúng chỗ ... và tôi cũng không phải là thầy bà gì cả

2/ Tôi dò 1 lúc file của bạn nếu nhìn mắt thường thì thấy có 1 Sheet

3/ Nhưng qua SQL của tôi thì thấy nó có 2 Sheet có dữ liệu như nhau ... còn tại sao thì tôi lại chưa biết

xem hình

View attachment 296607

Vì nó có 2 Sheet có dữ liệu như nhau nên khi lấy lên dữ liệu sẻ x 2 nên bạn sẻ thấy dữ liệu gấp đôi là đúng

hãy tự xử và tìm hiểu tại sao nó có 1 Sheet Ẩn và mắt thường khi nhìn trong VBE thì chỉ có 1 Sheet duy nhất xong = cách gì đó xóa cái Sheet không nhìn thấy kia đi là hết

View attachment 296608

Xem trong đó thấy chỉ có 1 Sheet nhưng thực tế có 2 Sheet

Dạ, xin lỗi đã làm anh khó chịu, em tay mơ nên không rành code chỉ lên tìm 1 số công cụ hữu ích phục vụ cho công việc đôi khi làm mất thời gian của anh. Anh có link hướng dẫn sử dụng Excel tool không ạ?

 

[YêuCN]

Tôi có coi qua file không hề có sheet nào ẩn !

 

[Kiều Mạnh]

Tôi có coi qua file không hề có sheet nào ẩn !

biết là sẻ vào vì bạn hay quan tâm tới nó ... sớm hay muộn sẻ dò ra thôi

xong ta lại lập thớt mới ... chủ đề là bảo mật Sheet của Excel chăng ??!!

Từ bài số 89 To 95 nói rất rõ như ban ngày rồi con gì nữa

dữ liệu của Sheet không nhìn thấy như hình sau