Le site de Christopher PECAUD

La boîte à Tutoriels de Christopher PECAUD

Création d’une base Access vide et importation de données à partir d’un script Powershell

Laisser un commentaire

SOMMAIRE

I Introduction
II Prérequis
III Un petit tour d’horizon des objets disponibles dans l’API ADOX
IV Code du script
1. Fonction de création de la base
2. Fonction de création de la table
3. La méthode de création des colonnes Create_Columns
4. La méthode d’importation des données
5. Le programme principal
IV Conclusion

I. Introduction

Dans ce document nous allons voir comment automatiser la création d’une base de données Access et y importer des données. Nous utiliserons l’API ADOX pour gérer la création de notre base de données de la création du fichier de base de données vide jusqu’à la création des champs en passant par la table. L’importation des données se fera par l’intermédiaire d’un objet « Ensemble d’enregistrement ».

II. Prérequis

Vous devez disposer du moteur de base de données Access 2016. Si tel n’est pas le cas vous pouvez le télécharger à l’adresse suivante : https://www.microsoft.com/en-us/download/details.aspx?id=54920

Pour ce qui est des données à importer nous allons utiliser la fonction Get-EventLog afin de récupérer les logs système avec un niveau d’alerte « Error ». Chaque propriété récupérée permettra de créer une colonne dans la table « System_Logs » que nous créerons auparavant. Nous essayerons tant bien que mal de formater les colonnes en fonction du type de chaque propriété.

III. Un petit tour d’horizon des objets disponibles dans l’API ADOX

Dans cette partie nous allons découvrir les principaux objets mis à notre disposition par cette API. Pour ce document nous allons utiliser principalement les objets suivants :

  • L’objet ADOX.Catalog qui est l’objet principal de l’API et nous permet de gérer une source de données. Nous utiliserons la méthode Append de la collection Tables associée pour ajouter la table créée à cet objet ;
  • L’objet ADOX.Table qui permet de créer une table. Nous utiliserons principalement la propriété Name pour lui donner le nom de la table dans Access. Nous utiliserons de même la collection Columns pour lui attribuer toutes les colonnes que nous allons créer. La collection Keys pour lui attribuer la clé primaire de la table ;
  • L’objet ADOX.Column qui permet de créer et configurer des colonnes dans Access. Nous utiliserons principalement les propriétés Name et Type ;
  • L’objet ADOX.Key qui permet de créer une clé primaire ou étrangère sur une table. Dans notre exemple nous allons utiliser les propriétés Name et Type qui permettent de donner un nom et un type à la clé créée. Nous utiliserons la collection Columns pour associer la clé créée à la colonne voulue ;
  • L’objet ADOX.Index qui permet de créer un index sur une table. Nous utiliserons les propriétés booléennes PrimaryKey et Unique pour savoir si l’index opère sur une colonne sur laquelle on a configuré une clé et si la colonne accepte ou non des valeurs uniques. Nous utiliserons de même la collection Columns pour associer l’index créé à la colonne désirée ;

Pour plus d’informations sur ces différents objets vous pouvez vous rendre à cette adresse : https://docs.microsoft.com/fr-fr/office/client-developer/access/desktop-database-reference/adox-api-reference

En ce qui concerne l’importation des données nous utiliserons un objet de type Connection et un autre de type Recordset de l’API ADODB.

Nous pouvons continuer avec le code de notre script.

IV. Code du script

1. Fonction de création de la base

Cette fonction permet de créer un fichier de base de données Access de type accdb complètement vide.

La fonction prend en paramètre le nom du fichier accdb à créer ainsi que l’objet hashtable comprenant tous les logs d’événement de type Error récupérés par la fonction Get-EventLog.

Nous créons donc dans cette fonction un objet de type Catalog par l’intermédiaire de la méthode Create et qui prend en paramètre le fournisseur de la base de données Access qui est dans notre cas Microsoft.ACE.OLEDB.16.0, suivi de la source de données qui n’est autre que le nom du fichier Access à créer. 

