[SQL Server] Importer des fichiers Excel, csv, txt ... etc vers une table SQL Server
-- =============================================
-- Author: Zouhaier KAHARROUBI
-- Create date: 05/01/2018
-- Description: Importer des fichiers Excel, csv, txt ... etc vers une table SQL Server
-- =============================================
DECLARE @Extension_Fichier VARCHAR(5)='csv' --.csv ou .xls ou xlsx ou .txt ... etc
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Fichier_Imports_Temp]'))
BEGIN
DROP TABLE #Fichier_Imports_Temp
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#FichiersTemp]'))
BEGIN
DROP TABLE #FichiersTemp
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Fichiers]'))
BEGIN
DROP TABLE #Fichiers
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
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#JournalTraitement]'))
BEGIN
DROP TABLE #JournalTraitement
END
-- Insert statements for procedure here
CREATE TABLE #FichiersTemp
(
NomFichier VARCHAR(250)
)
CREATE TABLE #Fichiers
(
Num_Fichier INT IDENTITY(1,1)
,NomFichier VARCHAR(250)
)
CREATE TABLE #Fichier_Imports_Temp
(
champ_1 varchar(20) NOT NULL,
champ_2 datetime NULL,
champ_3 varchar(20) NOT NULL,
champ_4 varchar(20) NULL,
champ_5 varchar(10) NULL,
champ_6 varchar(10) NULL,
champ_7 varchar(255) NULL,
champ_8 varchar(10) NULL,
champ_9 varchar(100) NULL,
champ_10 varchar(10) NULL,
champ_11 varchar(100) NULL,
champ_12 varchar(10) NULL,
champ_13 varchar(100) NULL,
champ_14 varchar(10) NULL,
champ_15 varchar(100) NULL,
champ_16 varchar(10) NULL,
champ_17 varchar(40) NULL,
champ_18 varchar(40) NULL,
champ_19 varchar(40) NULL,
champ_20 varchar(20) NULL,
champ_21 varchar(30) NULL,
champ_22 varchar(20) NULL,
champ_23 varchar(255) NULL,
champ_24 varchar(255) NULL,
champ_25 varchar(20) NULL,
champ_26 varchar(30) NULL,
champ_27 varchar(10) NULL,
champ_28 varchar(100) NULL,
champ_29 varchar(100) NULL,
champ_30 varchar(10) 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
(
Nom_Fichier VARCHAR(250) NULL
,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 #JournalTraitement
(
NomFichier VARCHAR(250) NULL,
Message_Erreur NVARCHAR(MAX) NULL,
Priorite BIT
)
DECLARE @Nbre_Fichier INTEGER
,@Num_Fichier INTEGER
,@Nbre_Lig INTEGER
,@Num_Lig INTEGER
,@NomFichier VARCHAR(250)
,@NomFichier_Source VARCHAR(250)
,@Requete NVARCHAR(MAX)
,@Chemin_Source NVARCHAR(250)
,@Chemin_Archive NVARCHAR(250)
,@Chemin_Erreur NVARCHAR(250)
,@CommandeShell NVARCHAR(2500)
,@emailSubject VARCHAR(100)
,@tableHTML NVARCHAR(MAX)
,@body VARCHAR(MAX)
,@Serveur_Lie_Excel VARCHAR(250)='ActivationIntegration'
,@Nom_Classeur_Excel VARCHAR(50)
IF CHARINDEX('.',@Extension_Fichier)<>1
BEGIN
SELECT @Extension_Fichier = '.' + @Extension_Fichier
END
BEGIN
SELECT @Chemin_Source = '\\monserveur\MesFichiers\Integration\Fichiers'
SELECT @Chemin_Archive = '\\monserveur\MesFichiers\Integration\Fichiers\Archive'
SELECT @Chemin_Erreur = '\\monserveur\MesFichiers\Integration\Fichiers\Erreur'
--Compléter les chemins par le carctère \
IF CHARINDEX('\',REVERSE(@Chemin_Source),0)<>1
BEGIN
SELECT @Chemin_Source = @Chemin_Source + '\'
END
IF CHARINDEX('\',REVERSE(@Chemin_Archive),0)<>1
BEGIN
SELECT @Chemin_Archive = @Chemin_Archive + '\'
END
IF CHARINDEX('\',REVERSE(@Chemin_Erreur),0)<>1
BEGIN
SELECT @Chemin_Erreur = @Chemin_Erreur + '\'
END
SELECT @CommandeShell = 'DIR ' + @Chemin_Source + '*' + @Extension_Fichier + ' /B /oD'
INSERT INTO #FichiersTemp EXEC Master..xp_cmdshell @CommandeShell
INSERT INTO #Fichiers(NomFichier)
SELECT NomFichier FROM #FichiersTemp WHERE NomFichier LIKE '%' + @Extension_Fichier
SELECT @Nbre_Fichier = COUNT(*) FROM #Fichiers
--Trouver le nom du classeur
SELECT @Num_Fichier = 1
WHILE @Num_Fichier <= @Nbre_Fichier AND @Extension_Fichier IN('.xls','.xlsx')
BEGIN
SELECT @NomFichier = NomFichier FROM #Fichiers WHERE Num_Fichier=@Num_Fichier
--Trouver le nom du classeur
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
SELECT @NomFichier_Source = @Chemin_Source + @NomFichier
EXEC sp_addlinkedserver @server = @Serveur_Lie_Excel,@srvproduct = 'Excel',@provider = 'Microsoft.ACE.OLEDB.12.0',@datasrc = @NomFichier_Source,@provstr = 'EXCEL 12.0 XML;HDR=YES';
TRUNCATE TABLE #Liste_Classeurs_Excel_Temp
INSERT INTO #Liste_Classeurs_Excel_Temp
EXEC sp_tables_ex @Serveur_Lie_Excel
INSERT INTO #Liste_Classeurs_Excel(Nom_Fichier,TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE,REMARKS)
SELECT @NomFichier,TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE,REMARKS FROM #Liste_Classeurs_Excel_Temp
SELECT @Num_Fichier = @Num_Fichier + 1
END
SELECT @Num_Fichier = 1
WHILE @Num_Fichier <= @Nbre_Fichier
BEGIN
TRUNCATE TABLE #Fichier_Imports_Temp
SELECT @NomFichier = NomFichier FROM #Fichiers WHERE Num_Fichier=@Num_Fichier
SELECT @Nom_Classeur_Excel = '[' + CAST(TABLE_NAME AS VARCHAR(50)) + ']' FROM #Liste_Classeurs_Excel WHERE TABLE_NAME NOT LIKE '%filter%' AND Nom_Fichier=@NomFichier
--Récuperer le nom de fichier sans l'extension
SELECT @NomFichier_Source = REPLACE(@NomFichier,@Extension_Fichier,'')
IF @Extension_Fichier IN('.xls','.xlsx')
BEGIN
SELECT @Requete = 'INSERT INTO #Fichier_Imports_Temp (champ_1,champ_2 ,champ_3 ,champ_4,champ_5 ,
champ_6 ,champ_7,champ_8,champ_9,champ_10,champ_11,champ_12,champ_13,champ_14,
champ_15,champ_16,champ_17,champ_18,champ_19,champ_20,champ_21,champ_22,champ_23,champ_24,
champ_25,champ_26,champ_27,champ_28 ,champ_29 ,champ_30)'
SELECT @Requete = @Requete + 'SELECT LTRIM(RTRIM(F1)) champ_1, LTRIM(RTRIM(F2)) champ_2 , LTRIM(RTRIM(F3)) champ_3 , LTRIM(RTRIM(F4)) champ_4, LTRIM(RTRIM(F5)) champ_5 ,
LTRIM(RTRIM(F6)) champ_6 ,LTRIM(RTRIM(F7)) champ_7, LTRIM(RTRIM(F8)) champ_8,LTRIM(RTRIM(F9)) champ_9, LTRIM(RTRIM(F10)) champ_10, LTRIM(RTRIM(F11)) champ_11,LTRIM(RTRIM(F12)) champ_12,LTRIM(RTRIM(F13)) champ_13,LTRIM(RTRIM(F14)) champ_14,
LTRIM(RTRIM(F15)) champ_15,LTRIM(RTRIM(F16)) champ_16, LTRIM(RTRIM(F17)) champ_17,LTRIM(RTRIM(F18)) champ_18,LTRIM(RTRIM(F19)) champ_19,LTRIM(RTRIM(F20)) champ_20,LTRIM(RTRIM(F21)) champ_21,LTRIM(RTRIM(F22)) champ_22,LTRIM(RTRIM(F23)) champ_23,LTRIM(RTRIM(F24)) champ_24,
LTRIM(RTRIM(F25)) champ_25,LTRIM(RTRIM(F26)) champ_26,LTRIM(RTRIM(F27)) champ_27, LTRIM(RTRIM(F28)) champ_28 ,LTRIM(RTRIM(F29)) champ_29 ,LTRIM(RTRIM(F30)) champ_30
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml; HDR=NO; IMEX=1; Database=' + @Chemin_Source + @NomFichier + ';TRUSTED_CONNECTION=NO;'',' + @Nom_Classeur_Excel +')'
+ 'WHERE ISNULL(F1,'''')<>''champ_1'''
END
ELSE
BEGIN
--Generer un nouveau fichier avec le codepage en UNICODE parceque le fichier source est en codepage UTF-8. Avec le code page UTF-8 les ponctuations ne sont pas importés dans la tables
SELECT @CommandeShell = N'PowerShell.exe "Get-Content ' + @Chemin_Source + @NomFichier + ' | Set-Content -Encoding unicode ' + @Chemin_Source + @NomFichier_Source + 'Unicode' + @Extension_Fichier + '"'
EXEC Master..xp_cmdshell@CommandeShell
SELECT @Requete = 'BULK INSERT #Fichier_Imports_Temp'
+ ' FROM ''' + @Chemin_Source + @NomFichier_Source + 'Unicode' + @Extension_Fichier + ''''
+ ' WITH'
+ ' ('
+ ' FIRSTROW = 2,'
+ ' FIELDTERMINATOR ='';'','
+ ' ROWTERMINATOR =''\n'''
+ ' );'
END
--Construire le nom du fichier archive
SELECT @NomFichier_Source = @NomFichier_Source + '_' + CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)) + CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(2))
+ CAST(DATEPART(DAY,GETDATE()) AS VARCHAR(2)) + CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR(2)) + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR(2))
+ CAST(DATEPART(SECOND,GETDATE()) AS VARCHAR(2)) + @Extension_Fichier
--Lancer la requête d'importation du fichier
EXECUTE sp_executesql@Requete
BEGIN TRANSACTION;
BEGIN TRY
UPDATE T1
SET [champ_2] = T2.champ_2
,[champ_3] = T2.champ_3
,[champ_4] = T2.champ_4
,[champ_5] = T2.champ_5
,[champ_6] = T2.champ_6
,[champ_7] = T2.champ_7
,[champ_8] = T2.champ_8
,[champ_9] = T2.champ_9
,[champ_10] = T2.champ_10
,[champ_11] = T2.champ_11
,[champ_12] = T2.champ_12
,[champ_13] = T2.champ_13
,[champ_14] = T2.champ_14
,[champ_15] = T2.champ_15
,[champ_16] = T2.champ_16
,[champ_17] = T2.champ_17
,[champ_18] = T2.champ_18
,[champ_19] = T2.champ_19
,[champ_20] = T2.champ_20
,[champ_21] = T2.champ_21
,[champ_22] = T2.champ_22
,[champ_23] = T2.champ_23
,[champ_24] = T2.champ_24
,[champ_25] = T2.champ_25
,[champ_26] = T2.champ_26
,[champ_27] = T2.champ_27
,[champ_28] = T2.champ_28
,[champ_29] = T2.champ_29
,[champ_30] = T2.champ_30
,[date_maj] = CAST(GETDATE() AS DATE)
,[login_maj] = SYSTEM_USER
FROM Fichier_Imports T1
JOIN #Fichier_Imports_Temp T2 ON T2.champ_1=T1.champ_1
WHERE T1.[champ_2] <> T2.champ_2
OR ISNULL(T1.[champ_3],'') <> ISNULL(T2.champ_3,'')
OR ISNULL(T1.[champ_4],'') <> ISNULL(T2.champ_4,'')
OR ISNULL(T1.[champ_5],'') <> ISNULL(T2.champ_5,'')
OR ISNULL(T1.[champ_6],'') <> ISNULL(T2.champ_6,'')
OR ISNULL(T1.[champ_7],'') <> ISNULL(T2.champ_7,'')
OR ISNULL(T1.[champ_8],'') <> ISNULL(T2.champ_8,'')
OR ISNULL(T1.[champ_9],'') <> ISNULL(T2.champ_9,'')
OR ISNULL(T1.[champ_10],'') <> ISNULL(T2.champ_10,'')
OR ISNULL(T1.[champ_11],'') <> ISNULL(T2.champ_11,'')
OR ISNULL(T1.[champ_12],'') <> ISNULL(T2.champ_12,'')
OR ISNULL(T1.[champ_13],'') <> ISNULL(T2.champ_13,'')
OR ISNULL(T1.[champ_14],'') <> ISNULL(T2.champ_14,'')
OR ISNULL(T1.[champ_15],'') <> ISNULL(T2.champ_15,'')
OR ISNULL(T1.[champ_16],'') <> ISNULL(T2.champ_16,'')
OR ISNULL(T1.[champ_17],'') <> ISNULL(T2.champ_17,'')
OR ISNULL(T1.[champ_18],'') <> ISNULL(T2.champ_18,'')
OR ISNULL(T1.[champ_19],'') <> ISNULL(T2.champ_19,'')
OR ISNULL(T1.[champ_20],'') <> ISNULL(T2.champ_20,'')
OR ISNULL(T1.[champ_21],'') <> ISNULL(T2.champ_21,'')
OR ISNULL(T1.[champ_22],'') <> ISNULL(T2.champ_22,'')
OR ISNULL(T1.[champ_23],'') <> ISNULL(T2.champ_23,'')
OR ISNULL(T1.[champ_24],'') <> ISNULL(T2.champ_24,'')
OR ISNULL(T1.[champ_25],'') <> ISNULL(T2.champ_25,'')
OR ISNULL(T1.[champ_26],'') <> ISNULL(T2.champ_26,'')
OR ISNULL(T1.[champ_27],'') <> ISNULL(T2.champ_27,'')
OR ISNULL(T1.[champ_28],'') <> ISNULL(T2.champ_28,'')
OR ISNULL(T1.[champ_29],'') <> ISNULL(T2.champ_29,'')
OR ISNULL(T1.[champ_30],'') <> ISNULL(T2.champ_30,'')
INSERT INTO Fichier_Imports
([champ_1],[champ_2],[champ_3],[champ_4]
,[champ_5],[champ_6]
,[champ_7],[champ_8],[champ_9],[champ_10],[champ_11],[champ_12]
,[champ_13],[champ_14],[champ_15],[champ_16],[champ_17],[champ_18]
,[champ_19],[champ_20],[champ_21],[champ_22],[champ_23],[champ_24]
,[champ_25],[champ_26],[champ_27],[champ_28],[champ_29],[champ_30]
,[date_import],[login_import],[date_maj],[login_maj])
SELECT T1.[champ_1],T1.[champ_2],T1.[champ_3],T1.[champ_4]
,T1.[champ_5],T1.[champ_6]
,T1.[champ_7],T1.[champ_8],T1.[champ_9],T1.[champ_10],T1.[champ_11],T1.[champ_12]
,T1.[champ_13],T1.[champ_14],T1.[champ_15],T1.[champ_16],T1.[champ_17],T1.[champ_18]
,T1.[champ_19],T1.[champ_20],T1.[champ_21],T1.[champ_22],T1.[champ_23],T1.[champ_24]
,T1.[champ_25],T1.[champ_26],T1.[champ_27],T1.[champ_28],T1.[champ_29],T1.[champ_30]
,CAST(GETDATE() AS DATE) AS [date_import],SYSTEM_USER AS [login_import], CAST(GETDATE() AS DATE) AS [date_maj], SYSTEM_USER AS [login_maj]
FROM #Fichier_Imports_Temp T1
LEFT JOIN Fichier_Imports T2 ON T2.champ_1=T1.champ_1
WHERE T2.champ_1 IS NULL
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
INSERT INTO #JournalTraitement(NomFichier,Message_Erreur,Priorite)
VALUES (@NomFichier,ERROR_MESSAGE(),1)
SELECT @CommandeShell = 'MOVE /Y "' + @Chemin_Source + @NomFichier + '" "' + @Chemin_Erreur + @NomFichier_Source + '"'
EXEC Master..xp_cmdshell@CommandeShell
END CATCH
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION;
INSERT INTO #JournalTraitement(NomFichier,Message_Erreur,Priorite)
VALUES (@NomFichier,'OK',2)
SELECT @CommandeShell = 'MOVE /Y "' + @Chemin_Source + @NomFichier + '" "' + @Chemin_Archive + @NomFichier_Source + '"'
EXEC Master..xp_cmdshell@CommandeShell
END
--Supprimer le fichier d'activation
SELECT @NomFichier_Source = REPLACE(@NomFichier,@Extension_Fichier,'')
SELECT @CommandeShell = 'DEL "' + @Chemin_Source + @NomFichier_Source + 'Unicode' + @Extension_Fichier + '"'
EXEC Master..xp_cmdshell@CommandeShell
SELECT @Num_Fichier = @Num_Fichier + 1
END
IF EXISTS(SELECT * FROM #JournalTraitement)
BEGIN
SELECT @emailSubject = ' - MISE A JOUR DES IMPORTS FICHIESR'
SET @tableHTML = N'<H><b><font size="4">Bonjour</font></b><BR></BR></H>' +
N'<H><b><font size="4">Veuillez trouver ci-dessous le rapport des mises à jour des activations : </font></b> <BR></BR></H>' +
N'<html><head><style>' +
N'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
N'</style></head><body>' +
N'<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">' +
N'<div style="margin-left:50px; font-family:Calibri;"><table cellpadding=0 cellspacing=0 border=0>' +
N'<tr bgcolor=#4b6c9e>' +
N'<td align=center><font face="calibri" color=White><b>N°</b></font></td>' +
N'<td align=center><font face="calibri" color=White><b>Fichier</b></font></td>' +
N'<td align=center><font face="calibri" color=White><b>Statut Intégration</b></font></td></tr>'
SELECT @body =
(
select td=ROW_NUMBER() OVER(ORDER BY Priorite,NomFichier) ,
td = NomFichier,
td = Message_Erreur
FROM #JournalTraitement
ORDER BY Priorite,NomFichier
FOR XML RAW('tr'), ELEMENTS
)
SET @body = REPLACE(@body, '<td>', '<td align=left><font face="calibri">')
SET @body = REPLACE(@body, '</td>', '</font></td>')
SET @body = REPLACE(@body, '_x0020_', SPACE(1))
SET @body = Replace(@body, '_x003D_', '=')
SET @tableHTML = @tableHTML + ISNULL(@body,'') + '</table></div></body></html>'
SET @tableHTML = @tableHTML + N'<H><BR></BR></H>'
SET @tableHTML = @tableHTML + N'<H><b><font size="4" color=Red>Merci de vérifier la validité de cette mise à jour.</font></b><BR></BR></H>'
SET @tableHTML = @tableHTML + N'<H><b><font size="4">Cordialement,</font></b><BR></BR></H>'
SET @tableHTML = @tableHTML + N'<H><b><font size="4">Service Base de données</font></b></H>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SqlServerProfilMessagerie',
@recipients = 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.',
@body = @tableHTML,
@subject = @emailSubject,
@body_format = 'HTML',
@importance='High'
END
END