Cum să exportați date în format CSV din SQL Server folosind sqlcmd? (Programare, Server Sql, Fișier, Csv, Sqlcmd)

Ray a intrebat.

Pot destul de ușor să arunc datele într-un fișier text, cum ar fi:

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

Cu toate acestea, m-am uitat la fișierele de ajutor pentru SQLCMD dar nu am văzut o opțiune specifică pentru CSV.

Există o modalitate de a transfera datele dintr-un tabel într-un fișier text CSV utilizând SQLCMD?

Comentarii

  • Trebuie să fie prin sqlcmd sau puteți utiliza un alt program, cum ar fi următorul: codeproject.com/KB/aspnet/ImportExportCSV.aspx –  > Por Bernhard Hofmann.
  • Nu trebuie să fie, dar am vrut să știu cu siguranță dacă sqlcmd poate sau nu să facă acest lucru înainte de a mă scufunda într-un alt utilitar de export. Un lucru care trebuie menționat este că trebuie să poată fi scriptat. –  > Por Ray.
  • Există un instrument addin SSMS 2008 care produce o ieșire CSV din tabelele dvs. care poate fi personalizată prin clauzele where și order by. store.nmally.com/software/sql-server-management-studio-addons/… – user975249
11 răspunsuri
scottm

Puteți rula ceva de genul acesta:

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" 
       -o "MyData.csv" -h-1 -s"," -w 700
  • -h-1 elimină anteturile cu numele coloanei din rezultat
  • -s"," setează separatorul de coloane la ,
  • -w 700 setează lățimea rândului la 700 de caractere (va trebui să fie la fel de mare ca și cel mai lung rând, altfel se va înfășura pe linia următoare)

Comentarii

    24

  • Atenție, dacă procedați în acest mod, este posibil ca datele dvs. să nu conțină virgule. –  > Por Sarel Botha.
  • @SarelBotha, puteți rezolva această problemă cu '""' + col1 + '""' AS col1, înfășurat în ghilimele duble (dublate) sau pur și simplu apelați o procedură stocată. –  > Por MisterIsaak.
  • @JIsaak Atunci asigurați-vă că datele dvs. nu au ghilimele duble sau asigurați-vă că înlocuiți ghilimelele duble cu două ghilimele duble. –  > Por Sarel Botha.
  • Ar putea cineva să clarifice ce trebuie făcut pentru a permite virgule în interiorul datelor? Trebuie să înconjurăm fiecare coloană cu ‘””‘+ ___ +'”””? –  > Por Ahmed.
  • Acest răspuns este acum depășit. Scripturile PowerShell sunt mai flexibile și pot fi rulate în SQL Server ca un agent de lucru. –  > Por Clinton Ward.

Cu PowerShell puteți rezolva problema în mod ordonat prin introducerea Invoke-Sqlcmd în 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 include SqlServer care conține modulul Invoke-Sqlcmd cmdlet, pe care îl veți avea chiar dacă instalați doar SSMS 2016. Înainte de aceasta, SQL Server 2012 includea vechiul modul SQLPS modul, care schimba directorul curent în SQLSERVER: atunci când modulul a fost utilizat pentru prima dată (printre alte erori), așa că, pentru acesta, va trebui să modificați fișierul #Requires linia de mai sus în:

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

Pentru a adapta exemplul pentru SQL Server 2008 și 2008 R2, eliminați linia #Requires în întregime și folosiți linia utilitarul sqlps.exe în loc de gazda PowerShell standard.

Invoke-Sqlcmd este echivalentul PowerShell al lui sqlcmd.exe. În loc de text, acesta emite System.Data.DataRow obiecte.

Adresa -Query funcționează ca și parametrul -Q parametrul din sqlcmd.exe. Transmiteți-i o interogare SQL care descrie datele pe care doriți să le exportați.

Parametrul -Database funcționează ca și parametrul -d parametrul din sqlcmd.exe. Treceți-i numele bazei de date care conține datele care urmează să fie exportate.

