112

Tôi có thể dễ dàng đổ dữ liệu vào một tệp văn bản như:

sqlcmd -S myServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" 
     -o "MyData.txt"

Tuy nhiên, tôi đã xem các tệp trợ giúp SQLCMDnhưng chưa thấy tùy chọn nào dành riêng cho CSV.

Có cách nào để chuyển dữ liệu từ bảng vào tệp văn bản CSV bằng cách sử dụng SQLCMDkhông?

|
119

Bạn có thể chạy một cái gì đó như thế này:

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" 
       -o "MyData.csv" -h-1 -s"," -w 700
  • -h-1 xóa tiêu đề tên cột khỏi kết quả
  • -s"," đặt ngăn cách cột thành,
  • -w 700 đặt chiều rộng của hàng thành 700 ký tự (cái này sẽ cần phải rộng bằng hàng dài nhất hoặc nó sẽ quấn đến dòng tiếp theo)
|
  • 1

    Thông báo trước khi thực hiện theo cách này là dữ liệu của bạn có thể không chứa bất kỳ dấu phẩy nào.

    – Lý Bích Thu 16:57:48 30/10/2012
  • 1

    @SarelBotha, bạn có thể giải quyết vấn đề đó bằng cách '""' + col1 + '""' AS col1trích dẫn (nhân đôi) dấu ngoặc kép hoặc chỉ cần gọi một thủ tục được lưu trữ.

    – Hoàng Tuấn Hải 17:17:18 11/12/2013
  • 1

    @JIsaak Sau đó, đảm bảo dữ liệu của bạn không có bất kỳ dấu ngoặc kép nào hoặc đảm bảo thay thế dấu ngoặc kép của bạn bằng hai dấu ngoặc kép.

    – Hoàng Kim Ngân 19:23:08 12/12/2013
  • 1

    Ai đó có thể làm rõ những gì cần làm để cho phép dấu phẩy bên trong dữ liệu? Chúng ta có phải bao quanh mọi cột bằng '""' + ___ + '""' không?

    – Hoàng Tố Nga 00:41:38 01/04/2015
  • 1

    Câu trả lời này đã lỗi thời. Các tập lệnh PowerShell linh hoạt hơn và có thể được chạy trong SQL Server với tư cách là Tác nhân Công việc.

    – Trịnh Ðình Ðôn 02:45:39 24/05/2016
62
sqlcmd -S myServer -d myDB -E -o "MyData.txt" ^
    -Q "select bar from foo" ^
    -W -w 999 -s","

Dòng cuối cùng chứa các tùy chọn dành riêng cho CSV.

  • -W   xóa dấu cách từ mỗi trường riêng lẻ
  • -s","   đặt ngăn cách cột thành dấu phẩy (,)
  • -w 999   đặt chiều rộng hàng thành 999 ký tự

Câu trả lời của scottm rất gần với những gì tôi sử dụng, nhưng tôi thấy đây -Wlà một bổ sung thực sự hay: Tôi không cần phải cắt khoảng trắng khi tôi sử dụng CSV ở nơi khác.

Cũng xem tài liệu tham khảo MSDN sqlcmd . Nó đặt /?đầu ra của tùy chọn để xấu hổ.

|
58

Đây không phải bcplà dành cho?

bcp "select col1, col2, col3 from database.schema.SomeTable" queryout  "c:\MyData.txt"  -c -t"," -r"\n" -S ServerName -T

Chạy này từ dòng lệnh của bạn để kiểm tra cú pháp.

bcp /?

Ví dụ:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]

Xin lưu ý rằng bcpkhông thể xuất các tiêu đề cột.

Xem: trang tài liệu bcp Utility .

Ví dụ từ trang trên:

