Power Query Choisir colonnes fichier XML avec POWERQUERY

StagExcelle

XLDnaute Junior
Bonjour,

Je n'arrive pas à manipuler POWERQUERY avec ce fichier XML.

Je voudrais qu'une fois ouvert dans POWERQUERY n'avoir que certaines colonnes dans le tableau EXCEL.

Je vous envoie un petit fichier Exemple.

Ce fichier XML est basé sur la norme ESPPADOM.

Je vais essayer de joindre un fichier XML plus rempli plus tard.
 

Pièces jointes

  • order.exemple.xml.zip
    2.9 KB · Affichages: 18

StagExcelle

XLDnaute Junior
Bonjour Roblochon

Merci pour votre code et vos explications
Voici le premier test que j'ai fait
VB:
Sub LireDocument_test()
 Dim oDoc As MSXML2.DOMDocument60
 '
 '  Variables de Collections d' éléments xml par sélection
 Dim oOrders As IXMLDOMSelection    '  1 - Elements CrossIndustryOrder
 Dim oElems As IXMLDOMSelection     '  2 - Elements divers en cours de boucles
 '
 '  Variable de fonctionnements
 Dim oElem As IXMLDOMElement        ' Un seul élément dans une collection
 '
 Dim Transactions       ' Tableau des transactions
 Dim i As Integer       ' Compteur pour boucle for
 '
 ' initialisation de la variable DomDocument
 Set oDoc = New DOMDocument60
 oDoc.validateOnParse = False       ' Ne pas valider le doc lors du parsing pour ne pas lever d'erreur de malformation xml
 ' Charger le document
 oDoc.Load (ThisWorkbook.Path & "\ESPPADOM_TEST_5.xml")
 '
 ' Récupérer la collection des transactions
 Set oOrders = oDoc.getElementsByTagName("rsm:CrossIndustryOrder")
 '
 If oOrders.Length = 0 Then
    MsgBox "Aucune transaction trouvée dans le document"
    GoTo FIN
 End If
 
 ReDim Transactions(1 To oOrders.Length, 1 To 11)
 '
 ' Parcourir chaque élément de transaction
 For Each oElem In oOrders
    i = i + 1
'    'Id de transaction
'    Set oElems = oElem.getElementsByTagName("ram:SellerCITradeParty")
'    If oElems.Length > 0 Then Transactions(i, 1) = oElems(0).Text
    'RAISON SOCIALE
    Set oElems = oElem.getElementsByTagName("pie:Name")
    If oElems.Length > 0 Then Transactions(i, 1) = oElems(0).Text
    'SIRET
    Set oElems = oElem.getElementsByTagName("pie:SIRET")
    If oElems.Length > 0 Then Transactions(i, 2) = oElems(0).Text
    'Rue
    Set oElems = oElem.getElementsByTagName("pie:LineOne")
    If oElems.Length > 0 Then Transactions(i, 3) = oElems(0).Text
    
    Set oElems = oElem.getElementsByTagName("pie:PostcodeCode")
    If oElems.Length > 0 Then Transactions(i, 4) = oElems(0).Text
    
    Set oElems = oElem.getElementsByTagName("pie:CityName")
    If oElems.Length > 0 Then Transactions(i, 5) = oElems(0).Text
 Next
 ' Affichage dans la feuille 1
 ThisWorkbook.Sheets("Feuil1").Range("A2").Resize(oOrders.Length, 11) = Transactions
 'Formatage SIRET
 ThisWorkbook.Sheets("Feuil1").Range("B2:B4").NumberFormat = "### ### ### #####"
FIN:
 Set oElems = Nothing
 Set oOrders = Nothing
 Set oDoc = Nothing
End Sub
Je me permets de vous demander les explications suivantes sur votre code
ReDim Transactions(1 To oOrders.Length, 1 To 11)
J'ai compris que Transaction est un tableau mais pourquoi 1 To 11
C'est pour restituer onze colonnes dans la feuille ?
 

StagExcelle

XLDnaute Junior
Bonjour

