Cập nhật. 2017-09-20 Cảm ơn Rick Fraser đã chỉ ra rằng tôi đã hiển thị $ServerConnection nhưng chưa xác định nó trong hàm hoặc riêng biệt hoặc hoàn toàn. Cảm ơn Rick
Tháng này chúng ta có Rob Sewell [ Blog. Twitter ] yêu cầu chúng tôi “lấy tất cả PoSh” [và vâng, tôi vừa chơi chữ ở đó]. Vì vậy, Thứ Ba T-SQL về PowerShell, làm sao tôi có thể từ chối. Đặc biệt là kể từ khi tôi có một bài đăng trên blog về PowerShell đang được triển khai [Xem. Bạn không phải là người duy nhất có thể chơi chữ Rob 😀 ]
Bạn đã bao giờ được cung cấp một tệp Excel để nhập vào SQL Server để “phân tích thêm” chưa?
Tôi đang nói về dữ liệu gần như phi cấu trúc, một câu nói chung chung “này, tôi đã tạo tệp excel này cho bạn” mà bạn nhìn vào và hơi co rúm lại?
Tôi đã gặp phải vấn đề này gần đây và được giao nhiệm vụ nhập dữ liệu vào SQL Server
T-SQL
Trước hết, chúng tôi có bảng của chúng tôi
Tệp này chứa văn bản Unicode hai chiều có thể được diễn giải hoặc biên dịch khác với nội dung hiển thị bên dưới. Để xem lại, hãy mở tệp trong trình chỉnh sửa hiển thị các ký tự Unicode bị ẩn.
Tìm hiểu thêm về ký tự Unicode hai chiều
Hiển thị ký tự ẩn
xem thô
được lưu trữ với ❤ bởi GitHub
Bây giờ, việc nhập này thường không phải là vấn đề vì tôi lười hiệu quả và sử dụng “Trình hướng dẫn nhập dữ liệu”
Tuy nhiên, trong trường hợp này, tôi không thể vì một thông báo lỗi không mong muốn…
Được rồi, bỏ qua ý tưởng đó, sao chúng ta không sử dụng gì ngoài T-SQL, không có mánh lới quảng cáo GUI?
Chúng tôi có thể thử sử dụng OPENROWSET[] để truy xuất dữ liệu nhưng khi chúng tôi thử điều đó
/*————————
SỬ DỤNG Pantheon;CHỌN *
TỪ OPENROWSET[
‘Microsoft. ÁT CHỦ. OLEDB. 12. 0’,
‘Excel 12. 0 Xml;
Cơ sở dữ liệu=C. \Users\soneill\Desktop\In Progress\Blogs\ImportingExcelIntoSqlServerUsingPowerShell\Disc Characters. xlsx',
[Sheet1]];
————————*/
Msg 15281, Cấp 16 .
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’
because this component is turned off as part of the security configuration for this server.
Quản trị viên hệ thống có thể cho phép sử dụng 'Truy vấn phân tán đặc biệt' bằng cách sử dụng sp_configure.
Để biết thêm thông tin về cách bật 'Truy vấn được phân phối đặc biệt', hãy tìm kiếm 'Truy vấn được phân phối đặc biệt' trong SQL Server Books Online.
Bây giờ, giả sử rằng chúng ta đang làm việc trong một môi trường không cho phép chúng ta thay đổi cấu hình máy chủ, nghĩa là OPENROWSET[] đã đóng đối với chúng ta
Thành thật mà nói, bảng tính của tôi chỉ có 8 hàng nên tôi chỉ cần chèn dữ liệu theo cách thủ công nhưng điều gì xảy ra nếu bảng tính lớn vài nghìn hàng?
Vì vậy, hầu hết các ý tưởng về T-SQL của tôi đã cạn kiệt, hãy xem PowerShell
PowerShell
Ngay lập tức, điều này có vẻ cực kỳ dễ dàng vì PowerShell có cmdlet Import-Csv sẽ giúp chúng tôi ở đây, miễn là các tệp được cung cấp cho chúng tôi là một. csv hoặc có thể được thay đổi/lưu vào. tệp csv
Bây giờ, tôi đã có kết quả từ tệp và vào bộ nhớ, đây là một quá trình đủ đơn giản để tải nó vào phiên bản của tôi
Tôi có nên dừng lại ở đó không?
khai trương. xlsx, thay đổi định dạng thành. csv và lưu nó dưới dạng. csv có vẻ hơi tốn công sức, đặc biệt là với những thông báo khó chịu về định dạng xuất hiện
Rất tiếc, chúng tôi có thể sử dụng Import-Csv trên tệp xlsx không?
Phải, vì vậy, trong nỗ lực lấy dữ liệu ra khỏi tệp Excel và đưa vào Hệ thống. Dữ liệu. DataTable để tôi có thể chèn hàng loạt, tôi đã tạo một hàm và sử dụng hàm đó thay thế
Kiểm tra nó ra ở đây
$FolderPath = '.'; $CsvFile = 'DiscCharacters.csv'; $XlsxFile = 'DiscCharacters.xlsx'; $Server = '.\SQLServer2K16'; $Database = 'Pantheon'; $ServerConnection = "Data Source='$Server';Integrated Security=true;Initial Catalog=$Database"; $Table = 'dbo.DiscCharacters'
Tệp này chứa văn bản Unicode hai chiều có thể được diễn giải hoặc biên dịch khác với nội dung hiển thị bên dưới. Để xem lại, hãy mở tệp trong trình chỉnh sửa hiển thị các ký tự Unicode bị ẩn.
Tìm hiểu thêm về ký tự Unicode hai chiều
Hiển thị ký tự ẩn
xem thô
được lưu trữ với ❤ bởi GitHub
Chạy chức năng này,
Import-Excel -FolderPath . -XlsxFile 'DiscCharacters.xlsx' -Server '.\SqlServer2k16' -Database Pantheon -Table 'dbo.DiscCharacters';
hy vọng sẽ điền vào bảng của chúng tôi, vì vậy chúng tôi kiểm tra nhanh bảng của mình…
Đúng
Inline-OutSource
Phần khó nhất về điều này là đối tượng Excel, việc tìm hiểu về nó rất khó khăn và đòi hỏi phải làm căng Google-fu của tôi
Đó là một vấn đề mà tôi tìm thấy với tất cả các đối tượng Com
Nó cũng không phải là một chức năng tuyệt vời, tên cột được mã hóa cứng và xác thực từ 0 đến không. Nếu thực tế là tôi không phải chạy cái này trên một vài bảng tính và sau đó chuyển xuống phần sâu của thư mục “các chức năng cũ” của mình, thì tôi đã không bận tâm đến việc nâng cấp tập lệnh thành một chức năng
Giá như có một cách mà công việc này có thể được thực hiện cho tôi…
Đợi một chút…
Trước đây tôi đã nói về việc tận dụng công việc khó khăn của các thành viên trong cộng đồng và trường hợp này cũng không khác. Nhập Doug Finke [. Twitter ] và Mô-đun Import-Excel của anh ấy [vâng, bạn đã đọc đúng đó…MÔ-đun, không phải tập lệnh. mô-đun]
Đây là một đoạn trích ngắn từ một trong các lệnh trong mô-đun của anh ấy. ConvertFrom-ExcelToSqlInsert…
Đó chỉ là một trong nhiều [[Get-Command -Module ImportExcel].Count; ] 38 lệnh mà tôi có sẵn từ mô-đun này và tận dụng điều này, có nghĩa là tôi không phải chơi với ComObject và có thể dành thời gian cho những thứ khác
Rất vui được biết rằng, không chỉ SQL mà cả cộng đồng PowerShell cũng ủng hộ tôi 🙂
Và hãy nhớ
Trí tuệ đến từ kinh nghiệm. Kinh nghiệm thường là kết quả của sự thiếu khôn ngoan
– Terry Pratchett
Chia sẻ cái này
Như thế này
Thích Đang tải.
Tác giả. Shane O'Neill
Người hâm mộ DBA, T-SQL và PowerShell, Thực phẩm, Cà phê, Rượu whisky [không nhất thiết phải theo thứ tự đó]. Xem tất cả các bài viết của Shane O'Neill