Tự động nhập excel vào máy chủ sql

Trong bài viết này, bạn sẽ tìm hiểu 2 cách dễ dàng để thực hiện một trong những tác vụ quản lý dữ liệu hữu ích nhất. cách chèn dữ liệu từ excel vào SQL Server.   

Mục lục

• 1. Tiểu sử
• 2. Cách nhập dữ liệu từ Excel sang SQL Server – Phương pháp Copy và Paste
  • a. Hướng dẫn từng bước một
  • b. Cách chèn dữ liệu từ Excel sang SQL Server bằng cột nhận dạng
  • c. Sao chép và dán dữ liệu từ Excel sang Chế độ xem máy chủ SQL
  • d. Nhập Excel sang SQL Server trên máy từ xa
  • e. Mẹo khi sao chép và dán dữ liệu từ Excel sang máy chủ SQL
  • f. Hiệu suất Excel sang SQL Server
  • g. Sao chép và dán – tham khảo nhanh
• • 3. Cách nhập dữ liệu từ Excel sang SQL Server – Phương pháp SQL Spread
    • a. Cài đặt bổ trợ SQL Spread cho Excel
    • b. Kết nối với cơ sở dữ liệu của bạn và chèn dữ liệu từ Excel
    • c. Chèn hàng mới vào SQL Server
    • d. Cập nhật dữ liệu hiện có trong SQL Server
    • 4. Các công cụ và kỹ thuật khác
    • 5. Tóm tắt – chèn dữ liệu từ Excel vào SQL Server

Tiểu sử

Trước khi tôi thành lập SQL Spreads [một Phần bổ trợ Excel để Nhập và Cập nhật dữ liệu SQL Server từ bên trong Excel], tôi đã làm việc với tư cách là nhà tư vấn Business Intelligence trong nhiều năm bằng cách sử dụng các công cụ BI của Microsoft, chẳng hạn như SQL Server, SSIS, Reporting Services, Excel

Tôi nhận thấy rằng khi làm việc trong các dự án khác nhau, tôi có xu hướng nắm bắt được một số điều tuyệt vời cần biết mà tôi có thể sử dụng lại nhiều lần. Một trong những thứ mà tôi sử dụng lại trong hầu hết mọi dự án là khả năng sao chép và dán dữ liệu từ Excel vào bảng trong SQL Server

Đó là một cách thực sự đơn giản và thuận tiện để nhanh chóng nhập dữ liệu vào bảng thông qua SQL Server Management Studio. Ví dụ: điền vào bảng thứ nguyên mới, thêm một số dữ liệu thử nghiệm hoặc nhập bất kỳ dữ liệu nào khác mà bạn cần để nhanh chóng truy cập vào bảng trong SQL Server

Nhưng nếu bạn muốn chèn dữ liệu từ Excel sang SQL mà không dùng Management Studio thì sao? . Nó làm cho các tác vụ nhập từ Excel sang SQL Server của bạn dễ thực hiện hơn nhiều

Do đó, trong bài viết này, tôi sẽ giải thích cách chèn dữ liệu từ Excel sang SQL Server bằng 2 phương pháp đơn giản này

• Sao chép và dán từ bảng Excel sang SQL thông qua SQL Server Management Studio
• Chèn trực tiếp từ bảng Excel vào SQL bằng cách sử dụng SQL Spread

Cách nhập dữ liệu từ Excel sang SQL Server – Phương pháp Copy và Paste

Hướng dẫn từng bước một

1. Mở SQL Server Management Studio và kết nối với cơ sở dữ liệu SQL Server của bạn
2. Mở rộng các thư mục Cơ sở dữ liệu và Bảng cho bảng mà bạn muốn chèn dữ liệu của mình từ Excel
   
3. Nhấp chuột phải vào bảng và chọn tùy chọn thứ tư – Chỉnh sửa 200 hàng trên cùng
   
4. Dữ liệu sẽ được tải và bạn sẽ thấy 200 hàng dữ liệu đầu tiên trong bảng
   
5. Chuyển sang Excel và chọn các hàng và cột để chèn từ Excel sang SQL Server.
   Nhấp chuột phải vào các ô đã chọn và chọn Sao chép.
   
6. Quay lại SQL Server Management Studio và cuộn xuống hàng cuối cùng ở dưới cùng và tìm hàng có dấu sao ở cột ngoài cùng bên trái
7. Nhấp chuột phải vào ngôi sao trong tiêu đề cột và chọn Dán
   
