[SQL Server] Importer dans une table unique le contenu de plusieurs classeurs de plusieurs fichiers Excel

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

-- Author     :   Zouhaier KHARROUBI

-- Create date:   25/10/2016

-- Description:   Insérer dans une table unique le contenu de plusieurs fichiers Excel

-- Les noms des classeurs doivent être en alphanumérique.

-- Le nom de classeur en numérique est une source d'anomalie. Excel entoure le nom du classeur avec des simples cotes.

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

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

   -- Insert statements for procedure here   

SET NOCOUNT ON;

SET FMTONLY OFF;

/*Script de création la table finale du résultat*/

--CREATE TABLE [dbo].[SOURCE_EXCEL](

--   [Champ1] [nvarchar](255) NULL,

--   [Champ2] [nvarchar](255) NULL,

--   [Champ3] [nvarchar](255) NULL,

--   [Champ4] [nvarchar](255) NULL,

--   [Champ5] [nvarchar](255) NULL,

--   [Champ6] [nvarchar](255) NULL,

--   [Champ7] [nvarchar](255) NULL,

--   [Champ8] [nvarchar](255) NULL,

--   [Champ9] [nvarchar](255) NULL,

--   [Champ10] [nvarchar](255) NULL,

--   [Champ11] [nvarchar](255) NULL,

--   [Champ12] [nvarchar](255) NULL,

--   [Champ13] [nvarchar](255) NULL,

--   [Champ14] [nvarchar](255) NULL,

--   [Champ15] [nvarchar](255) NULL,

--   [Champ16] [nvarchar](255) NULL,

--   [Champ17] [nvarchar](255) NULL,

--   [Champ18] [nvarchar](255) NULL,

--   [Champ19] [nvarchar](255) NULL,

--   [Champ20] [nvarchar](255) NULL,

--   [Champ21] [nvarchar](255) NULL,

--   [Champ22] [nvarchar](255) NULL,

--   [Champ23] [nvarchar](255) NULL,

--   [Champ24] [nvarchar](255) NULL,

--   [Champ25] [nvarchar](255) NULL,

--   [NbreColonnes] [nvarchar](255) NULL,

--   [Nom_Fichier] [nvarchar](255) NULL,

--   [Nom_Classeur] [nvarchar](255) NULL,

--   [Chemin_Fichier] [nvarchar](255) NULL,

--   [Info_Modification] [nvarchar](255) NULL,

--   STATUT_LIG TINYINT DEFAULT (0),

--   Matricule [nvarchar](255) NULL,

--   Nom [nvarchar](255) NULL,

--   Prenom [nvarchar](255) NULL

--) ON [MEKTABA_DATA]

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]..[#Fichiers_3]'))

      BEGIN

            DROP TABLE #Fichiers_3

      END

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

      BEGIN

            DROP TABLE #Fichiers_4

      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

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

      BEGIN

            DROP TABLE tempdb..SAS_SOURCE_EXCEL

      END

           

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

      BEGIN

            DROP TABLE #Liste_Classeurs_Excel

      END

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

      BEGIN

            DROP TABLE #Liste_Classeurs_Excel_Temp

      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

      ,Info_Modification      VARCHAR(250) NULL

)

CREATE TABLE #Fichiers_3

(

      Numero_Fichier         INT IDENTITY(1,1)

      ,Chemin_Fichier         VARCHAR(350) NULL

      ,Nom_Fichier            VARCHAR(250) NULL

      ,Info_Modification      VARCHAR(250) NULL

)

CREATE TABLE #Fichiers_4

(

      Info_Modification VARCHAR(250) NULL

)

CREATE TABLE #Liste_Classeurs_Excel_Temp

(

TABLE_CAT         VARCHAR(255) NULL

,TABLE_SCHEM      VARCHAR(255) NULL

,TABLE_NAME       VARCHAR(255) NULL

,TABLE_TYPE       VARCHAR(255) NULL

,REMARKS          VARCHAR(255) NULL

)

CREATE TABLE #Liste_Classeurs_Excel

(

Num_Classeur     INT IDENTITY (1,1)

,TABLE_CAT        VARCHAR(255) NULL

,TABLE_SCHEM      VARCHAR(255) NULL

,TABLE_NAME       VARCHAR(255) NULL

,TABLE_TYPE       VARCHAR(255) NULL

,REMARKS          VARCHAR(255) NULL

)

