Sql server - procedure d'edition d'un fichier plat (export csv)

Soyez le premier à donner votre avis sur cette source.

Vue 28 314 fois - Téléchargée 616 fois

Télécharger le projet
Commenter

Description

I. Introduction
Avec cette exemple trivial, mon intention est de mettre l'accent sur les possibilités offertes par la procédure xp_cmdshell (commandes DOS).
En préalable à l'exécution de l'exemple, il vous faudra activer la commande shell dans la surface d'exposition (cf. fichier joint). Et, naturellement, il faut que le serveur SQL ait un droit d'accès pour l'emplacement spécifié.

II. Caractères diacritiques
A. Caractéres réservés
Les caractères réservés ! ^ & % < > >> " | doivent être précédés par le caractère ^ pour ne pas être assimilés à une commande. La fonction SQL permet de gérer ceux-ci en les préfixant.

B. Caractères de retour à la ligne
Les caractères de retour à la ligne (#13 + #10) devront être supprimés ou remplacés par le caractère d'espacement. La fonction SQL assure également cette tâche.

C. Caractères accentués
Du fait de la redirection, les caractères accentués seront codés en MS-DOS (OEM). Il faudra donc les convertir sous WINDOWS (ANSI). Comme le montre la saisie d'écran, la méthode la plus simple consiste à utiliser le convertisseur du traitement de texte WORD. Si besoin, la macro en VBA ci-dessous vous permettra d'automatiser ce traitemenent sous WORD (voir variantes en commentaire).

Sub OemToAnsi(sFilePath As String, sFileName As String)
'Emplacement du fichier
ChangeFileOpenDirectory sFilePath
'Ouvrir le fichier en OEM, l'enregistrer en ANSI et le fermer
Documents.Open FileName:=sFileName, Encoding:=850
With ActiveDocument
.SaveAs FileName:=sFileName, Encoding:=1252
.Close
End With
End Sub

Source / Exemple : 


CREATE FUNCTION [dbo].[fctTranslateWordsDOS] (@EXP VARCHAR (8000))
RETURNS VARCHAR (8000)
AS 
-- Préfixe les huit caractères réservés de MS-DOS par l'accent circonflexe et
-- supprime ou remplace les caractères de retour à la ligne par un espace.
-- Exemples d'utilisation :
    -- 1. Mots réservés
        -- SELECT [dbo].[fctTranslateWordsDOS]('start ! " % & < > ^ | end')
    -- 2. Caractères de retour à la ligne
        -- SELECT [dbo].[fctTranslateWordsDOS](char(13) + char(10) + 'end')
        -- SELECT [dbo].[fctTranslateWordsDOS]('start' + char(13) + char(10) + 'end')
        -- SELECT [dbo].[fctTranslateWordsDOS]('start' + char(13) + char(10))
BEGIN
    DECLARE @WORD VARCHAR(1), @RETVAL VARCHAR(8000)
     IF (@EXP IS NULL) RETURN NULL
    SET @RETVAL=''  
    WHILE LEN(@EXP)>0
    BEGIN
        SET @WORD= SUBSTRING(@EXP,1, 1)
        -- Préfixe les mots réservés
        IF ((@WORD='!') OR (@WORD='"') OR (@WORD='%') OR (@WORD='&') OR (@WORD='<') OR (@WORD='>') OR (@WORD='^') OR (@WORD='|')) 
            SET @RETVAL =@RETVAL + '^' + SUBSTRING(@EXP, 1, 1)
        -- Remplacement conditionnel avec gestion des bornes
        ELSE IF @WORD=CHAR(13) AND ISNULL(RIGHT(@RETVAL, 1), ' ')<>' ' AND @EXP<>CHAR(10)
                SET @RETVAL = @RETVAL	+ ' '
        -- Suppression des caractères de retour à la ligne
        ELSE IF ASCII(@WORD)<>13 AND ASCII(@WORD)<>10
            SET @RETVAL =@RETVAL + SUBSTRING(@EXP, 1, 1) 
        SET @EXP= SUBSTRING(@EXP, 2, LEN(@EXP)-1)
    END -- END WHILE
    RETURN @RETVAL
END -- END FCT
GO

CREATE PROCEDURE [dbo].[SPR_SHELL_EDIT] (@FullPath VARCHAR(260), @DelimitedText VARCHAR(7730), @isCleaned BIT) AS
/* Edition d'un fichier plat (CSV) à partir d'une commande DOS -----------------
	  -- Exemples d'utilisation :
	  -- Edition sans suppression du contenu
	  DECLARE @Emplacement VARCHAR (260)
	  -- Emplacement du type \\serveur\partage\chemin\nom_fichier
	  SET @Emplacement= '<chemin d'accès à saisir>\Test.csv'
	  EXEC SPR_SHELL_EDIT @Emplacement, 'bijou;caillou;chou;genou;hibou;joujou;pou', 0
	  -- Edition avec suppression du contenu
	  EXEC SPR_SHELL_EDIT @Emplacement, 'bijou;caillou;chou;genou;hibou;joujou;pou', 1 
------------------------------------------------------------------------------ */
BEGIN
    DECLARE @Cmd VARCHAR(8000);

    IF @isCleaned=1 
         -- Supprimer le contenu existant puis ajouter la ligne de texte
        SET @Cmd = 'Echo ' + [dbo].[fctTranslateWordsDOS](@DelimitedText) + ' > ' + @FullPath;
    ELSE
        -- Ajouter la ligne de texte sans supprimer le contenu existant
        SET @Cmd = 'Echo ' + [dbo].[fctTranslateWordsDOS](@DelimitedText) + ' >> ' + @FullPath;

    EXEC xp_cmdshell @Cmd, NO_OUTPUT;
END

Conclusion :


Voir l'exemple concret en pièce jointe sur la base AdventureWorks.
L'argument NO_OUTPUT est facultatif. Supprimez le pour afficher un message d'erreur éventuel du genre « Accès refusé ».
Télécharger le projet

Codes Sources

A voir également

Ajouter un commentaire

Commentaires

Signaler
FENETRES
Messages postés
205
Date d'inscription
jeudi 15 juillet 2004
Statut
Membre
Dernière intervention
14 avril 2009
 -
Si l’exemple parait simple, sachez que grâce à cette solution, nous avons, en imbriquant des curseurs, construit une seule procédure d’export pour plus de 50 fichiers !
Signaler
Alighieri76
Messages postés
8
Date d'inscription
lundi 27 novembre 2006
Statut
Membre
Dernière intervention
23 novembre 2007
 -
Merci beaucoup pour cette source.Je ve étudier ca de prés. Je te met 8/10 car ca peut être appronfondie
Signaler
FENETRES
Messages postés
205
Date d'inscription
jeudi 15 juillet 2004
Statut
Membre
Dernière intervention
14 avril 2009
 -
L'export SQL vers un fichier CSV n'est qu'un exemple parmi tant de possibilités qu’il est tout à fait regrettable à mon avis que les commandes DOS ne soient pas davantage utilisées dans les scripts SQL.
Sinon, pourquoi faire simple quand on peut faire compliqué ?
Signaler
FENETRES
Messages postés
205
Date d'inscription
jeudi 15 juillet 2004
Statut
Membre
Dernière intervention
14 avril 2009
 -
Script d'activation de la commande shell :

EXEC sp_configure 'xp_cmdshell' , 1
GO
-- Appliquer la modification de configuration
RECONFIGURE
GO
Signaler
cs_bir
Messages postés
9
Date d'inscription
lundi 23 juin 2003
Statut
Membre
Dernière intervention
15 mars 2009
 -
bonjour, pas mal ton code , mais si tu pouvez donner un exemple avec une requête sql en paramètre ce serai mieux
Afficher les 19 commentaires
S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (FENETRES)