[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