[SQL Server] Rechercher une valeur dans un ensemble de fichiers Excel
-- =============================================
-- 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