CREATE TABLE tempdb..SAS_SOURCE_EXCEL(

      Num_Lig bigint IDENTITY(1,1) NOT NULL,

      Champ1 nvarchar(255) NULL,

      Champ2 nvarchar(255) NULL,

      Champ3 nvarchar(255) NULL,

      Champ4 nvarchar(255) NULL,

      Champ5 nvarchar(255) NULL,

      Champ6 nvarchar(255) NULL,

      Champ7 nvarchar(255) NULL,

      Champ8 nvarchar(255) NULL,

      Champ9 nvarchar(255) NULL,

      Champ10 nvarchar(255) NULL,

      Champ11 nvarchar(255) NULL,

      Champ12 nvarchar(255) NULL,

      Champ13 nvarchar(255) NULL,

      Champ14 nvarchar(255) NULL,

      Champ15 nvarchar(255) NULL,

      Champ16 nvarchar(255) NULL,

      Champ17 nvarchar(255) NULL,

      Champ18 nvarchar(255) NULL,

      Champ19 nvarchar(255) NULL,

      Champ20 nvarchar(255) NULL,

      Champ21 nvarchar(255) NULL,

      Champ22 nvarchar(255) NULL,

      Champ23 nvarchar(255) NULL,

      Champ24 nvarchar(255) NULL,

      Champ25 nvarchar(255) NULL,

      Nbre_Colonnes int NULL,

      Nbre_Lignes int NULL,

      Nom_Fichier nvarchar(255) NULL,

      Nom_Classeur nvarchar(255) NULL,

      Chemin_Fichier nvarchar(255) NULL,

      Info_Modification nvarchar(255) NULL,

      [STATUT_LIG] [tinyint] NULL DEFAULT 0

)

DECLARE @Nbre_Col                       AS INT

            ,@Numero_Col                 AS INT

            ,@Nom_Col                    AS VARCHAR(150)

            ,@Requete                    AS NVARCHAR(MAX)

           
,@Requete_Select_1           AS NVARCHAR(MAX)

            ,@Requete_Select_2           AS NVARCHAR(MAX)

            ,@NWhere                     AS NVARCHAR(MAX)       

            ,@Nbre_Lig                   AS INT

            ,@Chemin_Fichier        AS VARCHAR(350)

            ,@Nom_Fichier                AS NVARCHAR(250)

            ,@Nbre_Fichier               AS INT

            ,@Numero_Fichier        AS INT

            ,@Info_Modification          AS VARCHAR(250)

            ,@Serveur_Lie_Excel          AS VARCHAR(250)

            ,@Nom_Classeur_Excel    AS VARCHAR(50)

            ,@Num_Classeur               AS INT

            ,@Nbre_Classeur              AS INT

            ,@Nbre_Col_Classeur          AS INT

           

SELECT @Serveur_Lie_Excel ='MonServeurLieExcel'

--Récuperer la liste des fichiers Excel avec leurs chemins sur le disque

INSERT INTO #Fichiers_1

EXEC Master..xp_cmdshellN'DIR "\\MEKTABA\Mes_FichiersExcel\*.xlsx" /B /S'

--Récuperer la liste des fichiers avec leurs dates de modifications

INSERT INTO #Fichiers_4

EXEC Master..xp_cmdshellN'DIR "\\MEKTABA\Mes_FichiersExcel\*.xlsx" /S /t:w'

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,'')<>''

--Mettre à jour la date de modification du fichier

UPDATE #Fichiers_2

SET

Info_Modification = T2.Info_Modification

FROM #Fichiers_2 T1

JOIN #Fichiers_4 T2 ON T2.Info_Modification LIKE '%' + T1.Nom_Fichier + '%'

--Supprimer les fichiers qui ont été déjà intégrées.

DELETE FROM #Fichiers_2

FROM #Fichiers_2 T1

JOIN SOURCE_EXCEL T2 ON T2.Chemin_Fichier=T1.Chemin_Fichier AND T2.Info_Modification=T1.Info_Modification

INSERT INTO #Fichiers_3(Chemin_Fichier,Nom_Fichier,Info_Modification)

SELECT Chemin_Fichier,Nom_Fichier,Info_Modification FROM #Fichiers_2

