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é, l’erreur suivante s’affiche lors de la connexion à des 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 dans un hôte cloud (par exemple, 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 accédez à 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 qui ne contient pas spécifiquement de tableau unique, le navigateur Power Query tente de créer une liste suggérée de tableaux 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’une des tables suggérées, chaque table individuelle 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 lorsqu’elles sont 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 examinez la valeur réelle stockée, notez que dans le fichier .xlsx, 0,049 est réellement stocké sous la forme 0.0490000000000000000000002. Ce nombre est la valeur que Power Query lit à partir du .xlsx, et par conséquent, 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 d’accès du 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). En raison de cette dépendance, vous pouvez rencontrer des comportements inattendus lors de l’importation de classeurs hérités 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 numéro 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). Ce comportement se produit car, dans ce cas, ACE expose uniquement la valeur telle qu’elle apparaît dans Excel dans Power Query, et non le nombre sous-jacent.

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 ultérieures, ACE peut appliquer un type incorrect à cette colonne et retourner des valeurs Null pour toute valeur qui ne correspond 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 des 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 avec la valeur correcte.

• 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 vraie, cette option entraîne l'ignorance des dimensions stockées dans le classeur et permet de les déterminer 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

Les dimensions incorrectes peuvent également entraîner un chargement lent des données Excel. Toutefois, dans ce cas, les dimensions étant beaucoup plus grandes qu’elles ne doivent être à l’origine de la lenteur, plutôt que d’être trop petites. Des dimensions trop grandes amènent Power Query à lire une quantité de données bien plus importante à partir du classeur 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

Lors de la récupération de données à partir d’Excel sur votre ordinateur ou à partir de SharePoint, tenez compte du volume des données impliquées et de la complexité du classeur.

Vous pouvez remarquer une dégradation des performances lors de la récupération de fichiers très volumineux à partir de SharePoint. Toutefois, la taille du fichier n’est qu’une partie du problème. Si vous avez une logique métier importante dans un fichier Excel récupéré à partir de SharePoint, cette logique métier peut être exécutée lorsque vous actualisez vos données, ce qui peut entraîner des calculs compliqués. Envisagez d’agréger et de précalculer des données, ou de déplacer davantage de logique métier hors de la couche Excel et dans 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. Seul ACE peut lire les classeurs enregistrés au format « Feuille de calcul Open XML strict ». 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.

En règle générale, cette erreur indique qu’il existe un problème avec le format du fichier.

Toutefois, cette erreur peut parfois se produire lorsqu’un fichier semble être un fichier Open XML (tel que .xlsx), mais que le pilote ACE est nécessaire pour traiter le fichier. 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.
• L’option Excel.WorkbookuseHeaders convertit les nombres et les dates en texte à l’aide de la culture actuelle, et se comporte donc différemment lors de l’exécution dans des environnements avec des cultures de système d’exploitation différentes définies. Nous vous recommandons d’utiliser Table.PromoteHeaders à la place.