sqlcmd를 사용하여 데이터를 SQL Server에서 CSV 형식으로 내보내려면 어떻게 해야 합니까?

sqlcmd를 사용하여 데이터를 SQL Server에서 CSV 형식으로 내보내려면 어떻게 해야 합니까?

다음과 같은 텍스트 파일에 데이터를 쉽게 덤프할 수 있습니다.

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

이 도움말 파일에서는 '있다', '아니다', '아니다', '아니다', '아니다'에 대한 .SQLCMD CSV 전용 .

CSV를 사용하여 를 덤프하는 요?SQLCMD

다음과 같이 실행할 수 있습니다.

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" 
       -o "MyData.csv" -h-1 -s"," -w 700

• -h-1합니다.
  
• -s","는 컬럼 , 로 합니다.
  
• -w 700 긴 ).그렇지 않으면 다음 행으로 바꿉니다.

하면 PowerShell을 파이핑하여 할 수 .Invoke-SqlcmdExport-Csv.

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

Invoke-Sqlcmd 는 sqlcmd.exe와 동등한 PowerShell입니다.텍스트 대신 객체를 출력합니다.

-Query는 '하다'와 합니다.-Qsqlcmd의 .exe "xe" 입니다.내보낼 데이터를 설명하는 SQL 쿼리를 전달합니다.

-Database는 '하다'와 합니다.-dsqlcmd의 .exe "xe" 입니다.내보낼 데이터가 들어 있는 데이터베이스의 이름을 전달합니다.

-Server는 '하다'와 합니다.-Ssqlcmd의 .exe "xe" 입니다.내보낼 데이터가 들어 있는 서버의 이름을 전달합니다.

Export-CSV 는 범용 개체를 CSV로 시리얼화하는 PowerShell cmdlet입니다.PowerShell에 포함되어 있습니다.

-NoTypeInformation파라미터는 CSV 형식의 일부가 아닌 추가 출력을 억제합니다.기본적으로 cmdlet은 유형 정보를 사용하여 헤더를 작성합니다.나중에 를 사용하여 오브젝트를 역직렬화할 때 오브젝트의 유형을 알 수 있습니다.Import-Csv그러나 표준 CSV를 필요로 하는 툴을 혼란스럽게 합니다.

-Path는 '하다'와 합니다.-osqlcmd의 .exe "xe" 입니다.

-Encoding는 '하다'와 합니다.-f ★★★★★★★★★★★★★★★★★」-usqlcmd의 .exe exe exe exe exe exe exe exe 。기본적으로는 Export-Csv는 ASCII 문자만 출력하고 다른 모든 문자를 물음표로 바꿉니다.대신 UTF8을 사용하여 모든 문자를 유지하고 대부분의 다른 도구와 호환성을 유지합니다.

sqlcmd에 비해 이 솔루션의 주요 장점.exe 또는 bcp.exe는 유효한 CSV를 출력하기 위해 명령어를 해킹할 필요가 없습니다.Export-Csv cmdlet이 이 모든 것을 처리합니다.

가장 큰 단점은 이라는 것이다Invoke-Sqlcmd파이프라인에 전달하기 전에 결과 세트 전체를 읽습니다.내보낼 결과 세트 전체에 충분한 메모리가 있는지 확인합니다.

수십억 행에서는 원활하게 작동하지 않을 수 있습니다.문제가 있는 경우 다른 툴을 시험해 보거나 효율적인 버전을 직접 롤업할 수 있습니다.Invoke-Sqlcmd시스템을 사용합니다.Data.SqlClient.SqlDataReader 클래스

SQL Server 버전 간의 차이점

SQL Server 2016을 기준으로Invoke-Sqlcmd는 모듈의 일부로 출고됩니다.

대신 SQL Server 2012에는 이전 SQLPS 모듈이 있습니다.모듈을 Import하면 현재 위치가 다음과 같이 변경됩니다.SQLSERVER:\따라서, 이 설정을 변경할 필요가 있습니다.#Requires위의 행:

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

풀 패스Export-Csv의-Path파라미터는 오래된 SQLPS 모듈을 사용하는 경우에 가장 안전합니다.

및 R2의 Server 2008 '2008 R2'를 합니다.#Requires행 전체를 지정하고 표준 PowerShell 호스트 대신 sqlps.exe 유틸리티를 사용합니다.

sqlcmd -S myServer -d myDB -E -o "MyData.txt" ^
    -Q "select bar from foo" ^
    -W -w 999 -s","

마지막 줄에는 CSV 고유의 옵션이 포함되어 있습니다.

• -W
• -s","는 컬럼 로 합니다.
• -w 999는 행 합니다.

Scottm의 대답은 내가 사용하는 것에 매우 가깝지만, 나는 그 답을 발견한다.-W정말 、 가가추추추을 。CSV를 다른 곳에서 사용할 때 공백을 자를 필요가 없습니다.

MSDN sqlcmd 레퍼런스도 참조해 주세요.그 때문에,/?옵션의 출력은 수치입니다.

★★★★★★★★★★★★★★★★★★★★★★?bcp★★★★★★★★★★★★★★★★?

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

명령줄에서 이 명령을 실행하여 구문을 확인합니다.

bcp /?

예를 들어 다음과 같습니다.

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]

해 주세요bcp열 헤더를 출력할 수 없습니다.

bcp 유틸리티 문서 페이지를 참조하십시오.

위 페이지의 예:

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

이 작업을 수행하지만 열 머리글이 있는 사용자를 위한 참고 사항. 배치 파일을 사용한 솔루션은 다음과 같습니다.

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

그러면 초기 결과(헤더와 데이터 사이의 구분자 포함)가 임시 파일로 출력되고 findstr을 통해 필터링하여 해당 행을 삭제합니다. 점에 하세요.-,-하지 않고 출력에 열이1개만 있으면 동작하지 않고 해당 문자열을 포함하는 정규 행도 필터링합니다.

이 답변은 @iain-elder의 솔루션을 기반으로 합니다.이 솔루션은 (솔루션에서 지적된) 대규모 데이터베이스 케이스를 제외하고 잘 작동합니다.테이블 전체가 시스템 메모리에 들어가도록 해야 하는데, 이 방법은 선택사항이 아니었습니다.시스템을 사용하는 것이 최선의 해결책이라고 생각합니다.Data.SqlClient.SqlDataReader 및 커스텀 CSV 시리얼라이저(예에 대해서는 여기를 참조) 또는 MS SQL 드라이버와 CSV 시리얼라이제이션이 있는 다른 언어.아마 의존 관계가 없는 솔루션을 찾고 있었을 것 같은 원래의 질문의 정신으로, 아래의 PowerShell 코드가 효과가 있었습니다.특히 $chunk_size 행마다 $data 어레이를 인스턴스화하고 추가 모드에서 Export-Csv를 호출하는 것은 매우 느리고 비효율적입니다.

$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()

BCP를 사용한 대체 옵션:

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

은 ★★★sqlcmd(의 일부로서) 유틸리티가 부속되어 있다.mssql-toolsCSV를 사용하다

사용방법:

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

예를 들어 다음과 같습니다.

bcp.exe MyTable out data.csv

---

대응하는 CSV 파일에 모든 테이블을 덤프하려면 다음 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

위의 답변으로 거의 해결할 수 있었지만 구문 분석 CSV가 올바르게 생성되지 않았습니다.

내 버전은 다음과 같습니다.

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

그런 을 해?sqlcmd하는 PowerShell을 하기 위해 입니다.PowerShell은 PowerShell을 .이해하고 있습니다.sqlcmdWindows の windows windows windows windows windows windows windows windows 。Linux 를 사용하고 있습니다(Windows 를 사용하고 있는 경우는, PS 는 사용하지 않습니다).

그 말을 다 했으니, 난 정말이지bcp더 쉬운.

다음 두 가지 이유가 있으므로 CMD에서 솔루션을 실행해야 합니다.

1. 쿼리에 큰따옴표가 있을 수 있습니다.

2. 리모트 SQL Server 인스턴스를 쿼리하기 위해 사용자 이름과 비밀번호 로그인이 필요할 수 있습니다.

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

패키지 python을 해 .sqlcmd-csv쉼표로 구분된 출력을 유효한 csv로 후처리합니다.

https://github.com/shadiakiki1986/sqlcmd-csv

sqlcmd ... -s, ...
pip install git+https://github.com/shadiakiki1986/sqlcmd-csv.git
sqlcmd_csv out.txt out.csv

당신은 그것을 교묘한 방법으로 할 수 있다.해서 하세요.sqlcmd데이터에 큰따옴표나 콤마가 있으면 곤란합니다.

간단한 스크립트를 사용하여 올바르게 수행할 수 있습니다.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Data Exporter                                                 '
'                                                               '
' Description: Allows the output of data to CSV file from a SQL '
'       statement to either Oracle, SQL Server, or MySQL        '
' Author: C. Peter Chen, http://dev-notes.com                   '
' Version Tracker:                                              '
'       1.0   20080414 Original version                         '
'   1.1   20080807 Added email functionality                '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                 ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "dbhost"                 ' Hostname of the database server
dbName = "dbname"                 ' Name of the database/SID
dbUser = "username"               ' Name of the user
dbPass = "password"               ' Password of the above-named user
outputFile = "c:\output.csv"      ' Path and file name of the output CSV file
email = "email@me.here"           ' Enter email here should you wish to email the CSV file (as attachment); if no email, leave it as empty string ""
  subj = "Email Subject"          ' The subject of your email; required only if you send the CSV over email
  body = "Put a message here!"    ' The body of your email; required only if you send the CSV over email
  smtp = "mail.server.com"        ' Name of your SMTP server; required only if you send the CSV over email
  smtpPort = 25                   ' SMTP port used by your server, usually 25; required only if you send the CSV over email
sqlStr = "select user from dual"  ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''



dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
    conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
    conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
    conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

' Subprocedure to generate data.  Two parameters:
'   1. fPath=where to create the file
'   2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
    dim a, showList, intcount
    set a = fso.createtextfile(fPath)

    set showList = conn.execute(sqlstr)
    for intcount = 0 to showList.fields.count -1
        if intcount <> showList.fields.count-1 then
            a.write """" & showList.fields(intcount).name & ""","
        else
            a.write """" & showList.fields(intcount).name & """"
        end if
    next
    a.writeline ""

    do while not showList.eof
        for intcount = 0 to showList.fields.count - 1
            if intcount <> showList.fields.count - 1 then
                a.write """" & showList.fields(intcount).value & ""","
            else
                a.write """" & showList.fields(intcount).value & """"
            end if
        next
        a.writeline ""
        showList.movenext
    loop
    showList.close
    set showList = nothing

    set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = nothing

if email <> "" then
    dim objMessage
    Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = "Test Email from vbs"
    objMessage.From = email
    objMessage.To = email
    objMessage.TextBody = "Please see attached file."
    objMessage.AddAttachment outputFile

    objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
    objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort

objMessage.Configuration.Fields.Update

    objMessage.Send
end if

'You're all done!!  Enjoy the file created.
msgbox("Data Writer Done!")

^{출처: SQL 출력을 VBScript를 사용하여 CSV에 씁니다.}

언급URL : https://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd