Excel

Résumé

Prérequis

Pour vous connecter à un classeur hérité (tel que .xls ou .xlsb), le fournisseur OLEDB (ou ACE) du moteur de base de données Access est requis. Pour installer ce fournisseur, accédez à la page de téléchargement et sélectionnez la version appropriée (32 bits ou 64 bits). S'il n'est pas installé, vous verrez l'erreur suivante lors de la connexion aux classeurs hérités :

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

ACE ne peut pas être installé dans des environnements de service cloud. Par conséquent, si vous voyez cette erreur sur un hôte cloud (tel que Power Query Online), vous devez utiliser une passerelle sur laquelle ACE est installé pour vous connecter aux fichiers Excel hérités.

Fonctionnalités prises en charge

• Importer

Se connecter à un classeur Excel à partir de Power Query Desktop

Pour établir la connexion à partir de Power Query Desktop :

1. Sélectionnez Classeur Excel dans l’expérience d’obtention de données. L’expérience d’obtention de données dans Power Query Desktop varie selon les applis. Pour plus d’informations sur l’expérience d’obtention de données Power Query Desktop pour votre appli, accédez à Où obtenir des données.

2. Recherchez et sélectionnez le classeur Excel que vous souhaitez charger. Sélectionnez ensuite Ouvrir.

   

   Si le classeur Excel est en ligne, utilisez le connecteur Web pour vous y connecter.

3. Dans le Navigateur, choisissez les informations du classeur souhaitées, puis sélectionnez Charger pour charger les données ou Transformer les données pour continuer à transformer les données dans l'éditeur Power Query.

   

Se connecter à un classeur Excel à partir de Power Query Online

Pour établir la connexion à partir de Power Query Online :

1. Sélectionnez l’option Classeur Excel dans l’expérience d’obtention de données. Chaque appli a sa propre façon d’accéder à l’expérience d’obtention de données de Power Query Online. Pour plus d’informations sur la façon d’accéder à l’expérience d’obtention de données Power Query Online à partir de votre appli, accédez à Où obtenir des données.

   

2. Dans la boîte de dialogue Excel, entrez le chemin d'accès du classeur Excel.

   

3. Si nécessaire, sélectionnez une passerelle de données locale pour accéder au classeur Excel.

4. Si vous avez accédé à ce classeur Excel pour la première fois, sélectionnez le type d'authentification et connectez-vous à votre compte (si nécessaire).

5. Dans le navigateur, sélectionnez les informations du classeur souhaitées, puis Transformer les données pour continuer à transformer les données dans l'éditeur Power Query.

   

Tableaux suggérés

Si vous vous connectez à un classeur Excel ne contenant pas spécifiquement de tableau unique, le navigateur Power Query tente de créer une liste de tableaux suggérés parmi lesquels vous pouvez choisir. Par exemple, considérez l'exemple de classeur suivant contenant des données de A1 à C5, d'autres données de D8 à E10, et d'autres encore de C13 à F16.

Lorsque vous vous connectez aux données dans Power Query, le Navigateur Power Query crée deux listes. La première contient la feuille de classeur entière, et la deuxième contient trois tableaux suggérés.

Si vous sélectionnez la feuille entière dans le Navigateur, le classeur s'affiche comme dans Excel, avec toutes les cellules vides remplies de la valeur null.

Si vous sélectionnez l'un des tableaux suggérés, chaque tableau que Power Query a pu déterminer à partir de la disposition du classeur s'affiche dans le navigateur. Par exemple, si vous sélectionnez le Tableau 3, les données qui apparaissaient initialement dans les cellules C13 à F16 s'affichent.

Dépannage

Précision numérique (ou « Pourquoi mes nombres ont-ils changé ? »)

Lors de l'importation de données Excel, vous remarquerez peut-être que certaines valeurs numériques semblent changer légèrement une fois importées dans Power Query. Par exemple, si vous sélectionnez une cellule contenant la valeur 0,049 dans Excel, 0,049 s'affiche dans la barre de formule. En revanche, si vous importez la même cellule dans Power Query et la sélectionnez, les détails de l'aperçu affichent 0,049000000002 (même si la mise en forme dans l'aperçu du tableau est 0,049). Comment cela se fait-il ?

