Jak uruchomić zapytanie SQL w tabeli programu Excel?

Próbuję utworzyć tabelę podrzędną z innej tabeli wszystkich pól nazwisk posortowanych od A do Z, które mają pole numeru telefonu, które nie jest puste. Mógłbym zrobić to całkiem łatwo za pomocą SQL, ale nie mam pojęcia, jak wykonać zapytanie SQL w programie Excel. Kusi mnie, aby zaimportować dane do postgresql i po prostu tam zapytać, ale to wydaje się trochę przesadne.

W przypadku tego, co próbuję zrobić, zapytanie SQL załatwi SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastnamesprawę. Wydaje się zbyt proste, aby było to coś, czego program Excel nie może wykonać natywnie. Jak uruchomić takie zapytanie SQL w programie Excel?

Jest na to wiele dobrych sposobów, które inni już sugerowali. Poniżej znajduje się kilka wskazówek dotyczących „pobierania danych programu Excel za pomocą ścieżki SQL”.

1. Excel ma „Kreatora połączenia danych”, który umożliwia importowanie lub łączenie z innego źródła danych lub nawet w tym samym pliku Excel.

2. W ramach Microsoft Office (i OS) są dwaj interesujący dostawcy: stary „Microsoft.Jet.OLEDB” i najnowszy „Microsoft.ACE.OLEDB”. Szukaj ich podczas konfigurowania połączenia (na przykład w Kreatorze połączenia danych).

3. Po połączeniu ze skoroszytem programu Excel arkusz lub zakres jest odpowiednikiem tabeli lub widoku. Nazwa tabeli arkusza to nazwa arkusza z dołączonym znakiem dolara („$”) i otoczona nawiasami kwadratowymi („[” i „]”); zakresu, jest to po prostu nazwa zakresu. Aby określić nienazwany zakres komórek jako źródło rekordów, dołącz standardową notację wiersza / kolumny programu Excel na końcu nazwy arkusza w nawiasach kwadratowych.

4. Natywny SQL będzie (mniej więcej) SQL Microsoft Access. (W przeszłości nosił nazwę JET SQL; jednak Access SQL ewoluował i uważam, że JET jest przestarzałą starą technologią).

5. Przykład, czytanie arkusza roboczego: SELECT * FROM [Sheet1$]

6. Przykład, odczyt zakresu: SELECT * FROM MyRange

7. Przykład odczytywania nienazwanego zakresu komórek: SELECT * FROM [Sheet1$A1:B10]

8. Dostępnych jest wiele, wiele książek i witryn internetowych, które pomogą Ci przeanalizować szczegóły.

=== Dalsze uwagi ===

Domyślnie zakłada się, że pierwszy wiersz źródła danych programu Excel zawiera nagłówki kolumn, których można używać jako nazw pól. Jeśli tak nie jest, musisz wyłączyć to ustawienie, w przeciwnym razie pierwszy wiersz danych „znika” i zostanie użyty jako nazwy pól. Odbywa się to przez dodanie opcjonalnego HDR= settingdo właściwości rozszerzonych parametrów połączenia. Wartość domyślna, której nie trzeba określać, to HDR=Yes. Jeśli nie masz nagłówków kolumn, musisz określić HDR=No; dostawca nazywa twoje pola F1, F2 itp.

Przestroga dotycząca określania arkuszy: Dostawca zakłada, że ​​tabela danych zaczyna się od najwyższej, skrajnej lewej, niepustej komórki w określonym arkuszu. Innymi słowy, Twoja tabela danych może rozpocząć się w wierszu 3 w kolumnie C bez problemu. Nie możesz jednak na przykład wpisać tytułu arkusza roboczego powyżej i po lewej stronie danych w komórce A1.

Ostrzeżenie dotyczące określania zakresów: Gdy określisz arkusz jako źródło rekordów, dostawca dodaje nowe rekordy poniżej istniejących rekordów w arkuszu, o ile pozwala na to miejsce. Po określeniu zakresu (nazwanego lub nienazwanego) Jet dodaje również nowe rekordy poniżej istniejących rekordów w zakresie, na ile pozwala na to miejsce. Jednak w przypadku ponownego wyszukiwania w oryginalnym zakresie wynikowy zestaw rekordów nie zawiera nowo dodanych rekordów spoza zakresu.

