Power Query Choisir colonnes fichier XML avec POWERQUERY

[StagExcelle]

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.

 

[Hasco]

Re,

Ce que je vous propose est différents c'est d'importer dans un seule et même classeur excel toutes les données par collage spécial valeurs pour seulement ensuite les traiter dans powerquery si necessaire.

Voyez et intégrez (après compréhension et adaptations idoines) les lignes suivantes (voir fichier .xlsm joint)

Sub MacroNettoyage()
    Dim strXML_Fic As String, Classeur As Workbook
    strXML_Fic = ThisWorkbook.Path & "\ESPPADOM_TEST_5.xml"
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    'Ligne à integrer dans une boucle de traitement de fichier xml
    'ouvrir le fichier XML sans erreur
    Set Classeur = Workbooks.OpenXML(Filename:=strXML_Fic, LoadOption:=xlXmlLoadOpenXml)
    '
    'Copier les données sans les 2 lignes du haut
    With Classeur.Sheets(1).Range("A1").CurrentRegion
        .Offset(2).Resize(.Rows.Count - 2).Copy
    End With
    '
    ' Coller les valeurs dans ce classeur, feuille 1
    ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
    Application.CutCopyMode = False ' libérer le presspapier
    '
    ' Fermer le classeur
    Classeur.Close False
 
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
End Sub

Quand vous aurez toutes vos données dans une seule feuille ce sera plus facile.

Sinon powerquery est capable d'aller dans un répertoire et traiter plusieurs fichiers.

cordialement

 

[StagExcelle]

Bonjour Roblochon

Merci de votre aide

Pour PowerQuery, c'est ce j'ai fait Importer à partir d'un dossier dans le message # 30

Je regarde votre fichier.

En continuant mes recherches, j'ai tester un autre code qui ouvre le fichier XML sans erreur
(Données/Autres Sources/En provenance fichier XML)
Avec comme bonus (et je ne sais pas pourquoi) la transformation des dates en jj/mm/aaaa automatiquement.

Demain selon le travail qu'on me donnera, j'essaie de poster mes essais avec la boucle qui ouvre les fichiers XML.
Sinon ce sera lundi.

J'ai essayé d'utiliser l'enregistreur de macros avec PowerQuery.
D'après ce que je lis sur internet ce n'est pas possible

 

[Hasco]

Re,

Si vous joingnez un exemple de votre travail ainsi qu'un ou deux fichiers xml, ce sera le must.

bonne soirée

 

[StagExcelle]

Bonjour

Voici l'avancée de mon code VBA
(J'ai trouvé ces exemples de codes sur le forum et j'ai repris une partie du code Roblochon)
Est-ce qu'il faut mettre où on a trouvé les exemples dans les vieilles discussions?

Sub CompilationFichiersXML()
'Déclarations des variables
Dim ContenuXML
Dim Chemin As String, fn As String
Dim ClasseurMAITRE As Workbook, Classeur As Workbook
Dim rs As Range, rd As Range, rf As Range
Chemin = ThisWorkbook.Path & "\"
Set ClasseurMAITRE = ThisWorkbook
fn = Dir(Chemin & "*.xml")
Application.ScreenUpdating = False
'Boucle sur les fichiers XML dans le dossier
Do While fn <> ""
        If fn <> ClasseurMAITRE.Name Then
    Set Classeur = Workbooks.OpenXML(Filename:=Chemin & fn, LoadOption:=xlXmlLoadOpenXml) 'ouverture du fichier XML
    'création d'un tableau (sans les entêtes)
    With Classeur.Sheets(1).Range("A1").CurrentRegion
        Set rs = .Offset(2).Resize(.Rows.Count - 2)
     End With
    ContenuXML = rs.Value
    Classeur.Close False 'fermeture du fichier XML
    'On détermine les cellues de recopie du tableau Contenu_XML
        Set rd = ClasseurMAITRE.Worksheets(1).[B65536].End(xlUp).Offset(1, 0)
        Set rf = ClasseurMAITRE.Worksheets(1).[A65536].End(xlUp).Offset(1, 0)
        'recopie du tableau en mémoire dans la feuille du classeur Maitre
        rd.Resize(UBound(ContenuXML, 1), UBound(ContenuXML, 2)) = ContenuXML
        'recopie du nom des fichiers XML
        rf.Resize(UBound(ContenuXML, 1), 1) = fn
        Erase ContenuXML
        Set rs = Nothing
    End If
    fn = Dir
Loop
'insertion numéros de colonnes
ClasseurMAITRE.Sheets(1).Range("A1") = 1
ClasseurMAITRE.Sheets(1).Range("A1:DX1").DataSeries
End Sub

Ensuite je sais faire la partie PowerQuery pour la suppression des colonnes.

Il y a une réunion qui va commencer, donc je reviendrai que lundi je pense.

Merci déjà pour toute l'aide apportée.

Roblochon
Je serai content de lire vos commentaires sur le code que j'ai écrit (avec l'inspiration des codes des autres membres)

 

[Hasco]

Bonjour,

