Mise à jour d’une base Access par fichier CSV ou Excel – Episode 7

par Hervé Inisan · Publié 26 novembre 2013 · Mis à jour 12 mai 2014

Encore un épisode de notre saga sur l‘importation de fichiers CSV ou Excel dans une base Access.

Bientôt, j’aurai autant de chapitres que Star Wars, et je pourrai préparer des versions remasterisées, Dolby, BluRay. Et me faire racheter par Mickey ?! 😉

Dans ce nouvel article, on va monter d’un cran en puissance : jusqu’à présent, on n’a toujours importé qu’une table à la fois, à l’aide de la classe TableUpdater.

Dans la réalité, vous risquez de vouloir importer plusieurs fichiers en séquence. C’est ce que va faire la nouvelle classe DatabaseUpdater, détaillée dans la suite.

Si vous avez manqué le début

Relisez les articles précédents, si vous ne l’avez pas déjà fait… sinon, rien ne marchera 😉

Résumé des épisodes précédents

Rappel des titres

Pour l’instant, chaque importation de fichier consistait à :

Instancier un objet TableUpdater.
Le paramétrer (fichier source, table cible, clef primaire, etc.).
Exécuter l’importation.
Afficher un message de résultat.

Mais vous n’allez pas répéter tout ça, si chaque mois vous avez 10 fichiers CSV ou Excel à importer. 😉 On va donc :

Lister tous les paramètres d’importation (tous les paramètres de TableUpdater, donc) dans une table Access spécifique.
Faire en sorte de parcourir cette table pour enchaîner chaque importation.

La table des mises à jour

On va donc créer dans notre base Access une table, dont chaque champ correspond à une propriété de la classe TableUpdater. Voici la structure de cette table (qui s’appelle tbl Updates) :

Liste des mises à jour

En gros, vous obtenez une version « tablifiée » (ouf) de la classe TableUpdater. J’ai ajouté quelques champs :

Priority : Valeur numérique permettant de classer les importations. Concrètement, si vous importez un fichier Clients et un fichier Factures, le fichier Clients aura une priorité de 1, le fichier Factures une priorité de 2. Puisque les clients doivent être actualisés avant les factures.
Category : Ce champ est prévu pour contenir un texte quelconque. L’idée est de pouvoir importer plusieurs fichiers appartenant à une même catégorie, sans toujours importer tous les fichiers listés dans cette table. Ça ne doit pas être bien clair, tout ça, vous verrez dans les exemples ! 🙂
StartTime et EndTime : date/heure de début et de fin d’importation, si vous traitez des fichiers volumineux.

La classe DatabaseUpdater

Si vous avez suivi, l’idée est donc d’extraire x lignes de notre nouvelle table (x lignes correspondant à une même catégorie), de transformer ces lignes en objets TableUpdater, et de déclencher la mise à jour correspondante (vous vous souvenez qu’un TableUpdater gère un seul fichier et une seule table à la fois).

Voici la classe DatabaseUpdater qui va prendre ça en charge (à recopier dans un module de classe, et pas un module standard) :

' ----------------------------------------
' Module      : DatabaseUpdate
' Auteur      : Hervé Inisan
' Description : Classe d'import CSV/Excel.
'               Cette classe joue un scénario d'importation
'               de x fichiers CSV ou Excel dans autant de
'               tables Access.
' ----------------------------------------

Option Compare Database
Option Explicit

' ---
' CONSTANTES
' ---

Private Const UPDATE_TABLE As String = "tbl Updates"

' ---
' MEMBRES PRIVES
' ---

Private m_strLastImport As String

' ---
' EVENEMENTS
' ---

Public Event TableUpdating(ByVal lngImportId As Long)
Public Event TableUpdated(ByVal lngImportId As Long)

' ---
' PROPRIETES
' ---

Public Property Get LastImport() As String
    LastImport = m_strLastImport
End Property

' ---
' METHODES
' ---

' ---
' MISE A JOUR DE LA BASE DE DONNEES
' ---
' Entrée : strCategory <- Catégorie de fichiers à importer.
'                         La catégorie correspond au champ Category de
'                         la table [tbl Updates] et permet de déclencher
'                         une importation sélective de fichiers.
'
Public Function Update(Optional ByVal strCategory As String = "") As Boolean
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim blnError As Boolean

    ' Réinitialiser la table de mise à jour
    ClearLog strCategory

    ' Ouvrir la table des mises à jour
    strSQL = AddCategory("SELECT * FROM [{0}]", strCategory) _
        & " ORDER BY Priority"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    ' Traiter chaque mise à jour
    blnError = False
    Do While Not rst.EOF
        ' Importation et mises à jour
        If UpdateTable(rst) <> NoError Then
            m_strLastImport = StringFormat( _
                "Importation = {0}{3}Source = {1}{3}Cible = {2}", _
                rst("Id"), _
                rst("Source"), _
                rst("Target"), _
                vbCrLf)
            blnError = True
            Exit Do
        End If

        ' Mise à jour suivante
        rst.MoveNext
    Loop

    ' Libérer les ressources
    rst.Close
    Set rst = Nothing
    Update = Not blnError