bcp.exe MyTable out "D:\data.csv" -T -c -C 65001 -t , ...
|
  • 1

    ServerName = YourcomputerName \ SQLServerName, chỉ sau đó nó mới thực thi lỗi

    – Lý Bích Thu 12:40:53 18/10/2011
  • 1

    Trong trường hợp bạn muốn xem tài liệu đầy đủ cho bcp.exe: technet.microsoft.com/en-us/l

    – Hoàng Tuấn Hải 14:42:56 12/11/2013
  • 1

    Bạn sẽ làm gì nếu bạn cũng cần xuất các tên cột làm tiêu đề? Có một giải pháp chung đơn giản bằng cách sử dụng bcp?

    – Hoàng Kim Ngân 03:19:32 01/06/2014
  • 1

    @johndacosta cảm ơn rất nhiều. Làm thế nào bạn sẽ in các tiêu đề cột là tốt? Tôi không thấy một chuyển đổi dễ dàng đến bất cứ nơi nào. Cảm ơn!

    – Hoàng Tố Nga 23:15:41 17/08/2015
  • 1

    bcpkhông bao gồm tiêu đề (tên cột), nhưng nó nhanh hơn ~ 10 lần so với sqlcmd(theo kinh nghiệm của tôi). Đối với dữ liệu thực sự lớn, bạn có thể sử dụng bcpđể lấy dữ liệu và sử dụng sqlcmd(chọn top 0 * từ ...) để lấy tiêu đề và sau đó kết hợp chúng.

    – Trịnh Ðình Ðôn 23:12:14 04/06/2018
52

Với PowerShell, bạn có thể giải quyết vấn đề một cách gọn gàng bằng cách chuyển Invoke-Sqlcmd vào Export-Csv.

#Requires -Module SqlServer
Invoke-Sqlcmd -Query "SELECT * FROM DimDate;" `
              -Database AdventureWorksDW2012 `
              -Server localhost |
Export-Csv -NoTypeInformation `
           -Path "DimDate.csv" `
           -Encoding UTF8

SQL Server 2016 bao gồm mô-đun SqlServer chứa Invoke-Sqlcmdcmdlet, bạn sẽ có ngay cả khi bạn chỉ cài đặt SSMS 2016. Trước đó, SQL Server 2012 đã bao gồm mô-đun SQLPS, sẽ thay đổi thư mục hiện tại thành SQLSERVER:\khi mô-đun lần đầu tiên được sử dụng (trong số các lỗi khác), vì vậy, bạn cần thay đổi #Requiresdòng trên thành:

Push-Location $PWD
Import-Module -Name SQLPS
# dummy query to catch initial surprise directory change
Invoke-Sqlcmd -Query "SELECT 1" `
              -Database  AdventureWorksDW2012 `
              -Server localhost |Out-Null
Pop-Location
# actual Invoke-Sqlcmd |Export-Csv pipeline

Để điều chỉnh ví dụ cho SQL Server 2008 và 2008 R2, hãy xóa #Requireshoàn toàn dòng và sử dụng tiện ích sqlps.exe thay vì máy chủ PowerShell tiêu chuẩn.

Invoke-Sqlcmd là tương đương PowerShell của sqlcmd.exe. Thay vì văn bản, nó xuất ra các đối tượng System.Data.DataRow .

Các -Querythông số hoạt động như -Qtham số của sqlcmd.exe. Truyền cho nó một truy vấn SQL mô tả dữ liệu bạn muốn xuất.

Các -Databasethông số hoạt động như -dtham số của sqlcmd.exe. Truyền cho nó tên của cơ sở dữ liệu chứa dữ liệu sẽ được xuất.

Các -Serverthông số hoạt động như -Stham số của sqlcmd.exe. Truyền cho nó tên của máy chủ chứa dữ liệu sẽ được xuất.

Xuất-CSV là một lệnh ghép ngắn PowerShell nối tiếp các đối tượng chung thành CSV. Nó xuất xưởng với PowerShell.

