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

Небольшой экскурс в MS SQL

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

Небольшое предисловие. Имеется база данных MS SQL, в которой содержится определённая таблица, её нужно загрузить в Excel.

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

Я предпочитаю в таких случаях использовать Microsoft Management Studio, для поиска основных данных. Найти ярлык можно в меню Пуск.

• В консоли MS SQL есть несколько полей:
• Server Type — можно выбрать тип подключения (в этом случаем остаётся Database Engine);

• Analysis Services — сервисы аналитики;
• Reporting Services  — сервисы отчётов;
• Integration Services — сервисы интеграции (встраивания).

Далее следует имя сервера (его можно задать на этапе установки).

Тип аутентификации — WIndows Authentication или SQL Server Authentication, эти пункты позволяют выбрать тип проверки пользователя. Windows — можно войти под логином и паролем для операционной системы, SQL Server — под специальной учётной записью sa и заданным для неё паролем.

Оставим первый вариант — он проще (но запомните — только для тестов!). Хорошим тоном считается смешанная аутентификация и смена пароля для учётки sa на случай непредвиденных ситуаций.

Видно, что имя сервера — BLACKPRINCE, логин axiro. Пароль не скажу:) Осталось нажать кнопку «Connect» и зайти  в базу данных.

Поди цифрой 1 — список баз данных, под 2 — тестовая база с именем Test, под 3 — запущенный SQL Server Agent — он должен быть запущен иначе база данных может быть не видна для других программ, если на нем стоит крест — щёлкнуть правой кнопкой мышки и выбрать «Start».

Исходные данные есть, теперь необходимо создать таблицу с данными. Это можно сделать через графический интерфейс, но лучше воспользоваться запросом — «New Query».

Откроется окно запроса.

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

Создаём базу данных

Если необходимо создать новую базу данных, например, магазины (SHOPS), синтаксис будет следующий:

CREATE DATABASE SHOPS

Обязательно после написания запроса нажать «Execute» или F5! В противном случае запрос не будет выполнен.

Под цифрой 1 — «Execute», под 2 — текст запроса, под 3 — результат выполнения. Если в процессе выполнения будут выявлены ошибки, они незамедлительно будут показаны. Пока всё идёт по плану.

Создаём таблицу

Если необходимо выполнить запрос к определённой базе данных — щёлкаем правой кнопкой на неё и выбираем «New query».

В окне запроса введём текст запроса. Цель — создать таблицу с четырьмя столбцами: номер магазина, название магазина, выручка магазина, менеджер. Лучше все столбцы называть по английски (хотя бы на первоначальном этапе).

CREATE TABLE dbo.Shops
(ShopID int PRIMARY KEY NOT NULL,
ShopName varchar(25) NOT NULL,
Revenue money NULL,
Manager text NULL)
GO

Нажать «Execute». Таблица создалась. Проверить можно раскрыв дерево базы данных и раскрыть группу «Tables». 

Цель достигнута. В запросе также были упомянуты типы данных (varchar(25), money, text, PRIMARY KEY).

Соответственно — varchar это длина текста, в скобках указано, что длина названия магазина может быть до 25 символов включительно, money — тип данных, представляющий денежные (валютные) значения, text — обычный текст, PRIMARY KEY — ограничение, позволяющее однозначно идентифицировать каждую запись в таблице SQL. Также есть атрибут NULL и NOT NULL — позволяет значениям принимать нулевое значение или не принимать (может в поле стоять ноль или не может).

Добавим в таблицу одну строчку (пока). Открываем окно нового запроса базе данных и добавляем запрос.

INSERT INTO dbo.Shops VALUES ( ‘1’, ‘Ручеёк’, ‘120000’, ‘Петров В.И.’)

Добавилась одна строка.

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

Как было написано выше, нужно указать имя сервера SQL — BLACKPRINCE, и выбрать проверку подлинности — Windows. Нажать «Далее».

В следующем окне выберем базу данных SHOPS и отметим галкой таблицу Shops. Если нужно выбрать несколько таблиц — отметить галкой соответствующую настройку. Нажать «Далее».

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

Теперь можно выбрать расположение загруженной таблицы и указать необходимые настройки. Я пока указывать их не буду, просто нажму «ОК». Таблица будет размещена в ячейку A1.

