Nhưng có một số trường hợp tôi có thể sử dụng cách tiếp cận tự động hơn; . Do đó, thỉnh thoảng tôi sẽ tạo một macro Excel sẽ nhập dữ liệu từ bảng tính vào cơ sở dữ liệu
thiết lậpMẹo. Điều này giả định rằng dữ liệu của bạn không ở định dạng tab xoay/chéo. Nếu đúng như vậy, hãy xem hướng dẫn của tôi về cách chuyển đổi định dạng sao cho dữ liệu thân thiện với việc nhập cơ sở dữ liệu
Bắt đầu với việc tạo Macro Excel. Từ tab “Nhà phát triển” trong dải băng [có thể không có ở đó theo mặc định; bạn có thể thêm nó vào từ menu Tùy chọn], nhấp vào nút “Xem mã”
Nếu bạn đang sử dụng máy Windows, phím tắt là Alt + F11
Từ menu trên cùng. Chèn >> Mô-đun
Trong ngăn mới [nơi bạn có thể nhập mã], hãy tạo một hàm công khai
Public Function ImportData[]End
Mẹo. nếu bạn gõ “public function ImportData” và nhấn ‘Enter’ thì phần còn lại sẽ điền trước; . e. nó sẽ thêm dấu ngoặc đơn đóng và từ khóa 'Kết thúc'
Đảm bảo lưu tệp của bạn với một. xlsm, vì đây sẽ là sổ làm việc hỗ trợ macro [không chỉ là sổ làm việc “thông thường”]
Khái niệm tổng quan
Chúng tôi sẽ chạy một câu lệnh INSERT, trong đó SQL tuân theo định dạng này
INSERT INTO myTableName
[field1, field2]
VALUES
[row1Value1, row1Value2],
[row2Value1, row2Value2]Khá tự giải thích. Chúng tôi sẽ chia nó thành hai phần
- Tiêu đề SQL [CHÈN VÀO myTableName]
- Các “giá trị” được tạo động là sự kết hợp của các hàng dữ liệu riêng lẻ trong bảng tính
Đầu tiên chúng ta cần tạo một chuỗi kết nối. Tôi sẽ để lại giá trị chính xác cho bạn. Tất nhiên, bạn sẽ phải chắc chắn rằng người dùng có quyền bảo mật cơ sở dữ liệu thích hợp. Ví dụ bên dưới đang sử dụng Active Directory — vì vậy nó đang phát hiện thông tin đăng nhập Windows của tôi và không sử dụng tài khoản người dùng SQL cục bộ
Tôi đang kết nối qua ODBC. Nếu bạn không có cái này trên máy của mình, hãy tải xuống trình điều khiển
Với trường hợp sử dụng của tôi, chuỗi kết nối của tôi trông như thế này
Const strDbConn As String = "server=myServerName;Database=myDatabaseName;Trusted_Connection=Yes;Driver={ODBC Driver 17 for SQL Server}"Tôi đã tạo biến này dưới dạng một biến toàn cục, được đặt phía trên phần khai báo của hàm mà chúng ta vừa tạo
Là một biến toàn cục, chúng ta có thể sử dụng nó trong các chức năng khác
Tôi có một chức năng đơn giản để thực thi SQL
Lỗi ADODB?
Mã trong bài đăng này hoạt động với tôi mà không cần thêm tham chiếu bên ngoài. Nhưng nếu bạn gặp lỗi…
Trước đây- có lẽ đó là các phiên bản Excel trước- Tôi cần thêm vào một tài liệu tham khảo để sử dụng mã ADODB
Bạn có thể làm điều này bằng cách vào Tools >> References
Theo bài viết này, bạn cần đánh dấu vào tùy chọn Microsoft ActiveX Data Objects [x. x] Thư viện
Chèn dữ liệu bằng SQL. tiêu đề
Chúng ta cần tạo phần đầu của câu lệnh SQL, phần này sẽ là tĩnh [i. e. không thay đổi]
Tạo một hàm trả về kết quả được mã hóa cứng cho phần đầu tiên của câu lệnh INSERT cho các tên trường và bảng đã cho
Tất nhiên, bạn sẽ phải cập nhật nó để phản ánh tên bảng và các trường của bạn
Chèn dữ liệu bằng SQL. dữ liệu thực tế
Chúng tôi cần một cách để tạo động các giá trị sẽ được chèn vào cơ sở dữ liệu. Trước khi tạo SQL, chúng ta cần một cách để lặp qua các hàng trong Excel, từ trên xuống dưới. Bước đầu tiên trong đó là xác định chúng ta có bao nhiêu hàng
Tôi đang sử dụng hàm dưới đây để lấy số hàng trong trang tính
Tôi đang khai báo nó là loại Dài vì Số nguyên không đủ lớn cho tập dữ liệu của tôi. Một số nguyên được giới hạn ở ~32.000 hàng
Khi chúng ta lặp qua từng hàng, chúng ta sẽ tạo một chuỗi lấy các giá trị của các cột riêng lẻ và thêm nó vào câu lệnh SQL. Mã cho điều đó là ở đây
Tất nhiên, bạn sẽ phải căn chỉnh các chữ cái trong cột của mình với đúng tên trường và cập nhật các loại dữ liệu. Ví dụ: các giá trị số không được đặt trong dấu ngoặc kép
Tôi có hai chức năng "trợ giúp";
Nếu giá trị dựa trên văn bản, chúng ta cần thay thế [“escape”] một dấu nháy đơn, vì vậy tôi có chức năng này
Tôi cũng đã tạo một hàm nz[] [như trong “null-zero” được tích hợp sẵn cho một số ngôn ngữ lập trình] hàm này sẽ thay thế các giá trị số bằng “0” nếu cần
Đây là mã để lặp qua từng hàng trong bảng tính và tạo câu lệnh SQL
Sự cố từng bước là
- Gọi một hàm chuyển vào tên của trang tính [tab] có dữ liệu để tìm xem có bao nhiêu hàng trong tập dữ liệu
- Tạo một biến chứa phần đầu của câu lệnh SQL
- Lặp qua từng hàng trong tập dữ liệu, tạo câu lệnh SQL INSERT … VALUES
- Sau mỗi hàng dữ liệu, hãy thêm dấu phẩy
- Bỏ dấu phẩy cuối cùng trong câu lệnh
- Chạy SQL
Tôi đã sử dụng phương pháp thêm dấu phẩy sau mỗi hàng và sau đó chỉ cần xóa dấu phẩy cuối cùng trong chuỗi. Tôi đang sử dụng chức năng này
Cách khắc phục giới hạn nhập dữ liệu
Tôi gặp vấn đề, đôi khi các “giá trị” được nối quá dài và khiến Excel bị lỗi. Tôi hình dung rằng tôi nên giới hạn tập lệnh nhập trong 500 hàng cùng một lúc
Đây là mã được cập nhật
Bạn có thể thấy rằng [trên dòng 24], khi biến được tạo để lưu trữ các giá trị, khi nó đạt đến 500 ký tự, nó sẽ chèn dữ liệu - mà chúng ta có cho đến nay - vào cơ sở dữ liệu. Sau đó, sau vòng lặp, nó sẽ chèn lô 'cuối cùng' [hàng 39]
Tôi đã để lại một số mã trong đó, đã nhận xét, thông báo cho người dùng về tiến độ của quá trình nhập
Chỉnh sửa. trong mã cuối cùng, có một kiểm tra khác để chỉ chạy lô mã cuối cùng nếu cần. Nếu bạn đang cố gắng nhập một số hàng chia hết cho 500, bạn sẽ gặp lỗi. Nó ở dòng 40 trong “toàn bộ mã”
Đặt nó lại với nhau
Để làm cho điều này trở nên hữu ích hơn nữa, trước tiên chúng tôi có thể cần xóa mọi dữ liệu hiện có
Mặc dù bạn có thể lấy được "ngày cập nhật cuối cùng" từ tập dữ liệu, mã ví dụ này hiển thị một câu lệnh SQL bổ sung để chạy truy vấn CẬP NHẬT để cho biết ngày nhập
Tôi đã tạo một chương trình con gọi là autoImport để thực hiện việc này
- Xóa dữ liệu hiện có
- Chạy chức năng nhập dữ liệu
- Chạy truy vấn cập nhật để cho biết thời điểm dữ liệu được cập nhật
Thưởng. xóa các giá trị trống bằng Mã VBA
Tôi đã viết một bài viết riêng về cách bạn có thể tăng tốc quá trình nhập. loại bỏ một số hàng không thêm giá trị [nghĩa đen và nghĩa bóng]. Điều này có khả năng làm giảm đáng kể số lượng hàng, do đó giảm thời gian xử lý
Trong trường hợp sử dụng cụ thể của tôi, tôi có một bảng mà tôi chạy các truy vấn tổng hợp trên một trường duy nhất. Nếu trường đó có giá trị là “không” thì tôi không cần nhập nó vào