Các -NoTypeInformationtham số ngăn chặn thêm đầu ra đó không phải là một phần của định dạng CSV. Theo mặc định, lệnh ghép ngắn viết một tiêu đề với thông tin loại. Nó cho phép bạn biết loại đối tượng khi bạn khử lưu trữ nó sau này Import-Csv, nhưng nó gây nhầm lẫn các công cụ mong đợi CSV tiêu chuẩn.

Các -Paththông số hoạt động như -otham số của sqlcmd.exe. Đường dẫn đầy đủ cho giá trị này là an toàn nhất nếu bạn bị mắc kẹt khi sử dụng mô-đun SQLPS.

Các -Encodingthông số hoạt động như -fhoặc -utham số của sqlcmd.exe. Theo mặc định, Export-Csv chỉ xuất các ký tự ASCII và thay thế tất cả các ký tự khác bằng dấu chấm hỏi. Thay vào đó, hãy sử dụng UTF8 để bảo toàn tất cả các ký tự và luôn tương thích với hầu hết các công cụ khác.

Ưu điểm chính của giải pháp này so với sqlcmd.exe hoặc bcp.exe là bạn không phải hack lệnh để xuất CSV hợp lệ. Lệnh ghép ngắn Xuất-Csv xử lý tất cả cho bạn.

Nhược điểm chính là Invoke-Sqlcmdđọc toàn bộ kết quả trước khi đưa nó dọc theo đường ống. Đảm bảo bạn có đủ bộ nhớ cho toàn bộ tập kết quả bạn muốn xuất.

Nó có thể không hoạt động trơn tru cho hàng tỷ hàng. Nếu đó là một vấn đề, bạn có thể thử các công cụ khác hoặc cuộn phiên bản hiệu quả của riêng bạn Invoke-Sqlcmdbằng cách sử dụng lớp System.Data.SqlClient.SqlDataReader .

|
  • 1

    Câu trả lời khác trung thực, đây là cách duy nhất để làm điều đó một cách chính xác. Tôi ước rằng nó rõ ràng hơn mặc dù.

    – Lý Bích Thu 16:20:35 25/07/2014
  • 1

    Đây là một câu trả lời tốt hơn nhiều so với câu trả lời được chấp nhận.

    – Hoàng Tuấn Hải 19:05:05 14/08/2014
  • 1

    Trên SQL 2008 R2, tôi đã phải chạy công cụ "sqlps.exe" để sử dụng Invoke-Sqlcmd. Rõ ràng tôi cần SQL 2012 để sử dụng Mô-đun nhập khẩu? Anway nó hoạt động trong "sqlps.exe" - xem chủ đề này để biết chi tiết .

    – Hoàng Kim Ngân 20:11:55 03/02/2015
  • 1

    @Mister_Tom điểm tốt. Mô-đun SQLPS được giới thiệu với SQL 2012. Câu trả lời bây giờ giải thích cách điều chỉnh ví dụ cho các phiên bản cũ hơn.

    – Hoàng Tố Nga 23:11:34 03/02/2015
  • 1

    @JasonMatney PowerShell là giao diện quản trị mới cho các hệ thống Windows, nhưng rất nhiều lời khuyên về SQL Server đã được xuất bản trước khi nó trở thành tiêu chuẩn. Thông báo! :-)

    – Trịnh Ðình Ðôn 19:25:21 02/07/2015
10

Một lưu ý cho bất cứ ai muốn làm điều này nhưng cũng có các tiêu đề cột, đây là giải pháp mà tôi đã sử dụng một tệp bó:

sqlcmd -S servername -U username -P password -d database -Q "set nocount on; set ansi_warnings off; sql query here;" -o output.tmp -s "," -W
type output.tmp | findstr /V \-\,\- > output.csv
del output.tmp