Цель достигнута! Таблица из базы данных перекочевала в Excel и теперь над ней можно совершать привычные действия. 

В следующих уроках мы поговорим о более сложных манипуляциях с запросами к базам данных.

Вас может заинтересовать — Анализ план факт в Excel.

Источник: http://pcandlife.ru/import-dannih-sql-v-excel/

Excel — Подключение и получение данных с SQL сервера | Info-Comp.ru — IT-блог для начинающих

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

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

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

Задача для получения данных в Excel

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

Примечание! Все действия мы будем делать, используя Excel 2010. SQL сервер у нас будет MS Sql 2008.

И для начала разберем исходные данные, допустим, есть база test, а в ней таблица test_table, данные которой нам нужно получить, для примера будут следующими:

Эти данные располагаются в таблице test_table базы test, их я получил с помощью простого SQL запроса select, который я выполнил в окне запросов Management Studio.

И если Вы программист SQL сервера, то Вы можете выгрузить эти данные в Excel путем простого копирования (данные не большие), или используя средство импорта и экспорта MS Sql 2008.

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

Настройка Excel для получения данных с SQL сервера

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

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

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

Где,

• Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
• Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.

Далее необходимо выбрать базу, к которой подключаться, в нашем примере это база test.

Также это подключение можно настроить сразу на работу с определенной таблицей или представлением, список таблиц и представлений у Вас будет отображен, давайте мы сделаем именно так и настроем подключение сразу на нашу таблицу test_table.

Если Вы не хотите этого, а хотите чтобы Вы подключались к базе и потом выбирали нужную таблицу, то не ставьте галочку напротив пункта «Подключаться к определенной таблице», а как я уже сказал, мы поставим эту галочку и жмем «Далее».

В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:

После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:

В итоге у меня загрузятся из базы вот такие данные:

Т.е. в точности как в базе. Теперь когда, например, изменились данные в этой таблице, и Вы хотите выгрузить их повторно Вам не нужно повторять все заново, достаточно в excel перейти на вкладку «Данные» нажать кнопку «Существующие подключения» и выбрать соответствующее, т.е. то которое Вы только что создали.

Вот собственно и все, как мне кажется все достаточно просто.

Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!

Источник: https://info-comp.ru/obucheniest/375-excel-get-data-from-sql-server.html

Подключение Excel к SQL Server

Для обработки данных в Excel требуется сначала получить сами данные. Часто такая информация находится во внешних источниках, а именно базах данных. В этой статье описывается подключение электронных таблиц к БД MS SQL Server.

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

Теперь на экране появилось окно мастера подключения к данным. Изначально требуется указать имя сервера и учетные сведения для подключения (если Вы их не знаете, то обратитесь к администратору БД).

После заполнения формы нажмите кнопку «Далее», чтобы перейти к выбору базы данных.

Здесь же можно выбрать таблицы, из которых будут грузиться данные, или же снять галочку с пункта «Подключение к определенной таблице».

Нажмите кнопку «Далее», чтобы задать имя файла подключения и описание, чтобы в дальнейшем было проще найти данный файл.

Нажав кнопку «Готово», Вам будет предложено произвести импорт данных с сервера (если в мастере подключений Вами не была выбрана таблица, то сначала ее придется выбрать).

В текущем окне можно выбрать место и способ представления импортируемых данных: таблица, сводная таблица, сводная диаграмма или пропустить импорт, выбрав пункт «Только создать подключение».

SQL-запрос

По умолчанию, при импорте из SQL Server, Excel выгружает всю таблицу. Можно изменить выборку, указав SQL-запрос.

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

В свойствах подключения на вкладке «Определение» в поле «Тип команды» поменяйте значение на «SQL», а в поле «Текст команды» введите SQL-код и нажмите «ОК».

Теперь, чтобы получить данные с помощью нового подключения, достаточно вызвать его из существующих подключений на вкладке «Данные» в области «Получение внешних данных». Дальнейшая процедура импорта аналогична рассмотренному выше.

Источник: http://office-menu.ru/uroki-excel/14-professionalnoe-ispolzovanie-excel/86-podklyuchenie-excel-k-sql-server

SQL запросы: возможности SQL для создания запросов в Excel и напрямую к таблицам Excel