Se afișează -Server funcționează ca și parametrul -S parametrul din sqlcmd.exe. Treceți-i numele serverului care conține datele care urmează să fie exportate.

Export-CSV este un cmdlet PowerShell care serializează obiecte generice în CSV. Este livrat împreună cu PowerShell.

Aplicația -NoTypeInformation suprimă ieșirile suplimentare care nu fac parte din formatul CSV. În mod implicit, cmdlet-ul scrie un antet cu informații despre tip. Acesta vă permite să cunoașteți tipul obiectului atunci când îl deserializați ulterior cu Import-Csv, dar încurcă instrumentele care se așteaptă la CSV standard.

Adresa -Path funcționează ca și parametrul -o parametrul din sqlcmd.exe. O cale completă pentru această valoare este cea mai sigură dacă sunteți blocat folosind vechiul SQLPS vechi.

Adresa -Encoding funcționează ca și parametrul -f sau -u din sqlcmd.exe. În mod implicit, Export-Csv produce numai caractere ASCII și le înlocuiește pe toate celelalte cu semne de întrebare. Utilizați UTF8 în schimb pentru a păstra toate caracterele și a rămâne compatibil cu majoritatea celorlalte instrumente.

Principalul avantaj al acestei soluții față de sqlcmd.exe sau bcp.exe este că nu trebuie să modificați comanda pentru a scoate CSV-uri valide. cmdlet-ul Export-Csv se ocupă de toate acestea pentru dvs.

Principalul dezavantaj este că Invoke-Sqlcmd citește întregul set de rezultate înainte de a-l transmite de-a lungul pipeline-ului. Asigurați-vă că aveți suficientă memorie pentru întregul set de rezultate pe care doriți să îl exportați.

Este posibil să nu funcționeze fără probleme pentru miliarde de rânduri. Dacă aceasta este o problemă, puteți încerca celelalte instrumente sau puteți dezvolta propria versiune eficientă a lui Invoke-Sqlcmd utilizând System.Data.SqlClient.SqlDataReader class.

Comentarii

  • Celelalte răspunsuri sunt sincer nașpa, aceasta este singura modalitate de a o face corect. Mi-aș dori să fie mai evident totuși. –  > Por Shagglez.
  • Pe SQL 2008 R2, a trebuit să execut instrumentul „sqlps.exe” pentru a utiliza Invoke-Sqlcmd. Se pare că am nevoie de SQL 2012 pentru a folosi Import-Module? În orice caz, funcționează în cadrul „sqlps.exe” – consultați acest topic pentru detalii. –  > Por Domnul_Tom.
  • @Mister_Tom bună observație. Modulul SQLPS a fost introdus odată cu SQL 2012. Răspunsul explică acum cum să adaptați exemplul pentru edițiile mai vechi. –  > Por Iain Samuel McLean Elder.
  • @JasonMatney PowerShell este noua interfață administrativă pentru sistemele Windows, dar multe sfaturi pentru SQL Server au fost publicate înainte ca aceasta să devină standard. Răspândiți vorba! 🙂 –  > Por Iain Samuel McLean Elder.
  • Acest răspuns oferă informații utile și o modalitate ALTERNATIVĂ puternică și flexibilă de a rezolva problema, însă nu nu reușește să răspundă complet la întrebarea inițială, așa cum a fost pusă în mod specific. Și eu sunt un fan powershell, dar să nu lăsăm evanghelizarea să se transforme în isterie. SQLCMD nu va dispărea prea curând. –  > Por grătar.
ESV
sqlcmd -S myServer -d myDB -E -o "MyData.txt" ^
    -Q "select bar from foo" ^
    -W -w 999 -s","

Ultima linie conține opțiuni specifice CSV.

  • -W   eliminați spațiile rămase în urmă din fiecare câmp individual
  • -s","   setează separatorul de coloane la virgulă (,)
  • -w 999   stabilește lățimea rândului la 999 de caractere

Răspunsul lui scottm este foarte apropiat de ceea ce folosesc eu, dar găsesc că -W să fie o completare foarte bună: Nu mai trebuie să tai spațiile albe atunci când consum CSV-ul în altă parte.

Consultați, de asemenea, opțiunea Referința MSDN sqlcmd. Acesta pune /? ieșire a opțiunii de rușine.

Comentarii

    19

  • @sims „set nocount on” la începutul fișierului de interogare/inputfile –  > Por d-_-b.
  • Cum pot elimina sublinierea de pe Headers? –  > Por ntombela.
  • @gugulethun : Puteți face o uniune în interogare pentru a pune numele coloanei pe prima linie. –  > Por Nordes.
  • Acest lucru funcționează ca un farmec, dar dacă coloana conține separatorul, obțin un fișier csv corupt… –  > Por Peter.
  • Am adăugat și acest comentariu la răspunsul acceptat, dar… puteți rezolva această problemă cu '""' + col1 + '""' AS col1, înfășurat în ghilimele duble (dublate) sau pur și simplu apelați o procedură stocată. –  > Por MisterIsaak.
john.da.costa

Nu este aceasta bcp a fost menit?

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

Rulați acest lucru din linia de comandă pentru a verifica sintaxa.

bcp /?

De exemplu:

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]

Vă rugăm să rețineți că bcp nu poate emite anteturi de coloană.

A se vedea: Utilitar bcp pagina de documentație.

Exemplu din pagina de mai sus:

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

Comentarii

  • ServerName = YourcomputerNameSQLServerName, numai atunci se execută, altfel eroare –  > Por Hammad Khan.
  • În cazul în care doriți să vedeți documentația completă pentru bcp.exe: technet.microsoft.com/en-us/library/ms162802.aspx –  > Por Robert Bernstein.
  • Ce faceți dacă aveți nevoie să exportați și numele coloanelor ca antet? Există o soluție generică simplă și generică folosind bcp? –  > Por Iain Samuel McLean Elder.
  • @johndacosta mulțumesc foarte mult. Cum ați putea imprima și anteturile coloanelor? Nu văd nicăieri un comutator ușor pentru aceasta. Mulțumesc! –  > Por Rachael.
  • bcp nu include antetul (numele coloanelor), dar este de ~10X mai rapid decât sqlcmd (din experiența mea). Pentru date foarte mari, puteți utiliza bcp pentru a obține datele și să utilizați sqlcmd (select top 0 * from …) pentru a obține antetul și apoi să le combinați. –  > Por YJZ.
Rudism

O notă pentru oricine dorește să facă acest lucru, dar să aibă și antetele coloanelor, aceasta este soluția pe care am folosit-o într-un fișier batch:

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

Aceasta scoate rezultatele inițiale (inclusiv separatorii —-,—- dintre antetele și datele) într-un fișier temporar, apoi elimină linia respectivă prin filtrare prin findstr. Rețineți că nu este perfectă, deoarece filtrează -,--nu va funcționa dacă există doar o singură coloană în rezultat și va filtra și liniile legitime care conțin acest șir.

Comentarii

  • În schimb, utilizați următorul filtru: findstr /r /v ^-[,-]*$ > output.csv Din anumite motive, simle ^[,-]*$ se potrivește cu toate liniile. –  > Por Vladimir Korolev.
  • Puneți întotdeauna un regex cu ^ în interiorul ghilimelelor duble, altfel veți obține rezultate ciudate, deoarece ^ este un caracter de scăpare pentru cmd.exe. Ambele regexuri de mai sus nu funcționează corect, din câte îmi dau seama, dar aceasta funcționează: findstr /r /v „^-[-,]*-.$” (. înainte de $ pare să fie necesar atunci când se testează cu echo, dar s-ar putea să nu fie necesar pentru ieșirea sqlcmd) –  > Por JimG.
  • Există, de asemenea, o problemă cu datele care au 2 spații între dată și oră în loc de unul. Atunci când încercați să deschideți CSV-ul în Excel, acesta apare ca 00:00.0. O modalitate ușoară de a rezolva acest lucru ar fi să căutați și să înlocuiți toate ” ” cu ” ” ” pe loc folosind SED. Comanda de adăugat la scriptul dvs. ar fi: SED -i „s/ / / /g” output.csv. Mai multe despre SED gnuwin32.sourceforge.net/packages/sed.htm –  > Por PollusB.
  • acesta este răspunsul corect, funcționează perfect cu adăugarea lui @JimG. Am folosit punct și virgulă pentru separator și un fișier sql pentru intrare, fișierul conținea partea de noncount on și ansi_warning off, și comutatorul -W pentru eliminarea spațiului –  > Por robotik.
