Import dữ liệu lớn từ excel vào sql server nhanh nhất.

[quang0308]

Nhờ các anh, chị chỉ giúp có cách nào đưa dữ liệu từ excel vào sql server bằng VBA nhanh nhất đối với nguồn dữ liệu trên 10k dòng mà không bị treo file excel không ah, Em áp dụng câu lệnh insert into nó chậm và treo máy khi dữ liệu lớn.

 

[quang0308]

- Dính lỗi khai báo biến gộp. Có bài nói rồi.
- Mỗi lần lấy dữ liệu là mỗi lần tương tác với Sheet.
- Các Action query (Delete, Insert, update) thì chỉ cần thực thi, không cần phải qua đối tượng Recordset. Dùng thẳng đối tượng ADO Connection hoặc ADO Command.
- Code VBA còn lủng củng thì sẽ khó hơn khi tương tác với stored procedure của SQL Server.

Hic nó chi chạy chậm chứ nó không hề báo lỗi gì.Bạn có thể chia sẽ cho mình cách bulk insert vba được không?

 

[befaint]

Ợ. Tưởng vướng mắc gì, sao ngay bài #1 không úp code đã làm lên cho rồi.
Vậy mà phương án nào đưa ra cũng kêu chậm.

Gọi tới từng cell thế kia thì siêu rùa chứ chậm gì.

last_row còn gọi tới sheet cụ thể, lúc lấy dữ liệu thì gọi cell trống không.

 

[tigertiger]

Nhờ các anh, chị chỉ giúp có cách nào đưa dữ liệu từ excel vào sql server bằng VBA nhanh nhất đối với nguồn dữ liệu trên 10k dòng mà không bị treo file excel không ah, Em áp dụng câu lệnh insert into nó chậm và treo máy khi dữ liệu lớn.

Nên hỏi IT phụ trách cơ sở dữ liệu đó.
Nếu bạn là IT phụ trách luôn: thì sẽ biết nên hỏi vấn đề này ở đâu thay vì Excel

 

[ongke0711]

C1 mình đã làm nhưng vần không nhanh hơn được.
C2 thì mình không biết, bạn có thể hướng dẫn mình được không ?

Bạn xem file đính kèm.
- Mở SQL Server và chạy lần lượt 3 cái SQL script để tạo: TestDB, tblRawData và cái stored procedure Bulk insert.
- Chạy file Excel và nhập các thông số kết nối tới máy chủ SQL Server của bạn.

Bổ sung: Tùy phiên bản và thiết lập SQL Server của bạn mà thay đổi Connection String cho phù hợp nhé.

mConnectionString = "Network Library=DBMSSOCN;" & _
                    "PROVIDER=MSOLEDBSQL;DATA SOURCE=" & mServerName & _
                    ";INITIAL CATALOG=" & mDatabaseName & _
                    ";User Id=" & mUserName & ";Password=" & mPassword & ";"

mConnectionString = "Driver={SQL Server};" & _
                    "Server=" & mServerName & _
                    ";Database=" & mDatabaseName & _
                    ";Uid=" & mUserName & _
                    ";Pwd=" & mPassword & ";"
                    '";DataTypeCompatibility=80;"    'dung cho SQL SV 2019 với các datatype moi'

 

[hvnhpro]

Bạn xem file đính kèm.
- Mở SQL Server và chạy lần lượt 3 cái SQL script để tạo: TestDB, tblRawData và cái stored procedure Bulk insert.
- Chạy file Excel và nhập các thông số kết nối tới máy chủ SQL Server của bạn.

Cảm ơn Ongke0711 đã chia sẻ.
Em đang tìm hiểu về ADODB, bác có thể cho em xin thêm tài liệu hoặc link tham khảo được ko, em cảm ơn!!!

vd như qua ngâm cứu file bác gửi, em thấy bác khai báo mấy biến chung này mà vẫn ko hiểu khai báo này có mục đích để làm gì?

Private Const adUseClient As Long = 3
Private Const adLockReadOnly As Long = 1
Private Const adStateOpen As Long = 1
Private Const adCmdStoredProc As Long = 4
Private Const adParamOutput As Long = 2
Private Const adOpenDynamic As Long = 2
Private Const adOpenStatic As Long = 3

Public Enum DBaseType
dbSQLServer = 1
dbAccess = 2
dbIBMDADB2 = 3
End Enum

 

[ongke0711]