Порой таблицы Excel постепенно разрастаются настолько, что с ними становится неудобно работать. Поиск дубликатов, группировка, сложная сортировка, объединение нескольких таблиц в одну, т.д. — превращаются в действительно трудоёмкие задачи. Теоретически эти задачи можно легко решить с помощью языка запросов SQL… если бы только можно было составлять запросы напрямую к данным Excel.

Надстройка XLTools «SQL запросы» расширит Excel возможностями языка структурированных запросов:

• Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам
• Автогенерация запросов SELECT и JOIN
• Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
• Создание запросов в интуитивном редакторе с подстветкой синтаксиса
• Обращение к любым таблицам Excel из дерева данных

Добавить «SQL запросы» в Excel 2019, 2016, 2013, 2010

Подходит для: Microsoft Excel 2019 – 2010, desktop Office 365 (32-бит и 64-бит).

Скачать надстройку XLTools

Как работать с надстройкой:

Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами

По умолчанию Excel воспринимает данные как простые диапазоны. Но SQL применим только к реляционным базам данных. Поэтому, прежде чем создать запрос, преобразуйте диапазоны Excel в таблицу (именованный диапазон с применением стиля таблицы):

1. Выделите диапазон данных > На вкладке «Главная» нажмите «Форматировать как таблицу» > Примените стиль таблицы.
2. Выберите эту таблицу > Откройте вкладку «Конструктор» > Напечатайте имя таблицы.
   Напр., «КодТовара».
3. Повторите эти шаги для каждого диапазона, который планируете использовать в запросах.
   «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.
4. Готово, теперь эти таблицы будут служить реляционной базой данных и готовы к SQL запросам.

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

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

1. Нажмите кнопку «Выполнить SQL» на вкладке XLTools > Откроется окно редактора.
2. В левой части окна находится дерево данных со всеми доступными таблицами Excel.
   Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).
3. Выберите целые талицы или конкретные поля.
   По мере выбора полей, в правой части редактора автоматически генерируется запрос SELECT.

   Обратите внимание: редактор запросов SQL автоматически подсвечивает систаксис.

4. Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
5. Нажмите кнопку «Выполнить» > Готово!

Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel

XLTools использует стандарт SQLite. Пользователи, владеющие языком SQLite, могут создавать самые разнообразные запросы:

• LEFT JOIN – объединить две и более таблиц по общему ключевому столбцу
• ORDER BY – сортировка данных в выдаче запроса
• DISTINCT – удаление дубликатов из результата запроса
• GROUP BY – группировка данных в выдаче запроса
• SUM, COUNT, MIN, MAX, AVG и другие операторы

Совет: вместо набора названий таблиц вручную, просто перетягивайте названия из дерева данных в область редактора SQL запросов.

Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

Вы можете объединить несколько таблиц Excel в одну, если у них есть общее ключевое поле. Предположим, вам нужно объединить несколько таблиц по общему столбцу «КодТовара»:

1. Нажмите «Выполнить SQL» на вкладке XLTools > Выберите поля, которые нужно включить в объединённую таблицу.
   По мере выбора полей, автоматически генерируется запрос SELECT и LEFT JOIN.
2. Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
3. Нажмите «Выполнить» > Готово! Объединённая таблица появится в считанные секунды.

Появились вопросы или предложения? Оставьте комментарий ниже.

Источник: https://xltools.net/ru/sql-queries-in-excel/

Excel, SQL и легендарный барометр — решаем простую задачу разными способами

На прошлой неделе в каком-то обсуждении всплыл старый хабротекст «Стратегия для технического интервью». Точнее, приведённая в нём задача №4 Дано: .xls (Excel) файл с одним листом в 4 числовых колонки и 1000 строк. Требуется: Загрузить его в SQL базу данных, таблица с соответствующими колонками имеется. Ну и, сперва, оценить время на решение.

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

Update: В коментариях рассказывают методы заполнения столбцов без «протягивания»: раз, два

0. Прежде чем приступить к работе

В условиях задачи есть два очень важных пункта:

• нам дают готовый файл с данными
• таблица в базе данных уже создана

Оба эти пункта влияют на успешность решения поставленной задачи, состоящей в перемещении данных их XLS в SQL. Начну со второго. Находящиеся в таблице данные могут не дать записать те данные что есть у Вас.

Ну, например, если какой-то столбец это unique id, а в имеющейся таблице такой id уже есть. Тут всё просто. Узнаёте что делать с данными и либо первой операцией очищаете таблицу, либо вместо INSERT делаете REPLACE. А теперь про полученный файл.