End Function

' ---
' MISE A JOUR D'UNE TABLE
' ---
'
Private Function UpdateTable(ByVal rst As DAO.Recordset) As ImportInfo
    Dim tu As TableUpdater

    ' Lever un événement pour signaler le début
    ' de la mise à jour
    RaiseEvent TableUpdating(rst("Id"))

    Set tu = New TableUpdater
    With tu
        ' Fichier source (chemin, type, version)
        .Source = rst("Source")
        .SourceType = IIf(rst("SourceType") = "CSV", _
            SourceTypes.Csv, SourceTypes.Excel)
        .ExcelVersion = rst("ExcelVersion")

        ' Description des données
        .Headers = rst("Headers")
        .ImportSpecs = Nz(rst("ImportSpecs"), "")

        ' Table cible et table temporaire
        .Target = rst("Target")
        If Not IsNull(rst("TempTable")) Then
            .TempTable = rst("TempTable")
        End If

        ' Divers
        .AutoClean = rst("AutoClean")

        ' Date/Heure de départ
        rst.Edit
        rst("StartTime") = Now
        rst("EndTime") = Null
        rst.Update

        ' Importation
        .Import
        UpdateTable = .LastError.Number

        ' Stockage des résultats dans la table d'importation
        rst.Edit
        rst("ImportInfo") = .LastError.Number
        rst("InsertedRows") = .InsertedRows
        rst("UpdatedRows") = .UpdatedRows
        rst("EndTime") = Now
        rst.Update

        ' Lever un événement pour signaler la fin de la mise à jour
        If .LastError.Number = NoError Then
            RaiseEvent TableUpdated(rst("Id"))
        End If
    End With
    Set tu = Nothing
End Function

' ---
' REINITIALISATION DU JOURNAL D'UNE CATEGORIE
' ---
'
Private Sub ClearLog(Optional ByVal strCategory As String = "")
    Dim strSQL As String

    strSQL = AddCategory( _
        "UPDATE [{0}] SET InsertedRows = 0, UpdatedRows = 0", _
        strCategory)
    CurrentDb.Execute strSQL
End Sub

' ---
' AJOUT DE LA CATEGORIE
' ---
'
Private Function AddCategory( _
    ByVal strSQL As String, _
    ByVal strCategory As String)

    If strCategory <> "" Then
        strSQL = strSQL & " WHERE Category = '{1}'"
    End If
    strSQL = StringFormat(strSQL, _
        UPDATE_TABLE, _
        Replace(strCategory, "'", "''"))

    AddCategory = strSQL
End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

' ----------------------------------------

' Module : DatabaseUpdate

' Auteur : Hervé Inisan

' Description : Classe d'import CSV/Excel.

' Cette classe joue un scénario d'importation

' de x fichiers CSV ou Excel dans autant de

' tables Access.

' ----------------------------------------

Option Compare Database

Option Explicit

' ---

' CONSTANTES

' ---

Private Const UPDATE_TABLE As String = "tbl Updates"

' ---

' MEMBRES PRIVES

' ---

Private m_strLastImport As String

' ---

' EVENEMENTS

' ---

Public Event TableUpdating(ByVal lngImportId As Long)

Public Event TableUpdated(ByVal lngImportId As Long)

' ---

' PROPRIETES

' ---

Public Property Get LastImport() As String

LastImport = m_strLastImport

End Property

' ---

' METHODES

' ---

' MISE A JOUR DE LA BASE DE DONNEES

' ---

' Entrée : strCategory <- Catégorie de fichiers à importer.

' La catégorie correspond au champ Category de

' la table [tbl Updates] et permet de déclencher

' une importation sélective de fichiers.

Public Function Update(Optional ByVal strCategory As String = "") As Boolean

Dim rst As DAO.Recordset

Dim strSQL As String

Dim blnError As Boolean

' Réinitialiser la table de mise à jour

ClearLog strCategory

' Ouvrir la table des mises à jour

strSQL = AddCategory("SELECT * FROM [{0}]", strCategory) _

& " ORDER BY Priority"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

' Traiter chaque mise à jour

blnError = False

Do While Not rst.EOF

' Importation et mises à jour

If UpdateTable(rst) <> NoError Then

m_strLastImport = StringFormat( _

"Importation = {0}{3}Source = {1}{3}Cible = {2}", _

rst("Id"), _

rst("Source"), _

rst("Target"), _

vbCrLf)

blnError = True

Exit Do

End If

' Mise à jour suivante

rst.MoveNext

Loop

' Libérer les ressources

rst.Close

Set rst = Nothing

Update = Not blnError

End Function

' ---

' MISE A JOUR D'UNE TABLE

' ---

Private Function UpdateTable(ByVal rst As DAO.Recordset) As ImportInfo

Dim tu As TableUpdater

' Lever un événement pour signaler le début