function Create_Database {

    Param(

    [String]$filename,
    [Object]$log

    )    

    Write-Output("Nom du fichier à créer: "+ $filename)
    $cat = New-Object -ComObject ADOX.Catalog.6.0
    $cat.Create("Provider=Microsoft.ACE.OLEDB.16.0;Data Source="+ $filename)
    Create_Table $cat $log
    $cat = Null

}

C’est par l’intermédiaire de l’objet $cat que nous allons créer les autres objets de la base de données, c’est-à-dire la table, les différentes colonnes, les index et les clés.

Nous appelons en fin de fonction la méthode Create_Table qui va nous permettre de créer la table « System_Logs ».

2. Fonction de création de la table

Cette fonction reçoit en paramètre la variable de type Catalog $cat, ainsi que l’objet contenant la liste des logs récupérés par l’intermédiaire de la méthode Get-EventLog.

Au sein de cette fonction nous créons une instance de l’objet ADOX.Table qui nous permettra de créer la table nommée « System_Logs » dans la base de données Access que nous venons de créer, en utilisant la propriété Name de l’objet ADOX.Table.

Une fois la table créée nous appelons la fonction de création des colonnes de la table que nous verrons dans le paragraphe suivant.

Elle permet aussi pour l’exemple de créer une clé primaire en utilisant le type objet ADOX.Key et en utilisant les propriété Name et la collection Columns pour signifier que la clé doit être assignée à la colonne Index.

Nous créons pour l’exemple un index sur la colonne « Index » qui sera créée automatiquement dans la fonction Create_Columns en utilisant l’objet de type ADOX.Column avec les propriétés PrimaryKey et Unique ainsi que la collection Columns pour lui signifier que l’index sera assigné à la colonne « Index ».

Voici le corps complet de la fonction : 

function Create_Table {
    Param(
    [Object]$catalog,
    [Object]$log
    )

    $tbl = New-Object -ComObject ADOX.Table
    $tbl.Name = "System_Logs"
    $catalog.Tables.Append($tbl)

    Create_Columns $tbl $log
    $key = New-Object -ComObject ADOX.Key
    $key.Type = 1
    $key.Name = "Primary Key"
    $key.Columns.Append("Index")    
    $tbl.keys.Append($key)

    $index = New-Object -ComObject ADOX.Index

    $index.PrimaryKey = $True

    $index.Unique = $True

    $index.Columns.Append("Index")
    $key = Null
    $index = Null
    $tbl = Null

}

3. La méthode de création des colonnes Create_Columns

Cette fonction reçoit en paramètre l’objet de type ADOX.Table que nous avons créé dans la méthode Create_Table ($tbl) ainsi que l’ensemble des logs récupérés par la méthode Get-EventLog ($log)

Nous récupérons les différents types de chaque propriété inclus dans le premier exemplaire de la variable hashtable $log et nous appelons notre méthode GetPropertyType permettant de définir le type de colonne à créer.

Cette méthode reçoit en paramètre la propriété que la fonction aura à analyser par la suite. Elle renvoie le type de la colonne dans Access à créer.

La variable $property contient le type suivi du nom et des getter setter. Nous n’avons besoin que du type, c’est pour cette raison que nous allons effectuer une opération nous permettant de récupérer la sous-chaîne commençant au caractère 0 pour aller jusqu’au premier caractère « » (espace). 

    # On récupère le type et le nom de la propriété sous forme de chaîne de caractères
    $proptype = $prop.toString()

    # On crée cette variable pour stocker le caractère espace qui va nous permettre de récupérer une sous-chaine
    $pos = $proptype.IndexOf(" ")

    # Récupération du type de la propriété
    $type = $proptype.Substring(0, $pos)

Ensuite nous analysons le type obtenu dans une structure switch. Nous nous basons sur les constantes mises à disposition par Microsoft pour faire la correspondance. Elle est disponible à cette adresse :

https://docs.microsoft.com/fr-fr/sql/ado/reference/ado-api/datatypeenum?view=sql-server-ver15

Celle-ci est établie principalement pour SQL Server mais les valeurs sont compatibles avec Access. 