Roblochon
Je ne comprends pas comment le code VBA fait pour distinguer les TagName (je ne sais pas si c'est le bon terme) qui ont le même nom.
Sur l'image, comment le code sait que c'est LineOne de SelelctCITTradeParty qu'il faut prendre ?
Dans le code VBA de test que j'ai posté, je n'ai pas marqué l'information dans la macro, non?
Je ne comprends non plus pour la macro affiche seulement trois lignes car dans le fichier XML simplifié il y a 6 lignes ?
 

Pièces jointes

  • Explications_XML.png
    Explications_XML.png
    94.8 KB · Affichages: 10
Dernière édition:

Hasco

XLDnaute Barbatruc
Repose en paix
bonjour,

Faites un effort de compréhension.

Je ne comprends pas comment le code VBA fait pour distinguer les TagName (je ne sais pas si c'est le bon terme) qui ont le même nom.

Je vous ai expliqué que getElementsByTagName retournait une COLLECTION de n élements qui portent le TagName passé en paramètre.
C'est à vous à travers une boucle ou directement de choisir l'élément que vous souhaitez !
Je me suis contenté pour l'exemple de tirer le premier (indice 0):
VB:
'Id de transaction
    Set oElems = oElem.getElementsByTagName("ram:SpecifiedTransactionID")
    If oElems.Length > 0 Then Transactions(i, 1) = oElems(0).Text

oElems est une collection d'éléments"ram:SpecifiedTransactionID"
En admettant qu'elle retourne toujours 3 élément et que vous en voulez le 2 ce sera oElems(1).Text

Si vous voulez parcourir les éléments d'une collection, utilisez une boucle for ou for each.
Code:
  For j = 0 To oElems.Length-1
        If oElems(j).Text = "Mon item" Then
            Debug.Print oElems(j).Text
        End If
    Next j

Si vous voulez une collection de sous éléments particulers :
Code:
Set SousCollection = oElem.getElementsByTagName("Truc:Bidule")

Payez vous le luxe d'apprendre xml et le dom en suivant des tutos, vous trouverez vos propres techniques.

Je ne comprends non plus pour la macro affiche seulement trois lignes car dans le fichier XML simplifié il y a 6 lignes ?

Si vous suiviez ce qu'on vous dit, vous auriez vu que la première collection parcourrue "oOrders" réunit les éléments de TagName "rsm:CrossIndustryOrder" et que dans votre fichier exemple il n'y en a que 3.

Remarque : La propriété .Text peut retourner TOUT le text d'un element. S'il s'agit d'une collection d'élements .Text renverra le texte de l'élément et ses sous éléments. Mieux vaut trouver l'élément unique qu'on désire et utiliser la propriété .NodeValue

Par exemple oElem.Text (qui représente un élément de la collection oOrders) donnera comme texte :
Périodicité de la prestation : Mensuel
Type de calcul : Quantité * tarif départemental (+ rappel tarifaire dans un 2ème temps)
Unité de la quantité : Heure
2019-12-10T00:00:00Z 2022-12-31T00:00:00Z 20.80 PRE 13.00 37.79 30 ADPRS Heures prestataire agréé semaine
qui est TOUT le texte de ses sous éléments.

Quand vous écrivez une macro, afficher la fenêtre 'Variables locale', lancez votre macro en 'Pas-à-pas' ou mettez des points d'arrêt (F9) sur des lignes particulières et regardez l'état de vos variables, leur type, nature et valeur

Bonne journée
 
Dernière édition:

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour Roblochon

J'essaie de comprendre mais je n'ai pas les compétences des utilisateurs avancés d'Excel comme vous.
Je vous remercie de m'aider à comprendre.

Je vais bien lire vos explications et faire des tests avant de poser une nouvelle question.
Surtout trouvez des tutos :
 

StagExcelle

XLDnaute Junior
Bonsoir

Roblochon: Merci pour vos précisions et ajouts dans le message#19.

Je continue mes tests (grâce à votre code initial - merci encore)

Je n'arrive pas toujours à trouver la réponse à la question (hors VBA) que j'ai posé dans le message #2
(Je cherche a différence entre les deux fichiers XML qui fait que PowerQuery ne réagit pas pareil)

(j'ai bien lu votre réponse du message #8 qui parle du fichier books.xml)

Pourquoi le 1er fichier XML: order.exemple.xml (le fichier dans le zip) semble s'ouvrir correctement dans PowerQuery ?
Le navigateur s'affiche et on voit les "tables"
Image du message #2
(C'est un fichier issu d'un forum dédié à ESPPADOM)

Alors que le fichier XML de mon stage ne lance pas le navigateur.

Les deux fichiers sont des fichiers construits avec les règles ESPPADOM, non?
PowerQuery.PNG
 
Dernière édition:

Hasco

XLDnaute Barbatruc
Repose en paix
Les deux fichiers sont des fichiers construits avec les règles ESPPADOM, non?
Bonjour
Alors ça je n'ai aucun moyen de le savoir. Je ne peux que supputer que NON. vous voyez bien qu'ils ne sont pas construits de la même façon, que les espaces de noms ne sont pas les mêmes.

Avec tout ça PowerQuery fait ce qu'il peut pour tenter de vous trouver quelque chose. Que voulez-vous que je vous dise de plus ?
Voyez les gens du forum ESPPADOM et demandez leur.
 

StagExcelle

XLDnaute Junior
Bonjour

Roblochon: Vous maitrisez PowerQuery (C'est le sentiment que je me suis forgé en lisant beaucoup de vos réponses sur le forum)
Alors je me suis dit que vous saviez sans doute qu'est-ce qui fait que le navigateur de PowerQuery ne s'affiche pas par défaut (que ce soit avec un fichier XML ou un autre type de fichier) et que peut-être il y avait une option à cocher quelque part.
Quand je me suis inscris sur le forum, j'ai suivi les recommandations dans le mail du webmaster.
J'ai lu la charte et j'ai retenu qu'il n'y avait pas de question bête sur le forum.

J'ai relu toutes vos réponses dans cette discussion ce matin et comme j'ai toujours du mal avec DOM, j'ai repris votre idée du message #13 : nettoyer le fichier avec PowerQuery
J'ai donc fait ceci
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Autres colonnes supprimées" = Table.SelectColumns(Source,{"ns4:Name", "ns4:SIRET", "ns4:LineOne", "ns4:PostcodeCode", "ns4:CityName", "unitCode", "ns2:CalculationPercent"})
in
    #"Autres colonnes supprimées"

Je viens de voir qu'un autre membre a poser une question sur un fichier XML et que vous lui conseiller de détailler le travail à faire.

C'est ce que je me propose de faire également.

Dois-je le faire dans cette discussion ou j'en créé une nouvelle ?

Roblochon: je voulais vous demander si je pouvais vous suivre.
Mais je ne peux pas le faire. Ca bloque quand on clique sur votre pseudo.
 

StagExcelle

XLDnaute Junior
Roblochon, j'essaie de faire des essais avec votre code dans la discussion de seb01
(Mais pour le moment sans succès)

Dans cette discussion, vous parlez d'éditeur XML.
Alors j'ai fait des recherches
j'ai trouvé cette liste sur un site
Est-ce que je peux vous demander votre avis?
Ou pouvez-vous m'en recommander un qui n'est pas dans cette liste?
Merci.

Éditeurs XML
1)Oxygen
2)Emacs for XML
3)XML Notepad
4)Liquid Studio
5)Stylus Studio
6)Komodo
7)Kate
8)NotePad ++
9)XML Grid.net
10)Adobe FrameMaker
11)XMLSpy
12)ExtendsClass
13)Editix
14)Code Beautify
15)Online XML Tools
 

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Dois-je le faire dans cette discussion ou j'en créé une nouvelle ?
Dans cette discussion c'est faisable puisqu'en rapport avec son titre.
L'important est que vous ayez choisi une technique, qu'elle qu'elle soit et que vous vous y teniez, jusqu'au bout. Rien ne vous empêchera avec le temps et la pratique de changer votre fusil d'épaule. Mais au moins vous aurez un résultat entre temps.

