IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Export requête vers CSV


Sujet :

Développement SQL Server

  1. #1
    Membre confirmé
    Profil pro
    Étudiant
    Inscrit en
    Avril 2008
    Messages
    89
    Détails du profil
    Informations personnelles :
    Âge : 40
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2008
    Messages : 89
    Par défaut Export requête vers CSV
    Bonjour ,
    Je souhaite exporter le résultat d’une requête dans un fichier csv.
    La requête est :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    select text ,last_execution_time, max_worker_time/1000000  as [temps en seconde], execution_count as nombre_d_execution From  sys.dm_exec_query_stats AS qs  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  where max_worker_time > 1000000 Order By  last_execution_time desc
    Elle me permet de récupérer les requêtes et procédure stocké ayant mis plus d’une seconde à s’exécuter ainsi que le nombre de fois qu’elles ont été exécuté et la date de la dernière exécution.
    Cependant je rencontre un problème pour l’exporter en CSV.
    J’ai essayé avec la commande bcp , osql mais j’ai problème de mise en page .
    Dans l’analyseur de requête j’ai bien à chaque ligne : la requete ou PS , le temps d’exécution , la date et le nombre d’exécution.
    Dans mon CSV à cause de la requête ou PS je n’ai plus une ligne par enregistrement.
    J’ai tenté avec un package SSIS mais toujours sans résultat, j’ai toujours ce problème de délimiteur.
    Avez-vous une idée ?
    Cordialement,

  2. #2
    Membre Expert

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    Mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2010
    Messages : 1 278
    Par défaut
    Tu peux d'abord stocker le résultat de la requête dans une table temporaire puis faire l'export après.
    Etienne ZINZINDOHOUE
    Billets-Articles

  3. #3
    Membre confirmé
    Profil pro
    Étudiant
    Inscrit en
    Avril 2008
    Messages
    89
    Détails du profil
    Informations personnelles :
    Âge : 40
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2008
    Messages : 89
    Par défaut
    Re,
    j'ai essayé de passer par une table temporaire mais sans résultat :/.
    je pense que le problème vient du faite que je ne trouve ou ne peut pas avoir de délimiteur pour les colonnesdans mon CSV étant donnée qu'il y a tout type de caractère dans ma première colonne sous SQL Server

    De ce faite pour une une requête ou procédure stocké , il va me la mettre dans plusieurs colonne ou ligne dans Excel.

    Cordialement,

  4. #4
    Membre confirmé
    Profil pro
    Étudiant
    Inscrit en
    Avril 2008
    Messages
    89
    Détails du profil
    Informations personnelles :
    Âge : 40
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2008
    Messages : 89
    Par défaut
    avec un petit screen ça sera peu être plus parlant :
    requête vu dans l’analyseur



    Requete obtenu dans Excel


    Cordialement,

  5. #5
    Membre Expert

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2007
    Messages
    1 216
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : Août 2007
    Messages : 1 216
    Par défaut
    Vous pouvez le faire en powershell:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Invoke-Sqlcmd -ServerInstance "serverName\InstanceName" -Database Master -Query "SELECT text ,last_execution_time, max_worker_time/1000000  AS [temps en seconde], execution_count AS nombre_d_execution FROM  sys.dm_exec_query_stats AS qs  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  WHERE max_worker_time > 1000000 ORDER BY  last_execution_time DESC" | export-csv "c:\temp\extract_ps.csv"

  6. #6
    Membre Expert

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    Mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2010
    Messages : 1 278
    Par défaut
    Peux-tu montrer comment tu fais l'export pour générer le fichier CSV ?
    Par exemple pour la partie BCP.exe
    Etienne ZINZINDOHOUE
    Billets-Articles

  7. #7
    Membre confirmé
    Profil pro
    Étudiant
    Inscrit en
    Avril 2008
    Messages
    89
    Détails du profil
    Informations personnelles :
    Âge : 40
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2008
    Messages : 89
    Par défaut
    Re,
    merci Ptit_Dje mais la j'ai tous dans une seul colonne et il me manque des infos.

    zinzineti :
    Pour BCP
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    bcp "select * from tempdb.dbo.Extract" queryout "chemin\fichier.csv" -T -c
    j'ai éssayé avec -s est de trouvé un séparateur sans résultat

    Pour la commande osql j'ai éssayé
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    osql -S 127.0.0.1 -E -i d:\taches\requete.sql -n -o d:\taches\trace_log.csv
    Cordialement,

  8. #8
    Membre Expert

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2007
    Messages
    1 216
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : Août 2007
    Messages : 1 216
    Par défaut
    Comment tout dans une seule colonne ?
    Quelles informations manquent?

  9. #9
    Membre confirmé
    Profil pro
    Étudiant
    Inscrit en
    Avril 2008
    Messages
    89
    Détails du profil
    Informations personnelles :
    Âge : 40
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2008
    Messages : 89
    Par défaut
    Bonjour,
    Ci-joint le résultat obtenue :



    je pense que je vais plus passé par une trace via le profiler.
    je vous tiens informé .
    Cordialement,

  10. #10
    Membre Expert

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2007
    Messages
    1 216
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : Août 2007
    Messages : 1 216
    Par défaut
    Hello,

    Effectivement vous avez tout en une seule colonne.
    Si vous selectionnez votre colonne excel et faites:
    - (Menu) Data
    - Text to columns

    Le test realise chez moi montre bien les 4 colonnes (par default - surement du aux regional options et comment les delimiteurs sont configures dans excel).
    Le texte de la procedure tient en 1 seule cellule. Lorsque vous selectionnez celle-ci, elle se deploye.

    EDIT:
    Vous pouvez aussi utiliser le parametre -UseCulture qui devrait creer un csv se basant sur vos regional settings:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    Invoke-Sqlcmd -ServerInstance "serverName\InstanceName" -DATABASE Master -Query "SELECT text ,last_execution_time, max_worker_time/1000000  AS [temps en seconde], execution_count AS nombre_d_execution FROM  sys.dm_exec_query_stats AS qs  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  WHERE max_worker_time > 1000000 ORDER BY  last_execution_time DESC" | export-csv "c:\temp\extract_ps.csv" -UseCulture -NoTypeInformation
    J'ai aussi rajoute a la commande le parametre -NoTypeInformation qui vous permet de ne pas rajouter le type de donnee exportee (pas utile si vous ne re-importez pas vos donnees avec import-csv par la suite).

  11. #11
    Membre confirmé
    Profil pro
    Étudiant
    Inscrit en
    Avril 2008
    Messages
    89
    Détails du profil
    Informations personnelles :
    Âge : 40
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2008
    Messages : 89
    Par défaut
    Bonjour,
    je pense que la commande Export-Csv ne pourras pas fonctionné pour mon cas étant donnée que dans ma première colonne j'ai des requête et procédure stocké donc des virgules et tout un tas d'autre caractère.

    J'ai mis en place une technique à l'aide de sql profiler.

    je récupère un fichier de trace que j'envois directement au client (l’objectif était de récupérer toutes les requêtes et PS s'exécutant en plus d'une seconde et de lui envoyer quotidiennement).

    Tous les soir je stop la trace en cours , je l'envoie par mail ensuite je supprime le fichier et re-créer la trace.

    Merci à vous de m'avoir aider je retiendrai certainement la commande powershell pour d'autre cas

    Cordialement,

  12. #12
    Membre Expert

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2007
    Messages
    1 216
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : Août 2007
    Messages : 1 216
    Par défaut
    Citation Envoyé par krousty Voir le message
    je pense que la commande Export-Csv ne pourras pas fonctionné pour mon cas étant donnée que dans ma première colonne j'ai des requête et procédure stocké donc des virgules et tout un tas d'autre caractère.
    En complement de penser, testez...
    Pour moi ca fonctionne et les stored procedures sur les serveurs contiennent egalement toute sorte de caracteres.

    Citation Envoyé par krousty Voir le message
    J'ai mis en place une technique à l'aide de sql profiler.

    je récupère un fichier de trace que j'envois directement au client (l’objectif était de récupérer toutes les requêtes et PS s'exécutant en plus d'une seconde et de lui envoyer quotidiennement).
    Effectivement il y a toujours plusieures solutions a un probleme.

    Citation Envoyé par krousty Voir le message
    Merci à vous de m'avoir aider je retiendrai certainement la commande powershell pour d'autre cas
    Pas de soucis. Pouvez-vous fermer le post en resolu ?
    Merci.

  13. #13
    Membre confirmé
    Profil pro
    Étudiant
    Inscrit en
    Avril 2008
    Messages
    89
    Détails du profil
    Informations personnelles :
    Âge : 40
    Localisation : France, Somme (Picardie)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2008
    Messages : 89
    Par défaut
    Re,

    Citation Envoyé par Ptit_Dje Voir le message
    En complement de penser, testez...
    Pour moi ca fonctionne et les stored procedures sur les serveurs contiennent egalement toute sorte de caracteres.
    .
    je serais amener a re-tester je pense d'ici quelque temps mais actuellement je n'aurais pas le temps :/

    Ok pour la clôture.

    Cordialement,

  14. #14
    Membre Expert

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    Mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2010
    Messages : 1 278
    Par défaut
    solution avec BCP.exe

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    bcp "SELECT replace(replace(replace(Text,CHAR(13),' '),CHAR(10),' '),CHAR(9),' '),last_execution_time, max_worker_time/1000000  AS [temps en seconde], execution_count AS nombre_d_execution FROM  sys.dm_exec_query_stats AS qs  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  WHERE max_worker_time > 1000000 ORDER BY  last_execution_time DESC " queryout "c:\fichier.xls" -T -c
    Etienne ZINZINDOHOUE
    Billets-Articles

  15. #15
    Membre Expert

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    Mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2010
    Messages : 1 278
    Par défaut
    Tu peux utiliser une procédure générique pour faire ce traitement.
    Voici une proposition avec BCP.exe

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
     
     
    --> Creér la procédure d'export des données de la table vers le fichier Excel
    CREATE PROCEDURE EXPORT_Table_To_Excel
    @RepDestination NVARCHAR(255)-- repertoire de destination des fichiers de sorties
    ,@NomFichierExport  NVARCHAR(255) -- Nom du fichier Excel exporté
    ,@NomInstanceSQLServer NVARCHAR(255)  -- Nom de mon instance SQL SERVER,
    ,@Login NVARCHAR(255) -- login de l'instance
    ,@Pwd NVARCHAR(255)  -- mot de passe 
    ,@NomTableSource SYSNAME  -- Nom de la table à exporter.
     
    /* Exemple d'utilisation : EXPORT_Table_To_Excel N'C:\',N'FichierExport.xls',N'MonInstanceSQL',N'monLogin', N'monMotDePasse',N'MaBase.dbo.TMP2' */
    AS
    BEGIN
    SET NOCOUNT ON
    --> Tester l'existence du repertoire de destination
    DECLARE     @t_RepDestination TABLE
    (
    file_exists bit
    , file_is_a_directory bit
    , parent_directory_exists bit
    )
    INSERT  @t_RepDestination
    EXEC  xp_fileexist @RepDestination
    IF EXISTS
    (
    SELECT      *
    FROM  @t_RepDestination
    WHERE file_is_a_directory = 0
    )
    BEGIN
    RAISERROR('Le repertoire "%s" n''existe pas .', 16, 1, @RepDestination)
    RETURN
    END
    --> Traitement pour l'export
    DECLARE @FullNomFichier NVARCHAR(255) -- Nom absolu du fichier de sorti (@RepDestination + @NomFichierExport)
    DECLARE @bcp_cmd VARCHAR(1024)
    DECLARE @_export_fichier_temp_entete VARCHAR(1024) -- pour pré-formatter l'entete du fichier export
    DECLARE @_export_fichier_temp_data VARCHAR(1024) -- préparer lexport des données
    DECLARE @append VARCHAR(1024) -- pour ajouter les données dans le fichier de sorti
    DECLARE @del VARCHAR(1024)  -- pour supprimer les fichiers temporaires
    SET @FullNomFichier = @RepDestination + @NomFichierExport
    SET @_export_fichier_temp_entete = 'c:\temp\entete'+ REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), GETDATE(), 121),'.','_'),':','_'),'-','_'),' ','_')  +'.xls'
    SET @_export_fichier_temp_data = 'c:\temp\data'+ REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), GETDATE(), 121),'.','_'),':','_'),'-','_'),' ','_')  +'.xls'
     
    BEGIN TRY
    -- Copie de l'entete du fichier dans le fichier de sortie
    SET @bcp_cmd = 'bcp "SELECT ''[Text]'',''[last_execution_time]'',''[temps_en_seconde]'',''[nombre_d_execution]'' " QUERYOUT "' + @_export_fichier_temp_entete + '" -c -S "' + '" -U "'+@Login + '" -P "'+ @Pwd   
    --PRINT     @bcp_cmd
    EXEC xp_cmdshell @bcp_cmd
     
    SET @append = 'type ' + @_export_fichier_temp_entete + ' >> ' +  @FullNomFichier
    --PRINT @append
    EXEC master..xp_cmdshell @append
     
    -- Copie des données dans le fichier de sortie
    SET @bcp_cmd = 'bcp " select replace(replace(replace(Text,CHAR(13),'' ''),CHAR(10),'' ''),CHAR(9),'' ''),last_execution_time,temps_en_seconde,nombre_d_execution from ' + @NomTableSource + ' " QUERYOUT "' + @_export_fichier_temp_data + '" -c -S "' + '" -U "'+@Login + '" -P "'+ @Pwd   
    --PRINT @bcp_cmd
    EXEC xp_cmdshell @bcp_cmd
     
    SET @append = 'type ' + @_export_fichier_temp_data + ' >> ' +  @FullNomFichier
    --PRINT @append
    EXEC master..xp_cmdshell @append
     
    --Suppression des fichiers temporaires
    SET @del  = 'del "' + @_export_fichier_temp_data + '"'
    EXEC master..xp_cmdshell @del
    SET @del  = 'del "' + @_export_fichier_temp_entete + '"'
    EXEC master..xp_cmdshell @del
    END TRY
    -- Gestion des erreurs
    BEGIN CATCH
     SELECT 'Echec de l''export  '
    END CATCH;
    END
     
    --> Pour tester la procédure
    -->  Créer la table temporaire TMP2
    IF OBJECT_ID('TMP2') IS NOT NULL DROP TABLE TMP2 
    CREATE TABLE dbo.TMP2(
    	[text] nvarchar(max) NULL,
    	last_execution_time datetime NOT NULL,
    	temps_en_seconde bigint NULL,
    	nombre_d_execution bigint NOT NULL
    ) 
     
    --> Chargement des données dans la table temporaire TMP2
    insert into TMP2 (text,last_execution_time,temps_en_seconde,nombre_d_execution)
    SELECT text,last_execution_time, max_worker_time/1000000, execution_count FROM  sys.dm_exec_query_stats AS qs  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  WHERE max_worker_time > 1000000 ORDER BY  last_execution_time DESC
     
    --> Utilisation de la procédure stockée pour faire l'export dans un fichier Excel
    EXPORT_Table_To_Excel N'C:\',N'FichierExport.xls',N'MonInstanceSQL',N'monLogin', N'monMotDePasse',N'MaBase.dbo.TMP2'
    Etienne ZINZINDOHOUE
    Billets-Articles

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Export oracle vers csv
    Par MikeM dans le forum SQL
    Réponses: 5
    Dernier message: 11/02/2008, 14h11
  2. Exporter requête vers un fichier *.csv
    Par cédoula dans le forum Requêtes et SQL.
    Réponses: 0
    Dernier message: 14/01/2008, 17h37
  3. Exportation requête vers Excel dans un classeur ouvert
    Par sophiesallee dans le forum Requêtes et SQL.
    Réponses: 3
    Dernier message: 03/09/2007, 14h54
  4. [SQL] exportation php vers csv
    Par yveslens dans le forum PHP & Base de données
    Réponses: 8
    Dernier message: 10/02/2007, 07h06
  5. Réponses: 1
    Dernier message: 01/11/2005, 12h04

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo