[Google Sheets] ImportXML et XPath
Bonjour,
ImportXML
est l'une des 4 fonctions principales permettant de capter des données du Web sans passer par des scripts ou macros.
- ImportHTML : pages HTML
- ImportData : données au format csv (comma = virgule) ou tsv (tabulation)
- ImportFeed : flux rss
- ImportXML : tout fichier structuré, xml, xhtml, ...
Pourquoi capter des informations présentes sur le web ?
- Il est parfois utile de rapatrier des données à des fins d’utilisation personnelle ou professionnelle comme une conversion de devise, une évaluation d’un portefeuille boursier, des tendances météorologiques,…
- Dans ce cas, il faut parfois pouvoir capter des données publiées à l’état formaté mais non directement accessibles : formats xml, rss, csv
- Ceci doit toujours se faire conformément à la législation et dans le respect des droits d’auteurs et d’utilisation des données publiés le cas échéant.
Pré-requis
L'utilisation de ImportXML et XPath nécessite juste quelques notions basiques de la façon dont structurés les documents : balises, contenus et attributs des balises.
Tous les exemples cités sont ici :
https://docs.google.com/spreadsheets/d/1WTo-btVDi1AyyLPG65SJqQoYtTmWMGEKVxqW8Mre3eU/edit?usp=sharing
ImportXML et XPath
ImportXML
permettra d’importer les données au format xml, mais aussi beaucoup d’autres formats, dont le html. C’est donc un outil plus universel. Il utilisera comme mise en forme des paramètres de requête un autre langage qui est XPath
, (qui n‘est pas propre à Google Sheets).
XPath va indiquer le chemin qui permet d’aboutir à la valeur recherchée.
2 façons complémentaires de déterminer le chemin :
- En inspectant la page (Ctrl+Maj+I dans Chrome), puis en repérant l’élément, par
clic droit > copy > copy Xpath
ouCopy Full Xpath
- En affichant la source du document et en repérant l’élément souhaité
Navigation descendante simple dans le document
/ | Depuis la racine |
// | N’importe où dans le document |
@xyz | L' attribut xyz |
@* | Tous les attributs |
| | Permet de conjuguer plusieurs recherches |
exemples appliqués à https://www.excel-pratique.com/fr/fonctions
/html/head/title | donnera le titre du document, / indique le chemin en absolu |
//title | idem, la notation // indique « quelle que soit la position » dans le document |
//html/@lang | Donnera la valeur de l’attribut lang de la balise html |
//meta/@* | Donnera la liste complète des attributs des balises meta * signifie tous |
//a/@href | Donnera la liste des liens hypertexte |
//a/li | Donnera la liste des contenus des balises li contenues dans une balise a la lecture de la requête se fait de droite à gauche |
Il est aussi possible d’effectuer plusieurs recherches en joignant celles-ci avec |
(pipe)
https://www.prevision-meteo.ch/services/xml/geneve
//date | //hour | //description | //condition | Donnera le contenu de toutes les informations taguées date, hour, description et condition. |
les résultats seront empilés dans la même colonne ! L’alternative est d’écrire plusieurs fois la fonction ImportXML. Le changement dans l’ordre des requêtes n’affectera pas l’ordre des informations dans la colonne car la fonction liste les résultats dans l’ordre dans laquelle elle repère les informations dans la page.
[suite]
Paramètres de navigation avancée
[@xxx='yyy'] | Sélection des balises dont l’attribut xxx a la valeur yyy |
* | Toutes les balises |
[n] | Le rang de la balise |
| | Permet de conjuguer plusieurs recherches |
. | Le niveau courant |
.. | Le niveau parent |
exemples : Pour : https://www.excel-pratique.com/fr/fonctions
//div[@class='menu']/ul/a/li | Contenu des balises li, |
à l’intérieur des liens a, | |
dépendant des balises ul, | |
contenues dans les balises div dont l’attribut class a la valeur menu | |
//div[@class='menu']/ul/a/@href | Attributs href (liens hypertextes) des balises a, |
dépendant des balises ul, | |
contenues dans les balises div dont l’attribut class a la valeur menu | |
//*[@id="article"]/table[5]//a/@href | Les liens des balises quelle que soit leur position // de la 5ème [5] table de toutes * les balises dont l’attribut id est égal à article |
//a/@href[contains(.,'formation')] | Tous les liens hypertexte contenant le mot formation |
//a/@href[not(contains(.,'formation'))] | Tous les liens hypertexte ne contenant pas le mot formation |
Pour : https://www.prevision-meteo.ch/services/xml/geneve
//h3/../@href | H3 étant le titre des photos, on remontera au niveau du parent .. pour capter le lien hypertexte (@href) |
[suite]
Utilisation de noms locaux
Lorsque la page xml utilise un namespace, l’appel des balises peut s’avérer infructueux.
Il faut alors recourir à une définition locale du nom de la balise par local-name()=
Exemple : http://cloud.tfl.gov.uk/TrackerNet/LineStatus
Le XPath //Line/@*
est inopérant. Il faut le remplacer par
//*[local-name() ='Line']/@*
ou pour avoir le seul nom des stations du métropolitain
//*[local-name() ='Line']/@*[local-name() ='Name']
Tous les exemples cités sont ici :
https://docs.google.com/spreadsheets/d/1WTo-btVDi1AyyLPG65SJqQoYtTmWMGEKVxqW8Mre3eU/edit?usp=sharing
[FIN]
ImportXML
importera toujours les données dans une seule colonne.
Il peut être intéressant de coupler le résultat avec d'autres fonctions
- sélectionner des lignes du résultat
=filter(arrayformula(importdata(url)); search("ce_que_je_cherche";arrayformula(importdata(url))))
- extraire des informations
=VALUE(substitute(ImportXML("http://www.boursorama.com/cours.phtml?symbole=1rPFP";"//div[@class='c-faceplate__info']//span[@class='c-instrument c-instrument--last']");".";","))
https://forum.excel-pratique.com/sheets/importer-les-donnees-d-un-fichier-xml-150460#p929394
- splitter un résultat > il sera présenté alors en colonnes
=arrayformula(split( (arrayformula(importdata(url));search("ce_que_je_cherche ";arrayformula(importdata(url))));"caractere_de_decoupage"))
- mettre en colonne un résultat combinant plusieurs requêtes
//date | //description | //condition
Par l’utilisation de la fonction SEQUENCE
=ARRAYFORMULA( (VLOOKUP(SEQUENCE(ROUNDUP(COUNTA(A2:A)/nbcolonnes);nbcolonnes;ROW(A2));{ROW(A2:A)\A2:A};2;0) ) )
La fonction sequence
donnera par exemple ceci sur 3 colonnes
=SEQUENCE(ROUNDUP(COUNTA(A2:A)/3);3)
1 | 2 | 3 |
4 | 5 | 6 |
7 | 8 | 9 |
On exploitera cette séquence en créant une matrice virtuelle composée des n° de lignes et de la valeur par
{ROW(A2:A)\A2:A}
https://forum.excel-pratique.com/sheets/importer-les-donnees-d-un-fichier-xml-150460#p929394
- Messages
- 1'025
- Excel
- 2016 FR // 365
- Inscrit
- 19/04/2019
- Emploi
- Étudiant en 5e année d'école d'Ingénieur
Salut Steelson,
Merci pour toutes ces infos !
Est-il possible lorsque l'on récupère des données, de préciser le nombre ? Par exemple je souhaiterai récupérer les informations des 5 premières balises li de l'ul ayant la class = 'test-steelson' ?
Merci d'avance !
Bonne soirée/journée,
Baboutz
Bonjour Baboutz,
je ne sais pas ce qu'en pense l'auteur du topic, mais pour ma part j'encapsulerais la fonction par un query https://www.sheets-pratique.com/fr/query/limit
=QUERY(_______ici_la_fonction_importxml_________;"SELECT * LIMIT 5")
- Messages
- 1'025
- Excel
- 2016 FR // 365
- Inscrit
- 19/04/2019
- Emploi
- Étudiant en 5e année d'école d'Ingénieur
Salut Mikhail,
C'est en effet bien vu, merci beaucoup ! Je m'étais dit que Query était plutôt pour des bdd au sein d'un fichier GSheets, et je n'avais même pas testé... Merci !
Bonne journée,
Baboutz