Donc allez-y testez Power query et son langage M (celui que vous voyez dans la barre de formule de l'éditeur power query ) . Aidez vous de la référence microsoft (même si elle est parfois absconce) :
Et posez vos questions ? Qu'est-ce j'ai, où je veux aller, avec quoi, comment. Et s'il y a problème je serai heureux de vous aider si je le peux.

Cordialement
 

StagExcelle

XLDnaute Junior
Je suis désolé, c'est que votre code dans la discussion de seb01 m'a vivement intéressé

Je suis votre consigne et je parle plus que de PowerQuery dans cette discussion

Je détaille donc la tache que je dois faire ou tenter d'améliorer
Le service informatique génère tous les 15 jours une 100 centaines de fichiers XML ESPPADOM
Ces fichiers XML sont envoyés tel quel par mail.
Mais les clients les trouvant difficilement lisible, on me demande de les reformater en ne gardant que certaines colonnes.
Grâce à votre aide, j'ai une base en VBA (mais pour le moment encore compliqué pour moi)

Donc je prends la méthode PowerQuery
1er souci: Les messages d'erreur à l'ouverture
J'ai une piste découverte par hasard pour ne plus avoir d'erreur à l'ouverture

Comme je dois traiter la centaine de fichier, je pense faire cette méthode
Ouvrir les fichiers XML en VBA avec la méthode sans erreur

Ensuite utiliser PowerQuery (comme dans mon exemple du message # 24)
Mais je ne sais pas dire à PowerQuery de boucler sur les fichiers XML

Voila où j'en suis de mes tests
Code:
Sub MacroNettoyage()
Dim strXML_Fic As String, Classeur As Workbook
strXML_Fic = ThisWorkbook.Path & "\ESPPADOM_TEST_5.xml"
Application.ScreenUpdating = False
'ouvrir le fichier XML sans erreur
Set Classeur = Workbooks.OpenXML(Filename:=strXML_Fic, LoadOption:=xlXmlLoadOpenXml)
Classeur.Sheets(1).Copy 'copier le fichier XML
Classeur.Close False 'Fermer le fichier XML
'Mise en forme de la copie
ActiveSheet.Cells.RowHeight = 15
ActiveSheet.Rows(1).EntireRow.Delete
ActiveSheet.Range("A1").FormulaR1C1 = "COL1"
ActiveSheet.Range("A1").AutoFill Destination:=Range("A1:DW1"), Type:=xlFillDefault
ActiveSheet.Range("A1:DW1") = ActiveSheet.Range("A1:DW1").Value
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$DW$7"), , xlYes).Name = "Tableau1"
'Résultat: un tableau qu'on peut nettoyer avec PowerQuery
End Sub

Qu'en pensez-vous ?
 

StagExcelle

XLDnaute Junior
J'ai trouvé dans les discussions des codes VBA pour boucler sur les fichiers XML présents dans un dossier.
Je vois comment faire pour faire appliquer le nettoyage sur la copie des fichiers (avec ma macro précédente)

Je vais essayer d'enregistrer les copies en *.xlsx plus de les ouvrir avec PowerQuery pour qu'il fasse ca

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Autres colonnes supprimées" = Table.SelectColumns(Source,{"COL43", "COL44", "COL46", "COL48"})
in
    #"Autres colonnes supprimées"

Je reviendrai si j'ai des soucis pour la mise en oeuvre.
 

StagExcelle

XLDnaute Junior
J'ai enregistré les copies des XML en fichier XLSX dans le même dossier
Puis dans PowerQuery
J'ai fait ca
Code:
let
    Source = Folder.Files("D:\XML"),
    #"Fichiers masqués filtrés1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier à partir de XML", each #"Transformer le fichier à partir de XML"([Content])),
    #"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
    #"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier à partir de XML"}),
    #"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier à partir de XML", Table.ColumnNames(#"Transformer le fichier à partir de XML"(#"Exemple de fichier"))),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Colonne de tables développée1",{"COL43", "COL46", "COL48", "COL50"})
in
    #"Autres colonnes supprimées"
Je me rapproche du but.
Là, je n'ai encore trouvé comment dire à PowerQuery de décombiner le tableau global en se basant sur le nom des fichiers[/code]

Roblochon
Je continue à chercher en espérant que vous puissiez m'aider dans PowerQuery
 
Dernière édition:

Discussions similaires