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 !