Как сделать запрос sql в excel?

Импорт данных из Excel в Microsoft SQL Server на языке T-SQL

Microsoft SQL Server позволяет встроенными средствами языка T-SQL в SQL запросе импортировать данные из файла Excel в базу данных. Сегодня я подробно расскажу, как это делается, какие условия необходимо выполнить, чтобы эта операция проходила успешно, расскажу про особенности импорта для самых распространённых случаев конфигураций SQL сервера и приведу конкретный порядок действий и практические примеры.

Начну я с того, что импортировать данные из Excel в Microsoft SQL Server можно с помощью «Распределенных запросов» и с помощью «Связанных серверов». Это, скорей всего, Вы уже знаете, так как я уже не раз писал об этом (ссылки на соответствующие материалы указаны чуть выше).

Обратиться к файлу Excel и импортировать данные в Microsoft SQL Server можно с помощью T-SQL инструкций OPENDATASOURCE, OPENROWSET или OPENQUERY.

Однако в вышеупомянутых статьях я упустил несколько важных моментов, одним из которых является то, что у всех конфигурация SQL сервера разная, за счет чего у многих возникают различные проблемы и появляются ошибки во время выполнения распределенных запросов и обращений к связанным серверам. Также я описывал способ загрузки данных из Excel, который на сегодняшний день уже устарел, поэтому сегодня я постараюсь дать Вам немного больше информации о том, как импортировать данные из файла Excel в Microsoft SQL Server на языке T-SQL.

Итак, как я уже сказал, очень важную роль здесь играет конфигурация SQL сервера, в частности, какая версия сервера установлена, x86 или x64.

Если говорить о последних версиях Microsoft SQL Server 2016-2019, то они только x64 и устанавливаются на 64-разрядные версии Windows.

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

Для того чтобы быстро узнать, какая версия SQL Server установлена у Вас на компьютере, можете выполнить простой SQL запрос

Обращение к файлу Excel и, соответственно, импорт данных в Microsoft SQL Server происходит с помощью специальных провайдеров (поставщиков). Для работы с Excel в Microsoft SQL Server обычно используются:

  • Jet.OLEDB.4.0
  • ACE.OLEDB.12.0

Во всех примерах ниже я буду посылать простой запрос SELECT на выборку данных из Excel файла, для того чтобы проверить доступ к данным в файле Excel. Чтобы осуществить импорт данных (загрузить данные в БД), Вы можете использовать любой удобный для Вас способ, например, конструкцию SELECT INTO или INSERT INTO.

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

Импорт данных из Excel 2003 (файл xls) в Microsoft SQL Server x86

Шаг 1 – Проверяем наличие провайдера Microsoft.Jet.OLEDB.4.0 на SQL Server

Первое, с чего нам нужно начать, это проверить, зарегистрирован ли провайдер Microsoft.Jet.OLEDB.4.0 на SQL Server, так как в данном случае необходимо использовать именно этот провайдер. Это можно сделать с помощью следующей SQL инструкции

В результирующем наборе данных должна присутствовать строка с Microsoft.Jet.OLEDB.4.0. Если такого провайдера нет, то скорей всего в системе нет установленного Excel 2003 и, соответственно, его нужно установить.

Шаг 2 – Предоставление прав пользователю на временный каталог

Особенностью распределённых запросов и работы со связанным серверами Excel в x86 версиях SQL Server является то, что независимо от имени какой учетной записи посылается SQL запрос к Excel, эта учетная запись должна иметь права на запись во временный каталог той учетной записи, под которой работает сама служба SQL Server.Так как поставщик OLE DB создает временный файл во время запроса во временном каталоге SQL Server, используя учетные данные пользователя, выполняющего запрос.

Таким образом, если служба SQL Server работает от имени или локальной, или сетевой службы, необходимо дать соответствующие права на временный каталог этих служб всем пользователям, которые будут посылать распределенные запросы и обращаться к связанному серверу Excel (если сервер работает от имени пользователя, который посылает SQL запросы, то такие права давать не требуется, они у него уже есть).

Это можно сделать с помощью встроенной утилиты командной строки icacls.

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

Для сетевой службы

Вместо UserName укажите имя пользователя, который посылает запрос.

Шаг 3 – Включаем распределенные запросы на SQL Server

По умолчанию возможность использования распределённых запросов, в частности функций OPENDATASOURCE и OPENROWSET, в Microsoft SQL Server запрещена, поэтому данную возможность нужно сначала включить.

