Comment exécuter une requête SQL sur un tableau Excel ?

Question

J'essaie de créer un sous-tableau à partir d'une autre table de tous les champs de nom de famille triés de A à Z qui ont un champ de numéro de téléphone qui n'est pas nul. Je pourrais le faire assez facilement avec SQL, mais je n'ai aucune idée de la façon de procéder pour exécuter une requête SQL dans Excel. Je suis tenté d'importer les données dans postgresql et de les interroger à cet endroit, mais cela semble un peu excessif.

Pour ce que j'essaie de faire, la requête SQL SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname ferait l'affaire. Cela semble trop simple pour que ce soit quelque chose qu'Excel ne puisse pas faire nativement. Comment puis-je exécuter une requête SQL comme celle-ci à partir d'Excel ?

Answer 1

Il existe de nombreuses façons de procéder, que d'autres ont déjà suggérées. En suivant la piste "obtenir des données Excel via SQL", voici quelques conseils.

1. Excel a le "Assistant de connexion de données" qui vous permet d'importer ou de créer des liens à partir d'une autre source de données ou même au sein du même fichier Excel.

2. Deux fournisseurs intéressants font partie de Microsoft Office (et des systèmes d'exploitation) : l'ancien "Microsoft.Jet.OLEDB" et le dernier "Microsoft.ACE.OLEDB". Recherchez-les lorsque vous établissez une connexion (par exemple avec l'assistant de connexion de données).

3. Une fois connectée à un classeur Excel, une feuille de calcul ou une plage est l'équivalent d'un tableau ou d'une vue. Le nom de table d'une feuille de calcul est le nom de la feuille de calcul auquel est ajouté le signe dollar ("$"), entouré de crochets ("[" et "]") ; pour une plage, il s'agit simplement du nom de la plage. Pour spécifier une plage de cellules sans nom comme source d'enregistrement, ajoutez la notation standard Excel ligne/colonne à la fin du nom de la feuille, entre crochets.

4. Le SQL natif sera (plus ou moins) le SQL de Microsoft Access. (Dans le passé, il était appelé JET SQL ; cependant, Access SQL a évolué, et je crois que JET est une vieille technologie dépréciée).

5. Exemple, la lecture d'une feuille de travail : SELECT * FROM [Sheet1$]

6. Exemple, lecture d'une gamme : SELECT * FROM MyRange

7. Exemple, lecture d'une plage de cellules sans nom : SELECT * FROM [Sheet1$A1:B10]

8. Il existe de nombreux livres et sites web qui peuvent vous aider à résoudre ces problèmes.

Autres notes

Par défaut, il est supposé que la première ligne de votre source de données Excel contient des en-têtes de colonne qui peuvent être utilisés comme noms de champ. Si ce n'est pas le cas, vous devez désactiver ce paramètre, ou votre première ligne de données "disparaît" pour être utilisée comme nom de champ. Pour ce faire, ajoutez l'option HDR= setting dans les propriétés étendues de la chaîne de connexion. La valeur par défaut, qu'il n'est pas nécessaire de préciser, est la suivante HDR=Yes . Si vous n'avez pas d'en-têtes de colonne, vous devez spécifier HDR=No ; le fournisseur nomme vos champs F1, F2, etc.

Une mise en garde concernant la spécification des feuilles de calcul : Le fournisseur suppose que votre table de données commence par la cellule non vide la plus haute et la plus à gauche de la feuille de calcul spécifiée. En d'autres termes, votre tableau de données peut commencer à la ligne 3, colonne C sans problème. Cependant, vous ne pouvez pas, par exemple, taper un titre de feuille de calcul au-dessus et à gauche des données de la cellule A1.

Une mise en garde concernant la spécification des plages : Lorsque vous spécifiez une feuille de calcul comme source d'enregistrements, le fournisseur ajoute de nouveaux enregistrements sous les enregistrements existants dans la feuille de calcul, selon l'espace disponible. Lorsque vous spécifiez une plage (nommée ou non), Jet ajoute également de nouveaux enregistrements sous les enregistrements existants de la plage, dans la limite de l'espace disponible. Toutefois, si vous effectuez une nouvelle requête sur la plage d'origine, le jeu d'enregistrements résultant n'inclut pas les nouveaux enregistrements ajoutés en dehors de la plage.

Types de données (à essayer) pour CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal .

Connexion à Excel "old tech" (fichiers avec l'extension xls) : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\MyWorkbook.xls;Extended Properties=Excel 8.0; . Utilisez le type de base de données source Excel 5.0 pour les classeurs Microsoft Excel 5.0 et 7.0 (95) et utilisez le type de base de données source Excel 8.0 pour les classeurs Microsoft Excel 8.0 (97), 9.0 (2000) et 10.0 (2002).

Connexion à la "dernière" version d'Excel (fichiers avec l'extension xlsx) : Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"

Traiter les données comme du texte : Le paramètre IMEX traite toutes les données comme du texte. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

(Plus de détails à http://www.connectionstrings.com/excel )

Plus d'informations sur http://msdn.microsoft.com/en-US/library/ms141683(v=sql.90).aspx et à http://support.microsoft.com/kb/316934

Connexion à Excel via ADODB par VBA détaillée à l'adresse suivante http://support.microsoft.com/kb/257819

Détails sur le Microsoft JET 4 à l'adresse http://support.microsoft.com/kb/275561

Answer 2

tl;dr ; Excel fait tout cela nativement - utilisez filtres et ou tableaux

( http://office.microsoft.com/en-gb/excel-help/filter-data-in-an-excel-table-HA102840028.aspx )

Vous pouvez ouvrir Excel de manière programmatique via une connexion oledb et exécuter des requêtes SQL sur les tables de la feuille de calcul.

Mais vous pouvez faire tout ce que vous demandez sans formules, juste avec des filtres.

1. cliquez n'importe où dans les données vous êtes à la recherche de
2. aller à données sur la barre de ruban
3. sélectionnez "Filtre". il est à peu près au milieu et ressemble à un entonnoir
   • vous aurez des flèches sur le côté droit de chaque cellule dans la première ligne de votre tableau maintenant
4. cliquez sur la flèche du numéro de téléphone et désélectionner les blancs (dernière option)
5. cliquez sur la flèche du nom de famille et sélectionner une commande a-z (option du haut)

Jouez un peu quelques points à noter :

1. Vous pouvez sélectionner les lignes filtrées et les coller ailleurs.
2. dans la barre d'état à gauche, vous verrez combien de lignes répondent à vos critères de filtrage sur le nombre total de lignes. (par exemple, 308 des 313 enregistrements trouvés).
3. vous pouvez filtrer par couleur dans excel 2010 sur wards
4. Parfois, je crée des colonnes calculées qui donnent des statuts ou des versions nettoyées des données. Vous pouvez ensuite filtrer ou trier en fonction de ces colonnes. (par exemple, comme les formules dans les autres réponses).

Faites-le avec des filtres à moins que vous ne le fassiez souvent ou que vous vouliez automatiser l'importation de données quelque part ou autre chose mais pour être complet :

Une option c# :

 OleDbConnection ExcelFile = new OleDbConnection( String.Format( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES\"", filename));
 ExcelFile.Open();

un bon point de départ est de jeter un coup d'œil au schéma, car il y en a peut-être plus que vous ne le pensez :

List<String> excelSheets = new List<string>();

// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows) {
    string temp = row["TABLE_NAME"].ToString();
    if (temp[temp.Length - 1] == '$') {
         excelSheets.Add(row["TABLE_NAME"].ToString());
    }
}

puis lorsque vous voulez interroger une feuille :

 OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + sheet + "]", ExcelFile);
 dt = new DataTable();
  da.Fill(dt);

NOTE - Utilisez les tableaux dans excel !

Excel dispose d'une fonctionnalité "tableaux" qui permet de faire en sorte que les données se comportent comme un tableau. Cela présente de grands avantages mais ne vous permet pas d'effectuer tous les types de requêtes.

http://office.microsoft.com/en-gb/excel-help/overview-of-excel-tables-HA010048546.aspx

Pour les données tabulaires dans Excel, c'est ce que je fais par défaut. La première chose que je fais est de cliquer sur les données, puis de sélectionner "formater en tant que tableau" dans la section d'accueil du ruban. Cela vous donne le filtrage et le tri par défaut et vous permet d'accéder au tableau et aux champs par nom (par exemple, tableau[nom du champ]). Cela permet également des fonctions d'agrégation sur les colonnes, par exemple, max et moyenne.

Answer 3

Puis-je suggérer de donner QueryStorm Il s'agit d'un plugin pour Excel qui rend l'utilisation de SQL dans Excel très pratique.

De plus, c'est freemium. Si vous ne vous souciez pas de l'autocomplétion, des symboles d'erreur, etc., vous pouvez l'utiliser gratuitement. Il suffit de télécharger et d'installer, et vous avez le support SQL dans Excel.

Clause de non-responsabilité : je suis l'auteur.

Answer 4

Vous pouvez le faire en mode natif comme suit :

1. Sélectionnez le tableau et utilisez Excel pour le trier sur le nom de famille.
2. Créez un critère de filtrage avancé de 2 rangées par 1 colonne, par exemple dans E1 et E2, où E1 est vide et E2 contient la formule =C6="" où C6 est la première cellule de données de la colonne du numéro de téléphone.
3. Sélectionnez le tableau et utilisez le filtre avancé, copiez dans une plage, en utilisant la fonction la plage de critères en E1:E2 et spécifiez l'endroit où vous voulez copier les sortie vers

Si vous voulez faire cela de manière programmatique, je vous suggère d'utiliser l'enregistreur de macros pour enregistrer les étapes ci-dessus et regarder le code.

Answer 5

Les réponses acceptées ici relèvent de la vieille technologie et ne devraient pas être tentées.

À l'époque où cette question a été rédigée, Power Query n'était pas une option bien connue et n'était pas disponible à moins d'avoir la dernière version d'Office et de l'installer en tant que module complémentaire séparé.

Désormais, Power Query est inclus dans Excel et utilisé par défaut pour obtenir des données. C'est le bon moyen de le faire. Elle est simple, rapide et efficace.

Voici la réponse à la question dans Power Query. Faites une recherche sur "getting started with Power Query" si vous avez besoin d'aide pour reproduire ceci. Une fois que vous aurez commencé avec Power Query, vous verrez que c'est très basique et facile.

let
    Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"lastname", type text}, {"firstname", type text}, {"phonenumber", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([phonenumber] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"lastname", "firstname", "phonenumber"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"lastname", Order.Ascending}})
in
    #"Sorted Rows"