Private Const adUseClient As Long = 3
Private Const adLockReadOnly As Long = 1
Private Const adStateOpen As Long = 1
Private Const adCmdStoredProc As Long = 4
Private Const adParamOutput As Long = 2
Private Const adOpenDynamic As Long = 2
Private Const adOpenStatic As Long = 3

Public Enum DBaseType
dbSQLServer = 1
dbAccess = 2
dbIBMDADB2 = 3
End Enum

Code gốc của tôi dùng khai báo muộn (Late binding) với thư viện ADODB, nên nếu dùng tên hằng như:
rs1.Open select_query, cn , adOpenKeySet, adLockOptimistic
sẽ báo lỗi ngay vì VBA nó không hiểu các tham số đó là gì nếu chưa khai báo nó.

Nếu truyền tham số trong câu lệnh bằng các con số thì sẽ không báo lỗi nhưng không phải ai cũng nhớ nó là tham số gì. Người ta hay gọi là Magic number.

rs1.Open select_query, cn, 1, 3

Do đó tôi khai báo các hằng trên để có thể dùng cái tên nó luôn trong câu lệnh đối với Late binding.

Còn phần khai báo: DBType là vì tôi dùng bộ code này để kết nối với 2 loại CSDL khác là Access và IBMDB. Khi kết nối với CSDL nào thì cung cấp tham số này để code nó tạo chuỗi kết nối phù hợp với loại CSDL đó.

Tài liệu tham khảo: đính kèm bên dưới.

 

[hvnhpro]

Code gốc của tôi dùng khai báo muộn (Late binding) với thư viện ADODB, nên nếu dùng tên hằng như:
rs1.Open select_query, cn , adOpenKeySet, adLockOptimistic
sẽ báo lỗi ngay vì VBA nó không hiểu các tham số đó là gì nếu chưa khai báo nó.

Nếu truyền tham số trong câu lệnh bằng các con số thì sẽ không báo lỗi nhưng không phải ai cũng nhớ nó là tham số gì. Người ta hay gọi là Magic number.

rs1.Open select_query, cn, 1, 3

Do đó tôi khai báo các hằng trên để có thể dùng cái tên nó luôn trong câu lệnh đối với Late binding.

Còn phần khai báo: DBType là vì tôi dùng bộ code này để kết nối với 2 loại CSDL khác là Access và IBMDB. Khi kết nối với CSDL nào thì cung cấp tham số này để code nó tạo chuỗi kết nối phù hợp với loại CSDL đó.

Tài liệu tham khảo: đính kèm bên dưới.

lần thứ n cảm ơn Bác.
Có tài liệu đọc sẽ hiểu gốc vấn đề hơn học mót.

 

[quang0308]

Bạn xem file đính kèm.
- Mở SQL Server và chạy lần lượt 3 cái SQL script để tạo: TestDB, tblRawData và cái stored procedure Bulk insert.
- Chạy file Excel và nhập các thông số kết nối tới máy chủ SQL Server của bạn.

Bổ sung: Tùy phiên bản và thiết lập SQL Server của bạn mà thay đổi Connection String cho phù hợp nhé.

mConnectionString = "Network Library=DBMSSOCN;" & _
                    "PROVIDER=MSOLEDBSQL;DATA SOURCE=" & mServerName & _
                    ";INITIAL CATALOG=" & mDatabaseName & _
                    ";User Id=" & mUserName & ";Password=" & mPassword & ";"

mConnectionString = "Driver={SQL Server};" & _
                    "Server=" & mServerName & _
                    ";Database=" & mDatabaseName & _
                    ";Uid=" & mUserName & _
                    ";Pwd=" & mPassword & ";"
                    '";DataTypeCompatibility=80;"    'dung cho SQL SV 2019 với các datatype moi'

cảm ơn bạn rất nhiều.

 

[befaint]

khai báo mấy biến chung này

Cái đó gọi là enum (value of Constant).
Khi dùng kết nối sớm thì dùng Constant (cũng có thể dùng value). Lúc gõ phương thức/ thuộc tính môi trường lập trình đưa sẵn gợi ý constant để lựa chọn.

Khi dùng kết nối muộn, object chỉ chấp nhận dùng enum. Và lập trình tường minh, rõ ràng thì phải khai báo các trị đó rõ ràng dạng hằng, để dễ nhận biết, quản trị.
Muốn biết enum của Constant tương ứng là gì thì gõ vào Google là thấy.