Она включается с помощью системной хранимой процедуры sp_configure, которая отвечает за системные параметры сервера. Нам необходимо параметру Ad Hoc Distributed Queries присвоить значение 1, для этого выполняем следующую SQL инструкцию.

Шаг 4 – Выполняем SQL запрос, обращение к файлу Excel

Ниже я приведу несколько вариантов обращения к файлу Excel (TestExcel.xls).

OPENROWSET

OPENDATASOURCE

Linked Server

Импорт данных из Excel 2007 и выше (файл xlsx) в Microsoft SQL Server x86

Шаг 1 – Проверяем наличие провайдера Microsoft.ACE.OLEDB.12.0 на SQL Server

Точно так же, как и в предыдущем примере, сначала проверяем, установлен ли у нас необходимый нам провайдер, в данном случае нам нужен Microsoft.ACE.OLEDB.12.0.

Шаг 2 – Установка провайдера Microsoft.ACE.OLEDB.12.0 (32-bit)

Если провайдера нет, то его необходимо установить.

Вот ссылка на скачивание провайдера

Выберите и скачайте файл, соответствующий архитектуре x86 (т.е. в названии без x64).

Шаг 3 – Предоставление прав пользователю на временный каталог

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

Используем все ту же утилиту командной строки icacls.

Для локальной службы

Вместо UserName укажите имя пользователя, который посылает запрос.

Шаг 4 – Включаем распределенные запросы на SQL Server

Включаем возможность использования OPENDATASOURCE и OPENROWSET на Microsoft SQL Server, повторюсь, что по умолчанию данная возможность отключена.

Шаг 5 – Настройка провайдера Microsoft.ACE.OLEDB.12.0

В данном случае дополнительно потребуется настроить провайдер Microsoft.ACE.OLEDB.12.0. Для этого включим следующие параметры провайдера (для отключения укажите 0 вместо 1).

Если не включать данные параметры, то, скорей всего, появится ошибка примерно следующего содержания

«Сообщение 7399, уровень 16, состояние 1, строка 25
Поставщик OLE DB «Microsoft.ACE.OLEDB.12.0» для связанного сервера «(null)» сообщил об ошибке. Поставщик не предоставил данных об ошибке.
Сообщение 7330, уровень 16, состояние 2, строка 25
Не удалось получить строку от поставщика OLE DB «Microsoft.ACE.OLEDB.12.0» для связанного сервера «(null)».»

Шаг 6 – Выполняем SQL запрос, обращение к файлу Excel

Примеры обращения к файлу Excel (TestExcel.xlsx).

OPENROWSET

OPENDATASOURCE

Linked Server

Импорт данных из Excel (любые файлы) в Microsoft SQL Server x64

Шаг 1 – Проверяем наличие провайдера Microsoft.ACE.OLEDB.12.0 на SQL Server

В данном случае мы также используем провайдер Microsoft.ACE.OLEDB.12.0, сначала проверяем, зарегистрирован ли он на сервере.

Шаг 2 – Установка провайдера Microsoft.ACE.OLEDB.12.0 (64-bit)

В случае, если провайдер не установлен, его необходимо скачать и установить.

Скачиваем файл x64.

Шаг 3 – Включаем распределенные запросы на SQL Server

Необходимость включения возможности использования распределенных запросов (OPENDATASOURCE и OPENROWSET) на Microsoft SQL Server x64 также есть, поэтому сначала включаем ее, выполнив точно такую же инструкцию.

Шаг 4 – Настройка провайдера Microsoft.ACE.OLEDB.12.0

В этом случае, скорей всего, настройка провайдера не потребуется, поэтому сначала сразу пробуем выполнить SQL запросы (обратиться к данным в Excel), и если возникает ошибка (все с тем же сообщением 7399 и 7330), то пробуем включить параметры AllowInProcess и DynamicParameters (для отключения укажите 0 вместо 1).

Шаг 5 – Выполняем SQL запрос, обращение к файлу Excel

Здесь используются точно такие же параметры в SQL запросах, что и в предыдущем примере. Для удобства продублирую их еще раз.

Примеры обращения к файлу Excel (TestExcel.xlsx).

OPENROWSET

OPENDATASOURCE

Linked Server

Подведение итогов

Ну и в заключение я сгруппирую действия, которые необходимо выполнять в зависимости от выпуска SQL Server (x68 или x64) и версии файла Excel (xls или xlsx), в одну таблицу, для Вашего удобства.

Как сделать запрос sql в excel?

299562 просмотра

13 ответа

1498 Репутация автора

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

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

-1 плюса

1950 Репутация автора

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

Читать еще:  Как сделать дисперсию в excel?

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

8 плюса