switch ( $type )

    {

        "Int16" { $result = 203    }
        "long" { $result = 203   }
        "int"  { $result = 3}
        "byte[]" { $result = 202}
        "string" { $result = 203   }
        "string[]" { $result = 203  }
        "datetime" { $result = 7  }
        default { $result = 202    }
    }

    return $result

}

Access gère beaucoup moins de type que ne le permet SQL Server c’est pour cela que pour certain type obtenu nous utiliserons le type Texte long ou Texte Court.

Une fois le type récupéré par la méthode Create_Columns nous assignons la valeur directement à la propriété Type de l’objet de type ADOX.Column : 

        $col.Type = GetPropertyType($property)

Nous ajoutons ensuite la colonne créée à l’objet de type ADOX.Table : 

        $table.Columns.Append($col)

Voici le code complet de la méthode permettant de déterminer le type d’une colonne à créer : 

function GetPropertyType {

    Param (
        [Object]$prop
    )

    # On récupére le type et le nom de la propriété
    $proptype = $prop.toString()

    # On crée cette variable pour stocker le caractère espace qui va nous permettre de récupérer une sous-chaine
    $pos = $proptype.IndexOf(" ")

    # Récupération du type de la propriété
    $type = $proptype.Substring(0, $pos)

   

    #Traitement de la variable $type pour récupérer le type de colonne à créer dans la base de données
    switch ( $type )
    {

        "Int16" { $result = 203    }
        "long" { $result = 203   }
        "int"  { $result = 3}
        "byte[]" { $result = 202}
        "string" { $result = 203   }
        "string[]" { $result = 203  }
        "datetime" { $result = 7  }
        default { $result = 202    }
    }

    return $result

}

Voici le code complet de la méthode de création des colonnes de la table. 

function Create_Columns {

    Param(
    [Object]$table,
    [Object]$log
    )

     #Nous ajoutons les différentes propriétés incluses dans la variable $log au table
    foreach($property in ($log[1] | Get-Member -MemberType Property )) {

        $col = New-Object -ComObject ADOX.Column
        $col.Name = $property.Name
        $col.Type = GetPropertyType($property)
        $table.Columns.Append($col)
        $col = Null
    }

}

A ce stade la structure de la base de données est maintenant terminée il nous reste plus qu’à importer les données récupérées dans la table créée.

4. La méthode d’importation des données

Il nous reste plus qu’à importer les données au sein de la base de données que nous venons de créer pour se faire nous devons ouvrir une connexion vers celle-ci. On crée dans un premier temps une boucle qui va permettre d’itérer chaque événement inclus dans la liste d’objets : 

foreach ($datarow in $dataToImport) { 

….

}

A l’intérieur de cette boucle, nous allons recourir à la création d’une instance d’objet de ADODB.Connection. Nous utilisons la méthode Open pour ouvrir la connexion avec la base de données Access en fournissant comme chaîne de connexion le fournisseur de base de données et le chemin vers le fichier physique comme source de données : 

         $connection = New-Object -ComObject ADODB.Connection

        $connection.Open("Provider=Microsoft.ACE.OLEDB.16.0;Data Source="+ $filename)

Une fois la connexion établie, il suffit d’importer chaque log récupéré dans un enregistrement de la base de données. On crée donc un objet de type RecordSet qui va nous permettre de récupérer la structure de la table dans un premier temps et de remplir celle-ci avec les différents enregistrements que l’on a récupérés : 

       $rs = New-Object -ComObject ADODB.RecordSet

On crée ensuite la requête qui va nous retourner l’ensemble des champs de la base de données : 

       $query = "Select * FROM System_Logs"

On associe ensuite l’objet créé à la connexion ouverte à l’étape précédente mais aussi à la requête que l’on vient de créer : 

      $rs.open($query, $connection, 3, 3)

On crée un nouvel enregistrement dans la base de données en utilisant la méthode AddNew : 

      $rs.AddNew()

Pour gérer chaque champ de la base nous créons une deuxième boucle imbriquée : 

      foreach ($property_name in $dataToImport | Get-Member -MemberType Property) { 

            ….

      }