SELECT @Nbre_Fichier   = COUNT(*) FROM #Fichiers_3

SELECT @Numero_Fichier = 0

Debut_TraiTement:

SELECT @Numero_Fichier = @Numero_Fichier + 1

BEGIN TRY

      WHILE @Numero_Fichier <= @Nbre_Fichier

            BEGIN

           

           

                       

                  SELECT   @Chemin_Fichier    = NULL

                             , @Nom_Fichier          = NULL

                             , @Info_Modification = NULL

                             , @NWhere               = ' WHERE 1=1'

                 

                  SELECT @Chemin_Fichier=Chemin_Fichier,@Nom_Fichier=Nom_Fichier, @Info_Modification=Info_Modification FROM #Fichiers_3 WHERE Numero_Fichier = @Numero_Fichier

                 

                  IF EXISTS(SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @Serveur_Lie_Excel)

                        BEGIN

                             EXEC master.dbo.sp_dropserver@server=@Serveur_Lie_Excel, @droplogins='droplogins'                            

                        END

                  EXEC sp_addlinkedserver@server = @Serveur_Lie_Excel,@srvproduct = 'Excel',@provider = 'Microsoft.ACE.OLEDB.12.0',@datasrc = @Chemin_Fichier,@provstr = 'EXCEL 12.0 XML;HDR=YES';                     

           

                  TRUNCATE TABLE #Liste_Classeurs_Excel

                  TRUNCATE TABLE #Liste_Classeurs_Excel_Temp

                 

                  INSERT INTO #Liste_Classeurs_Excel_Temp

                  EXEC sp_tables_ex@Serveur_Lie_Excel

                 

                  INSERT INTO #Liste_Classeurs_Excel SELECT * FROM #Liste_Classeurs_Excel_Temp WHERE TABLE_NAME NOT LIKE '%filter%'

                 

                  SELECT @Nbre_Classeur   = COUNT(*) FROM #Liste_Classeurs_Excel

                  SELECT @Num_Classeur    = 1

                 

                  --Traiter tous les classeurs

                  WHILE @Num_Classeur <= @Nbre_Classeur

                        BEGIN

                            

                             SELECT @Nom_Classeur_Excel = '[' + CAST(TABLE_NAME AS VARCHAR(50)) + ']' FROM #Liste_Classeurs_Excel WHERE Num_Classeur = @Num_Classeur

                             SELECT @Nbre_Col_Classeur = 0

                             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 @Requete_Select_2 = 'SELECT * INTO tempdb..Fichier_Excel_Temp FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml; HDR=YES; IMEX=1; Database=' + @Chemin_Fichier + ''',' + @Nom_Classeur_Excel + ')'

                             EXECUTE sp_executesql@Requete_Select_2

                            

                            

                             INSERT INTO #Colonnes(Nom_Col)

                             SELECT 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%'

                             ORDER BY T2.column_id

                             SELECT @Nbre_Col       = COUNT(*) FROM #Colonnes

                             SELECT @Numero_Col           = 1

                             ----Recreer la table tempdb..Fichier_Excel_Temp avec toutes les colonnes de type NVARCHAR(255)

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

                                                                 BEGIN

                                                                       DROP TABLE tempdb..Fichier_Excel_Temp

                                                                 END '

                             SELECT @Requete = @Requete + ' CREATE TABLE tempdb..Fichier_Excel_Temp ('

                             WHILE @Numero_Col<=@Nbre_Col

                                   BEGIN

                                         SELECT @Nom_Col = '[' + Nom_Col + '] nvarchar(255) NULL,' FROM #Colonnes WHERE Num_Col=@Numero_Col

                                         SELECT @Requete = @Requete + @Nom_Col

                                         SELECT @Numero_Col = @Numero_Col +1

                                   END

                            

                             SELECT @Requete = SUBSTRING(@Requete,1,LEN(@Requete)-1) + ')'

                             EXECUTE sp_executesql@Requete

                            

                             --Réalimenter la table tempdb..Fichier_Excel_Temp, mettre HDR=NO pour permettre le chargement des noms colonnes et surtout charger toutes les valeurs en format text

                             SELECT @Requete_Select_2 = 'INSERT INTO tempdb..Fichier_Excel_Temp SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml; HDR=NO; IMEX=1; Database=' + @Chemin_Fichier + ''',' + @Nom_Classeur_Excel + ')'

                             EXECUTE sp_executesql@Requete_Select_2

                            

                            

                             SELECT @Numero_Col           = 1

                             SELECT @Requete_Select_1 = ' '

                             SELECT @Requete_Select_2 = ' '

                             SELECT @Nbre_Lig     = 0

                             SELECT @Nbre_Col_Classeur = 0

                            

                             WHILE @Numero_Col <= @Nbre_Col

                                   BEGIN

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

                                         SELECT @Requete   ='SELECT @Nbre_LigOUT=COUNT(*) FROM tempdb..Fichier_Excel_Temp WHERE ISNULL([' + @Nom_Col + '],'''')<>'''''

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

                                        

                                         IF @Nbre_Lig>0 --Pour la colonne il existe des lignes qui ne sont pas vides. c'est pour éviter d'inserer des lignes vides dans la table finale

                                               BEGIN

                                                     SELECT @Nbre_Col_Classeur = @Nbre_Col_Classeur + 1

                                                     SELECT @Requete_Select_1 = @Requete_Select_1 + '''' + @Nom_Col + ''' AS Champ' + CAST(@Numero_Col AS VARCHAR(25)) + ','

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

                                               END

                                          ELSE

                                               BEGIN

                                                     SELECT @Requete_Select_1 = @Requete_Select_1 + 'NULL AS Champ' + CAST(@Numero_Col AS VARCHAR(25)) + ','

                                                     SELECT @Requete_Select_2 = @Requete_Select_2 + 'NULL AS Champ' + CAST(@Numero_Col AS VARCHAR(25)) + ','                                        

                                               END        

                                        

                                         SELECT @NWhere = @NWhere + ' AND ISNULL(Champ' + + CAST(@Numero_Col AS VARCHAR(25)) + ','''')='''''                       

                                         SELECT @Numero_Col      = @Numero_Col + 1

                                   END

                       

                             SELECT @Nbre_Col = 25

                            

                             --Continuer à construire les colonnes manquantes

                             WHILE @Numero_Col <= @Nbre_Col

                                   BEGIN

                                         SELECT @Requete_Select_1 = @Requete_Select_1 + 'NULL AS Champ' + CAST(@Numero_Col AS VARCHAR(25)) + ','

                                         SELECT @Requete_Select_2 = @Requete_Select_2 + 'NULL AS Champ' + CAST(@Numero_Col AS VARCHAR(25)) + ','

                                         SELECT @Numero_Col      = @Numero_Col + 1

                                   END  

                            

                             --Vider la table

                             TRUNCATE TABLE tempdb..SAS_SOURCE_EXCEL

                                              

                             ----Insérer les noms des colonnes du fichier Excel

                             --SELECT @Requete_Select_1 ='INSERT INTO tempdb..SAS_SOURCE_EXCEL (Champ1,Champ2,Champ3,Champ4,Champ5,Champ6,Champ7,Champ8,Champ9,Champ10,Champ11,Champ12,Champ13,Champ14,Champ15,Champ16,Champ17,Champ18,Champ19,Champ20,Champ21,Champ22,Champ23,Champ24,Champ25,Nbre_Colonnes,Nbre_Lignes,Nom_Fichier,Nom_Classeur,Chemin_Fichier,Info_Modification)'

                             --+ ' SELECT ' + @Requete_Select_1 + CAST(@Nbre_Col AS VARCHAR(25)) + ' AS NbreColonnes,' + CAST(@Nbre_Lig AS VARCHAR(25)) + ' AS Nbre_Lignes,' + 'Nom_Fichier =''' + @Nom_Fichier + ''',Chemin_Fichier=''' + @Chemin_Fichier + ''',Nom_Classeur=''' + @Nom_Classeur_Excel + ''',Info_Modification=''' + @Info_Modification + ''''

                             --EXECUTE sp_executesql @Requete_Select_1

                                              

                             --Insérer les valeurs trouvées dans le fichier Excel                        

                             SELECT @Requete_Select_2 ='INSERT INTO tempdb..SAS_SOURCE_EXCEL (Champ1,Champ2,Champ3,Champ4,Champ5,Champ6,Champ7,Champ8,Champ9,Champ10,Champ11,Champ12,Champ13,Champ14,Champ15,Champ16,Champ17,Champ18,Champ19,Champ20,Champ21,Champ22,Champ23,Champ24,Champ25,Nbre_Colonnes,Nom_Fichier,Nom_Classeur,Chemin_Fichier,Info_Modification)'

                             + ' SELECT ' + @Requete_Select_2 + CAST(@Nbre_Col_Classeur AS VARCHAR(25)) + ' AS Nbre_Colonnes,' + 'Nom_Fichier =''' + @Nom_Fichier + ''',Nom_Classeur=''' + @Nom_Classeur_Excel + ''',Chemin_Fichier=''' + @Chemin_Fichier + ''',Info_Modification=''' + @Info_Modification + ''' FROM tempdb..Fichier_Excel_Temp '

                             EXECUTE sp_executesql@Requete_Select_2 

                            

                             --Supprimer les lignes avec toutes les colonnes vides

                             SELECT @Requete_Select_1 = 'DELETE FROM tempdb..SAS_SOURCE_EXCEL ' + @NWhere

                             EXECUTE sp_executesql@Requete_Select_1

                            

                             --Renseigner le nombre de lignes importées

                             UPDATE tempdb..SAS_SOURCE_EXCEL

                                   SET

                                         Nbre_Lignes =(SELECT COUNT(Num_Lig) FROM tempdb..SAS_SOURCE_EXCEL)

--Supprimer les données anciennement intégrées.

DELETE FROM MEKTABA..SOURCE_EXCEL

FROM MEKTABA..SOURCE_EXCEL T1

JOIN tempdb..SAS_SOURCE_EXCEL T2 ON T2.Nom_Fichier=T1.Nom_Fichier AND T2.Nom_Classeur=T1.Nom_Classeur

                            

                             INSERT INTO MEKTABA..SOURCE_EXCEL( Num_Lig,Champ1,Champ2,Champ3,Champ4,Champ5,Champ6,Champ7,Champ8,Champ9,Champ10,Champ11,Champ12,Champ13,Champ14,Champ15,Champ16,Champ17,Champ18,Champ19,Champ20,Champ21,Champ22,Champ23,Champ24,Champ25,Nbre_Colonnes,Nbre_Lignes,Nom_Fichier,Nom_Classeur,Chemin_Fichier,Info_Modification,STATUT_LIG )

                             SELECT Num_Lig,Champ1,Champ2,Champ3,Champ4,Champ5,Champ6,Champ7,Champ8,Champ9,Champ10,Champ11,Champ12,Champ13,Champ14,Champ15,Champ16,Champ17,Champ18,Champ19,Champ20,Champ21,Champ22,Champ23,Champ24,Champ25,Nbre_Colonnes,Nbre_Lignes,Nom_Fichier,Nom_Classeur,Chemin_Fichier,Info_Modification,STATUT_LIG FROM tempdb..SAS_SOURCE_EXCEL                            

                            

                            

                             SELECT @Num_Classeur    = @Num_Classeur + 1

                        END

           

                  SELECT @Numero_Fichier = @Numero_Fichier + 1

            END

/**** Renseigner les donnnées, importées à partir des fichiers Excel, dans les colonnes finales Matricule, Nom et Prenom ****/

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

      BEGIN

            DROP TABLE #Liste_Fichiers

      END

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

      BEGIN

            DROP TABLE #Colonnes

      END

DECLARE @Num_Lig            AS INT

            ,@Requete               AS NVARCHAR(MAX)

            ,@Nbre_LigTrouve  AS INT

            ,@Nbre_LigTrouveOUT     AS INT

SELECT      ,@Nbre_Lig                   = 0

            ,@Nom_Fichier                = NULL

            ,@Nom_Classeur_Excel    = NULL

            ,@Numero_Col                 = 0

            ,@Nbre_Col                   = 0

            ,@Nom_Col                    = NULL           

CREATE TABLE #Liste_Fichiers

(

      Num_Lig           INT IDENTITY(1,1),

      Nom_Fichier nvarchar(255) NULL,

      Nom_Classeur nvarchar(255) NULL

)

CREATE TABLE #Colonnes

(

      Num_Col INT IDENTITY(1,1)

      ,Nom_Col VARCHAR(150)

)

INSERT INTO #Colonnes(Nom_Col)

SELECT T2.name FROM sys.objects T1

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

WHERE T1.name='SOURCE_EXCEL'

AND(T2.name LIKE 'champ%' OR T2.name LIKE 'Nom_Fichier%')

ORDER BY T2.column_id

SELECT @Nbre_Col = COUNT(*) FROM #Colonnes

                            

INSERT INTO #Liste_Fichiers

SELECT DISTINCT Nom_fichier,Nom_Classeur FROM dbo.SOURCE_EXCEL WHERE Num_Lig>1 AND STATUT_LIG=0

SELECT @Nbre_Lig = COUNT(*) FROM #Liste_Fichiers

SELECT @Num_Lig         = 1

WHILE @Num_Lig <= @Nbre_Lig

      BEGIN

            SELECT @Nom_Fichier = Nom_fichier, @Nom_Classeur_Excel = Nom_Classeur FROM #Liste_Fichiers WHERE Num_Lig = @Num_Lig

            SELECT @Numero_Col           = 1  

                       

            WHILE @Numero_Col <= @Nbre_Col

                  BEGIN

                        SELECT   @Nbre_LigTrouve     = 0

                                   ,@Nbre_LigTrouveOUT = 0

                       

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

                                                          

                        --Renseigner MATRICULE EMPLOYE

                        SELECT   @Nbre_LigTrouve     = 0

                                   ,@Nbre_LigTrouveOUT = 0

                       

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

                        SELECT @Requete   ='SELECT @Nbre_LigTrouveOUT=COUNT(*) FROM SOURCE_EXCEL WHERE ([' + @Nom_Col + '] LIKE ''%matricule%'''

                        SELECT @Requete   = @Requete + ' OR [' + @Nom_Col + '] LIKE ''%num%employe%'' '

                        SELECT @Requete   = @Requete + ' OR [' + @Nom_Col + '] LIKE ''%identifient%'' )'

                        SELECT @Requete   = @Requete + ' AND Nom_fichier=''' + @Nom_Fichier + ''''

                        SELECT @Requete   = @Requete + ' AND Nom_Classeur=''' + @Nom_Classeur_Excel + ''''

                        SELECT @Requete   = @Requete + ' AND STATUT_LIG=0'

                        SELECT @Requete   = @Requete + ' AND Num_Lig=1'

                        EXECUTE sp_executesql@Requete,N'@Nbre_LigTrouveOUT int OUTPUT',@Nbre_LigTrouveOUT=@Nbre_LigTrouve OUTPUT

                       

                        IF @Nbre_LigTrouve>0

                             BEGIN

                                   SELECT @Requete   ='UPDATE SOURCE_EXCEL SET MATRICULE= REPLACE(' + @Nom_Col + ',SPACE(1),'''') '

                                   SELECT @Requete   = @Requete + ' WHERE Nom_fichier=''' + @Nom_Fichier + ''''

                                   SELECT @Requete   = @Requete + ' AND Nom_Classeur=''' + @Nom_Classeur_Excel + ''''

                                   SELECT @Requete   = @Requete + ' AND STATUT_LIG=0'

                                   SELECT @Requete   = @Requete + ' AND Num_Lig>1'

                                   EXECUTE sp_executesql@Requete

                             END                               

                        --Renseigner NOM EMPLOYE

                        SELECT   @Nbre_LigTrouve     = 0

                                   ,@Nbre_LigTrouveOUT = 0

                       

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

                        SELECT @Requete   ='SELECT @Nbre_LigTrouveOUT=COUNT(*) FROM SOURCE_EXCEL WHERE ([' + @Nom_Col + '] LIKE ''%NOM%'''

                        SELECT @Requete   = @Requete + ' OR [' + @Nom_Col + '] LIKE ''%NAME%'') '

                        SELECT @Requete   = @Requete + ' AND Nom_fichier=''' + @Nom_Fichier + ''''

                        SELECT @Requete   = @Requete + ' AND Nom_Classeur=''' + @Nom_Classeur_Excel + ''''

                        SELECT @Requete   = @Requete + ' AND STATUT_LIG=0'

                        SELECT @Requete   = @Requete + ' AND Num_Lig=1'

                        EXECUTE sp_executesql@Requete,N'@Nbre_LigTrouveOUT int OUTPUT',@Nbre_LigTrouveOUT=@Nbre_LigTrouve OUTPUT                                      

                        IF @Nbre_LigTrouve>0

                             BEGIN

                                   SELECT @Requete   ='UPDATE SOURCE_EXCEL SET NOM= REPLACE(' + @Nom_Col + ',SPACE(1),'''') '

                                   SELECT @Requete   = @Requete + ' WHERE Nom_fichier=''' + @Nom_Fichier + ''''

                                   SELECT @Requete   = @Requete + ' AND Nom_Classeur=''' + @Nom_Classeur_Excel + ''''

                                   SELECT @Requete   = @Requete + ' AND STATUT_LIG=0'

                                   SELECT @Requete   = @Requete + ' AND Num_Lig>1'

                                   EXECUTE sp_executesql@Requete

                             END  

                                                                           

                        --Renseigner PRENOM EMPLOYE

                        SELECT   @Nbre_LigTrouve     = 0

                        ,@Nbre_LigTrouveOUT = 0

                       

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

                        SELECT @Requete   ='SELECT @Nbre_LigTrouveOUT=COUNT(*) FROM SOURCE_EXCEL WHERE ([' + @Nom_Col + '] LIKE ''%prenom%'''

                        SELECT @Requete   = @Requete + ' OR [' + @Nom_Col + '] LIKE ''%surname%'')'

                        SELECT @Requete   = @Requete + ' AND Nom_fichier=''' + @Nom_Fichier + ''''

                        SELECT @Requete   = @Requete + ' AND Nom_Classeur=''' + @Nom_Classeur_Excel + ''''

                        SELECT @Requete   = @Requete + ' AND STATUT_LIG=0'

                        SELECT @Requete   = @Requete + ' AND Num_Lig=1'

                        EXECUTE sp_executesql@Requete,N'@Nbre_LigTrouveOUT int OUTPUT',@Nbre_LigTrouveOUT=@Nbre_LigTrouve OUTPUT                                      

                        IF @Nbre_LigTrouve>0

                             BEGIN

                                   SELECT @Requete   ='UPDATE SOURCE_EXCEL SET Prenom=[' + @Nom_Col + ']'

                                   SELECT @Requete   = @Requete + ' WHERE Nom_fichier=''' + @Nom_Fichier + ''''

                                   SELECT @Requete   = @Requete + ' AND Nom_Classeur=''' + @Nom_Classeur_Excel + ''''

                                   SELECT @Requete   = @Requete + ' AND STATUT_LIG=0'

                                   SELECT @Requete   = @Requete + ' AND Num_Lig>1'

                                   SELECT @Requete   = @Requete + ' AND ISDATE(' + @Nom_Col + ')=1'

                                   EXECUTE sp_executesql@Requete

                             END                                                                                                                        

                                  

                        SELECT @Numero_Col = @Numero_Col + 1

                  END

           

            --Vérifier que toutes lignes du même fichier Excel, du même classeur ont été traitées

            IF NOT EXISTS(SELECT Nom_Fichier,Nom_Classeur

                             FROM [dbo].[SOURCE_EXCEL]

                             WHERE Nom_Fichier=@Nom_Fichier and Nom_Classeur=@Nom_Classeur_Excel

                             and(ISNULL(Matricule,'')='' OR ISNULL(Nom,'')='' OR ISNULL(Prenom,'')='')

                             AND Num_Lig<>1 )

                  BEGIN

                        --Mettre à jour l'entête du fichier Excel

                        UPDATE [dbo].[SOURCE_EXCEL]

                             SET

                                   STATUT_LIG=1

                        WHERE Nom_fichier=@Nom_Fichier

                        AND Nom_Classeur= @Nom_Classeur_Excel

                        AND Num_Lig=1                     

                  END

                 

            SELECT @Num_Lig = @Num_Lig + 1

      END -- @Num_Lig <= @Nbre_Lig

     

END TRY

BEGIN CATCH

      IF @@ERROR > 0

            INSERT INTO dbo.LOGS_ERREUR(DATE_LOG,MESSAGE_ERREUR) VALUES (GETDATE(),'Importation Fichier Excel : ' + @Nom_Fichier + ', Classeur : ' + @Nom_Classeur_Excel + ', Message Erreur : ' + ERROR_MESSAGE() )

            GOTO Debut_TraiTement

END CATCH

END