Typy danych (warto spróbować) dla C REATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.

Podłączanie do „starych Tech” Excel (pliki z rozszerzeniem xls) na: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\MyWorkbook.xls;Extended Properties=Excel 8.0;. W przypadku skoroszytów programu Microsoft Excel 5.0 i 7.0 (95) należy używać źródłowego typu bazy danych programu Excel 5.0, a dla skoroszytów programu Microsoft Excel 8.0 (97), 9.0 (2000) i 10.0 (2002) należy używać źródłowego typu bazy danych programu Excel 8.0.

Łączenie się z „najnowszym” Excelem (pliki z rozszerzeniem xlsx): Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"

Traktowanie danych jako tekstu: ustawienie IMEX traktuje wszystkie dane jako tekst. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

(Więcej szczegółów na http://www.connectionstrings.com/excel )

Więcej informacji można znaleźć pod adresem http://msdn.microsoft.com/en-US/library/ms141683(v=sql.90).aspx i pod adresem http://support.microsoft.com/kb/316934

Łączenie się z programem Excel przez ADODB przez VBA, szczegółowo opisane na http://support.microsoft.com/kb/257819

Szczegóły dotyczące Microsoft JET 4 są dostępne pod adresem http://support.microsoft.com/kb/275561

tl; dr; Excel robi to wszystko natywnie - użyj filtrów i / lub tabel

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

Program Excel można otwierać programowo przez połączenie oledb i wykonywać SQL na tabelach w arkuszu.

Ale możesz zrobić wszystko, o co prosisz, bez formuł, tylko filtry.

1. kliknij gdziekolwiek w danych patrzysz
2. przejdź do danych na pasku wstążki
3. wybierz „Filtr” mniej więcej pośrodku i wygląda jak lejek
   • teraz będziesz mieć strzałki po wąskiej stronie każdej komórki w pierwszym rzędzie tabeli
4. kliknij strzałkę na numerze telefonu i odznacz puste pola (ostatnia opcja)
5. kliknij strzałkę przy nazwisku i wybierz kolejność az (górna opcja)

pobaw się ... kilka rzeczy do zapamiętania:

1. możesz wybrać przefiltrowane wiersze i umieścić je w innym miejscu
2. na pasku stanu po lewej stronie zobaczysz, ile wierszy spełnia kryteria odfiltrowania z łącznej liczby wierszy. (np. 308 z 313 znalezionych rekordów)
3. możesz filtrować według kolorów w programie Excel 2010 na oddziałach
4. Czasami tworzę kolumny obliczeniowe, które podają statusy lub wyczyszczone wersje danych, które możesz następnie filtrować lub sortować według tez. (np. jak formuły w innych odpowiedziach)

ZRÓB to z filtrami, chyba że masz zamiar robić to dużo lub chcesz zautomatyzować importowanie danych gdzieś lub coś w tym stylu ... ale dla kompletności:

Opcja 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();

wygodnym miejscem do rozpoczęcia jest przyjrzenie się schematowi, ponieważ może być tam więcej, niż myślisz:

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());
    }
}

wtedy gdy chcesz zapytać o arkusz:

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

UWAGA - Użyj tabel w programie Excel !:

Excel ma funkcję „tabel”, która sprawia, że ​​dane zachowują się bardziej jak tabela. Daje to wiele korzyści, ale nie pozwala na wykonywanie każdego rodzaju zapytań.

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

W przypadku danych tabelarycznych w programie Excel jest to moje ustawienie domyślne. Najpierw klikam dane, a następnie wybieram opcję „formatuj jako tabelę” w sekcji głównej na wstążce. zapewnia to domyślne filtrowanie i sortowanie oraz umożliwia dostęp do tabeli i pól według nazwy (np. tabela [nazwa pola]) umożliwia to również funkcje agregacji na kolumnach np. maks. i średnia