A l’intérieur de celle-ci on crée une variable permettant de récupérer chaque nom correspondant à chaque propriété incluse dans l’objet de type log système, $membername : 

           $membername = ($datarow | Get-Member -Name $property_name.Name).Name

Ensuite il suffit de créer une deuxième variable permettant de récupérer la valeur associée, $member_value : 

          $member_value = ($datarow | Select-Object -Property $membername).$membername

Ensuite on enregistre cette valeur dans la table System_Logs en utilisant la collection Fields de l’objet RecordSet : 

         $rs.Fields.Item($property_name.Name) = $member_value

Pour que l’enregistrement du champ soit effectif il faut effectuer une mise à jour de l’objet RecordSet : 

    $rs.Update()

Il ne reste plus qu’à fermer le RecordSet et la connexion : 

    $rs.close()

    $connection.Close()

Voici le code complet de la fonction : 

function ImportLogsData {
    Param(
        [String]$filename, 
        [Object]$dataToImport

    )

   Write-Host "Importation des données dans la base..."

    foreach ($datarow in $dataToImport) {
        $connection = New-Object -ComObject ADODB.Connection
        $connection.Open("Provider=Microsoft.ACE.OLEDB.16.0;Data Source="+ $filename)
        $rs = New-Object -ComObject ADODB.RecordSet
        $query = "Select * FROM System_Logs"
        $rs.open($query, $connection, 3, 3)
        $rs.AddNew()

        foreach ($property_name in $dataToImport | Get-Member -MemberType Property) {

            $membername = ($datarow | Get-Member -Name $property_name.Name).Name
            $proptype = $property_name.toString()
            $pos = $proptype.IndexOf(" ")
            $type = $proptype.Substring(0, $pos)
            #if (($type -eq "long") -or ($type -eq "int16")) {
            #    $member_value = [Int]($datarow | Select-Object -Property $membername).$membername
            #}
            $member_value = ($datarow | Select-Object -Property $membername).$membername
            if (!$member_value) {
                $member_value = " "
            }
            if ($membername -eq "ReplacementStrings") {
                $member_value = " "
            }

            #Write-Host "Valeur du membre " + $membername + ": " + $member_value
            $rs.Fields.Item($property_name.Name) = $member_value
        }

        $rs.Update()
        $rs.close()

        $connection.Close()    

    }

}

5. Le programme principal

Le programme principal comme nous l’avons dit consiste à exécuter la méthode Powershell Get-EventLog afin de récupérer uniquement ceux qui ont un statut Error (propriété EntryType) et qui ont été créés à partir du jour d’avant. En Powershell cela se traduit comme ceci :

$date = (Get-Date).AddDays(-1) 

$event_log_error_list = Get-EventLog -LogName System -EntryType Error -After $date

On vérifie ensuite si le fichier de la base de données existe. Si tel n’est pas le cas on crée la base de données en appelant la méthode que l’on a créée précédemment à cet effet Create_Database et qui appelle elle-même les méthodes de création de table, et des colonnes : 

$dbName = "chemin vers votre fichier .accdb "

if ((Test-Path "chemin vers votre fichier .accdb") -eq $false) {
    Write-Host "Création de la base de données..."
    Create_Database $dbName $event_log_error_list

}

Ensuite on invoque la méthode d’importation des données que l’on a créée précédemment, ImportLogsData : 

ImportLogsData $dbname $event_log_error_list

Et on quitte le script : 

Exit

Conclusion

Ce document nous a permis de voir une procédure d’automatisation de création d’une base de données Access vide et d’importation de données en utilisant un script Powershell.

Nous avons pu voir les différentes utilisations que l’on peut effectuer du moteur de base de données Access et de ses différents objets et méthodes. L’utilisation des objets ADOX a été spécialement défini pour des bases de données SQL Server mais reste compatible avec Access malgré un nombre de types de champs moins importants. Ceci peut être bloquant quant à la conversion des types de données récupérés vers les types de champs pris en charge par Access.

blog comments powered by Disqus