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

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

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

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

1. В Excel есть «Мастер подключения данных», который позволяет импортировать или ссылку из другого источника данных или даже в том же файле Excel.

2. В составе Microsoft Office (и ОС) есть два интересных поставщика: старый ” Microsoft.Jet.OLEDB »и последний« Microsoft.ACE.OLEDB ». Ищите их при настройке соединения (например, с помощью мастера подключения к данным).

3. После подключения к книге Excel рабочий лист или диапазон являются эквивалентом таблицы или представления. Имя таблицы рабочего листа – это имя рабочего листа со знаком доллара («$»), добавленным к нему и заключенным в квадратные скобки («[» и «]»); диапазона, это просто имя диапазона. Чтобы указать безымянный диапазон ячеек в качестве источника записей, добавьте стандартную нотацию строк/столбцов Excel в конец имени листа в квадратных скобках.

4. Собственный SQL будет (более или менее) SQL Microsoft Access. (В прошлом он назывался JET SQL; однако Access SQL эволюционировал, и я считаю, что JET устарел, старая технология.)

5. Пример, чтение рабочего листа : SELECT * FROM [Sheet1 $]

6. Пример чтения диапазона: SELECT * FROM MyRange

7. Пример, чтение безымянного диапазона ячеек: SELECT * FROM [Sheet1 $ A1: B10]

8. Существует много-много книг и веб-сайтов, которые помогут вам разобраться в деталях.

=== Дополнительные примечания ===

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

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

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

Типы данных (стоит попробовать) для C REATE TABLE: Short , Long, Single, Double, Currency, DateTime, Bit, Byte, 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 = ДА; "

Обработка данных как текста: настройка IMEX обрабатывает все данные как текст. Provider = Microsoft.ACE.OLEDB.12.0; Data Source = Excel2007file.xlsx; Extended Properties = "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

  OleDbConnection ExcelFile = new OleDbConnection (String.Format ("Provider  = Microsoft.ACE.OLEDB.12.0; Источник данных = {0}; Расширенные свойства =  "Excel 12.0; HDR = ДА " ", имя файла));  ExcelFile.Open ();  

  List  excelSheets = new List  ();//Добавляем имя листа в строковый array.foreach (строка DataRow в dt.Rows) {string temp = row ["TABLE_NAME"]  .Нанизывать();  если (temp [temp.Length - 1] == '$') {excelSheets.Add (row ["TABLE_NAME"]. ToString ());  }}  

  OleDbDataAdapter da = new OleDbDataAdapter ("выберите * из ["  + лист + "]", ExcelFile);  dt = новый DataTable ();  da.Fill (dt);  

tl; dr; Все это делает Excel изначально – используйте фильтры и/или таблицы

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

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

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

1. щелкните в любом месте данных , на которые вы смотрите
2. перейти к данным на панели ленты
3. выбрать «Фильтр» примерно посередине и выглядит как воронка
   • теперь у вас будут стрелки на узкой стороне каждой ячейки в первой строке таблицы.
4. щелкните стрелку на номере телефона и отмените выбор пробелов (последний вариант)
5. щелкните стрелку на фамилии и выберите аз-порядок (верхний вариант)

поиграйте … кое-что на заметку:

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

СДЕЛАЙТЕ это с фильтрами, если вы не собираетесь делать это много или вы хотите автоматизировать импорт данных где-то или что-то в этом роде.. но для полноты:

Вариант c #:

  OleDbConnection ExcelFile = new OleDbConnection (String.Format (  "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties = " Excel 12.0; HDR = YES  "", имя файла));  ExcelFile.Open ();  

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

  List  excelSheets = new List  ();//Добавляем имя листа в строковый array.foreach (строка DataRow в dt.Rows) {string temp = row ["TABLE_NAME"]  .Нанизывать();  если (temp [temp.Length - 1] == '$') {excelSheets.Add (row ["TABLE_NAME"]. ToString ());  }}  

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

  OleDbDataAdapter da = new OleDbDataAdapter ("выберите * из ["  + лист + "]", ExcelFile);  dt = новый DataTable ();  da.Fill (dt);  

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

Excel имеет “таблицы “функциональность, которая заставляет данные вести себя больше как таблица … это дает вам большие преимущества, но не позволит вам выполнять все типы запросов.

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

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

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

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

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

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

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

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

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

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

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

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

• A: фамилия
• B: имя
• C: номер телефона

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

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

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

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

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

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

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

-1

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

Улучшите этот ответ

ответил 20 сен. ’13 в 23:47

добавить комментарий |

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

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

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

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

  ИНФОРМАЦИЯ: Открытие `Book1.xlsx 'с использованием драйвера` XLSX' выполнено успешно. Имя слоя: SELECTGeometry: NoneFeature Count: 36Layer SRS WKT: (unknown  ) name: String (0.0) count (*): Integer (0.0) OGRFeature (SELECT): 0 name (String) = Red count (*) (Integer) = 849OGRFeature (SELECT): 1 name (String) = Green count (  *) (Целое число) = 265 ...  

  $ ogr2ogr -f CSV out.csv -dialect sqlite  -sql "ВЫБРАТЬ имя, количество (*) FROM sheet1 GROUP BY name" Book1.xlsx $ cat out.csvname, count (*  ) Красный, 849Зеленый, 265 ...  

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

  ogrinfo -dialect sqlite -sql "  ВЫБРАТЬ имя, количество (*) ИЗ листа1 ГРУППА ПО имени "Книга1. xlsx  

выполнит запрос SQLite на sheet1 и выдаст результат запроса в необычной форме:

   ИНФОРМАЦИЯ: Открытие `Book1.xlsx 'с использованием драйвера` XLSX' успешно. Имя слоя: SELECTGeometry: NoneFeature Count: 36Layer SRS WKT: (unknown) name: String (0.0) count (*): Integer (0.0  ) OGRFeature (SELECT): 0 name (String) = Red count (*) (Integer) = 849OGRFeature (SELECT): 1 name (String) = Green count (*) (Integer) = 265 ...  

Или выполните тот же запрос с помощью ogr2ogr , чтобы создать простой файл CSV:

  $ ogr2ogr -f CSV  out.csv -dialect sqlite  -sql "ВЫБРАТЬ имя, количество (*) ИЗ листа1 ГРУППА ПО имени" Book1.xlsx $ cat out.csvname, count (*) Красный, 849Зеленый, 265 ...  

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

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

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

Выполнение SQL-запросов в Excel