Importer et formater des données dans Excel pour Mac (Power Query)

Vous pouvez importer des données dans Excel à l’aide de Power Query à partir d’un large éventail de sources de données : Classeur Excel, Texte/CSV, XML, JSON, Base de données SQL Server, Liste SharePoint Online, OData, Table vide et Requête vide.

1. Sélectionnez Données > Obtenir des données.
   
   

2. Pour sélectionner la source de données souhaitée, sélectionnez Obtenir des données (Power Query).

3. Dans la boîte de dialogue Choisir une source de données , sélectionnez l’une des sources de données disponibles. 
   
   

4. Connectez-vous à la source de données. Pour en savoir plus sur la connexion à chaque source de données, consultez Importer des données à partir de sources de données.

5. Choisissez le type de données que vous souhaitez importer.

6. Chargez les données en cliquant sur le bouton Charger .

Result (Résultat)

Les données importées apparaissent dans une nouvelle feuille.

Étapes suivantes

Pour mettre en forme et transformer des données à l’aide de l’Éditeur Power Query, sélectionnez Transformer les données. Pour plus d’informations, consultez Mettre en forme les données avec l’Éditeur Power Query.

Procédure

1. Sélectionnez Données > Obtenir des données (Power Query).

2. Pour ouvrir l’Éditeur de requête, sélectionnez Lancer l’Éditeur Power Query.
   
   

   Conseil : Vous pouvez également accéder à l’Éditeur de requête en sélectionnant Obtenir des données (Power Query), en choisissant une source de données, puis en cliquant sur Suivant.

3. Mettez en forme et transformez vos données à l’aide de l’Éditeur de requête, comme vous le feriez dans Excel pour Windows.
   
   
   
   Pour plus d’informations, consultez Aide sur Power Query pour Excel.

4. Lorsque vous avez terminé, sélectionnez Accueil > Fermer et charger.

Result (Résultat)

Les données nouvellement importées apparaissent dans une nouvelle feuille.

Vous pouvez actualiser les sources de données suivantes : fichiers SharePoint, listes SharePoint, dossiers SharePoint, OData, fichiers texte/CSV, classeurs Excel (.xlsx), fichiers XML et JSON, tables et plages locales, et une base de données Microsoft SQL Server.

Actualiser la première fois

La première fois que vous essayez d’actualiser des sources de données basées sur des fichiers dans vos requêtes de classeur, vous devrez peut-être mettre à jour le chemin d’accès au fichier.

1. Sélectionnez Données, la flèche en regard de Obtenir des données, puis Paramètres de source de données. La boîte de dialogue Paramètres de source de données s’affiche.

2. Sélectionnez une connexion, puis sélectionnez Modifier le chemin d’accès au fichier.

3. Dans la boîte de dialogue Chemin d’accès au fichier, sélectionnez un nouvel emplacement, puis sélectionnez Obtenir des données.

4. Sélectionnez Fermer.

Actualiser les fois suivantes

Pour actualiser :

• Toutes les sources de données du classeur, sélectionnez Données > Actualiser tout.

• Une source de données spécifique, cliquez avec le bouton droit sur une table de requête sur une feuille, puis sélectionnez Actualiser.

• Un tableau croisé dynamique, sélectionnez une cellule dans le tableau croisé dynamique, puis sélectionnez Analyser le tableau croisé dynamique > Actualiser les données.

La première fois que vous accédez à SharePoint, SQL Server, OData ou d’autres sources de données nécessitant une autorisation, vous devez fournir les informations d’identification appropriées. Vous pouvez également effacer les informations d’identification pour en entrer de nouvelles.

Entrer les informations d’identification

Lorsque vous actualisez une requête pour la première fois, vous devrez peut-être vous connecter. Sélectionnez la méthode d’authentification, puis spécifiez les informations d’identification de connexion pour vous connecter à la source de données et poursuivre l’actualisation.

Si la connexion est requise, la boîte de dialogue Entrer les informations d’identification s’affiche.

Par exemple :

• Informations d’identification SharePoint :
  
  

• informations d’identification SQL Server :
  
  

Effacer les informations d’identification

1. Sélectionnez Données > Obtenir des données > Paramètres de source de données.

2. Dans la boîte de dialogue Paramètre de source de données, sélectionnez la connexion souhaitée.

3. En bas, sélectionnez Effacer les autorisations.

4. Confirmez que c’est bien ce que vous souhaitez, puis sélectionnez Supprimer.