Est-ce qu'il faut mettre où on a trouvé les exemples dans les vieilles discussions?

C'est à vous de voir.

Je serai content de lire vos commentaires sur le code que j'ai écrit

Impeccable.

Je pourrai dire (sans vouloir vous faire tourner en bourrique) que vous seriez capable de supprimer les colonnes non désirées par vba. Ce qui serait tout aussi efficace et vous passer de powerquery. S'il ne s'agit bien-entendu que de suppression de colonnes et pas de transformation plus complexes sur les données elles-même.

Cordialement

 

[StagExcelle]

Bonjour,

Roblochon: Vous avez changé de pseudo ?

Alors Bonjour Hasco

Puis-je vous demander votre avis sur ces deux options?
-copier certaines colonnes
ou
-supprimer toutes les colonnes sauf celles qu'il faut garder
(quelle serait la méthode la plus rapide?)

J'aurai ensuite une question sur PowerQuery.

Sinon, j'essaie de comprendre votre code VBA DOM dans la discussion de seb01
(et de faire des tests (sans succès pour le moment))
Est-ce je peux le réutiliser dans mon autre discussion (orientée VBA) et auriez-vous du temps, svp, à me consacrer en explications si je n'arrive pas à adapter et tester votre code avec mon fichier XML?

 

[Hasco]

Bonjour,

Puis-je vous demander votre avis sur ces deux options?
-copier certaines colonnes
ou
-supprimer toutes les colonnes sauf celles qu'il faut garder
(quelle serait la méthode la plus rapide?)

Personnellement je supprimerai exemple pour supprimer les colonnes E,G,M:
Activesheet.Range("E1,G1,M1").Entirecolumn.Delete

Est-ce je peux le réutiliser dans mon autre discussion (orientée VBA) et auriez-vous du temps, svp, à me consacrer en explications si je n'arrive pas à adapter et tester votre code avec mon fichier XML?

Euh? Comment dire, xml avec des espaces de noms, c'est compliqué à gérer. Et franchement c'est pas l'enthousiasme qui m'envahit

M'enfin j'y jetterai un oeil et si je le sens je vous répondrez.

Hasco est le pseudo que j'avais en 2007 à ma première inscription et que je récupère aujourd'hui

Cordialement

 

[StagExcelle]

Lors de mes tests de suppression de colonnes (je ne sais plus dans quel message je l'ai marqué), Excel se figeait ou plantait.
(mais je crois que c'était quand le code VBA tentait de supprimer les colonnes dans un Tableau (format Excel)

Hasco
Pour XML, c'est juste pour arriver à ce qu'un test avec SingleNodes fonctionne
(Pour le moment, Debug.Print ou MsgBox renvoie du vide)
(Je posterai mes essais plus tard)

Je suis sur une piste pour faire récupérer les colonnes sans passer par copier/coller.

Je reviens quand j'ai fini mes essais.

 

[StagExcelle]

Bonjour Hasco

Tout d'abord merci pour votre fichier et le code VBA avec la méthode DOM dans l'autre discussion.

Comme vous m'aviez dit que maintenant on parle de PowerQuery, je vais poster mes essais VBA dans l'autre discussion (celle ou vous avez posté votre fichier Excel hier)

Concernant PowerQuery, je vous pose la question que j'évoquais dans le message # 36.
PowerQuery ne peut que combiner des données issues de plusieurs fichiers dans un dossier ?

Dans l'exemple que je dois traiter, une fois les fichiers combinés dans PowerQuery et les colonnes supprimées, PowerQuery ne sait pas "décombiner" les données en créant plusieurs classeurs en se basant sur le nom dans la colonne 1 du tableau créé par PowerQuery?

C'est devant cette difficulté que j'ai cherché une autre piste (heureusement que Internet existe
(que j'évoquais aussi dans le message # 36)

C'est le code VBA que je poste dans l'autre discussion cet aprés-midi.

Je me permets encore une question.
Quel avantage du format XML par rapport à CSV ?
Car j'ai des fichiers ESPPADOM qui font plus de 7 MO
Je pense qu'ils seraient moins gros en CSV

 

[Hasco]

Bonjour,

Non powerquery ne sait pas créer de fichier. Ils rassemble des tables, les combines, permet les transformations de toutes sortes mais ne crée pas de fichier.

Dans excel, powerquery peut créer une feuille par requête, c'est tout.
Comme il peut également combiner du texte, on peut facilement créer des données pour un .csv
En combinant vba et une requête paramétrée sur la colonne1 on peut éclater un tableau et en exporter les parties en .csv

Quant aux avantages et inconvénients entre csv et xml, les deux n'ont pas le même rôle.
Xml peut comme dans votre fichier esppadom exposer une toute hiérarchie d'informations diverses et variées, en prévoir par .xsd les limitations et la présentation (.xlst).
Le .csv n'expose que des données à une seule dimension (fichier plat) en lignes et colonnes.
Ce sont généralement les concepteurs des systèmes d'échange d'informations qui décident ce genre de chose.

Cordialement