[SQL Server] Traitement d'un fichier txt via la commande BULK INSERT
-- =============================================
-- Auteur : KHARROUBI Zouhaier
-- Date Création : 10 Avril 2014
-- Description : Lecture et traitement d'un fichier txt via la commande BULK INSERT
-- =============================================
Format de fichier txt :
TYPE = CLIENT
CLIENTS = CLT001
ACTION = ADD_OR_UPDATE
;========== GENERAL ==========
CLIENT_TYPE = WINDOWS_NT
STATUS = ENABLE
PASSWORD = p@ssw0rd<br< a=""> />DRIVE = C
PATH = \Program Files\Remote\
TIMEZONE = 1
CONFIG = APPLICATIONS
CONFIG_OFF = FILE_ATTRIBUTES
CONFIG = ESD
CONFIG = INBOUND_OBJECT_SELECTION
;========= CLIENT COMMUNICATIONS =========
PROTOCOL = TCP/IP - Default
SERVER_PHONE1 = <Serveur1>
INBOUND_TIME = 15:00:00
RETRIES = 0
LISTEN = OFF
;========= SERVER COMMUNICATIONS =========
PRIMARY_RESOURCE = TCP/IP
PRIMARY_NETWORK_GROUP = Default
PRIMARY_PHONE =
ALTERNATE_RESOURCE = TCP/IP
ALTERNATE_NETWORK_GROUP = Default
ALTERNATE_PHONE =
PRIMARY_CONFIG = PRIMARY
ALTERNATE_CONFIG = PRIMARY
;#############################################################
CLIENTS = CLT9222
ACTION = ADD_OR_UPDATE
;========== GENERAL ==========
CLIENT_TYPE = WINDOWS_NT
DESCRIPTION = 9222
STATUS = ENABLE
PASSWORD = 9222
DRIVE = C
PATH = \Program Files\Remote\
TIMEZONE = 1
CONFIG = APPLICATIONS
CONFIG_OFF = FILE_ATTRIBUTES
CONFIG = ESD
CONFIG = INBOUND_OBJECT_SELECTION
;========= CLIENT COMMUNICATIONS =========
PROTOCOL = TCP/IP - Default
SERVER_PHONE1 = <Serveur1>
INBOUND_TIME = 05:00:00
RETRIES = 1
LISTEN = OFF
;========= SERVER COMMUNICATIONS =========
PRIMARY_RESOURCE = TCP/IP
PRIMARY_NETWORK_GROUP = Default
PRIMARY_PHONE = 14.13.5.12
ALTERNATE_NETWORK_GROUP =
ALTERNATE_PHONE =
PRIMARY_CONFIG = PRIMARY
;========= COMMENTS =========
CONTACT1_NAME = CLT9222
CONTACT1_PHONE = 6
;###########################################################
CLIENTS = CLT0291
ACTION = ADD_OR_UPDATE
;========== GENERAL ==========
CLIENT_TYPE = WINDOWS_NT
DESCRIPTION = 9291
STATUS = ENABLE
PASSWORD = BEMD0291
DRIVE = C
PATH = \Program Files\Remote\
TIMEZONE = 1
CONFIG = APPLICATIONS
CONFIG_OFF = FILE_ATTRIBUTES
CONFIG = ESD
CONFIG = INBOUND_OBJECT_SELECTION
;========= CLIENT COMMUNICATIONS =========
PROTOCOL = TCP/IP - Default
SERVER_PHONE1 = <Serveur1>
INBOUND_TIME = 05:00:00
RETRIES = 1
LISTEN = OFF
;========= SERVER COMMUNICATIONS =========
PRIMARY_RESOURCE = TCP/IP
PRIMARY_NETWORK_GROUP = Default
PRIMARY_PHONE = 14.13.5.12
ALTERNATE_NETWORK_GROUP =
ALTERNATE_PHONE =
PRIMARY_CONFIG = PRIMARY
;========= COMMENTS =========
CONTACT1_NAME = CLT2291
;####################################################
Le résultat Final :
Le Script :
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#FICHIERS_Temp ]'))
DROP TABLE #FICHIERS_Temp
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#FICHIERS]'))
DROP TABLE #FICHIERS
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Resultat]'))
DROP TABLE #Resultat
CREATE TABLE #FICHIERS_Temp
(
DESC_LIG [varchar](MAX) NULL
)
CREATE TABLE #FICHIERS
(
NUM_LIG [int] IDENTITY(1,1) NOT NULL,
DESC_LIG [varchar](MAX) NULL
)
CREATE TABLE #Resultat
(
NOM_CLIENT varchar(MAX) NULL,
DESCRIPTION_CLIENT varchar(MAX) NULL
)
--Insérer le contenu de fichiers dans la table temporaire
BULK INSERT #FICHIERS_Temp FROM 'C:\fichiers.txt'
INSERT INTO #FICHIERS(DESC_LIG) SELECT DESC_LIG FROM #FICHIERS_Temp
DECLARE @debut_rech AS INT
DECLARE @fin_rech AS INT
DECLARE @nbre_lig AS INT
DECLARE @nom_client AS VARCHAR(70)
DECLARE @desc_client AS VARCHAR(70)
DECLARE @exit_while AS BIT
SELECT @nbre_lig=MAX(NUM_LIG) FROM #FICHIERS WHERE DESC_LIG like '%CLIENTS%'
SELECT @debut_rech=MIN(NUM_LIG) FROM #FICHIERS WHERE DESC_LIG like '%CLIENTS%'
SELECT @fin_rech=0
WHILE @debut_rech<=@nbre_lig
BEGIN
SELECT @nom_client=NULL
SELECT @desc_client=NULL
SELECT @debut_rech=MIN(NUM_LIG) FROM #FICHIERS WHERE DESC_LIG like '%CLIENTS%' and NUM_LIG>=@fin_rech
SELECT @fin_rech=MIN(NUM_LIG) FROM #FICHIERS WHERE DESC_LIG like '%CLIENTS%' and NUM_LIG>@debut_rech
IF @fin_rech IS NULL
BEGIN
SELECT @fin_rech=MIN(NUM_LIG) FROM #FICHIERS WHERE DESC_LIG like '%DESCRIPTION%' and NUM_LIG>@debut_rech
IF @fin_rech IS NULL
BEGIN
SELECT @exit_while=1
END
END
SELECT @nom_client=DESC_LIG FROM #FICHIERS WHERE NUM_LIG=@debut_rech
SELECT @desc_client=DESC_LIG FROM #FICHIERS WHERE NUM_LIG>=@debut_rech and NUM_LIG<=@fin_rech and DESC_LIG like '%DESCRIPTION%'
INSERT INTO #Resultat([NOM_CLIENT],[DESCRIPTION_CLIENT]) VALUES (@nom_client,@desc_client)
IF @exit_while=1
BEGIN
BREAK
END
END
--
UPDATE #Resultat
SET
NOM_CLIENT=SUBSTRING(NOM_CLIENT,CHARINDEX('=',NOM_CLIENT,0)+1,LEN(NOM_CLIENT) - CHARINDEX('=',NOM_CLIENT,0))
,DESCRIPTION_CLIENT=SUBSTRING(DESCRIPTION_CLIENT,CHARINDEX('=',DESCRIPTION_CLIENT,0)+1,
LEN(DESCRIPTION_CLIENT) - CHARINDEX('=',DESCRIPTION_CLIENT,0))
DELETE FROM #Resultat WHERE NOM_CLIENT IS NULL AND DESCRIPTION_CLIENT IS NULL
--Afficher le résultat
SELECT * FROM #Resultat
--Supprimer les tables temporaire
DROP TABLE #FICHIERS_Temp
DROP TABLE #FICHIERS
DROP TABLE #Resultat