8. Bây giờ bạn đã hoàn tất quá trình nhập SQL Server của mình và dữ liệu của bạn từ Excel hiện có trong một bảng trong SQL Server

Nhớ lại. Luôn bắt đầu với việc sao chép và dán một hàng dữ liệu từ Excel sang SQL Server. Điều này là để kiểm tra xem không có sự không khớp giữa dữ liệu của bạn từ Excel và bảng SQL Server [chẳng hạn như số lượng cột] và dữ liệu của bạn trong Excel có hợp lệ với các kiểu dữ liệu trong bảng SQL Server không. Xem phần “Mẹo và thủ thuật” bên dưới để biết thêm chi tiết

Cách chèn dữ liệu từ Excel sang SQL Server bằng cột nhận dạng

Kỹ thuật tương tự cũng có thể được sử dụng để sao chép và dán dữ liệu vào các bảng có cột ID tăng tự động [cột nhận dạng]

Điều cần lưu ý ở đây là cũng bao gồm thêm một cột trống ngoài cùng bên trái trong Excel khi sao chép dữ liệu từ Excel sang SQL Server

Làm theo các bước sau để sao chép và dán dữ liệu từ Excel sang SQL Server bằng bảng có cột ID tăng tự động

1. Mở SQL Server Management Studio và kết nối với cơ sở dữ liệu SQL Server của bạn
2. Mở rộng các thư mục Cơ sở dữ liệu và Bảng cho bảng mà bạn muốn dán dữ liệu Excel
   
3. Nhấp chuột phải vào tên bảng và chọn Chỉnh sửa 200 hàng trên cùng, tùy chọn thứ tư từ trên xuống
   
4. Thao tác này sẽ hiển thị lưới có 200 hàng dữ liệu đầu tiên trong bảng
   
5. Chuyển sang Excel và chọn các hàng và cột để sao chép. Không bao gồm hàng tiêu đề.
   Bây giờ, hãy nhớ bao gồm thêm một cột trống ngoài cùng bên trái trong lựa chọn của bạn.
   
   
   Sau đó, nhấp chuột phải vào các ô đã chọn và chọn Sao chép.
6. Chuyển về SQL Server Management Studio và chọn tab có 200 hàng từ bảng của bạn.
   Chuyển đến hàng cuối cùng ở dưới cùng và tìm hàng có dấu sao ở cột ngoài cùng bên trái.
   
7. Nhấp chuột phải vào ngôi sao và chọn Dán
   
8. Dữ liệu của bạn từ Excel hiện được dán vào bảng của bạn trong SQL Server và SQL Server sẽ tự động tạo các giá trị trong cột ID/khóa cho bạn
   

Sao chép và dán dữ liệu từ Excel sang Chế độ xem máy chủ SQL

Phương pháp sao chép và dán cũng hoạt động khi quá trình nhập Excel sang SQL Server của bạn là Chế độ xem thay vì Bảng. Yêu cầu duy nhất là Chế độ xem chỉ được chứa dữ liệu từ một bảng

Trong Dạng xem trong SQL Server có chứa dữ liệu từ một số bảng đã tham gia, bạn không thể chèn hàng mới, nhưng bạn có thể cập nhật dữ liệu, miễn là bạn chỉ cập nhật các cột bắt nguồn từ cùng một bảng cơ sở

Nhập Excel sang SQL Server trên máy từ xa

Khi làm việc với cơ sở dữ liệu SQL Server trên máy từ xa, nơi bạn kết nối với máy từ xa bằng Remote Desktop Connection, bạn vẫn có thể sử dụng kỹ thuật sao chép và dán tương tự để di chuyển dữ liệu từ Excel của máy cục bộ sang cơ sở dữ liệu SQL Server trên máy của bạn.

Nếu bạn không thể sao chép và dán dữ liệu vào Máy chủ SQL của mình khi được kết nối bằng Kết nối Máy tính Từ xa, trước tiên hãy kiểm tra xem sao chép và dán đã được bật cho Kết nối Máy tính Từ xa chưa

1. Mở kết nối máy tính từ xa
2. Nhấp vào tùy chọn Hiển thị…
   
3. Chọn tab Tài nguyên cục bộ, sau đó kiểm tra xem thuộc tính Clipboard đã được chọn chưa
   

