Come esportare i dati in formato CSV da SQL Server usando sqlcmd?

Posso facilmente scaricare i dati in un file di testo come:

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

Tuttavia, ho esaminato i file di aiuto per SQLCMD ma non ho visto un’opzione specifica per CSV.

C’è un modo per scaricare dati da una tabella in un file di testo CSV usando SQLCMD ?

Puoi eseguire qualcosa come questo:

 sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" -o "MyData.csv" -h-1 -s"," -w 700 
  • -h-1 rimuove le intestazioni dei nomi delle colonne dal risultato
  • -s"," imposta il separatore di colonna su,
  • -w 700 imposta la larghezza della riga su 700 caratteri (questa dovrà essere larga quanto la riga più lunga o verrà spostata alla riga successiva)
 sqlcmd -S myServer -d myDB -E -o "MyData.txt" ^ -Q "select bar from foo" ^ -W -w 999 -s"," 

L’ultima riga contiene opzioni specifiche per CSV.

  • -W rimuove gli spazi finali da ogni singolo campo
  • -s"," imposta il separatore di colonne nella virgola (,)
  • -w 999 imposta la larghezza della riga su 999 caratteri

La risposta di scottm è molto vicina a quello che uso, ma trovo che il -W sia un’aggiunta davvero carina: non ho bisogno di tagliare gli spazi bianchi quando consumo il CSV altrove.

Vedi anche il riferimento sqlcmd MSDN . Mette il /? l’output delle opzioni da vergognare.

Questo non è stato pensato per bcp ?

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

Esegui questo dalla tua riga di comando per verificare la syntax.

 bcp /? 

Per esempio:

 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] 

Si prega di notare che bcp non può emettere intestazioni di colonna.

Vedi: pagina dei documenti di utilità bcp .

Esempio dalla pagina sopra:

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

Con PowerShell puoi risolvere il problema ordinatamente piping Invoke-Sqlcmd in 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 il modulo SqlServer , che contiene il cmdlet Invoke-Sqlcmd , che verrà installato anche se si installa SSMS 2016. Precedentemente, SQL Server 2012 includeva il vecchio modulo SQLPS , che avrebbe modificato la directory corrente in SQLSERVER:\ quando il modulo è stato usato per la prima volta (tra gli altri bug) quindi per questo, dovrai modificare la riga #Requires alto per:

 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 

Per adattare l’esempio per SQL Server 2008 e 2008 R2, rimuovere completamente la riga #Requires e utilizzare l’ utilità sqlps.exe anziché l’host PowerShell standard.

Invoke-Sqlcmd è l’equivalente di PowerShell di sqlcmd.exe. Invece del testo, emette oggetti System.Data.DataRow .

Il parametro -Query funziona come il parametro -Q di sqlcmd.exe. Passa una query SQL che descrive i dati che desideri esportare.

Il parametro -Database funziona come il parametro -d di sqlcmd.exe. Passa il nome del database che contiene i dati da esportare.

Il parametro -Server funziona come il parametro -S di sqlcmd.exe. Passa il nome del server che contiene i dati da esportare.

Export-CSV è un cmdlet di PowerShell che serializza oggetti generici in CSV. Viene fornito con PowerShell.

Il parametro -NoTypeInformation sopprime l’output extra che non fa parte del formato CSV. Per impostazione predefinita, il cmdlet scrive un’intestazione con informazioni sul tipo. Ti consente di conoscere il tipo di object quando lo deserializzi in seguito con Import-Csv , ma confonde gli strumenti che si aspettano CSV standard.

Il parametro -Path funziona come il parametro -o di sqlcmd.exe. Un percorso completo per questo valore è più sicuro se si è bloccati utilizzando il vecchio modulo SQLPS .

Il parametro -Encoding funziona come i parametri -f o -u di sqlcmd.exe. Per impostazione predefinita, Export-Csv emette solo caratteri ASCII e sostituisce tutti gli altri con punti interrogativi. Usa invece UTF8 per conservare tutti i caratteri e rimanere compatibile con la maggior parte degli altri strumenti.

Il vantaggio principale di questa soluzione su sqlcmd.exe o bcp.exe è che non è necessario modificare il comando per generare un CSV valido. Il cmdlet Export-Csv gestisce tutto ciò che fa per te.

Lo svantaggio principale è che Invoke-Sqlcmd legge l’intero set di risultati prima di passarlo lungo la pipeline. Assicurati di avere abbastanza memoria per l’intero set di risultati che desideri esportare.

Potrebbe non funzionare correttamente per miliardi di righe. Se questo è un problema, puoi provare gli altri strumenti o eseguire il rollover della tua versione efficiente di Invoke-Sqlcmd utilizzando la class System.Data.SqlClient.SqlDataReader .

Una nota per chi cerca di fare questo ma ha anche le intestazioni delle colonne, questa è la soluzione che ho usato un file 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 

Questo produce i risultati iniziali (compresi i separatori —-, —- tra intestazioni e dati) in un file temporaneo, quindi rimuove quella linea filtrandola attraverso findstr. Nota che non è perfetto dato che sta filtrando -,- -non funzionerà se c’è solo una colonna nell’output e filtra anche le righe legittime che contengono quella stringa.

Opzione alternativa con BCP:

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

Questa risposta si basa sulla soluzione di @ iain-elder, che funziona bene ad eccezione del caso di database di grandi dimensioni (come indicato nella sua soluzione). L’intera tabella deve adattarsi alla memoria del sistema e per me questa non era un’opzione. Sospetto che la soluzione migliore utilizzi System.Data.SqlClient.SqlDataReader e un serializzatore CSV personalizzato ( vedi qui per un esempio ) o un’altra lingua con un driver MS SQL e serializzazione CSV. Nello spirito della domanda originale che probabilmente stava cercando una soluzione senza dipendenza, il codice di PowerShell di seguito ha funzionato per me. È molto lento e inefficiente soprattutto nell’istanziare l’array $ di dati e chiamare Export-Csv in modalità append per ogni riga $ chunk_size.

 $chunk_size = 10000 $command = New-Object System.Data.SqlClient.SqlCommand $command.CommandText = "SELECT * FROM " $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() 

Solitamente sqlcmd viene fornito con utilità bcp (come parte di mssql-tools ) che esporta in CSV per impostazione predefinita.

Uso:

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

Per esempio:

 bcp.exe MyTable out data.csv 

Per scaricare tutte le tabelle in file CSV corrispondenti, ecco lo script 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 $( $table.desc && \ bcp $table out $table.csv -S $server -U $user -P $pass -d $dbname -c done 

Una risposta sopra quasi lo ha risolto per me ma non crea correttamente un CSV analizzato.

Ecco la mia versione:

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

Qualcuno che afferma che sqlcmd è obsoleto a favore di alcune alternative di PowerShell sta dimenticando che sqlcmd non è solo per Windows. Sono su Linux (e quando su Windows evito PS comunque).

Detto questo, trovo più facile il bcp .

Puoi farlo in un modo hacker. Attento usando l’hack sqlcmd . Se i dati hanno doppie virgolette o virgole, ti troverai nei guai.

Puoi usare uno script semplice per farlo correttamente:

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

Fonte: scrittura dell’output SQL in CSV con VBScript .