19875 Репутация автора

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

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

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

-1 плюса

740 Репутация автора

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

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

Автор: jbo5112 Размещён: 20.09.2013 12:52

-1 плюса

1353 Репутация автора

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

2 плюса

1190 Репутация автора

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

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

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

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

Автор: unique2 Размещён: 21.09.2013 06:38

-1 плюса

4248 Репутация автора

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

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

5 плюса

Репутация автора

ТЛ; др; Excel делает все это изначально — используйте фильтры и / или таблицы

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

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

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

поиграть .. некоторые вещи на заметку:

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

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

Опция c #:

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

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

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

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

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

3 плюса

2347 Репутация автора

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

49 плюса

3649 Репутация автора

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

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

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

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

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

Пример чтения рабочей таблицы: SELECT * FROM [Sheet1 $]

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

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

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

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

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

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

Типы данных (стоит попробовать) для CREATE 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: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 обрабатывает все данные как текст. Поставщик = Microsoft.ACE.OLEDB.12.0; Источник данных = Excel2007file.xlsx; Расширенные свойства = «Excel 12.0 Xml; HDR = YES; IMEX = 1»;

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

Автор: rskar Размещён: 24.09.2013 02:48

-1 плюса

23182 Репутация автора

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

Читать еще:  Как сделать таблицу в excel список клиентов?

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

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

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

Автор: Mike T Размещён: 13.10.2016 04:05

плюса

1279 Репутация автора

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

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

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

Автор: anakic Размещён: 13.06.2017 02:05

1 плюс

11 Репутация автора

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

Автор: dhruba Размещён: 23.01.2019 06:44

плюса

1 Репутация автора

какой язык SQL именно это работает на VBA? Моя проблема в том, что у меня есть код запроса, работающий на MS Access, я вставил его на VBA, но не работает. Есть ли конвертер?

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

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

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

  • 9
  • 12
  • 13 окт 2019 2019-10-13 06:00:25
  • Vap0r

12 ответов

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

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

  • 13 окт 2019 2019-10-13 06:00:28
  • Akhilesh Singh

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

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

Или запустите тот же запрос, используя ogr2ogr, чтобы сделать простой CSV файл:

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

  • 13 окт 2019 2019-10-13 06:00:28
  • Mike T

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

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

  • 13 окт 2019 2019-10-13 06:00:28
  • jbo5112

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

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

  • 13 окт 2019 2019-10-13 06:00:27
  • Juan Antonio Gomez Moriano

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

  • 13 окт 2019 2019-10-13 06:00:27
  • Holmes IV

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

  • 13 окт 2019 2019-10-13 06:00:27
  • dhruba

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

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

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

  • 13 окт 2019 2019-10-13 06:00:27
  • anakic

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

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

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

  • D2: =if(A2 = «», 1, 0) , это столбец фильтра, соответствующий вашему условию
  • 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.

  • 13 окт 2019 2019-10-13 06:00:27
  • unique2

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

  • 13 окт 2019 2019-10-13 06:00:26
  • Piotr Kołaczkowski

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

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

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

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

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

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

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

Параметр С#:

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

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

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

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

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

  • 13 окт 2019 2019-10-13 06:00:26
  • [email protected]

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

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

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

  • 13 окт 2019 2019-10-13 06:00:26
  • Charles Williams

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

Читать еще:  Как сделать разрыв на графике в excel?

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

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

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

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

12 Ответов

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

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

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

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

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

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

Например, чтение диапазона: SELECT * от MyRange

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

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

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

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

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

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

Подключение к «old tech» 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) рабочих тетрадей.

Подключение к «latest» 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;Data Source=Excel2007file.xlsx;Extended Properties=»Excel 12.0 Xml; HDR=YES;IMEX=1″;

Подключение к Excel через ADODB через VBA подробная информация на http://support.microsoft.com/kb/257819

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

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

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

tl;dr; Excel делает все это изначально-использует фильтры и таблицы or

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

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

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

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

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

DO это с фильтрами, если вы не собираетесь делать это много или вы хотите автоматизировать импорт данных где-то или что-то.. но для полноты картины:

Вариант c# :

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

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

NOTE-используйте таблицы в excel!:

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

Для табличных данных в excel это мое значение по умолчанию.. первое,что я делаю, это нажимаю на данные, а затем выбираю «format as table» из раздела home на ленте. это дает вам фильтрацию и сортировку по умолчанию и позволяет получить доступ к таблице и полям по имени (например table[fieldname]) это также позволяет агрегировать функции по столбцам, например max и average

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

Ссылка на основную публикацию
Adblock
detector