[SQL Server] Rechercher une valeur dans un ensemble de fichiers Excel

  • Imprimer

-- =============================================

-- Author     :   Zouhaier KHARROUBI

-- Create date:   29/09/2016

-- Description:   Rechercher une valeur dans un ensemble de fichiers Excel

-- =============================================

SET NOCOUNT ON;

SET NOCOUNT ON;

SET FMTONLY OFF; -- C'est pour pouvoir afficher le résultat dans un fichier Excel   

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Fichiers_1]'))

      BEGIN

            DROP TABLE #Fichiers_1

      END  

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Fichiers_2]'))

      BEGIN

            DROP TABLE #Fichiers_2

      END

     

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..#Resultat_Final'))

      BEGIN

            DROP TABLE #Resultat_Final

      END  

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..Fichier_Excel_Temp'))

      BEGIN

            DROP TABLE tempdb..Fichier_Excel_Temp

      END

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..#Colonnes'))

      BEGIN

            DROP TABLE #Colonnes

      END

           

CREATE TABLE #Colonnes

(

      Num_Col INT IDENTITY(1,1)

      ,Nom_Col VARCHAR(150)

)

CREATE TABLE #Fichiers_1

(

      Chemin_Fichier VARCHAR(350) NULL

)

CREATE TABLE #Fichiers_2

(

      Numero_Fichier         INT IDENTITY(1,1)

      ,Chemin_Fichier         VARCHAR(350) NULL

      ,Nom_Fichier            VARCHAR(250) NULL

)

DECLARE @Nbre_Col                 AS INT

            ,@Numero_Col            AS INT

            ,@Nom_Col               AS VARCHAR(150)

            ,@Requete_1             AS NVARCHAR(MAX)

            ,@Requete_Select  AS NVARCHAR(MAX)

            ,@NWhere                AS NVARCHAR(250)

            ,@Nbre_Lig              AS INT

            ,@Chemin_Fichier  VARCHAR(350)

            ,@Nom_Fichier           AS NVARCHAR(250)

            ,@Nbre_Fichier          AS INT

            ,@Numero_Fichier  AS INT

            ,@Valeur_Recherche      AS NVARCHAR(250)

SELECT @Valeur_Recherche='mektaba'

INSERT INTO #Fichiers_1

EXEC Master..xp_cmdshellN'DIR "\\Mektaba\Les_Fichiers_Excel\*.xlsx" /B /S'

INSERT INTO #Fichiers_2(Chemin_Fichier,Nom_Fichier)

SELECT Chemin_Fichier,

REVERSE(SUBSTRING(REVERSE(Chemin_Fichier),1,CHARINDEX('\',REVERSE(Chemin_Fichier),1) -1)) Nom_Fichier FROM #Fichiers_1 WHERE ISNULL(Chemin_Fichier,'')<>''

SELECT @Nbre_Fichier   = COUNT(*) FROM #Fichiers_2

SELECT @Numero_Fichier = 0

Debut_TraiTement:

SELECT @Numero_Fichier = @Numero_Fichier + 1

BEGIN TRY

      WHILE @Numero_Fichier <= @Nbre_Fichier

            BEGIN

           

                  TRUNCATE TABLE #Colonnes

                  IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..Fichier_Excel_Temp'))

                        BEGIN

                             DROP TABLE tempdb..Fichier_Excel_Temp

                        END

                  SELECT @Chemin_Fichier  = NULL

                  SELECT @Nom_Fichier          = NULL

                 

                  SELECT @Chemin_Fichier=Chemin_Fichier,@Nom_Fichier=Nom_Fichier FROM #Fichiers_2 WHERE Numero_Fichier = @Numero_Fichier

                                  

                  SELECT @Requete_Select = 'SELECT * INTO tempdb..Fichier_Excel_Temp FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml; HDR=YES; IMEX=1; Database=' + @Chemin_Fichier + ''',[Feuil1$])'

                  EXECUTE sp_executesql@Requete_Select

                 

                 

                  INSERT INTO #Colonnes(Nom_Col)

                  SELECT DISTINCT T2.name FROM tempdb.sys.objects T1

                  JOIN tempdb.sys.columns T2 ON T2.object_id=T1.object_id

                  WHERE T1.name LIKE 'Fichier_Excel_Temp%'

                  SELECT @Nbre_Col  = COUNT(*) FROM #Colonnes

                  SELECT @Numero_Col           = 1

                  SELECT @Requete_Select = 'SELECT '

                  SELECT @Nbre_Lig     = 0

                  SELECT @NWhere             = NULL

                  WHILE @Numero_Col <= @Nbre_Col

                        BEGIN

                             SELECT @Nom_Col = Nom_Col FROM #Colonnes WHERE Num_Col=@Numero_Col

                             SELECT @Requete_1='SELECT @Nbre_LigOUT=COUNT(*) FROM tempdb..Fichier_Excel_Temp WHERE [' + @Nom_Col + ']=''' + @Valeur_Recherche + ''''

                             EXECUTE sp_executesql@Requete_1,N'@Nbre_LigOUT int OUTPUT',@Nbre_LigOUT=@Nbre_Lig OUTPUT

                            

                             IF @Nbre_Lig>0

                                   BEGIN

                                         SELECT @NWhere = ' FROM tempdb..Fichier_Excel_Temp WHERE [' + @Nom_Col + ']=''' + @Valeur_Recherche + ''''

                                   END

                            

                             SELECT @Requete_Select = @Requete_Select + '[' + @Nom_Col + '] AS Champ' + CAST(@Numero_Col AS VARCHAR(25)) + ','

                                        

                             SELECT @Numero_Col      = @Numero_Col + 1

                        END

                  IF ISNULL(@NWhere,'')<>''

                        BEGIN

                             --forcer le quit de la boucle et afficher le résultat

                             SELECT @Numero_Fichier = @Nbre_Fichier + 1

                             SELECT @Requete_Select = @Requete_Select + 'Fichier =''' + @Nom_Fichier + ''',Chemin_Fichier=''' + @Chemin_Fichier + '''' + @NWhere

                             EXECUTE sp_executesql@Requete_Select

                        END

           

           

                  SELECT @Numero_Fichier = @Numero_Fichier + 1

            END

END TRY

BEGIN CATCH

      IF @@ERROR > 0

            SELECT ERROR_MESSAGE()

            GOTO Debut_TraiTement

END CATCH