sqlcmd를 사용하여 SQL Server에서 데이터를 CSV 형식으로 내보내는 방법은 무엇입니까?

Programming

sqlcmd를 사용하여 SQL Server에서 데이터를 CSV 형식으로 내보내는 방법은 무엇입니까?

procodes 2020. 7. 13. 22:25

sqlcmd를 사용하여 SQL Server에서 데이터를 CSV 형식으로 내보내는 방법은 무엇입니까?

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

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

그러나 도움말 파일을 살펴 보았지만 SQLCMDCSV 전용 옵션을 보지 못했습니다.

?를 사용하여 테이블의 데이터를 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 행 너비를 700 자로 설정합니다 (가장 긴 행만큼 넓어야하거나 다음 행으로 줄 바꿈).

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

마지막 줄에는 CSV 관련 옵션이 있습니다.

-W 각 개별 필드에서 후행 공백 제거
-s"," 열 구분자를 쉼표 (,)로 설정합니다.
-w 999 행 너비를 999 자로 설정

scottm의 대답 은 내가 사용하는 것에 매우 가깝지만 -W실제로 추가 할 수 있다는 것을 알았습니다. 다른 곳에서 CSV를 사용할 때 공백을자를 필요가 없습니다.

MSDN sqlcmd 참조 도 참조하십시오 . 그것은두고 /?수치 옵션의 출력을.

PowerShell을 사용하면 Invoke-Sqlcmd를 Export-Csv로 파이프하여 문제를 깔끔하게 해결할 수 있습니다.

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

SQL 서버 2016가 포함 SQLSERVER의 포함 모듈, Invoke-Sqlcmd당신은 단지 2016 년 그 전에 SSMS를 설치하는 경우에도 당신이해야 cmdlet을, SQL 서버 2012 이전 포함 SQLPS의 모듈 에 현재 디렉토리를 변경할 것, 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

SQL Server 2008 및 2008 R2의 예를 수정하려면 #Requires줄을 완전히 제거 하고 표준 PowerShell 호스트 대신 sqlps.exe 유틸리티 를 사용하십시오 .

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

이 -Query매개 -Q변수는 sqlcmd.exe 의 매개 변수 와 같이 작동합니다 . 내보낼 데이터를 설명하는 SQL 쿼리를 전달하십시오.

이 -Database매개 -d변수는 sqlcmd.exe 의 매개 변수 와 같이 작동합니다 . 내보낼 데이터가 포함 된 데이터베이스 이름을 전달하십시오.

이 -Server매개 -S변수는 sqlcmd.exe 의 매개 변수 와 같이 작동합니다 . 내보낼 데이터가 포함 된 서버 이름을 전달하십시오.

Export-CSV 는 일반 개체를 CSV로 직렬화하는 PowerShell cmdlet입니다. PowerShell과 함께 제공됩니다.

이 -NoTypeInformation매개 변수는 CSV 형식의 일부가 아닌 추가 출력을 억제합니다. 기본적으로 cmdlet은 형식 정보가 포함 된 헤더를 작성합니다. 나중에와 함께 직렬화 해제 할 때 객체의 유형을 알려 Import-Csv주지만 표준 CSV가 필요한 도구를 혼동합니다.

이 -Path매개 -o변수는 sqlcmd.exe 의 매개 변수 와 같이 작동합니다 . 이전 SQLPS 모듈을 사용하는 경우이 값의 전체 경로가 가장 안전합니다 .

-Encoding매개 변수는 같이 작동 -f또는 -usqlcmd.exe를의 매개 변수를 설정합니다. 기본적으로 Export-Csv는 ASCII 문자 만 출력하고 다른 모든 문자는 물음표로 바꿉니다. 대신 모든 문자를 유지하고 대부분의 다른 도구와 호환되도록 UTF8을 사용하십시오.

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

주요 단점은 Invoke-Sqlcmd파이프 라인을 따라 전달하기 전에 전체 결과 세트 를 읽는다는 것입니다. 내보내려는 전체 결과 세트에 충분한 메모리가 있는지 확인하십시오.

수십억 행에 대해 원활하게 작동하지 않을 수 있습니다. 문제가있는 경우 다른 도구를 사용해 보거나 System.Data.SqlClient.SqlDataReader 클래스 를 Invoke-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을 통해 필터링하여 해당 행을 제거합니다. 필터링되지 않기 때문에 완벽 -,-하지는 않습니다. 출력에 열이 하나만 있으면 작동하지 않으며 해당 문자열이 포함 된 합법적 인 행도 필터링됩니다.

BCP의 대체 옵션 :

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

이 답변은 @ iain-elder의 솔루션을 기반으로합니다.이 솔루션은 대용량 데이터베이스 사례를 제외하고는 잘 작동합니다 (솔루션에서 지적 했음). 전체 테이블은 시스템의 메모리에 맞아야하며 나에게 이것은 옵션이 아니 었습니다. 가장 좋은 솔루션은 System.Data.SqlClient.SqlDataReader 및 사용자 정의 CSV 시리얼 라이저 ( 예를 들어 여기 참조 ) 또는 MS SQL 드라이버 및 CSV 직렬화를 사용하는 다른 언어를 사용하는 것 같습니다. 의존성 솔루션이없는 원래 질문의 정신에서 아래 PowerShell 코드가 저에게 효과적이었습니다. 특히 $ data 배열을 인스턴스화하고 매 $ chunk_size 행마다 추가 모드에서 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()

일반적 sqlcmd으로 bcp유틸리티는 (의 일부로 mssql-tools) 기본적으로 CSV로 내 보냅니다.

용법:

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 대안에 찬성하여 구식 이라고 말하는 사람 sqlcmd은 Windows 용이 아니라는 것을 잊고 있습니다. 나는 Linux에 있습니다 (그리고 Windows에서는 PS를 피하십시오).

모든 것을 말하면, 나는 bcp더 쉽게 찾을 수 있습니다.

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

검색어에 큰 따옴표가있을 수 있습니다

원격 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

You can do it in a hackish way. Careful using the sqlcmd hack. If the data has double quotes or commas you will run into trouble.

You can use a simple script to do it properly:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 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!")

^{Source: Writing SQL output to CSV with VBScript.}

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

'Programming' 카테고리의 다른 글

루비 옵션 매개 변수 (0)	2020.07.13
Java로 간단한 경고 메시지를 표시하는 방법은 무엇입니까? (0)	2020.07.13
Visual Studio 프로젝트의 폴더에 대한 "링크로 추가" (0)	2020.07.13
uiwebview에서 로컬 HTML을 사용하여 상대 경로에서 리소스로드 (0)	2020.07.13
PHP에서 두 날짜를 어떻게 비교할 수 있습니까? (0)	2020.07.13

현재글sqlcmd를 사용하여 SQL Server에서 데이터를 CSV 형식으로 내보내는 방법은 무엇입니까?

procodes

sqlcmd를 사용하여 SQL Server에서 데이터를 CSV 형식으로 내보내는 방법은 무엇입니까?

sqlcmd를 사용하여 SQL Server에서 데이터를 CSV 형식으로 내보내는 방법은 무엇입니까?

'Programming' 카테고리의 다른 글

'Programming'의 다른글

티스토리툴바

sqlcmd를 사용하여 SQL Server에서 데이터를 CSV 형식으로 내보내는 방법은 무엇입니까?

sqlcmd를 사용하여 SQL Server에서 데이터를 CSV 형식으로 내보내는 방법은 무엇입니까?

'Programming' 카테고리의 다른 글

'Programming'의 다른글

관련글

티스토리툴바