jeffmax

Acest răspuns se bazează pe soluția de la @iain-elder, care funcționează bine, cu excepția cazului bazei de date mari (așa cum a subliniat în soluția sa). Întregul tabel trebuie să încapă în memoria sistemului dvs. și, pentru mine, aceasta nu a fost o opțiune. Bănuiesc că cea mai bună soluție ar fi utilizarea System.Data.SqlClient.SqlDataReader și un serializator CSV personalizat (a se vedea aici pentru un exemplu) sau un alt limbaj cu un driver MS SQL și serializare CSV. În spiritul întrebării inițiale, care probabil căuta o soluție fără dependență, codul PowerShell de mai jos a funcționat pentru mine. Este foarte lent și ineficient, în special în ceea ce privește instanțierea matricei $data și apelarea Export-Csv în modul de adăugare pentru fiecare linie $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?
            [email protected]()
            $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()

arnav

Opțiune alternativă cu BCP:

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

kenorb

De obicei, sqlcmd vine cu bcp utilitate (ca parte din mssql-tools) care exportă în mod implicit în CSV.

Utilizare:

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

De exemplu:

bcp.exe MyTable out data.csv

Pentru a descărca toate tabelele în fișiere CSV corespunzătoare, iată Bash script:

#!/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

Comentarii

Hack-R

Un răspuns de mai sus aproape că a rezolvat-o pentru mine, dar nu creează corect un CSV analizat.

Iată versiunea mea:

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

Cineva care spune că sqlcmd este depășit în favoarea unei alternative PowerShell uită că sqlcmd nu este doar pentru Windows. Eu sunt pe Linux (și când sunt pe Windows evit oricum PS).

Acestea fiind spuse toate astea, eu găsesc bcp mai ușor.

Mohsen Abasi

Din următoarele 2 motive, ar trebui să rulați soluția mea în CMD:

  1. Este posibil să existe ghilimele duble în interogare
  2. Numele de utilizator de conectare & parola este uneori necesară pentru a interoga o instanță SQL Server la distanță

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

Sarel Botha

Puteți face acest lucru într-un mod hackish. Folosiți cu atenție utilizarea sqlcmd hack. Dacă datele au ghilimele duble sau virgule, veți avea probleme.

Puteți folosi un script simplu pentru a face acest lucru în mod corespunzător:

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

Sursa: Scrierea ieșirii SQL în CSV cu VBScript.

Comentarii

  • O explicație pentru downvote ar fi drăguță. Răspunsul meu este corect: nu puteți face acest lucru cu sqlcmd. De asemenea, am oferit o modalitate alternativă de a îndeplini sarcina. –  > Por Sarel Botha.
  • Votul negativ este pentru că este clar că poți face ceea ce OP cere cu sqlcmd. –  > Por Brian Driscoll.
  • @BrianDriscoll, El nu a spus asta. nu se poate face acest lucru cu sqlcmd, noi doar am afirmat faptul că sqlcmd nu scapă în mod corespunzător de virgulă, fiind astfel abia utilizabil pentru orice serios ieșire CSV. –  > Por Sebastian.
  • Chiar am spus asta, dar m-am săturat de downvotes, așa că mi-am editat răspunsul. O să fac editarea mea mai veridică. –  > Por Sarel Botha.