' de la mise à jour

RaiseEvent TableUpdating(rst("Id"))

Set tu = New TableUpdater

With tu

' Fichier source (chemin, type, version)

.Source = rst("Source")

.SourceType = IIf(rst("SourceType") = "CSV", _

SourceTypes.Csv, SourceTypes.Excel)

.ExcelVersion = rst("ExcelVersion")

' Description des données

.Headers = rst("Headers")

.ImportSpecs = Nz(rst("ImportSpecs"), "")

' Table cible et table temporaire

.Target = rst("Target")

If Not IsNull(rst("TempTable")) Then

.TempTable = rst("TempTable")

End If

' Divers

.AutoClean = rst("AutoClean")

' Date/Heure de départ

rst.Edit

rst("StartTime") = Now

rst("EndTime") = Null

rst.Update

' Importation

.Import

UpdateTable = .LastError.Number

' Stockage des résultats dans la table d'importation

rst.Edit

rst("ImportInfo") = .LastError.Number

rst("InsertedRows") = .InsertedRows

rst("UpdatedRows") = .UpdatedRows

rst("EndTime") = Now

rst.Update

' Lever un événement pour signaler la fin de la mise à jour

If .LastError.Number = NoError Then

RaiseEvent TableUpdated(rst("Id"))

End If

End With

Set tu = Nothing

End Function

' ---

' REINITIALISATION DU JOURNAL D'UNE CATEGORIE

' ---

Private Sub ClearLog(Optional ByVal strCategory As String = "")

Dim strSQL As String

strSQL = AddCategory( _

"UPDATE [{0}] SET InsertedRows = 0, UpdatedRows = 0", _

strCategory)

CurrentDb.Execute strSQL

End Sub

' ---

' AJOUT DE LA CATEGORIE

' ---

Private Function AddCategory( _

ByVal strSQL As String, _

ByVal strCategory As String)

If strCategory <> "" Then

strSQL = strSQL & " WHERE Category = '{1}'"

End If

strSQL = StringFormat(strSQL, _

UPDATE_TABLE, _

Replace(strCategory, "'", "''"))

AddCategory = strSQL

End Function

En y regardant de plus près, la méthode principale de DatabaseUpdater est la méthode Update(), qui reçoit une catégorie à filtrer, qui extrait les lignes correspondantes à l’aide d’un Recordset, et qui boucle sur ces lignes pour les traiter une à une.
La seconde méthode est UpdateTable(), qui se charge de la transformation d’une ligne de table en instance de TableUpdater, et qui importe un fichier donné dans la table correspondante. Cette méthode est appelée à chaque « tour de boucle » de la fonction Update().

Je vous laisse digérer cette bouillie 😉
On verra dans le prochain article comment tester DatabaseUpdater.

Étiquettes : access 2002 access 2003 access 2007 access 2010 access 2013 csv excel fichier table vba

Rémy dit :

5 mars 2017 à 16 h 56 min

Merci encore pour tous ce travail et ces bons conseils, néanmoins je bloc à l’étape 2 lors du test du fichier csv, il me donne comme info:
Erreur Compilation
type défini par l’utilisateur non défini
et point sur cette ligne:
Sub TestExportCSV5()
‘ Variables
Dim ce As New CSVExport

Merci de votre aide

Répondre
- Hervé Inisan dit :
  
  6 mars 2017 à 8 h 52 min
  
  La classe CSVExport doit faire partie du projet, elle est décrite dans les premiers articles de la série (notamment celui-ci : Export CSV personnalisé.
  Au cas où : le commentaire est posté sur un article qui ne concerne pas l’export, mais l’import CSV. 😉
  
  Répondre
  - remy dit :
    
    8 mars 2017 à 9 h 17 min
    
    Bonjour
    Désolé pour cette erreur, mais c’était la première fois que je me servais du forum.
    J’ai écris un nouveau message dans la série Export CSV -6.
    Merci pour votre réponse et apprécie vraiment votre travail.
    
    Répondre
    
    Répondre
    - Hervé Inisan dit :
      
      8 mars 2017 à 11 h 47 min
      
      Pas de souci, il n’y a pas mal ! 🙂
      
      Répondre

Mise à jour d’une base Access par fichier CSV ou Excel – Episode 7

Si vous avez manqué le début

Rappel des titres

La table des mises à jour

La classe DatabaseUpdater

Vous aimerez aussi...

4 réponses

Laisser un commentaire Annuler la réponse

En direct des forums self-access.com

Thèmes Access

Mise à jour d’une base Access par fichier CSV ou Excel – Episode 7

Si vous avez manqué le début

Rappel des titres

La table des mises à jour

La classe DatabaseUpdater

Vous aimerez aussi...

Mise à jour d’une base Access par fichier CSV ou Excel – Episode 4

Scripting Runtime

Lister les fichiers d’un dossier et de ses sous-dossiers

4 réponses

Laisser un commentaire Annuler la réponse

En direct des forums self-access.com

Thèmes Access