Trình điều khiển CData ODBC cho MySQL sử dụng giao diện ODBC tiêu chuẩn để liên kết dữ liệu MySQL với các ứng dụng như Microsoft Access và Excel. Làm theo các bước bên dưới để sử dụng Microsoft Query nhằm nhập dữ liệu MySQL vào bảng tính và cung cấp giá trị cho truy vấn được tham số hóa từ các ô trong bảng tính
Nếu bạn chưa có, trước tiên hãy chỉ định thuộc tính kết nối trong ODBC DSN [tên nguồn dữ liệu]. Đây là bước cuối cùng của quá trình cài đặt trình điều khiển. Bạn có thể sử dụng Quản trị viên nguồn dữ liệu ODBC của Microsoft để tạo và định cấu hình DSN ODBC
Thuộc tính Máy chủ và Cổng phải được đặt thành máy chủ MySQL. Nếu IntegratedSecurity được đặt thành false, thì Người dùng và Mật khẩu phải được đặt thành thông tin xác thực người dùng hợp lệ. Tùy chọn, Cơ sở dữ liệu có thể được đặt để kết nối với cơ sở dữ liệu cụ thể. Nếu không được đặt, các bảng từ tất cả các cơ sở dữ liệu sẽ được trả về
Sau đó, bạn có thể làm việc với dữ liệu MySQL trực tiếp trong Excel
- Trong Excel, mở tab Dữ liệu và chọn Từ các nguồn khác -> Từ Microsoft Query.
Chỉnh sửa dữ liệu MySQL trong Excel
MySQL cho Excel cho phép bạn tải và chỉnh sửa dữ liệu MySQL trực tiếp từ Microsoft Excel hoặc bạn có thể thực hiện thủ công bằng cách nhấn Cam kết thay đổi
Ví dụ bên dưới sử dụng bảng vị trí của cơ sở dữ liệu nhân viên ví dụ, nhưng màn hình sẽ trông giống nhau đối với bất kỳ bảng nào. Trong MySQL dành cho Excel, Mở kết nối MySQL, nhấp vào lược đồ nhân viên, Tiếp theo, chọn vị trí bảng, nhấp vào Chỉnh sửa dữ liệu MySQL, sau đó chọn Nhập để nhập dữ liệu vào trang tính Microsoft Excel mới để chỉnh sửa
Dưới đây là hướng dẫn từng bước để chỉnh sửa và cam kết dữ liệu
Bước 1
Tải Microsoft Office Excel 7
Bước 2
Bấm vào Data Tab, xem hình trên, biểu tượng Cơ sở dữ liệu "MySQL for Excel" sẽ xuất hiện như bên dưới
Bước 3
Nhấp vào biểu tượng Cơ sở dữ liệu "MySQL cho Excel". Nó mở ra một thanh bên Excel mới với các tùy chọn MySQL cho Excel có sẵn. Thanh điều hướng với biểu tượng MySQL for Excel được hiển thị trong hình dưới đây
Ở đây Cơ sở dữ liệu của chúng tôi là nhân viên và chúng tôi đang làm việc với bảng vị trí, nhưng màn hình sẽ giống nhau đối với bất kỳ bảng nào
Bước 4
Trong thanh bên MySQL cho Excel, mở kết nối MySQL bằng cách nhấp đúp. Ở đây kết nối của chúng ta là Local instance MySQL5. 6 và với màn hình sau sẽ xuất hiện để chấp nhận mật khẩu
Bước 5
Nhập mật khẩu để kết nối với máy chủ MySQL. Cơ sở dữ liệu sẽ hiển thị trong thanh bên MySQL cho Excel. Cơ sở dữ liệu của chúng tôi là nhân viên. Xem hình sau
Bước 6
Nhấp đúp vào cơ sở dữ liệu mong muốn của bạn và các bảng trong cơ sở dữ liệu sẽ hiển thị. bảng của chúng tôi là vị trí. Xem hình sau
Bước 7
Chọn bảng mà bạn muốn chỉnh sửa, nhấp vào "Chỉnh sửa dữ liệu MySQL" bên trong thanh điều hướng như hình trên và xem ảnh chụp màn hình sau
Bước 8
Nhấp vào nút Nhập như được đề cập trong hình trên và xem ảnh chụp màn hình sau. Dữ liệu của bảng đã chọn sẽ xuất hiện và nếu bạn đặt con trỏ vào phạm vi dữ liệu, nút Hoàn nguyên Dữ liệu và Cam kết Thay đổi [được chỉ định bởi hình chữ nhật màu đỏ] sẽ xuất hiện, nếu không thì không
Bước 9
Ở đây tại bảng của chúng tôi, ID cột đầu tiên là khóa chính. Nếu chúng ta thay đổi giá trị của ID trong A19 ô 17 thay vì 18 và nhấn phím enter, màu của ô này sẽ là màu xanh lá cây. Bây giờ chúng tôi đã thay đổi giá trị của ô F16 là London và nhấn phím enter, màu ô sẽ chuyển thành màu xanh lá cây và màu vàng cuối cùng, cho biết các ô chấp nhận dữ liệu mới. Dữ liệu được nhập vào đây được chèn vào bảng MySQL
Bước 10
Bây giờ nếu chúng ta nhấp vào nút Hoàn nguyên dữ liệu, hãy xem ảnh chụp màn hình sau
Ở đây trong hình trên nếu bạn nhấp vào Làm mới dữ liệu từ DB, dữ liệu sẽ được làm mới và hiển thị dữ liệu gốc từ DB và nếu bạn nhấp vào Hoàn nguyên dữ liệu đã thay đổi, bạn sẽ mất các thay đổi vừa thực hiện. Trong giai đoạn này, nếu bạn nhấp vào nút Cam kết thay đổi, hãy xem ảnh chụp màn hình sau
Ở hình trên, bạn đang xem một thông báo lỗi và màu của ô A19 thay đổi từ xanh sang đỏ, đó là do cột ID là khóa chính và tính duy nhất đã bị vi phạm ở đây. Bây giờ chúng tôi đã trả lại giá trị của ô A19 về giá trị ban đầu của nó, tôi. e. 18 và nhấp vào nút Cam kết thay đổi, và bây giờ hãy xem ảnh chụp màn hình bên dưới
Trong hình trên cho thấy màu của ô F16 đã chuyển từ xanh lục sang xanh lam, điều đó có nghĩa là Cam kết thay đổi được thực hiện thành công. Bây giờ bạn có thể đóng cửa sổ Excel lưu hoặc không nhưng bảng cơ sở dữ liệu đã được cập nhật. Bạn có thể xem để thực hiện lại bước. Nếu bạn đã chọn hộp kiểm Tự động Cam kết, hiệu ứng sẽ ngay lập tức được nhìn thấy trong trang tính của bạn và dữ liệu sẽ được cập nhật trong cơ sở dữ liệu MySQL
Nhập dữ liệu MySQL vào Excel
Dữ liệu có thể được nhập từ MySQL vào bảng tính Microsoft Excel bằng cách sử dụng tùy chọn Nhập dữ liệu MySQL sau khi chọn bảng, dạng xem hoặc quy trình để nhập
Trước hết, bạn thực hiện 6 bước đầu tiên được mô tả ở trên trong "Chỉnh sửa dữ liệu MySQL trong Excel", sau đó chọn bảng bạn muốn nhập. Ở đây bảng của chúng tôi là vị trí. Vì vậy, hãy chọn bảng vị trí và sau đó nhấp vào "Nhập dữ liệu MySQL" và xem ảnh chụp màn hình xuất hiện ở đây trong phần bên dưới
Chọn cột để nhập
Theo mặc định, tất cả các cột được chọn và sẽ được nhập. Các cột cụ thể có thể được chọn [hoặc không được chọn] bằng cách sử dụng phương pháp tiêu chuẩn của Microsoft Windows là Điều khiển + Nhấp chuột để chọn các cột riêng lẻ hoặc Shift + Nhấp chuột để chọn một dải cột
Màu nền trắng cho biết cột hoặc các cột đã được chọn và chúng sẵn sàng được nhập, mặt khác, màu xám cho biết cột chưa được chọn và cột sẽ không được nhập
Nhấp chuột phải vào bất kỳ đâu trong lưới xem trước sẽ mở menu ngữ cảnh với tùy chọn Chọn Không có hoặc Chọn Tất cả, tùy thuộc vào trạng thái hiện tại
Nhập bảng
Bao gồm tên cột làm tiêu đề. Theo mặc định, tùy chọn này được bật và tùy chọn này coi tên cột ở đầu bảng tính Microsoft Excel là hàng "tiêu đề" và sẽ được chèn làm tiêu đề
Giới hạn và Hàng và Bắt đầu với Hàng. Theo mặc định, tùy chọn này bị tắt nếu được bật, điều này sẽ giới hạn phạm vi dữ liệu được nhập. Tùy chọn Giới hạn đối với mặc định là 1 và giới hạn này có thể được thay đổi bằng cách xác định số lượng hàng cần nhập. Tùy chọn Bắt đầu với Hàng mặc định là 1, i. e. bắt đầu từ hàng đầu tiên và nó có thể được thay đổi bằng cách xác định số từ nơi bắt đầu nhập. Mỗi tùy chọn có giá trị tối đa là COUNT[hàng] trong bảng
Bây giờ, giả sử rằng chúng tôi muốn nhập các cột LOATION_ID và CITY. Nhấp chuột vào cột LOCATION_ID, sau đó nhấn và giữ phím CTRL và nhấp vào cột THÀNH PHỐ, và xem ảnh chụp màn hình sau
Bây giờ nếu chúng ta nhấp vào nút Nhập, tất cả các hàng cho hai cột này sẽ được nhập trong Trang tính Microsoft Excel
Giả sử rằng, chúng tôi chỉ muốn nhập 6 hàng bắt đầu từ hàng thứ 3. Bây giờ hãy xem ảnh chụp màn hình sau
Ở đây trong hình trên cho thấy tất cả các cột đã được chọn và giá trị của Giới hạn là 6, điều đó có nghĩa là một số 6 hàng sẽ được nhập và bắt đầu nhập sẽ bắt đầu từ hàng thứ 3 vì chúng tôi đã đặt giá trị của Bắt đầu bằng . Bây giờ hãy nhấp vào nút Nhập và xem ảnh chụp màn hình sau
Nối dữ liệu Excel vào MySQL
Dữ liệu bảng tính Microsoft Excel có thể được thêm vào bảng cơ sở dữ liệu MySQL bằng cách sử dụng tùy chọn Nối dữ liệu Excel vào bảng
ánh xạ cột
Việc ánh xạ các cột Excel tới các cột MySQL có thể được thực thi tự động [mặc định], thủ công hoặc bằng cách sử dụng quy trình ánh xạ được lưu trữ. Để nối dữ liệu từ Excel vào MySQL, cần thực hiện theo bước sau
Lúc đầu, hãy thực hiện 6 bước trên của "Chỉnh sửa dữ liệu MySQL trong Excel", sau đó nhập các bản ghi trong bảng tính excel tương thích với cấu trúc của bảng MySQL mà bạn muốn nối thêm các bản ghi. Đây là ví dụ chúng tôi chỉ lấy một hàng, bạn có thể lấy nhiều hàng rồi chọn các bản ghi. Xem ảnh chụp màn hình sau
Bây giờ hãy nhấp vào Nối dữ liệu Excel vào bảng như đã đề cập ở trên và xem màn hình như
Ở đây trong hình trên, bạn đang tìm kiếm hai màu cho chú giải. Một là Đỏ và một là Xanh
Màu Xanh lục cho biết cột nguồn được ánh xạ [ ánh xạ có nghĩa là các cột nguồn trong lưới phía trên được tạo trong bảng tính đã khớp với cấu trúc của bảng MySQL dưới dạng các cột mục tiêu được hiển thị trong lưới bên dưới] với cột mục tiêu và ở đây trong hình trên
Nếu bạn kéo bất kỳ tiêu đề cột mục tiêu nào được hỗ trợ bởi màu xanh lá cây và để nó ở bên ngoài lưới, màu của cột mục tiêu sẽ là Đỏ và màu của cột nguồn sẽ là Xám. Xem trong hình bên dưới
Ở đây trong hình trên, cột mục tiêu 4 trở thành Màu đỏ và cột nguồn 4 trở thành Màu xám
Màu Đỏ cho biết cột mục tiêu không được ánh xạ và màu Xám cho biết cột nguồn không được ánh xạ
Cần lưu ý rằng, một cột nguồn có thể được ánh xạ tới nhiều cột mục tiêu, mặc dù hành động này tạo ra hộp thoại cảnh báo và việc nhấp chuột phải vào cột mục tiêu sẽ hiển thị menu có các tùy chọn Xóa bản đồ cột cho một cột hoặc để Xóa
Giả sử, chúng tôi đang ánh xạ thủ công bằng cách kéo cột nguồn4 với cột đích4 và cột5. Cột mục tiêu 4 sẽ được ánh xạ đơn giản nhưng khi thực hiện quy trình cho cột mục tiêu 5, hộp thoại cảnh báo sẽ xuất hiện, hiển thị bên dưới
phương pháp lập bản đồ
Dưới đây là ba phương pháp lập bản đồ
Phương pháp ánh xạ tự động cố gắng khớp tên cột nguồn Excel với tên cột bảng mục tiêu MySQL
Trong phương pháp ánh xạ thủ công, tên cột nguồn được kéo theo cách thủ công với tên cột đích. Sau khi phương pháp Tự động được chọn, phương pháp kéo Thủ công cũng có thể được thực hiện
Bạn có thể lưu trữ các kiểu ánh xạ của riêng mình bằng nút Lưu trữ ánh xạ và lưu một tên như "tên [tên db. tablename]" và nó sẽ có sẵn trong hộp tổ hợp Mapping Method
Ánh xạ được lưu trữ có thể bị xóa hoặc đổi tên trong hộp thoại Tùy chọn nâng cao
Tùy chọn nâng cao
Nếu chúng ta nhấp vào nút Tùy chọn nâng cao, hộp thoại trông giống như
Thực hiện ánh xạ tự động khi hộp thoại mở ra. Theo các tùy chọn này, nó sẽ tự động ánh xạ mục tiêu và nguồn khi hộp thoại Nối dữ liệu được mở
Tự động lưu trữ ánh xạ cột cho bảng đã cho. Để kiểm tra tùy chọn này, hãy lưu trữ từng quy trình ánh xạ sau khi thực hiện thao tác Nối thêm. Quy trình ánh xạ được lưu bằng cách sử dụng "bảng tên bảng [dbname. tên bảng]" định dạng. Nó có thể được thực hiện thủ công bằng cách sử dụng nút Ánh xạ cửa hàng
Tự động tải lại ánh xạ cột được lưu trữ cho bảng đã chọn. Nếu tồn tại một thói quen ánh xạ được lưu trữ khớp với tất cả các tên cột trong lưới nguồn với lưới đích, thì nó sẽ tự động được tải
Tùy chọn dữ liệu trường nâng cao
Sử dụng 100 hàng dữ liệu Excel [mặc định] đầu tiên để xem trước và tính toán các loại dữ liệu. Điều này xác định số hàng mà bản xem trước hiển thị và các giá trị ảnh hưởng đến tính năng ánh xạ tự động
Khi bạn kiểm tra Sử dụng các giá trị được định dạng, dữ liệu từ Excel được coi là Văn bản, Đôi hoặc Ngày. Điều này được kích hoạt theo mặc định. Nếu chúng tôi tắt tính năng này, thì dữ liệu hiện có sẽ không bao giờ được coi là loại Ngày, vì vậy, chẳng hạn, điều này có nghĩa là một ngày có thể được biểu thị dưới dạng số
Ánh xạ cột được lưu trữ là danh sách các ánh xạ cột đã lưu được lưu bằng tính năng "Tự động lưu trữ ánh xạ cột cho bảng đã cho" hoặc theo cách thủ công với tùy chọn Ánh xạ cửa hàng
Sau khi hoàn thành tất cả các bước nếu chúng ta nhấp vào nút Nối, màn hình sau sẽ xuất hiện
Xuất dữ liệu Excel vào MySQL
Dữ liệu của bảng tính Microsoft Excel có thể được xuất sang bảng cơ sở dữ liệu MySQL mới bằng cách sử dụng tùy chọn Xuất dữ liệu Excel sang bảng mới. Đầu tiên các bạn thực hiện 6 bước đầu tiên bên trên mô tả trong Chỉnh sửa dữ liệu MySQL trong Excel. Sau đó, nhập một số bản ghi có tiêu đề như bạn muốn vào bảng tính Excel của bạn và chọn bản ghi. Bây giờ hãy xem các bản ghi mẫu trong hình sau -
Bây giờ hãy nhấp vào Xuất dữ liệu Excel sang Bảng mới được đề cập bằng mũi tên trong hình trên và nhìn vào màn hình sau
1 - Chỉ định một tên duy nhất cho bảng MySQL. Giả sử tên của bảng MySQL là "hóa đơn"
2 - Nếu nút radio Thêm cột khóa chính được chọn, một cột khóa chính bổ sung sẽ được thêm vào, bạn có thể thay đổi tên của cột và theo mặc định, kiểu dữ liệu sẽ là một số nguyên được hiển thị trong 7
3 - Khi bạn chọn nút radio Sử dụng cột hiện có, tên cột sẽ có sẵn trong hộp tổ hợp và bạn có thể chọn cột khóa chính từ danh sách và cột sẽ được chọn. Giả sử bạn chọn cột INVOICE_NO, hãy xem hình sau
Ở hình trên, cột INVOICE_NO đã được chọn và bây giờ bạn có thể thay đổi Tên cột [điểm 6], Loại dữ liệu [điểm 7] và ràng buộc [điểm 9] đang hoạt động
4 - Nếu bạn chọn hộp kiểm Hàng đầu tiên chứa tên cột [điểm 4] thì cột đầu tiên trong dữ liệu excel của bạn sẽ là tiêu đề nếu không nó sẽ là cột1,cột2,. vân vân
5 - Giả sử bạn thay đổi Kiểu dữ liệu và tên cột cho các cột khác, bạn có thể nhấp vào tiêu đề cột. Giả sử chúng ta muốn thay đổi Data type của INVOICE_DT, click vào INVOICE_DT, và cột này sẽ được chọn và sau đó bạn có thể sử dụng điểm 6,7 và 9
6 - Bạn có thể thay đổi Tên Cột
7 - Bạn có thể thay đổi Loại dữ liệu
8 - Tùy chọn nâng cao hiển thị hộp thoại sau
Tùy chọn nâng cao
Sử dụng 100 hàng dữ liệu Excel [mặc định] đầu tiên để xem trước và tính toán các loại dữ liệu. Tùy chọn này xác định số hàng mà bản xem trước hiển thị và các giá trị được chỉ định ảnh hưởng đến tính năng ánh xạ tự động
Phân tích và cố gắng phát hiện loại dữ liệu chính xác dựa trên nội dung trường cột. Tùy chọn này cố gắng phân tích dữ liệu và xác định kiểu dữ liệu cho cột. Khi cột chứa nhiều loại dữ liệu thì nó được xác định là VARCHAR
Thêm bộ đệm bổ sung vào độ dài VARCHAR [làm tròn tới 12, 25, 45, 125, 255]. Khi được bật, tính năng này sẽ tự động phát hiện loại dữ liệu và đặt thành VARCHAR, sau đó tính năng này sẽ tìm độ dài tối đa cho tất cả các hàng trong cột và làm tròn độ dài tối đa đó thành một trong các độ dài đã xác định ở trên và khi bị tắt, VARCHAR sẽ được làm tròn
Tự động kiểm tra hộp kiểm Chỉ mục cho các cột Số nguyên. Theo mặc định, tùy chọn này được bật và khi đó các cột có kiểu dữ liệu Số nguyên sẽ được bật tùy chọn Tạo chỉ mục theo mặc định
Tự động đánh dấu hộp kiểm Allow Empty cho các cột không có chỉ mục. Theo mặc định, tùy chọn này được bật và khi được bật, các cột không có hộp kiểm Tạo chỉ mục được chọn sẽ tự động bật tùy chọn Cho phép cấu hình trống
Sử dụng các giá trị được định dạng. Theo mặc định, tùy chọn này được bật và dữ liệu từ Excel được coi là Văn bản, Đôi hoặc Ngày nhưng khi tắt, dữ liệu là độc lập, nghĩa là chúng không có loại dữ liệu cụ thể
Xóa các cột không chứa dữ liệu, nếu không, hãy gắn cờ các cột đó là "Đã loại trừ". Khi bật tùy chọn này, các cột không có dữ liệu trong Excel sẽ bị xóa và không hiển thị trong bảng xem trước. Theo mặc định, tùy chọn này bị tắt và sau đó các cột này sẽ tồn tại nhưng đã chọn tùy chọn Loại trừ Cột
Làm cách nào để tích hợp MySQL với Excel?
Kết nối Excel với MySQL bằng Get & Transform [Power Query] .Bấm vào Dữ liệu trong Excel, sau đó mở rộng danh sách thả xuống Lấy dữ liệu. .Trong hộp thoại Từ ODBC, hãy chọn tên nguồn dữ liệu [DSN] của bạn. .Nếu bạn đang sử dụng tên người dùng hoặc mật khẩu cơ sở dữ liệu, hãy chọn Cơ sở dữ liệu và nhập thông tin đăng nhập của bạn vào hộp thoại quay số, sau đó nhấp vào Kết nốiBạn có thể sử dụng SQL và Excel cùng nhau không?
Microsoft Excel có thể là một công cụ mạnh mẽ để đánh giá dữ liệu SQL. Trong Excel, có thể tạo một kết nối để liên kết trực tiếp đến một cơ sở dữ liệu cụ thể được lọc theo yêu cầu của bạn . Điều này cho phép bạn báo cáo dữ liệu SQL, đính kèm bảng dữ liệu vào Excel, tạo bảng tổng hợp và thao tác tốt hơn với dữ liệu SQL của bạn.