Best way để upload 100k rows file excel vào database

B có thể lưu đường dẫn file kết quả vào database Mỗi user upload sẽ có job_id, status, result_file

• Nếu là valid từng row trước khi thực thi thì chỉ bắt được phần nào lỗi liên quan tới cấu trúc, cú pháp. Concept valid single row - excute sẽ tiêu tốn nhiều thời gian. Phần này bạn tự tùy biến.

• Đa số trường hợp lỗi xảy ra sau khi thực thi câu lệnh: invalid column name, invalid table name...

Đối với trường hợp của bạn:

1. Insert Database số lượng lớn dùng batch sql để tối ưu.
2. Sử dụng Procedure để thực thi và handling exception, lưu executable table.
3. Callback tùy biến select executable table trả về cho user.

Có nhiều hướng tiếp cận:

1. Sử dụng JS để đọc file excel và hiển thị trực tiếp nội dung file trên browser. Khi hiển thị thì đồng thời thực hiện validate dữ liệu và báo lỗi luôn. Chỗ này có thể cho sửa trực tiếp dữ liệu ngay trên browser. Khi nhấn upload thì không gửi file lên server mà sẽ gửi dữ liệu theo từng batch, VD: Đọc một cục dữ liệu theo từng 5, 10, 50 rows để gửi lên server. Nếu server có lỗi thì trả về thông tin cần thiết cho browser như lỗi row nào, do đâu để browser hiển thị lại trên màn hình.
2. Vẫn đọc file excel trên browser & validate + hiển thị lỗi luôn. Sau khi user fix hết lỗi thì cho upload file lên server.
3. Upload file thẳng lên server:

• Server lưu lại file đồng thời cũng lưu một record vào database, kiểu như: File A, Status: Queued, Created Time, Completed Time.
• Đẩy một job UploadExcelFile vào queue. Bạn có thể dùng queue id để tạo channel web socket hiển thị kết quả xử lý realtime nếu cần. Job này có nhiệm vụ là thực hiện đọc dữ liệu từ file excel theo từng batch 100 rows /1 lần đọc (các lib thường có option này). Thực hiện validate từng row, cái nào hợp lệ thì lưu, cái nào lỗi thì có thể bắn về cho client qua web socket hoặc lưu lại ra file excel có cấu trúc tương tự. Job chạy xong thì cập nhật lại Status trong Database, Tên File kết quả các dòng lỗi nếu có.

Nhìn chung là có thể làm như trên hoặc phối kết hợp các cách trên hoặc cũng có thể thêm thắt các tùy biến khác sao cho phù hợp với tình hình, kiến trúc hệ thống và cả tiến độ dự án.

Mình đã làm qua bài toán tương tự, bạn tham khảo các bước như sau mình từng làm nhé:

1. Upload file lên S3 hoặc các dịch vụ tương tự, khi ấy chúng ta sẽ có đường dẫn để tải file đó về
2. Tạo service task scheduler/cronjob hoặc bắn kafka qua 1 service nào đó để xử lý ngầm. Chúng ta có thể thiết lập service task scheduler để chạy định kỳ hoặc xử lý ngay lập tức khi có file mới được tải lên.
3. Truy xuất file từ S3, service task scheduler sẽ truy xuất file từ S3 bằng cách sử dụng đường dẫn đã được cung cấp khi tải file lên.
4. Validate từng row dữ liệu, nếu row không hợp lệ, ghi lại thông tin lỗi tương ứng với row đó vào log hoặc database,... và lưu thêm trạng thái xử lý thành công hay thất bại
5. Trả lại kết quả lỗi cho người dùng, ở đây bạn có thể lấy data từ log/database lên để trả về file kết quả lỗi, hoặc bước này bạn có thể làm ở bước ở trên, lưu file vào s3, khi người dùng cần tải thông tin lỗi thì mình sẽ trả lại liên kết file đấy chúc bạn sớm tìm dc giải pháp