Bien que la création dans l’Éditeur Power Query ne soit pas disponible dans Excel pour Mac, VBA prend en charge la création de Power Query. Le transfert d’un module de code VBA dans un fichier d’Excel pour Windows vers Excel pour Mac est un processus en deux étapes. Un exemple de programme vous est fourni à la fin de cette section.

Étape 1 : Excel pour Windows

1. Dans Excel pour Windows, développez des requêtes à l’aide de VBA. Le code VBA qui utilise les entités suivantes dans le modèle objet d’Excel fonctionne également dans Excel pour Mac : objet Queries, objet WorkbookQuery, Propriété Workbook.Queries. Pour plus d’informations, consultez Référence Excel VBA.

2. Dans Excel, assurez-vous que le Visual Basic Editor est ouvert en appuyant sur Alt+F11.

3. Cliquez avec le bouton droit sur le module, puis sélectionnez Exporter le fichier. La boîte de dialogue Exporter s’affiche.

4. Entrez un nom de fichier, vérifiez que l’extension de fichier est .bas, puis sélectionnez Enregistrer.

5. Chargez le fichier VBA vers un service en ligne pour rendre le fichier accessible à partir du Mac.
   
   Vous pouvez utiliser Microsoft OneDrive. Pour plus d’informations, consultez Synchroniser des fichiers avec OneDrive sur Mac OS X.

Étape 2 : Excel pour Mac

1. Téléchargez le fichier VBA dans un fichier local, le fichier VBA que vous avez enregistré dans « Étape 1 : Excel pour Windows » et chargé dans un service en ligne.

2. Dans Excel pour Mac, sélectionnez Tools > Macro > Visual Basic Editor. La fenêtre Visual Basic Editor s’affiche.

3. Cliquez avec le bouton droit sur un objet dans la fenêtre Projet, puis sélectionnez Importer le fichier. La boîte de dialogue Importer le fichier s’affiche.

4. Localisez le fichier VBA, puis sélectionnez Ouvrir.

Exemple de code

Voici un code de base que vous pouvez adapter et utiliser. Il s’agit d’un exemple de requête qui crée une liste avec des valeurs comprises entre 1 et 100.

Sub CreateSampleList()
    ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
        "let" & vbCr & vbLf & _
            "Source = {1..100}," & vbCr & vbLf & _
            "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
            "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
        "in" & vbCr & vbLf & _
            "RenamedColumns"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SampleList]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "SampleList"
        .Refresh BackgroundQuery:=False
    End With
End Sub

1. Ouvrez le classeur Excel.

2. Si vous recevez un avertissement de sécurité concernant la désactivation des connexions de données externes, sélectionnez Activer le contenu.

3. Si la boîte de dialogue Accorder l’accès aux fichiers s’affiche, sélectionnez Sélectionner, puis sélectionnez Accorder l’accès au dossier de niveau supérieur contenant les fichiers de source de données.

4. Sélectionnez Données> À partir du texte (hérité). La boîte de dialogue Recherche s’affiche.

5. Localisez le fichier .txt ou .csv, puis sélectionnez Ouvrir. L’Assistant Importation de texte s’affiche.
   
   Conseil    Vérifiez à plusieurs reprises le volet Aperçu des données sélectionnées pour confirmer vos choix.

6. Dans la première page, procédez comme suit :

   Type de fichier    Pour choisir le type de fichier texte, sélectionnez Délimité ou Largeur fixe.
   
   Numéro de ligne    Dans Démarrer l’importation à la ligne, sélectionnez un numéro de ligne pour spécifier la première ligne de données à importer.
   
   Jeu de caractères    Dans Origine du fichier, sélectionnez le jeu de caractères utilisé dans le fichier texte. Dans la plupart des cas, vous pouvez laisser ce paramètre à sa valeur par défaut.