Nếu bạn vẫn không thể sao chép và dán dữ liệu giữa Excel trên máy cục bộ và SQL Server trên máy chủ cơ sở dữ liệu từ xa, hãy xác minh với quản trị viên máy chủ của bạn rằng tính năng sao chép và dán đã được bật cho Kết nối Máy tính Từ xa trên máy chủ

Mẹo khi sao chép và dán dữ liệu từ Excel sang máy chủ SQL

Xác thực dữ liệu của bạn – bắt đầu với một hàng dữ liệu

Nếu dữ liệu mà bạn sao chép từ tài liệu Excel của mình không khớp với kiểu dữ liệu của các cột trong bảng SQL Server, việc chèn dữ liệu sẽ bị hủy và bạn sẽ nhận được thông báo cảnh báo. Điều này sẽ xảy ra với mọi hàng bạn dán từ Excel sang SQL Server. Nếu bạn dán 500 hàng từ Excel với số cột sai, bạn sẽ nhận được một thông báo cảnh báo cho mỗi và mọi hàng mà bạn dán

Để tránh điều này, mẹo là bắt đầu chỉ sao chép một hàng dữ liệu và dán nó vào bảng SQL Server. Nếu bạn nhận được thông báo cảnh báo về các loại dữ liệu không chính xác, bạn có thể sửa lỗi không khớp và lặp lại quy trình sao chép và dán cho đến khi tất cả các cột Excel của bạn vừa với bảng trong SQL Server. Khi tất cả các cột khớp nhau, hãy chọn các hàng còn lại và dán tất cả chúng vào bảng SQL Server trong một bước

Chèn giá trị NULL từ Excel vào bảng SQL Server

Khi bạn có các cột trong bảng SQL Server cho phép giá trị NULL và bạn muốn chèn giá trị NULL vào bảng, chỉ cần nhập văn bản NULL vào ô trong Excel, sau đó sao chép và dán dữ liệu từ Excel vào SQL Server

Các giá trị NULL sẽ được chèn vào bảng trong SQL Server

Bảng có cột được tính toán

Đối với các bảng SQL Server có chứa các cột được tính toán, bạn có thể dán dữ liệu từ Excel vào bảng đó chỉ bằng cách để trống dữ liệu cho cột được tính toán trong Excel, sau đó sao chép và dán dữ liệu từ Excel vào bảng SQL Server

Cách lấy tên cột từ bảng trong SQL Server sang Excel

Khi bạn chuẩn bị dữ liệu trong Excel để nhập vào bảng SQL Server hiện có, sẽ rất hữu ích khi có các tiêu đề cột và một vài hàng dữ liệu mẫu làm tham chiếu trong Excel

Có một kỹ thuật mà bạn có thể sao chép dữ liệu hiện có trong SQL Server sang Excel và bao gồm các tên cột của bảng làm tên tiêu đề

Làm theo các bước sau để bao gồm cả tên cột khi sao chép một vài hàng dữ liệu từ bảng SQL Server vào Excel

1. Trong SQL Server Management Studio, định vị cơ sở dữ liệu của bạn và mở rộng thư mục Bảng
2. Nhấp chuột phải vào tên bảng của bạn và chọn tùy chọn thứ ba – Chọn Top 1000 hàng
   
3. Chọn các hàng để sao chép sang Excel bằng cách giữ nút CTRL và nhấp vào số hàng ở bên trái
4. Khi các hàng của bạn được chọn, nhấp chuột phải vào một hàng và chọn tùy chọn Sao chép có tiêu đề
   
5. Chuyển đến Excel và dán dữ liệu vào một ô. Các tiêu đề từ bảng trong SQL Server giờ đây sẽ được thêm làm hàng đầu tiên
   

Hiệu suất Excel sang SQL Server

Sao chép và dán dữ liệu từ Excel sang SQL Server là một phương pháp thực sự đơn giản để nhập dữ liệu từ Excel vào cơ sở dữ liệu SQL Server của bạn. Một trong những nhược điểm là nó không phải là phương pháp nhanh nhất nếu bạn cần chèn lượng dữ liệu lớn hơn, chẳng hạn như hàng trăm nghìn hàng dữ liệu trở lên

Để tham khảo giới hạn hiệu suất, tôi đã chạy một số thử nghiệm trên máy i7 cục bộ của mình với 8 GB RAM có cài đặt Microsoft Excel và SQL Server trên cùng một máy

Tôi đã có kết quả sau. sao chép dữ liệu trong Excel với 10 cột kiểu dữ liệu hỗn hợp sang SQL Server mất khoảng 2 giây cho 100 hàng, khoảng 30 giây cho 1000 hàng và khoảng 10 phút cho 20.000 hàng

