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

Ответ 1

Существует много прекрасных способов сделать это, о чем другие уже высказали. Следуя "Получить данные 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:\MyFolder\MyWorkbook.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

Ответ 2

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

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

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

Ответ 3

TL;DR; Excel делает все это изначально - используйте фильтры и tables

(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]), это также позволяет создавать агрегатные функции в столбцах, например. макс и средний

Ответ 4

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

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

Ответ 5

Если вам нужно сделать это, просто следуйте описаниям Чарльза, но это также можно сделать с помощью формул 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.

Ответ 6

Могу я предложить дать QueryStorm попробовать - это плагин для Excel, что делает его весьма удобным для использования SQL в Excel.

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

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

Ответ 7

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

Ответ 8

Я нашел отличный пример, где вы можете рассматривать листы как таблицы.

ссылка на видео

Ответ 9

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

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

Ответ 10

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

Ответ 11

Я предлагаю вам взглянуть на MySQL механизм хранения csv, который по сути позволяет загружать любой файл csv (легко созданный из excel) в базу данных, как только вы это сделаете, вы можете использовать любую команду SQL, которую вы хотите.

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

Ответ 12

Если у вас GDAL/OGR, скомпилированный с помощью библиотеки Expat, вы можете использовать драйвер XLSX для чтения файлов .xlsx и запуска SQL-выражений из командной строки. Например, из osgeo4w оболочки в том же каталоге, что и электронная таблица, используйте ogrinfo:

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

выполнит запрос SQLite на 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, чтобы сделать простой 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
...

Чтобы сделать то же самое со старыми файлами .xls, вам понадобится драйвер XLS, созданный против библиотеки FreeXL, что на самом деле не является обычным явлением ( например, не из OSGeo4w).

Ответ 13

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

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