Możesz to zrobić natywnie w następujący sposób:

1. Wybierz tabelę i użyj programu Excel, aby posortować ją według nazwiska
2. Utwórz zaawansowane kryteria filtrowania z 2 wierszami i 1 kolumną, na przykład w E1 i E2, gdzie E1 jest puste, a E2 zawiera formułę =C6="" gdzie C6 to pierwsza komórka danych w kolumnie numeru telefonu.
3. Wybierz tabelę i użyj zaawansowanego filtru, skopiuj do zakresu, używając zakresu kryteriów w E1: E2 i określ, gdzie chcesz skopiować wynik

Jeśli chcesz to zrobić programowo, sugeruję użycie rejestratora makr, aby zarejestrować powyższe kroki i spojrzeć na kod.

Państwo może używać SQL w programie Excel. Jest tylko dobrze ukryty. Zobacz ten samouczek:

http://smallbusiness.chron.com/use-sql-statements-ms-excel-41193.html

Mogę zasugerować podanie QueryStorm wypróbowanie - jest to wtyczka do Excela, która sprawia, że ​​korzystanie z SQL w Excelu jest dość wygodne.

Poza tym jest to freemium. Jeśli nie zależy Ci na autouzupełnianiu, zawijasach błędów itp., Możesz z niego korzystać za darmo. Wystarczy pobrać i zainstalować, aby mieć obsługę SQL w programie Excel.

Zastrzeżenie: jestem autorem.

Jeśli chcesz to zrobić raz, po prostu postępuj zgodnie z opisami Charlesa, ale można to również zrobić za pomocą formuł programu Excel i kolumn pomocniczych, jeśli chcesz, aby filtr był dynamiczny.

Załóżmy, że dane znajdują się w arkuszu DataSheet i zaczynają się w wierszu 2 następujących kolumn:

• A: nazwisko
• B: imię
• C: numer telefonu

Potrzebujesz dwóch kolumn pomocniczych na tym arkuszu.

• D2:, =if(A2 = "", 1, 0)to jest kolumna filtru odpowiadająca warunkowi Where
• E2: =if(D2 <> 1, "", sumifs(D$2:D$1048576, A$2:A$1048576, "<"&A2) + sumifs(D$2:D2, A$2:A2, A2))odpowiada zamówieniu wg

Skopiuj te formuły, jeśli chodzi o dane.

Na arkuszu, który powinien wyświetlać twój wynik, utwórz następujące kolumny.

• Odp .: sekwencja liczb zaczynająca się od 1 w wierszu 2, ogranicza to całkowitą liczbę wierszy, które można uzyskać (trochę jak limit w sequelu)
• B2: =match(A2, DataSheet!$E$2:$E$1048576, 0)to jest wiersz odpowiednich danych
• C2:, =iferror(index(DataSheet!A$2:A$1048576, $B2), "")to są rzeczywiste dane lub puste, jeśli żadne dane nie istnieją

Skopiuj wzory z B2 i C2 i skopiuj kolumnę C do D i E.

Jeśli bezpośrednio odpytujesz pliki programu Excel, wypróbuj esProc. Zobacz https://esprocforbp.medium.com/directly-query-excel-text-files-using-sql-5315788231e4 . Obsługuje nie tylko zwykły SQL, ale także obsługuje Group HAVING, Subquery, Nested Subquery, Join, a nawet „with… as” Table Expression, na przykład:

$with A as
(select NAME as DEPT from E:/department.xlsx where NAME='HR' or NAME='Sales') 
select A.DEPT DEPT,count(*) NUM,avg(B.SAL_ARY) AVG_SALARY from A left join E:/employee.xlsx B on A.DEPT=B.DEPT
where B.GENDER='F' group by A.DEPT

Jeśli chcesz wykonać zapytanie o obszar w programie Excel, skopiuj dane do schowka. Następnie esProc uzyskuje tabelę danych, uzyskując dostęp do funkcji schowka, a następnie wykonuje zapytania dotyczące jednej tabeli. Jeśli chcesz zapytać o wiele obszarów, skopiuj każdy obszar danych do esProc i pobierz wiele tabel w esProc i wykonaj zapytanie wielotabelowe, jak w przykładzie.

