Vitesse d'une macro d'importation depuis JSON

Bonjour à tous,

J'ai créé une macro qui importe depuis un serveur distant une base de données stockée en JSON.

La macro marche très bien, elle effectue un GET, puis convertit le JSON en liste d'objets, puis crée une liste de listes qu'elle colle et redimensionne dans la première cellule de mon tableau.

J'ai opté pour une liste de listes car écrire dans chaque cellule une par une était très long..

La macro s'exécute tout de même en 3 à 4 secondes pour 1500 lignes, ce que je trouve plutôt long, je viens donc vers vous pour réfléchir à un moyen de raccourcir ce temps d'exécution car je devrai gérer plusieurs milliers de lignes à l'avenir.

Voici l'extrait de la macro responsable de 90% du temps d'exécution.

'Requete GET
    Dim objHTTP As Object, Url As String
    Dim body As String

    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    Url = xxx
    body = "{""TABLE"":""" & sh & """}"

    objHTTP.Open "GET", Url, False
    objHTTP.Send body

    On Error GoTo Errorhandler
    JSONTEXT = objHTTP.ResponseText
    On Error GoTo 0

    'Conversion du JSON en STRING
    Set jsonObject = JsonConverter.ParseJson(JSONTEXT)

    'Comptage nb lignes de la BDD
    Dim nblignes As Long
    nblignes = 0
    For Each item In jsonObject("ENTRIES")
        nblignes = nblignes + 1
    Next item

    'Initialisation numéro de colonne de la dernière colonne de données
    Dim endcol As Integer
    endcol = .Range("A1").End(xlToRight).Column

    'Initialisation variables
    Dim c As Integer
    Dim Colonnes()
    Dim BDD()
    If nblignes <> 0 Then
        ReDim BDD(1 To nblignes, 1 To endcol)
    End If

    'Initialisation première ligne du tableau
    L = 1

    'Parcourt chaque item de la réponse serveur, génère une liste de listes et la colle dans la première colonne
    For Each item In jsonObject("ENTRIES")

        ReDim Colonnes(1 To endcol)

        For c = 1 To endcol
            Colonnes(c) = .Cells(1, c).Value
            'S'il s'agit d'une date, met en forme
            If Left(Colonnes(c), 4) = "DATE" Then
                If item(Colonnes(c)) <> "" Then
                    BDD(L, c) = CDate(item(Colonnes(c)))
                Else
                    BDD(L, c) = ""
                End If
            'S'il s'agit d'un téléphone, met en forme
            ElseIf Left(Colonnes(c), 3) = "TEL" Then
                If item(Colonnes(c)) <> "" Then
                    BDD(L, c) = IIf(Left(item(Colonnes(c)), 1) <> "+", Format(item(Colonnes(c)), "0# ## ## ## ##"), Format(item(Colonnes(c)), "+## # ## ## ## ##"))
                Else
                    BDD(L, c) = ""
                End If
            'S'il s'agit d'un code postal, met en forme
            ElseIf Left(Colonnes(c), 2) = "CP" Then
                If item(Colonnes(c)) <> "" Then
                    If Len(item(Colonnes(c))) <= 2 Then
                        BDD(L, c) = IIf(Left(item(Colonnes(c)), 1) = 0, Format(item(Colonnes(c)), "0#"), Format(item(Colonnes(c)), "##"))
                    Else
                        BDD(L, c) = IIf(Left(item(Colonnes(c)), 1) = 0, Format(item(Colonnes(c)), "0####"), Format(item(Colonnes(c)), "#####"))
                    End If
                Else
                    BDD(L, c) = ""
                End If
            Else
                BDD(L, c) = item(Colonnes(c))
            End If
        Next c

        L = L + 1

    Next item

    'Ecriture des lignes
    If nblignes <> 0 Then
        .Range(tableau & "[" & Colonnes(1) & "]").Rows(1).Resize(L - 1, endcol) = BDD
    End If

Merci beaucoup pour votre temps et votre aide !

Salut,

Perso, je pense que ton temps le plus long se passe ici

 'Conversion du JSON en STRING
    Set jsonObject = JsonConverter.ParseJson(JSONTEXT)

Salut,

Merci pour ta réponse !

Je viens de tester avec les debug. Print(now)

La ligne que tu as mise en évidence met 1 seconde à s'exécuter, tandis que la boucle for en dessous dure entre 2 et 3 secondes :/

Re,

Ok, tu travaille dans une variable tableau, doc ça devrait effectivement être rapide

Ton

  ReDim Colonnes(1 To EndCol)

après la boucle For, est-ce normal ?

Après, ça peut-être les conversions de format qui peuvent prendre du temps...

Re,

En effet, je pourrais remplir mon tableau Colonnes avant la boucle For, ce qui m'éviterait de le refaire à chaque itération, merci !

Je vais essayer ça. Mais pas certain que cela règle le problème entièrement, je laisse le sujet ouvert.

Je vais faire quelques essais aussi sans les mises en forme, mais malheureusement j'en ai cruellement besoin

Merci pour ton aide

bonjour,

tu fais 2 fois la boucle json entry

la première fois pour connaitre le nombre de lignes pour pouvoir dimensionner ton tableau.

dimensionne ton tableau à la taille maximum à laquelle tu t'attends et fais un seul passage sur jsonentry

quelque chose du genre

        
    'Initialisation numéro de colonne de la dernière colonne de données
    Dim endcol As Integer
    endcol = .Range("A1").End(xlToRight).Column

    'Initialisation variables
    Dim c As Integer
    Dim Colonnes()
    Dim BDD()
        ReDim BDD(1 To 200000, 1 To endcol)
        ReDim Colonnes(1 To endcol)

alternative à investiguer

jsonobject n'a-t-il pas un paramètre qui donne le nombre d'éléments (length ?)

Bonjour,

Bon, 3 à 4 secondes, c'est pas mal du tout. Ton code est bien foutu. Je me pose la question des différents IF pour détecter DATE, TEL, CP

La piste de h2so4 est intéressante

jsonobject n'a-t-il pas un paramètre qui donne le nombre d'éléments (length ?)

Très souvent, les json commencent par un paramètre parfois appelé nhits

Sinon, il est possible de le prototyper en définissant une fonction comme suit :

    Dim ScriptEngine As Object
    Set ScriptEngine = CreateObject("ScriptControl")
    With ScriptEngine
        .Language = "JScript"
        .AddCode "function N(jsonObj){return jsonObj.length;} "
    End With
    Dim JsonObject As Object
    Set JsonObject = ScriptEngine.Eval("(" + JSONTEXT + ")")

et ensuite l'utiliser dans la macro comme suit :

ScriptEngine.Run("N",JsonObject)

exemple ici : https://forum.excel-pratique.com/viewtopic.php?p=840103#p840103

Merci beaucoup à vous 2 !

Effectivement, je vais éviter cette première boucle sur les entry, j'avais juste peur de dimensionner mon tableau "au hasard" mais cela ne doit pas être très gourmand.

Très intéressante cette méthode Steelson, à tester !

Rechercher des sujets similaires à "vitesse macro importation json"