Вы вот прямо так будете гнать его в базу? Уверены? А вы уверены что вам туда ничего лишнего не напихали? Все 1000 строк глазами проглядывать будете? Я сделал просто — прямо в редакторе XLS-файла (в моём случае — LibreOffice Calc) применил регулярные выражения для удаления всего кроме числовых значений. В результате остались только цифры, разделитель «запятая» и знак «минус». Дальше я сделал замену «запятая» на «точка» и при сохранении в CSV получал данные вот такого вида:11,4667.25,6874573,21336 12,466726,-6874574,21337 Теперь данные безопасны и SQL-friendly. Я этому так много времени уделяю не из природного занудства, а потому что если данные не будут загружены из-за уже имеющихся или уничтожены из-за инъекции, то поставленная задача не будет выполнена. Как говорил известный эксперт: «Лучше день потерять, зато потом за пять минут долететь!» Итак, подготовительный этап завершён — полетели. В смысле, приступаем к выполнению задания различными способами.

Update 2: в х навели на идею. Данные могут быть безопасны, но состоять из бессмысленного набора цифр, «-» и «,». В этом случае импорт сработает неполностью. Как поступаем:

— сперва делать прогон на тестовой таблице — сразу в рабочую, но с роллбэком ?

1. Загружаю CSV в phpMyAdmin

Если есть phpMyAdmin (или аналог для используемого SQL), то:

• обеззараживаем данные (см. п.0)
• первой строкой в файле прописываем имена полей в SQL
• сохраняем CSV
• загружаем

2. SQL в веб-форму

Если нет веб-морды принимающей CSV, но есть принимающая просто SQL запросы то, казалось бы, это указанный в исходном тексте вариант:Добавлю колонку в excel файле, куда во всех ячейках вставлю (растяну) «insert into» и дополнительные колонки с запятыми, получу sql скрипт. Сразу плюс, даже в оценке не нуждаемся. А вот и нет.

Ну правда же, протягивать колонку с INSERT INTO ещё можно, но протягивать запятые. На 1000 строк. И так три раза. Нафиг-нафиг. Тут вариантов два. Во-первых, можно:

• обеззараживаем данные (см. п.0)
• сохранить данные в CSV, используя разделитель «запятая»
• открыть CSV в code-based текстовом редакторе (в моём случае — Notepad++ )
• заменить перевод строк на

);
INSERT INTO … (

• поправить первую и последнюю строчку файла
• пульнуть через форму

Во-вторых, можно не сохранять в CSV, а через буфер вставить содержимое таблицы в Notepad++ (предварительно выполнив п.0). Потом заменяем «табуляция» на «запятая», переносы строка на инсёрты, правим начало и конец файла. Постим через веб-форму.

3. Клиент SQL

Виндового клиента MySQL у меня нет уже давно (ни гуёвого, ни консольного). Да и доступ извне к нему врядли дадут. Поэтому заливаю файл полученный в п. 2 на сервер и делаю там в консоли. mysql … < ...sql

4. PHP-скрипт

Конечно же, идеальным вариантом будет написать скрипт на 10 строк, который будет делать fgetcsv(), формировать INSERT INTO и пулять всё это в базу.

Ну правда же, тот кто даёт Вам это тестовое задание в любой момент скажет «Ой, а мне надо что бы строчки у которых в третьем столбце стоят нечётные целые числа шли в другую таблицу» или «а в пятый столбец нужно было записывать кубический корень из произведения значений данных из всех 4 столбцов».

И у него не будет ответа на вопрос «чувак, а чё ты сам эти расчёты в Excel не сделал?». Всё что он сможет сказать «не я такой — жизнь такая». Кстати, в этот скрипт можно вставить веб-форму с загрузкой CSV-файла, сделать обеззараживание данных и пусть автор задания сам всё грузит. Хотя, конечно же, такой вариант не подходит.

Этот скрипт с формочкой потом останется на сайте, про него забудут и будет какая-никакая, а дырка. Поэтому, решаем задание так же как в п.2, только сохраняем всё в php-файл и вместо );
INSERT INTO … ( делаем: )»,$connect);
mysql_query(«INSERT INTO … ( Ну и mysql_connect в начале

5. У меня же теперь есть Linux!

После обретения Windows Subsystem for Linux жизнь прям заиграла новыми красками. Поэтому:

• обеззараживаем данные (см. п.0)
• сохранить данные в CSV, используя разделитель «запятая»
• и….

cat test.

csv | awk '{ gsub(»
«, «»); print «INSERT INTO … ( … ) VALUES (» $0 «);»; }' | ssh … mysql .. Готово. * * *