Vì vậy, tôi muốn nói rằng giới hạn sử dụng tính năng sao chép và dán là khoảng vài nghìn đến vài chục nghìn hàng dữ liệu. Nếu bạn cần thực hiện nhập Excel sang SQL Server với nhiều dữ liệu hơn thì bạn nên sử dụng Trình hướng dẫn Nhập và Xuất SQL Server .

Sao chép và dán – tham khảo nhanh

• Trước tiên, sao chép dữ liệu từ Excel, sau đó dán dữ liệu đó vào bảng SQL Server bằng cách sử dụng tùy chọn menu Cơ sở dữ liệu > Bảng > Chỉnh sửa 200 hàng trên cùng
• Luôn bắt đầu bằng cách sao chép và dán một hàng dữ liệu để xác thực các loại dữ liệu
• Đối với các bảng SQL Server có cột nhận dạng, hãy thêm một cột trống ngoài cùng bên trái trước khi sao chép từ Excel
• Sao chép và dán từ Excel sang SQL Server có thể được sử dụng để chèn tới vài chục nghìn hàng dữ liệu
• Để đưa tiêu đề cột SQL Server vào Excel, bấm chuột phải vào bảng trong SQL Server và chọn Sao chép bằng tiêu đề
• Đừng quên rằng kỹ thuật này cũng hoạt động hiệu quả trên Remote Desktop Connections

Cách nhập dữ liệu từ Excel sang SQL Server – Phương pháp SQL Spread

Phương pháp này lý tưởng cho những người dùng không muốn chèn dữ liệu bằng SQL Server Management Studio

Cài đặt bổ trợ SQL Spread cho Excel

Bạn có thể tải xuống bản dùng thử SQL Spreads miễn phí từ tại đây .

1. Chạy SQLSpreadsSetup. exe và làm theo hướng dẫn
2. Khởi động lại Excel và chấp nhận xác nhận Add-In
3. Bạn sẽ tìm thấy SQL Spreads trong menu tab trong Excel
   

Để biết thêm chi tiết về cài đặt, hãy xem phần Cài đặt SQL Spread trong Cơ sở kiến ​​thức của chúng tôi.

Kết nối với cơ sở dữ liệu SQL Server của bạn

Sau khi cài đặt SQL Spreads, bạn sẽ thấy rằng nó đã được thêm dưới dạng một tab dải băng mới

1. Nhấp vào SQL Spreads và sau đó nhấp vào nút Chế độ thiết kế
   
2. Trong bảng điều khiển SQL Spreads Designer ở bên phải, nhấp vào nút Chỉnh sửa để mở hộp thoại kết nối SQL Server
   
3. Nhập tên Máy chủ SQL của bạn vào trường tên Máy chủ SQL
   
4. Chọn xem bạn nên kết nối bằng thông tin đăng nhập Windows [Xác thực Windows] hay nhập tên người dùng và mật khẩu [Xác thực máy chủ SQL]. Xác thực Windows an toàn hơn trong hai tùy chọn
   
5. Nhấp vào OK. SQL Spreads sẽ cố gắng kết nối với cơ sở dữ liệu. Nếu kết nối thành công, cơ sở dữ liệu của bạn sẽ hiển thị trong bảng điều khiển SQL Spreads Designer
   
6. Bây giờ chúng ta đã tạo kết nối từ Excel đến SQL Server, chúng ta có thể chọn bảng dữ liệu nào chúng ta muốn sử dụng trong Excel. Trong Trình thiết kế trải rộng SQL, bấm vào cơ sở dữ liệu rồi chọn bảng của bạn
   

Ngay khi bạn chọn một bảng, dữ liệu trong bảng sẽ được điền vào trang tính Excel. Giờ đây, bạn có thể xem tất cả dữ liệu trong bảng SQL Server của mình và sử dụng dữ liệu đó trong sổ làm việc Excel của mình. Sức mạnh thực sự của SQL Spreads là khả năng cập nhật hoặc thêm vào dữ liệu trong SQL Server trực tiếp từ Excel

Chèn hàng mới vào SQL Server

Để nhập dữ liệu mới vào SQL Server, hãy cuộn xuống hàng trống đầu tiên và nhập dữ liệu mới của bạn hoặc dán một tập hợp các hàng được sao chép từ một sổ làm việc Excel khác