Điều này đưa ra các kết quả ban đầu (bao gồm các dấu phân cách ----, ---- giữa các tiêu đề và dữ liệu) vào một tệp tạm thời, sau đó loại bỏ dòng đó bằng cách lọc nó qua findstr. Lưu ý rằng nó không hoàn hảo vì nó lọc -,-raititit sẽ không hoạt động nếu chỉ có một cột trong đầu ra và nó cũng sẽ lọc ra các dòng hợp pháp có chứa chuỗi đó.

|
  • 1

    Thay vào đó, hãy sử dụng bộ lọc sau: findstr / r / v ^ \ - [, \ -] * $> output.csv Vì một số lý do simle ^ [, \ -] * $ khớp với tất cả các dòng.

    – Hoàng Ðình Chương 15:27:19 08/09/2011
  • 1

    Luôn đặt regex với ^ bên trong dấu ngoặc kép hoặc bạn nhận được kết quả lạ, vì ^ là ký tự thoát cho cmd.exe. Cả hai biểu thức trên đều không hoạt động chính xác, theo như tôi có thể nói, nhưng điều này có: findstr / r / v "^ - [-,] * -. $" (. Trước khi $ dường như là cần thiết khi thử nghiệm với echo, nhưng có thể không cho đầu ra sqlcmd)

    – Thuong Vo 07:01:59 09/03/2012
  • 1

    Cũng có một vấn đề với ngày có 2 khoảng cách giữa ngày và thời gian thay vì một. Khi bạn cố gắng mở CSV trong Excel, nó sẽ hiển thị là 00: 00.0. Một cách dễ dàng để giải quyết vấn đề này là tìm kiếm và thay thế tất cả "" bằng "" tại chỗ bằng SED. Lệnh để thêm vào tập lệnh của bạn sẽ là: SED -i "s / / / g" output.csv. Thông tin thêm về SED gnuwin32.sourceforge.net/packages/sed.htm

    – Hồ Anh Ðức 16:04:11 13/06/2012
  • 1

    đây là câu trả lời đúng, hoạt động hoàn hảo với sự bổ sung của @ JimG. Tôi đã sử dụng dấu chấm phẩy cho dấu phân cách và một tệp sql cho đầu vào, tệp chứa phần không đếm được và phần ansi_warning, và công tắc -W để xóa không gian

    – Lý Hoài An 21:07:18 17/11/2015
1

Tùy chọn thay thế với BCP:

exec master..xp_cmdshell 'BCP "sp_who" QUERYOUT C:\av\sp_who.txt -S MC0XENTC -T -c '
|
0

Vì sau 2 lý do, bạn nên chạy giải pháp của tôi trong CMD:

  1. Có thể có dấu ngoặc kép trong truy vấn
  2. Đăng nhập tên người dùng và mật khẩu đôi khi cần thiết để truy vấn một phiên bản SQL Server từ xa

    sqlcmd -U [your_User]  -P[your_password] -S [your_remote_Server] -d [your_databasename]  -i "query.txt" -o "output.csv" -s"," -w 700
|
0

Một câu trả lời ở trên gần như đã giải quyết nó cho tôi nhưng nó không tạo ra một CSV được phân tích chính xác.

Đây là phiên bản của tôi:

sqlcmd -S myurl.com -d MyAzureDB -E -s, -W -i mytsql.sql | findstr /V /C:"-" /B > parsed_correctly.csv

Ai đó nói rằng sqlcmdđã lỗi thời trong một số thay thế PowerShell là quên rằng điều đó sqlcmdkhông chỉ dành cho Windows. Tôi đang dùng Linux (và khi ở trên Windows, tôi vẫn tránh PS).

Đã nói tất cả, tôi thấy bcpdễ dàng hơn.

|
0

Thường sqlcmdđi kèm với bcptiện ích (như một phần của mssql-tools) xuất theo mặc định CSV.

Sử dụng:

bcp {dbtable | query} {in | out | queryout | format} datafile

Ví dụ:

bcp.exe MyTable out data.csv

Để kết xuất tất cả các bảng vào các tệp CSV tương ứng, đây là tập lệnh Bash :