А вот сейчас будет кусок из-за которых этот текст не только в хабе «MySQL», но и в хабе «Разработка веб-сайтов».

Кроме приведённых ранее очевидных вариантах решения поставленной задачи есть ещё 3:

• нанять суб-подрядчика
• поставить задачу подчинённому
• свалить эту хрень на другой отдел

Не надо, из-за желания доказать себе и окружающим, что ты ещё торт, делать эту фигню и отвлекаться от решения других задач. Есть случаи, когда человек не то что бы «может», а вот просто «обязан» переделегировать задачу.

Пора уже научиться расставлять приоритеты.

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

Источник: https://habr.com/post/312834/

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

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

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

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

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

2. в составе Microsoft Office (и ОС) есть два интересующих поставщика: старый » Microsoft.Реактивный.OLEDB», и последний «Microsoft.ТУЗ.Для oledb». Ищите их при настройке соединения (например, с помощью мастера подключения к данным).

3. после подключения к книге Excel рабочий лист или диапазон эквивалентны таблице или представлению.

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

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

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

5. пример чтения рабочего листа: выберите * из [Sheet1$]

6. пример, чтение диапазона: выберите * из Миранж!—3—>

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

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

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

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

, или ваша первая строка данных «исчезает» для использования в качестве имен полей. Это делается путем добавления необязательного параметра HDR= в расширенные свойства строки подключения. Значение по умолчанию, которое не нужно указывать, равно HDR=Yes.

Если у вас нет заголовков столбцов, вам нужно указать HDR=No; поставщик называет ваши поля F1, F2 и т. д.

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

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

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

Однако при запросе исходного диапазона результирующий набор записей не включает вновь добавленные записи вне диапазона.

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

подключение к» 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).

обработка данных как текста: настройка IMEX обрабатывает все данные как текст. Поставщик=Microsoft.ТУЗ.Oledb для.12.0; Источник Данных=Excel2007file.XLSX-файл;расширенные свойства=»значение Excel 12.0 XML;и ДРЧ=да;компания IMEX=1″;

(подробности на http://www.connectionstrings.com/excel)

больше информации на http://msdn.microsoft.com/en-US/library/ms141683 (v=sql.90).aspx, и at http://support.microsoft.com/kb/316934

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

Microsoft JET 4 подробнее в http://support.microsoft.com/kb/275561

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

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

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

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. нажмите стрелку на фамилию и выберите-Z на заказ (верхний вариант)

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

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

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

опция 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();

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

List excelSheets = new List();

// 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 это мое значение по умолчанию.. первое, что я делаю, это щелкнуть по данным, а затем выбрать «формат как таблица» из раздела «главная» на ленте. это дает вам фильтрацию и сортировку по умолчанию и позволяет получить доступ к таблице и полям по имени (например, таблица[fieldname]) это также позволяет агрегатные функции на столбцах, например max и в среднем

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

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

• A: фамилия
• Б: «имя»
• C: номер телефона

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

• D2:=if(A2 = «», 1, 0), Это столбец фильтра, соответствующий вашему условию where
• E2:=if(D2 1, «», sumifs(D:D48576, A:A48576, «

Источник: https://askdev.ru/q/kak-zapustit-sql-zapros-v-tablice-excel-46474/

Как запустить 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 = Да. Если у вас нет заголовков столбцов, вам нужно указать HDR = Нет; провайдер называет ваши поля F1, F2 и т. д.

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

Когда вы указываете диапазон (именованный или безымянный), 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»;

(Подробнее на 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

Источник: https://www.it-swarm.net/ru/sql/kak-zapustit-sql-zapros-k-tablice-excel/1041926834/

Как запустить 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 = Да. Если у вас нет заголовков столбцов, вам нужно указать HDR = No; поставщик называет ваши поля F1, F2 и т. д.

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

Когда вы укажете диапазон (названный или неназванный), 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

Источник: https://MoreDez.ru/q/40012106/