Khi bạn đã thêm hoặc dán các hàng mới, hãy nhấp vào nút 'Lưu vào Cơ sở dữ liệu' để nhận các thay đổi được ghi vào bảng trong SQL Server

Cập nhật dữ liệu hiện có trong SQL Server

Bạn cũng có thể cập nhật giá trực tiếp trong bảng sản phẩm trong Excel và lưu các thay đổi trở lại SQL Server. Để thực hiện việc này, bạn chỉ cần thực hiện các chỉnh sửa trong bảng trong Excel, sau đó nhấp vào nút ‘Lưu vào Cơ sở dữ liệu’ để ghi các thay đổi vào bảng trong SQL Server

Các công cụ và kỹ thuật khác

Có một số cách khác để nhập tệp Excel vào bảng trong SQL Server. Dưới đây là một số phương pháp khác

• SQL Server Import Wizard – một công cụ nhập dựa trên thuật sĩ bên trong SQL Server Management Studio. Lý tưởng cho việc nhập một lần khi bạn có tài liệu Excel cần nhập vào bảng trong SQL Server. Ưu điểm bao gồm tính linh hoạt và nhiều cài đặt để tinh chỉnh quá trình nhập. Hạn chế lớn nhất là bạn cần chạy qua hàng chục hộp thoại Trình hướng dẫn với nhiều cài đặt mỗi khi bạn cần nhập dữ liệu. Thông tin thêm về Trình hướng dẫn nhập SQL Server có sẵn tại đây .
• SSIS – đây là tàu chở dầu để di chuyển dữ liệu giữa các nguồn khác nhau. Bạn có thể thực hiện hầu hết mọi nhiệm vụ mà mình thích, nhưng bạn sẽ cần dành nhiều thời gian để bắt đầu và sẽ còn mất nhiều thời gian hơn để duy trì và thay đổi giải pháp sau này. Ưu điểm bao gồm tính linh hoạt tốt và nhiều tính năng có sẵn; . Thông tin thêm về SSIS có sẵn tại đây .
• Tiện ích BCP – một công cụ dựa trên dòng lệnh cung cấp một số lượng lớn các cài đặt – nếu bạn là một lập trình viên, đây là công cụ nên sử dụng. Thông tin thêm về tiện ích BCP có sẵn tại đây .

Tóm tắt – chèn dữ liệu từ Excel vào SQL Server

Trong bài viết này, chúng tôi đã xem xét 2 cách dễ dàng để chèn dữ liệu từ Excel sang SQL Server

Nếu bạn biết cách sử dụng SQL Server Management Studio, tính năng sao chép và dán là một lựa chọn tuyệt vời khi bạn cần nhập dữ liệu từ Excel sang SQL Server một cách nhanh chóng và dễ dàng. Quá trình này đơn giản và không yêu cầu bất kỳ kiến ​​thức hay công cụ đặc biệt nào và có thể được sử dụng trong các bảng có tối đa vài chục nghìn hàng dữ liệu. Nó cũng có thể phục vụ cho các tình huống chẳng hạn như các bảng có khóa nhận dạng tăng tự động hoặc nếu bạn cần kết nối với SQL Server trên một máy từ xa bằng Remote Desktop Connection

Nếu bạn không có quyền truy cập vào SQL Server Management Studio, bạn có thể sử dụng Phần bổ trợ SQL Spread Excel để chèn dữ liệu từ Excel sang SQL. Nó nhanh chóng và dễ sử dụng cho người dùng không có kỹ thuật. Đối với những người dùng nâng cao hơn, có một số tính năng thú vị như cột tra cứu, tùy chọn trục và xác thực dữ liệu cho phép bạn tạo các giải pháp quản lý dữ liệu mạnh mẽ

Tải xuống bản dùng thử miễn phí SQL Spreads và liên hệ với chúng tôi if you have any questions.

Bài viết của

Johannes Åkesson

Đã làm việc trong ngành Business Intelligence trong hơn 15 năm qua

Người sáng lập SQL Spread – giải pháp
quản lý dữ liệu để nhập, cập nhật và quản lý
SQL Server data from within Excel.

Để lại một câu trả lời

Click vào đây để hủy bỏ trả lời

Họ và tên

E-mail

URL trang web

Bình luận

Δ

Bình luận [7]

Đáp lại

Đan » 20. Tháng 12 năm 2018

Cảm ơn đã hiển thị chức năng sao chép/dán siêu đơn giản. Tôi không thể làm cho Trình hướng dẫn nhập khẩu hoạt động