ZRZECZENIE SIĘ: Chodzi o nasze narzędzie esProc. To jest freemium.

Możesz eksperymentować z natywnym sterownikiem bazy danych dla programu Excel w wybranym języku / platformie. W świecie Java możesz wypróbować http://code.google.com/p/sqlsheet/, który udostępnia sterownik JDBC do bezpośredniej pracy z arkuszami Excel. Podobnie możesz pobrać sterowniki dla technologii DB dla innych platform.

Mogę jednak zagwarantować, że wkrótce trafisz na ścianę z liczbą funkcji, które zapewniają te biblioteki opakowań. Lepszym sposobem będzie użycie Apache HSSF / POI lub podobnego poziomu biblioteki, ale będzie to wymagało więcej wysiłku w kodowaniu.

Mogę źle mnie zrozumieć, ale czy nie jest to dokładnie to, co robi tabela przestawna? Czy masz dane w tabeli czy tylko przefiltrowaną listę? Jeśli to nie jest tabela, stwórz ją (ctrl + l), jeśli tak jest, po prostu aktywuj dowolną komórkę w tabeli i wstaw tabelę przestawną na innym arkuszu. Następnie dodaj kolumny lastname, firstname, phonenumber do sekcji wierszy. Następnie dodaj numer telefonu do sekcji filtru i odfiltruj wartości null. Teraz Sortuj jak zwykle.

Proponuję przyjrzeć się silnikowi pamięci masowej MySQL csv, który zasadniczo pozwala załadować dowolny plik csv (łatwo utworzony z programu Excel) do bazy danych, gdy już to zrobisz, możesz użyć dowolnego polecenia SQL.

Warto się temu przyjrzeć.

Jeśli masz GDAL / OGR skompilowany z biblioteką Expat, możesz użyć sterownika XLSX do odczytywania plików .xlsx i uruchamiania wyrażeń SQL z wiersza poleceń. Na przykład z powłoki osgeo4w w tym samym katalogu co arkusz kalkulacyjny użyj narzędzia ogrinfo :

ogrinfo -dialect sqlite -sql "SELECT name, count(*) FROM sheet1 GROUP BY name" Book1.xlsx

uruchomi zapytanie SQLitesheet1 i wyświetli wynik zapytania w nietypowej formie:

INFO: Open of `Book1.xlsx'
      using driver `XLSX' successful.

Layer name: SELECT
Geometry: None
Feature Count: 36
Layer SRS WKT:
(unknown)
name: String (0.0)
count(*): Integer (0.0)
OGRFeature(SELECT):0
  name (String) = Red
  count(*) (Integer) = 849

OGRFeature(SELECT):1
  name (String) = Green
  count(*) (Integer) = 265
...

Lub uruchom to samo zapytanie, używając ogr2ogr, aby utworzyć prosty plik CSV :

$ ogr2ogr -f CSV out.csv -dialect sqlite \
          -sql "SELECT name, count(*) FROM sheet1 GROUP BY name" Book1.xlsx

$ cat out.csv
name,count(*)
Red,849
Green,265
...

Aby zrobić podobnie ze starszymi plikami .xls, potrzebujesz sterownika XLS , zbudowanego w oparciu o bibliotekę FreeXL, co nie jest zbyt powszechne (np. Nie z OSGeo4w).

Microsoft Access i LibreOffice Base mogą otworzyć arkusz kalkulacyjny jako źródło i uruchamiać na nim zapytania sql. Byłby to najłatwiejszy sposób uruchamiania wszelkiego rodzaju zapytań i unikania bałaganu związanego z uruchamianiem makr lub pisaniem kodu.

Excel ma również autofiltry i sortowanie danych, które wykonują wiele prostych zapytań, takich jak Twój przykład. Jeśli potrzebujesz pomocy z tymi funkcjami, Google byłby lepszym źródłem samouczków niż ja.