Как запустить SQL-запрос к таблице Excel?
==========================================

 Я пытаюсь создать вложенную таблицу из другой таблицы всех полей фамилии, отсортированных по AZ, которые имеют поле номера телефона, которое не является нулевым. Я мог бы сделать это довольно легко с помощью SQL, но я понятия не имею, как выполнить запрос SQL в Excel. Я испытываю желание импортировать данные в postgresql и просто запрашивать их там, но это кажется немного чрезмерным.

  Для того, что я пытаюсь сделать, SQL-запрос `SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname` поможет. Это кажется слишком простым для того, чтобы Excel не мог сделать это изначально. Как я могу выполнить такой запрос SQL из Excel?

 

Существует много прекрасных способов сделать это, о чем другие уже высказали. Следуя "Получить данные Excel через SQL-трек", вот несколько указателей.

* В Excel есть "Мастер подключения к данным", который позволяет вам импортировать или ссылаться из другого источника данных или даже в тот же файл Excel.
* В составе Microsoft Office (и ОС) есть два поставщика, представляющих интерес: старый "Microsoft.Jet.OLEDB" и последний "Microsoft.ACE.OLEDB". Ищите их при настройке соединения (например, с помощью мастера подключения к данным).
* После подключения к книге Excel рабочий лист или диапазон эквивалентны таблице или представлению. Имя таблицы рабочего листа - это имя рабочего листа с прикрепленным к нему значком доллара ( "$" ) и окруженным квадратными скобками ( "[" и "]" ); диапазона, это просто название диапазона. Чтобы указать неназванный диапазон ячеек в качестве источника записи, добавьте стандартную нотацию столбца/столбца Excel в конец имени листа в квадратных скобках.
 Собственный SQL будет (более или менее быть) SQL Microsoft Access. (Раньше он назывался JET SQL, однако Access SQL эволюционировал, и я считаю, что JET устарел старый tech.)
 Пример: чтение рабочего листа: SELECT  FROM [Sheet1 $]
* Пример: чтение диапазона: SELECT * FROM MyRange
* Пример: чтение неназванного диапазона ячеек: SELECT * FROM [Sheet1 $A1: B10]
 Существует много многих книг и веб-сайтов, которые помогут вам разобраться в деталях.

=== Дальнейшие примечания ===

По умолчанию предполагается, что первая строка источника данных Excel содержит заголовки столбцов, которые можно использовать в качестве имен полей. Если это не так, вы должны отключить этот параметр, или ваша первая строка данных "исчезает" для использования в качестве имен полей. Это делается добавлением дополнительного параметра HDR = в расширенные свойства строки подключения. Значение по умолчанию, которое не нужно указывать, - HDR = Да. Если у вас нет заголовков столбцов, вам нужно указать HDR = No; поставщик называет ваши поля F1, F2 и т.д.

Предупреждение об указании рабочих листов. Поставщик предполагает, что ваша таблица данных начинается с самой последней, самой левой, не пустой ячейки на указанном листе. Другими словами, ваша таблица данных может начинаться с строки 3, столбец C без проблем. Однако вы не можете, например, напечатать заголовок workheeet выше и слева от данных в ячейке A1.

Предупреждение о задании диапазонов. Когда вы указываете рабочий лист в качестве источника записи, поставщик добавляет новые записи ниже существующих записей на листе, как позволяет пространство. Когда вы укажете диапазон (названный или неназванный), Jet также добавляет новые записи под существующими записями в диапазоне, как позволяет пространство. Однако, если вы запрашиваете исходный диапазон, результирующий набор записей не включает вновь добавленные записи за пределами диапазона.

Типы данных (стоит попробовать) для CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, бит, байт, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.

Подключение к "старой технологии" Excel (файлы с расширением xls): Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:MyFolderMyWorkbook.xls;Extended Properties=Excel 8.0;. Используйте исходный тип базы данных Excel 5.0 для книг Microsoft Excel 5.0 и 7.0 (95) и используйте исходный тип данных Excel 8.0 для книг Microsoft Excel 8.0 (97), 9.0 (2000) и 10.0 (2002).

Подключение к "последнему" Excel (файлы с расширением xlsx): Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"

Обработка данных в виде текста: установка IMEX обрабатывает все данные как текст. Provider = Microsoft.ACE.OLEDB.12.0; Источник данных = Excel2007file.xlsx; Расширенные свойства = "Excel 12.0 Xml; HDR = YES; IMEX = 1";

