Comment exporter des données au format CSV à partir de SQL Server en utilisant sqlcmd ?

Question

Je peux très facilement transférer des données dans un fichier texte, par exemple :

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

Cependant, j'ai regardé les fichiers d'aide pour SQLCMD mais je n'ai pas vu d'option spécifique pour le CSV.

Existe-t-il un moyen de vider les données d'une table dans un fichier texte CSV en utilisant la fonction SQLCMD ?

Answer 1

Vous pouvez exécuter quelque chose comme ça :

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

• -h-1 supprime les en-têtes de nom de colonne du résultat

• -s"," définit le séparateur de colonne à ,

• -w 700 fixe la largeur de la ligne à 700 caractères (cette largeur doit être égale à celle de la ligne la plus longue, sinon la ligne suivante sera ignorée).

Answer 2

Avec PowerShell, vous pouvez résoudre le problème de façon très simple en intégrant Invoke-Sqlcmd dans 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 inclut la SqlServer qui contient le module Invoke-Sqlcmd cmdlet, que vous aurez même si vous installez SSMS 2016. Avant cela, SQL Server 2012 incluait l'ancienne commande SQLPS module ce qui changerait le répertoire actuel en SQLSERVER:\ lorsque le module a été utilisé pour la première fois (parmi d'autres bogues), donc pour cela, vous devrez modifier le fichier #Requires ligne ci-dessus à :

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

Pour adapter l'exemple à SQL Server 2008 et 2008 R2, supprimez la balise #Requires entièrement et utiliser l'option Utilitaire sqlps.exe au lieu de l'hôte PowerShell standard.

Invoke-Sqlcmd est l'équivalent PowerShell de sqlcmd.exe. Au lieu d'un texte, il affiche System.Data.DataRow objets.

Le site -Query fonctionne comme le paramètre -Q de sqlcmd.exe. Passez-lui une requête SQL qui décrit les données que vous voulez exporter.

Le site -Database fonctionne comme le paramètre -d de sqlcmd.exe. Passez-lui le nom de la base de données qui contient les données à exporter.

Le site -Server fonctionne comme le paramètre -S du paramètre sqlcmd.exe. Passez-lui le nom du serveur qui contient les données à exporter.

Export-CSV est une cmdlet PowerShell qui sérialise les objets génériques au format CSV. Elle est fournie avec PowerShell.

Le site -NoTypeInformation supprime la sortie supplémentaire qui ne fait pas partie du format CSV. Par défaut, la cmdlet écrit un en-tête avec des informations sur le type. Cela vous permet de connaître le type de l'objet lorsque vous le désérialisez ultérieurement avec la commande Import-Csv mais cela perturbe les outils qui s'attendent à un CSV standard.

Le site -Path fonctionne comme le paramètre -o de sqlcmd.exe. Un chemin complet pour cette valeur est plus sûr si vous êtes coincé en utilisant l'ancienne version de SQLPS module.

Le site -Encoding fonctionne comme le paramètre -f ou -u des paramètres de sqlcmd.exe. Par défaut, Export-Csv ne produit que des caractères ASCII et remplace tous les autres par des points d'interrogation. Utilisez plutôt UTF8 pour préserver tous les caractères et rester compatible avec la plupart des autres outils.

Le principal avantage de cette solution par rapport à sqlcmd.exe ou bcp.exe est que vous n'avez pas à modifier la commande pour obtenir un CSV valide. La cmdlet Export-Csv s'occupe de tout pour vous.

Le principal inconvénient est que Invoke-Sqlcmd lit l'ensemble des résultats avant de les transmettre au pipeline. Assurez-vous que vous disposez de suffisamment de mémoire pour l'ensemble des résultats que vous souhaitez exporter.

Il se peut que cela ne fonctionne pas sans problème pour des milliards de rangs. Si c'est un problème, vous pouvez essayer les autres outils, ou créer votre propre version efficace de Invoke-Sqlcmd en utilisant Système.Data.SqlClient.SqlDataReader classe.

Answer 3

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

La dernière ligne contient des options spécifiques au CSV.

• -W   supprimer les espaces de fin de chaque champ individuel
• -s","   fixe le séparateur de colonne à la virgule (,)
• -w 999   fixe la largeur de la ligne à 999 caractères

La réponse de scottm est très proche de ce que j'utilise, mais je trouve que les -W est un ajout très appréciable : Je n'ai pas besoin de couper les espaces blancs lorsque je consomme le CSV ailleurs.

Voir également le Référence MSDN sqlcmd . Il met le /? Le résultat de l'option est une honte.

Answer 4

N'est-ce pas bcp était destiné ?

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

Exécutez ceci depuis votre ligne de commande pour vérifier la syntaxe.

bcp /?

Par exemple :

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]

Veuillez noter que bcp ne peut pas sortir les en-têtes de colonne.

Voir : bcp d'utilitaires page de documentation.

Exemple de la page ci-dessus :

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

Answer 5

Une note pour tous ceux qui veulent faire cela mais aussi avoir les en-têtes de colonne, voici la solution que j'ai utilisée dans un fichier 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

Cette méthode produit les résultats initiaux (y compris les séparateurs ----,---- entre les en-têtes et les données) dans un fichier temporaire, puis supprime cette ligne en la filtrant par findstr. Notez que ce n'est pas parfait puisqu'il filtre les lignes suivantes -,- -cela ne fonctionnera pas s'il n'y a qu'une seule colonne dans la sortie, et cela filtrera également les lignes légitimes qui contiennent cette chaîne.