7. Dans la deuxième page, procédez comme suit :
   
   Délimité
   
   Si vous avez choisi Délimité sur la première page, sous Délimiteurs, sélectionnez le caractère délimiteur ou utilisez la case à cocher Autre pour en entrer un qui n’est pas répertorié.
   
   Sélectionnez Traitez les délimiteurs consécutifs comme un si vos données contiennent un délimiteur de plusieurs caractères entre les champs de données ou si vos données contiennent plusieurs délimiteurs personnalisés.
   
   Dans Qualificateur de texte, sélectionnez le caractère qui entoure les valeurs dans votre fichier texte, qui est le plus souvent le caractère de guillemet (").

   Largeur fixe
   
   Si vous avez choisi Largeur fixe sur la première page, suivez les instructions pour créer, supprimer ou déplacer une ligne d’arrêt dans la zone Aperçu des données sélectionnées .

8. Dans la troisième page, procédez comme suit :
   
   Pour chaque colonne sous Aperçu des données sélectionnées, sélectionnez-la, puis modifiez-la dans un format de colonne différent si vous le souhaitez. Vous pouvez définir davantage le format de date et sélectionner Avancé pour modifier les paramètres de données numériques. Vous pouvez également convertir les données après les avoir importées.
   
   Sélectionnez Terminer. La boîte de dialogue Importer des données s’affiche.

9. Choisissez l’emplacement où vous souhaitez ajouter les données : sur la feuille existante, sur une nouvelle feuille ou dans un tableau croisé dynamique.

10. Sélectionnez OK.

    Pour vous assurer que la connexion fonctionne, entrez des données, puis sélectionnez Connexions > Actualiser.

1. Sélectionnez Données > À partir de SQL Server ODBC. La boîte de dialogue Se connecter à une source de données SQL Server ODBC apparaît.
   
   

2. Entrez le serveur dans la zone Nom du serveur et, éventuellement, entrez la base de données dans la zone Nom de la base de données.
   
   Obtenir ces informations auprès de l’administrateur de base de données.

3. Sous Authentification, sélectionnez une méthode dans la liste : Nom d’utilisateur/mot de passe, Kerberos ou NTLM.

4. Entrez les informations d’identification dans les zones Nom d’utilisateur et Mot de passe .

5. Sélectionnez Connexion. La boîte de dialogue Navigateur s’affiche.

6. Dans le volet gauche, accédez à la table souhaitée, puis sélectionnez-la.

7. Confirmer l’instruction SQL dans le volet droit. Vous pouvez modifier l’instruction SQL comme vous le souhaitez.

8. Pour afficher un aperçu des données, sélectionnez Exécuter.

9. Lorsque vous êtes prêt, sélectionnez Renvoyer les données. La boîte de dialogue Importer des données s’affiche. 
   
   

10. Choisissez l’emplacement où vous souhaitez ajouter les données : sur la feuille existante, sur une nouvelle feuille ou dans un tableau croisé dynamique.

11. Pour définir les propriétés de connexion dans les onglets Utilisation et Définition de la boîte de dialogue Propriétés , sélectionnez Propriétés. Après avoir importé les données, vous pouvez également sélectionner Données > Connexions, puis, dans la boîte de dialogue Propriétés de connexion, sélectionnez Propriétés.

12. Sélectionnez OK.

13. Pour vous assurer que la connexion fonctionne, entrez des données, puis sélectionnez Données > Actualiser tout.

Si vous souhaitez utiliser une source externe qui n’est pas une base de données SQL (par exemple, FileMaker Pro), vous pouvez utiliser un pilote Open Database Connectivity (ODBC) installé sur votre Mac. Les informations sur les pilotes sont disponibles sur cette page web. Une fois le pilote de votre source de données installé, procédez comme suit :

1. Sélectionnez Données > À partir de la base de données (Microsoft Query).

2. Ajoutez la source de données de votre base de données, puis sélectionnez OK.

3. À l’invite des informations d’identification SQL Server, entrez la méthode d’authentification, le nom d’utilisateur et le mot de passe.
   
   

4. Sur la gauche, sélectionnez la flèche en regard du serveur pour afficher les bases de données.

5. Sélectionnez la flèche en regard de la base de données souhaitée.

6. Sélectionnez le tableau souhaité.

7. Pour afficher un aperçu des données, sélectionnez Exécuter.

8. Lorsque vous êtes prêt, sélectionnez Renvoyer les données.

9. Dans la boîte de dialogue Importer des données, choisissez l’emplacement souhaité pour la localisation des données : sur la feuille existante, sur une nouvelle feuille ou dans un tableau croisé dynamique.

10. Sélectionnez OK.

11. Pour vous assurer que la connexion fonctionne, entrez des données, puis sélectionnez Données > Actualiser tout.

Si vos autorisations ne fonctionnent pas, vous devrez peut-être les effacer d’abord, puis vous connecter.

1. Sélectionnez Données> Connexions. La boîte de dialogue Connexions du classeur s’affiche.

2. Sélectionnez la connexion souhaitée dans la liste, puis sélectionnez Effacer les autorisations.