(Подробнее см. <http://www.connectionstrings.com/excel>)

Дополнительная информация на <http://msdn.microsoft.com/en-US/library/ms141683(v=sql.90).aspx> и <http://support.microsoft.com/kb/316934>

Подключение к Excel через ADODB через VBA, подробно описанное в <http://support.microsoft.com/kb/257819>

Сведения о Microsoft JET 4 в <http://support.microsoft.com/kb/275561>

 

Вы можете сделать это изначально следующим образом:   

 Выберите таблицу и используйте Excel для сортировки по имени
 Создайте критерии расширенного фильтра с 2 строками по 1 столбцу, скажем, в
E1 и E2, где E1 пуст, а E2 содержит формулу =C6=""
где C6 - первая ячейка данных столбца телефонного номера.
 Выберите таблицу и используйте расширенный фильтр, скопируйте в диапазон, используя
диапазон критериев в E1: E2 и укажите, где вы хотите скопировать
вывод на

Если вы хотите сделать это программно, я предлагаю вам использовать Macro Recorder для записи вышеуказанных шагов и просмотра кода.

 

  TL;DR; Excel делает все это изначально - используйте [фильтры](http://office.microsoft.com/en-gb/excel-help/filter-data-in-an-excel-table-HA102840028.aspx) и [tables](http://office.microsoft.com/en-gb/excel-help/overview-of-excel-tables-HA010048546.aspx)

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

Вы можете открыть программу excel через соединение oledb и выполнить SQL в таблицах на листе.

Но вы можете делать все, что вы просите, чтобы без формул просто фильтровать.

 щелкните в любом месте данных, которые вы ищете
 перейти к данным на ленточной панели
 выберите "Фильтр"  вокруг середины и выглядит как воронка
	+ у вас будут стрелки на жесткой стороне каждой ячейки в первой строке таблицы.
 щелкните стрелку на номер телефона и  снимите флажки (последняя опция)
 щелкните стрелку на фамилии и  выберите a-z ordering (верхний вариант)

есть игра вокруг.. некоторые вещи, чтобы отметить:

 вы можете выбрать отфильтрованные строки и пастовать их где-то еще.
 в строке состояния слева вы увидите, сколько строк соответствует критериям фильтрации из общего количества строк. (например, 308 из 313 найденных записей).
 вы можете фильтровать по цвету в excel 2010 на подопечных
 Иногда я создаю вычисленные столбцы, которые предоставляют статусы или очищенные версии данных, которые вы затем можете фильтровать или сортировать по этим тезисам. (например, как формулы в других ответах).

Сделайте это с помощью фильтров, если вы не собираетесь делать это много или вы хотите автоматизировать импорт данных где-то или что-то в этом роде, но для полноты:

  Параметр С#:

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

удобное место для начала - взглянуть на схему, поскольку там может быть больше, чем вы думаете:

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

```

 затем, когда вы хотите запросить лист:

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

```

  **ПРИМЕЧАНИЕ. - Используйте таблицы в excel!:**

В Excel есть "таблицы", которые делают данные более похожими на таблицу. Это дает вам некоторые большие преимущества, но не позволит вам делать все типы запросов.

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

Для табличных данных в excel это мое значение по умолчанию. Первое, что я делаю, - это щелкнуть мышью по данным, а затем выбрать "формат в виде таблицы" из домашнего раздела на ленте. это дает вам фильтрацию и сортировку по умолчанию и позволяет вам получить доступ к таблице и полям по имени (например, table [fieldname]), это также позволяет создавать агрегатные функции в столбцах, например. макс и средний

 

Вы можете использовать SQL в Excel. Это только хорошо скрыто.
См. Этот учебник:

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

 

Если вам нужно сделать это, просто следуйте описаниям Чарльза, но это также можно сделать с помощью формул Excel и вспомогательных столбцов, если вы хотите сделать динамический фильтр.

Предположим, что данные находятся на листе DataSheet и начинаются в строке 2 следующих столбцов:

* A: lastname
* B: firstname
* C: phonenumber

Вам нужно два вспомогательных столбца на этом листе.

* D2: `=if(A2 = "", 1, 0)`, это столбец фильтра, соответствующий вашему условию
* E2: `=if(D2 <> 1, "", sumifs(D$2:D$1048576, A$2:A$1048576, "<"&A2) + sumifs(D$2:D2, A$2:A2, A2))`, это соответствует порядку на

Скопируйте эти формулы в соответствии с вашими данными.

На листе, который должен отображать ваш результат, создайте следующие столбцы.

* A: последовательность чисел, начинающихся с 1 в строке 2, ограничивает общее количество строк, которые вы можете получить (вроде как предел в дальнейшем)
* B2: `=match(A2, DataSheet!$E$2:$E$1048576, 0)`, это строка соответствующих данных
* C2: `=iferror(index(DataSheet!A$2:A$1048576, $B2), ""), это фактические данные или пустое, если не существует данных

Скопируйте формулы в B2 и C2 и скопируйте столбец C в D и E.

 

Могу я предложить дать [QueryStorm](https://www.querystorm.com) попробовать - это плагин для Excel, что делает его весьма удобным для использования SQL в Excel.

Кроме того, это freemium. Если вас не интересуют автозаполнение, ошибки squigglies и т.д., Вы можете использовать его бесплатно. Просто загрузите и установите, и у вас есть поддержка SQL в Excel.

Отказ от ответственности: я автор.

 

 Если вы хотите запустить SQL при объединении данных из нескольких листов Excel или из нескольких файлов Excel, вы можете использовать облачную службу под названием [Rockset](https://rockset.com). Rockset предоставляет вам интерфейс SQL, который может объединять данные из Excel, Pdf или txt/xml/json/csv.

 

Я нашел отличный пример, где вы можете рассматривать листы как таблицы.
[![enter image description here]()](/img/69332e7d0bc71ce7e293aca0d392d801.png)

[ссылка на видео](https://www.youtube.com/watch?v=ox1HJCwfav8)



Вы можете поэкспериментировать с собственным драйвером DB для Excel на выбранном вами языке/платформе. В Java-мире вы можете попробовать <http://code.google.com/p/sqlsheet/>, который предоставляет драйвер JDBC для работы с листами Excel напрямую. Аналогичным образом, вы можете получить драйверы для технологии БД для других платформ.

Однако я могу гарантировать, что вы скоро нажмете на стену с количеством функций, предоставляемых этими библиотеками-обложками. Лучше будет использовать Apache HSSF/POI или аналогичный уровень библиотеки, но для этого потребуется больше усилий по кодированию.

 

Возможно, я не понимаю, но разве это не сводная таблица? У вас есть данные в таблице или только отфильтрованный список? Если его таблица не делает ее одной (ctrl + l), если она есть, тогда просто активируйте любую ячейку в таблице и вставьте сводную таблицу на другой лист. Затем добавьте столбцы lastname, firstname, phonenumber в раздел строк. Затем добавьте номер телефона в раздел фильтра и отфильтруйте нулевые значения. Теперь Сортировка выглядит как обычно.

 

Я предлагаю вам взглянуть на MySQL [механизм хранения csv](http://dev.mysql.com/doc/refman/5.0/en/csv-storage-engine.html), который по сути позволяет загружать любой файл csv (легко созданный из excel) в базу данных, как только вы это сделаете, вы можете использовать любую команду SQL, которую вы хотите.

Стоит взглянуть на это.

 

Если у вас  [GDAL/OGR](http://www.gdal.org/), скомпилированный с помощью библиотеки Expat, вы можете использовать [драйвер XLSX](http://www.gdal.org/drv_xlsx.html) для чтения файлов .xlsx и запуска SQL-выражений из командной строки. Например, из [osgeo4w](https://trac.osgeo.org/osgeo4w/) оболочки в том же каталоге, что и электронная таблица, используйте  [ogrinfo](http://www.gdal.org/ogrinfo.html):

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

выполнит запрос [SQLite](http://www.gdal.org/ogr_sql_sqlite.html) на sheet1 и выведет результат запроса в необычной форме:

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
...

Или запустите тот же запрос, используя  [ogr2ogr](http://www.gdal.org/ogr2ogr.html), чтобы сделать простой [CSV](http://www.gdal.org/drv_csv.html) файл:

$ 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
...

```

Чтобы сделать то же самое со старыми файлами .xls, вам понадобится [драйвер XLS](http://www.gdal.org/drv_xls.html), созданный против библиотеки FreeXL, что на самом деле не является обычным явлением ( например, не из OSGeo4w).

 

Microsoft Access и LibreOffice Base могут открывать электронную таблицу в качестве источника и запускать SQL-запросы на нее. Это был бы самый простой способ запускать все виды запросов, а также избежать беспорядка запуска макросов или написания кода.

В Excel также есть автофильтры и сортировка данных, которые будут выполнять множество простых запросов, подобных вашему примеру. Если вам нужна помощь в этих функциях, Google станет лучшим источником учебников, чем я.