La réponse est un peu compliquée et a trait à la façon dont Excel stocke les nombres à l'aide d'une notation binaire à virgule flottante. En résumé, il y a certaines valeurs numériques qu'Excel ne peut pas représenter avec une précision de 100 %. Si vous ouvrez le fichier .xlsx et regardez la valeur réelle stockée, vous pouvez constater que, dans le fichier .xlsx, 0,049 est en fait stocké sous la forme 0,049000000000000002. C'est la valeur que Power Query lit à partir du fichier .xlsx, et donc la valeur qui apparaît lorsque vous sélectionnez la cellule dans Power Query (pour plus d'informations sur la précision numérique dans Power Query, consultez les sections « Nombre décimal » et « Nombre décimal fixe » dans Types de données dans Power Query).

Connexion à un classeur Excel en ligne

Si vous souhaitez vous connecter à un document Excel hébergé dans Sharepoint, vous pouvez le faire via le connecteur Web dans Power BI Desktop, Excel et Flux de données, ainsi qu'avec le connecteur Excel dans Flux de données. Pour obtenir le lien vers le fichier :

1. Ouvrez le document dans la version de bureau d'Excel.
2. Ouvrez le menu Fichier, sélectionnez l'onglet Informations, puis sélectionnez Copier le chemin d'accès.
3. Copiez l'adresse dans le champ Chemin de fichier ou URL, puis supprimez ?web=1 de la fin de l'adresse.

Connecteur ACE hérité

Power Query lit les classeurs hérités (notamment .xls ou .xlsb) à l'aide du fournisseur OLEDB du moteur de base de données Access (ou ACE). Pour cette raison, il se peut que, lors de l'importation de classeurs hérités, vous rencontriez des comportements inattendus qui ne se produisent pas lors de l'importation de classeurs OpenXML (tels que .xlsx). Voici quelques exemples courants.

Mise en forme de valeur inattendue

En raison d'ACE, il se peut que les valeurs d'un classeur Excel hérité soient importées avec moins de précision ou de fidélité que prévu. Par exemple, imaginez que votre fichier Excel contient le nombre 1024,231 que vous avez mis en forme pour afficher « 1 024,23 ». Lors de l'importation dans Power Query, cette valeur est représentée comme valeur de texte « 1 024.23 » au lieu de la valeur numérique sous-jacente exacte (1024,231). Cela est dû au fait que, dans ce cas, ACE ne présente pas la valeur sous-jacente à Power Query, mais uniquement la valeur telle qu'affichée dans Excel.

Valeurs null inattendues

Quand ACE charge une feuille, il examine les huit premières lignes pour déterminer les types de données des colonnes. Si les huit premières lignes ne sont pas représentatives des lignes situées plus bas, il se peut qu'ACE applique un type incorrect à cette colonne et retourne des valeurs null pour toute valeur ne correspondant pas au type. Par exemple, si une colonne contient des nombres dans les huit premières lignes (par exemple, 1000, 1001, etc.), mais contient des données non numériques dans les lignes situées plus bas (telles que « 100Y » et « 100Z »), ACE en conclut que la colonne contient des nombres et que toutes les valeurs non numériques retournées sont null.

Mise en forme de valeur incohérente

Dans certains cas, ACE retourne des résultats complètement différents d'une actualisation à l'autre. En reprenant l'exemple de la section Mise en forme, il se peut que vous voyiez soudainement la valeur 1024,231 au lieu de « 1 024,23 ». Cette différence peut être due à l'ouverture du classeur hérité dans Excel lors de son importation dans Power Query. Pour résoudre ce problème, fermez le classeur.

Données Excel manquantes ou incomplètes

Parfois, Power Query ne parvient pas à extraire toutes les données d'une feuille de calcul Excel. Cette défaillance est souvent due à des dimensions incorrectes de la feuille de calcul (par exemple, des dimensions de A1:C200 alors que les données réelles occupent plus de trois colonnes ou 200 lignes).

Comment diagnostiquer des dimensions incorrectes

Pour afficher les dimensions d'une feuille de calcul :

1. Renommez le fichier avec une extension .zip.
2. Ouvrez le fichier dans l'Explorateur de fichiers.
3. Accédez à xl\worksheets.
4. Copiez le fichier xml correspondant à la feuille problématique (par exemple, Sheet1.xml) du fichier zip vers un autre emplacement.
5. Inspectez les premières lignes du fichier. Si le fichier est assez petit, ouvrez-le dans un éditeur de texte. Si le fichier est trop volumineux pour être ouvert dans un éditeur de texte, exécutez la commande suivante à partir d'une invite de commandes : more Sheet1.xml.
6. Recherchez une balise <dimension .../> (par exemple, <dimension ref="A1:C200" />).

Si votre fichier a un attribut de dimension qui pointe vers une seule cellule (par exemple, <dimension ref="A1" />), Power Query utilise cet attribut pour trouver les ligne et colonne de début des données dans la feuille.

En revanche, si votre fichier a un attribut de dimension qui pointe vers plusieurs cellules (par exemple, <dimension ref="A1:AJ45000"/>), Power Query utilise cette plage pour trouver les ligne et colonne de début, ainsi que les ligne et colonne de fin. Si cette plage ne contient pas toutes les données de la feuille, certaines données ne sont pas chargées.

Comment corriger des dimensions incorrectes

Vous pouvez résoudre les problèmes causés par des dimensions incorrectes en effectuant l'une des actions suivantes :

• Ouvrez et réenregistrez le document dans Excel. Cette action remplace les dimensions incorrectes stockées dans le fichier par les valeurs correctes.

• Vérifiez que l'outil qui a généré le fichier Excel est paramétré pour générer correctement les dimensions.

• Mettez à jour votre requête M pour ignorer les dimensions incorrectes. Depuis la version de décembre 2020 de Power Query, Excel.Workbook prend en charge une option InferSheetDimensions. Lorsque la valeur est true, cette option a pour effet que la fonction ignore les dimensions stockées dans le classeur et les détermine en inspectant les données.

  Voici un exemple montrant comme fournir cette option :

  Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

Performances médiocres ou lentes lors du chargement de données Excel

Un chargement lent de données Excel peut également être dû à des dimensions incorrectes. Toutefois, dans ce cas, la lenteur est due à des dimensions beaucoup plus grandes que nécessaire, plutôt que trop petites. Des dimensions trop grandes ont pour effet que Power Query lit une quantité de données du classeur beaucoup plus importante que nécessaire.

Pour résoudre ce problème, vous pouvez vous référer à Localiser et réinitialiser la dernière cellule d'une feuille de calcul afin d'obtenir des instructions détaillées.

Performances médiocres de chargement de données à partir de SharePoint

Lorsque vous récupérez des données sur votre ordinateur à partir d'Excel ou de SharePoint, tenez compte du volume des données impliquées, ainsi que de la complexité du classeur.

Vous constaterez une dégradation des performances lors de la récupération de fichiers très volumineux à partir de SharePoint. Toutefois, il ne s'agit que d'une partie du problème. Si vous avez une logique métier conséquente dans un fichier Excel récupéré à partir de SharePoint, il se peut qu'elle doive s'exécuter lorsque vous actualisez vos données, ce qui peut entraîner des calculs complexes. Songez à agréger et à pré-calculer les données, ou à déplacer davantage de logique métier de la couche Excel vers la couche Power Query.

Erreurs lors de l'utilisation du connecteur Excel pour importer des fichiers CSV

Même si des fichiers CSV peuvent être ouverts dans Excel, il ne s'agit pas de fichiers Excel. Utilisez plutôt le connecteur Text/CSV.

Erreur lors de l'importation de classeurs au format « Feuille de calcul Open XML strict »

L'erreur suivante peut survenir lors de l'importation de classeurs enregistrés au format « Feuille de calcul Open XML strict » d'Excel :

DataFormat.Error: The specified package is invalid. The main part is missing.

Cette erreur se produit lorsque le pilote ACE n'est pas installé sur l'ordinateur hôte. Les classeurs enregistrés au format « Feuille de calcul Open XML strict » ne peuvent être lus que par ACE. Toutefois, étant donné que ces classeurs utilisent la même extension de fichier que les classeurs Open XML standard (.xlsx), nous ne pouvons pas utiliser l'extension pour afficher le message d'erreur the Access Database Engine OLEDB provider may be required to read this type of file habituel.

Pour résoudre l'erreur, installez le pilote ACE. Si l'erreur se produit dans un service cloud, vous devez utiliser une passerelle s'exécutant sur un ordinateur sur lequel le pilote ACE est installé.

Erreurs « Le fichier contient des données endommagées »

L'erreur suivante peut survenir lors de l'importation de certains classeurs Excel.

DataFormat.Error: File contains corrupted data.

Généralement, cette erreur indique un problème avec le format du fichier.

Toutefois, elle peut parfois survenir lorsqu'un fichier semble être un fichier Open XML (notamment .xlsx), mais que le pilote ACE est réellement nécessaire pour le traiter. Accédez à la section Connecteur ACE hérité pour plus d'informations sur le traitement des fichiers qui nécessitent le pilote ACE.

Problèmes connus et limitations

• Power Query Online ne peut pas accéder aux fichiers Excel chiffrés. Dans la mesure où les fichiers Excel étiquetés avec des types de sensibilité autres que « Public » ou « Non-Business » sont chiffrés, ils ne sont pas accessibles par le biais de Power Query Online.
• Power Query Online ne prend pas en charge les fichiers Excel protégés par un mot de passe.