[SQL Server] Traitement d'un fichier txt via la commande BULK INSERT

  • Imprimer

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

-- 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 :

bulk insert resultat

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