#!/usr/bin/env bash
# Script to dump all tables from SQL Server into CSV files via bcp.
# @file: bcp-dump.sh
server="sql.example.com" # Change this.
user="USER" # Change this.
pass="PASS" # Change this.
dbname="DBNAME" # Change this.
creds="-S '$server' -U '$user' -P '$pass' -d '$dbname'"
sqlcmd $creds -Q 'SELECT * FROM sysobjects sobjects' > objects.lst
sqlcmd $creds -Q 'SELECT * FROM information_schema.routines' > routines.lst
sqlcmd $creds -Q 'sp_tables' | tail -n +3 | head -n -2 > sp_tables.lst
sqlcmd $creds -Q 'SELECT name FROM sysobjects sobjects WHERE xtype = "U"' | tail -n +3 | head -n -2 > tables.lst

for table in $(<tables.lst); do
  sqlcmd $creds -Q "exec sp_columns $table" > $table.desc && \
  bcp $table out $table.csv -S $server -U $user -P $pass -d $dbname -c
done
|
0

Câu trả lời này được xây dựng dựa trên giải pháp từ @ iain-Eld, hoạt động tốt ngoại trừ trường hợp cơ sở dữ liệu lớn (như được chỉ ra trong giải pháp của anh ấy). Toàn bộ bảng cần phải nằm gọn trong bộ nhớ hệ thống của bạn và đối với tôi đây không phải là một tùy chọn. Tôi nghi ngờ giải pháp tốt nhất sẽ sử dụng System.Data.SqlClient.SqlDataReader và trình tuần tự CSV tùy chỉnh ( xem ví dụ ở đây ) hoặc một ngôn ngữ khác với trình điều khiển MS SQL và tuần tự hóa CSV. Theo tinh thần của câu hỏi ban đầu có lẽ đang tìm kiếm một giải pháp không phụ thuộc, mã PowerShell bên dưới hoạt động với tôi. Nó rất chậm và không hiệu quả, đặc biệt là trong việc khởi tạo mảng dữ liệu $ và gọi Export-Csv ở chế độ chắp thêm cho mỗi dòng $ chunk_size.

$chunk_size = 10000
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = "SELECT * FROM <TABLENAME>"
$command.Connection = $connection
$connection.open()
$reader = $command.ExecuteReader()

$read = $TRUE
while($read){
    $counter=0
    $DataTable = New-Object System.Data.DataTable
    $first=$TRUE;
    try {
        while($read = $reader.Read()){

            $count = $reader.FieldCount
            if ($first){
                for($i=0; $i -lt $count; $i++){
                    $col = New-Object System.Data.DataColumn $reader.GetName($i)
                    $DataTable.Columns.Add($col)
                }
                $first=$FALSE;
            }

            # Better way to do this?
            $data=@()
            $emptyObj = New-Object System.Object
            for($i=1; $i -le $count; $i++){
                $data +=  $emptyObj
            }

            $reader.GetValues($data) | out-null
            $DataRow = $DataTable.NewRow()
            $DataRow.ItemArray = $data
            $DataTable.Rows.Add($DataRow)
            $counter += 1
            if ($counter -eq $chunk_size){
                break
            }
        }
        $DataTable | Export-Csv "output.csv" -NoTypeInformation -Append
    }catch{
        $ErrorMessage = $_.Exception.Message
        Write-Output $ErrorMessage
        $read=$FALSE
        $connection.Close()
        exit
    }
}
$connection.close()
|

Câu trả lời của bạn (> 20 ký tự)

Bằng cách click "Đăng trả lời", bạn đồng ý với Điều khoản dịch vụ, Chính sách bảo mật and Chính sách cookie của chúng tôi.

Không tìm thấy câu trả lời bạn tìm kiếm? Duyệt qua các câu hỏi được gắn thẻ hoặc hỏi câu hỏi của bạn.