SQL запросы в Microsoft Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

SQL запросы в Microsoft Excel

Как запустить 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?

11 ответов

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

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

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

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

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

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

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

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

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

по умолчанию предполагается, что первая строка источника данных 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): Prov >. Используйте исходный тип базы данных 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): Prov

Импорт данных из 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.

Читайте также:  Составление заголовка в Microsoft Excel

Шаг 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), в одну таблицу, для Вашего удобства.

Действие / НастройкаИмпорт Excel 2003 (файл xls) в SQL Server x86Импорт Excel 2007 (файл xlsx) в SQL Server x86Импорт Excel (любые файлы) в SQL Server x64
Установка Excel 2003ДаНетНет
Установка провайдера Microsoft.ACE.OLEDB.12.0НетДа (x86)Да (x64)
Предоставление прав на временный каталог служб (если SQL сервер работает от имени служб)ДаДаНет
Настройка провайдера Microsoft.ACE.OLEDB.12.0НетДаНет (по необходимости)
Параметры подключения в SQL запросахMicrosoft.Jet. OLEDB.4.0 и Excel 8.0Microsoft.ACE. OLEDB.12.0 и Excel 12.0Microsoft.ACE. OLEDB.12.0 и Excel 12.0
Включение распределённых запросов на SQL ServerДаДаДа

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

На сегодня это все, удачи Вам в освоении языка T-SQL!

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

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

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

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

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

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

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

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

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

А теперь про полученный файл. Вы вот прямо так будете гнать его в базу? Уверены? А вы уверены что вам туда ничего лишнего не напихали? Все 1000 строк глазами проглядывать будете?

Я сделал просто — прямо в редакторе XLS-файла (в моём случае — LibreOffice Calc) применил регулярные выражения для удаления всего кроме числовых значений.

В результате остались только цифры, разделитель «запятая» и знак «минус».

Дальше я сделал замену «запятая» на «точка» и при сохранении в CSV получал данные вот такого вида:

Теперь данные безопасны и SQL-friendly.

Я этому так много времени уделяю не из природного занудства, а потому что если данные не будут загружены из-за уже имеющихся или уничтожены из-за инъекции, то поставленная задача не будет выполнена.

Как говорил известный эксперт: «Лучше день потерять, зато потом за пять минут долететь!»

Итак, подготовительный этап завершён — полетели. В смысле, приступаем к выполнению задания различными способами.

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

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

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

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

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

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

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

Тут вариантов два.

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

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

3. Клиент SQL

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

4. PHP-скрипт

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

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

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

Кстати, в этот скрипт можно вставить веб-форму с загрузкой CSV-файла, сделать обеззараживание данных и пусть автор задания сам всё грузит.

Хотя, конечно же, такой вариант не подходит. Этот скрипт с формочкой потом останется на сайте, про него забудут и будет какая-никакая, а дырка.

Поэтому, решаем задание так же как в п.2, только сохраняем всё в php-файл и вместо

Ну и mysql_connect в начале

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

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

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

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

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

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

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

Читайте также:  Выделение ячеек в Microsoft Excel

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

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

NC’s Blog

Just another weblog for students, IT people and programmers

Импорт данных из Excel в SQL Server

Microsoft Office Exel уже довольно давно стал ключевой программой при переносе разных баз, тк практически любая даже очень старая база работает с Exel и перевести в него данный не состовляет ни каких проблем. Это пошаговое руководство описывает различные способы импорта данных из листов Microsoft Excel в базы данных Microsoft SQL Server.

Требования перед началом

В приведенном ниже списке перечислены рекомендованные оборудование, программное обеспечение, сетевая инфраструктура, а также необходимые пакеты обновления:

  • Экземпляр Microsoft SQL Server 7.0, Microsoft SQL Server 2000, Microsoft SQL Server 2005 или Microsoft SQL Server 2008
  • Microsoft Visual Basic 6.0 для примеров объектов ADO, использующих Visual Basic

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

  • Службы преобразования данных
  • Связанные серверы и распределенные запросы
  • Разработка объектов ADO на Visual Basic

Описание

В примерах, приведенных в данной статье, импорт данных Excel выполняется с помощью следующих функций:

  • Службы преобразования данных SQL Server (DTS)
  • Службы интеграции Microsoft SQL Server 2005 (SSIS)
  • Связанные серверы SQL Server
  • Распределенные запросы SQL Server
  • Поставщик объектов данных ActiveX(ADO) и Microsoft OLE DB для SQL Server
  • Поставщик ADO и Microsoft OLE DB для Jet 4.0

Примеры

Import или Append

В примерах команд SQL, используемых в статье, показаны запросы Create Table для импорта данных Excel в новую таблицу SQL Server с использованием конструкций SELECT…INTO…FROM. При сохранении ссылок на объекты-источники и получатели выражения, приведенные в примерах, могут быть преобразованы в запросы Append с использованием конструкций INSERT INTO…SELECT…FROM.

Использование DTS или SSIS

Для импорта данных Excel в таблицы SQL Server могут быть использованы мастер импорта служб преобразования данных (DTS) SQL Server или мастер импорта и экспорта SQL Server. При работе с мастером и выборе исходных таблиц Excel помните, что имена объектов Excel со знаком доллара ($) являются именами листов (например Лист1$), а имена объектов без знака доллара являются названиями именованных диапазонов Excel.

Использование связанного сервера

Для упрощения запросов книга Excel может быть настроена как связанный сервер в SQL Server. Для получения дополнительных сведений щелкните приведенный ниже номер статьи базы знаний Майкрософт: 306397 (http://support.microsoft.com/kb/306397/RU/ )

Следующая программа импортирует данные из рабочего листа «Customers» связанного сервера Excel «EXCELLINK» в новую таблицу SQL Server с именем XLImport1:

При использовании OPENQUERY источнику может быть передан сквозной запрос:

‘SELECT * FROM [Customers$]’)

Использование распределенных запросов

Если устанавливать существующее подключение к книге Excel как связанный сервер нежелательно, данные могут быть импортированы с использованием функций OPENDATASOURCE или OPENROWSET. В следующих примерах кода также производится импорт данных из рабочего листа Excel «Customers» в новые таблицы SQL Server:

Использование ADO и SQLOLEDB

Синтаксис «распределенных запросов», приведенный в разделе Использование распределенных запросов, может быть использован также в приложении ADO для импорта данных Excel в SQL Server, если для подключения к SQL Server используется Microsoft OLE DB для SQL Server (SQLOLEDB).

Для работы следующего примера программы на Visual Basic 6.0 требуется добавление ссылки на проект в объекты данных ActiveX (ADO). В этом примере показано использование функций OPENDATASOURCE и OPENROWSET для подключения SQLOLEDB.

on Visual Basic

Использование ADO и Поставщика данных Jet

В примере из предыдущего раздела при импорте из Excel в SQL для связи с получателем использовались ADO и поставщик SQLOLEDB. Для подключения к источнику Excel можно воспользоваться поставщиком данных OLE DB для Jet 4.0.

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

  • [ Полный путь к базе данных Microsoft Access ].[ Название таблицы ]
  • [ Название ISAM ; Строка подключения ISAM ].[ Название таблицы ]
  • [ODBC; Строка подключения ODBС ].[ Название таблицы ]

В этом разделе для создания подключения ODBC к базе данных SQL Server используется третий формат. Может использоваться имя источника данных (DSN) ODBC или строка подключения без определения DSN:

Для работы следующего примера программы на Visual Basic 6.0 требуется добавление ссылки на проект в ADO. В примере показан импорт данных Excel в SQL Server через подключение ADO с использованием поставщика данных Jet 4.0.

Для импорта данных Excel в другие базы данных Microsoft Access, базы данных индексно-последовательного метода доступа (ISAM) или базы ODBC также могут использоваться конструкции, поддерживаемые поставщиком данных Jet.

Устранение неполадок

  • Помните, что объекты Excel, имена которых содержат знак доллара ($), являются листами (например, Лист1$), другие объекты являются именованными диапазонами Excel.
  • В некоторых ситуациях, особенно если источник данных Excel представлен именем таблицы, а не запросом SELECT, столбцы таблицы-получателя SQL Server переупорядочиваются по алфавиту.Для получения дополнительных сведений о проблеме в работе поставщика данных Jet щелкните следующий номер статьи базы знаний Майкрософт:
    299484 (http://support.microsoft.com/kb/299484/RU/ )
  • Обнаружив, что в столбцах Excel содержатся числовые и текстовые данные, поставщик данных Jet выбирает «доминирующий» тип данных и возвращает NULL вместо значений другого типа.Для получения дополнительных сведений об обходе этой проблемы щелкните следующий номер статьи базы знаний Майкрософт:
    194124 (http://support.microsoft.com/kb/194124/RU/ )

Полезные ссылки:

Использование Excel со связанными серверами SQL Server и распределенными запросами

При использовании ADOX для получения столбцов таблицы Access столбцы упорядочиваются по алфавиту

При использовании DAO OpenRecordset данным в Excel присваивается значение NULL

SQL и VBA

SQL (Structured Query Language – Язык структурированных запросов) – это общепринятый стандарт организации запросов баз данных (запрос – это команда, которая получает или изменяет информацию, которая содержится в базе данных). Любая система управления данных, будь то Jet или Oracle, поддерживает язык SQL. SQL- это полноправный язык программирования, однако лучше всего он подходит именно для управления базами данных. Вы создаете инструкции SQL для выбора, изменения или удаления определенных наборов записей.

А как же SQL и VBA дополняют друг друга? VBA позволяет передавать ядру базы данных инструкции SQL для их дальнейшей обработки. После обработки инструкций и извлечения необходимого набора записей ядром базы данных вам снова потребуется VBA для отображения и форматирования результатов: SQL при этом не используется.

Все об объектах баз данных

Сам по себе язык VBA не предоставляет никаких средств для доступа к базам данных и манипулирования содержащейся в них информацией. Однако после объединения с библиотекой объектов баз данных VBA тотчас превращается в инструмент для программирования баз данных с очень широкими возможностями.

Основная задача, выполняемая библиотекой объектов базы данных,- представление базы данных и всех ее компонентов (таблицы, запросы, отчеты и т.д.) в объекты, по своему проявлению мало отличающиеся от других объектов, используемых в VBA. Благодаря этому вы получаете возможность манипулировать ими, используя их свойства, методы и события (подробности в главе 12). Поскольку база данных представляется в виде набора объектов, ее библиотека объектов избавляет вас от необходимости беспокоиться о деталях сложной структуры базы данных. Кроме того, вы можете использовать тот же набор объектов для манипулирования базами данных других типов.

Вы можете выбирать среди нескольких библиотек баз данных; чаще других пользуются предложения компании Microsoft. Сюда относятся ADO (ActiveX Data Objects- Объекты данных ActiveX), текущий стандарт, и DAO (Data Access Objects – Объекты доступа к данным), старая библиотека объектов, но все еще широко используемая. Другие поставщики предлагают свои собственные библиотеки объектов баз данных. Например, если вы работаете исключительно с файлами dBase, вам следует подумать об использовании CodeBase (продукт компании Sequiter Software).

В данный момент компания Microsoft настоятельно рекомендует использовать библиотеку объектов ADO. Эта библиотека предлагает простую в использовании объектную модель, которая одинаково хорошо подходит для работы как с локальными, так и удаленными данными.

Она содержит все инструменты, необходимые для одновременной работы с масштабируемыми сетевыми приложениями большого количества пользователей, но также замечательно подходит и для отдельных проектов, используемых на настольных компьютерах. ADO позволяет получать доступ не только к SQL-данным, но и данным других типов, таким как сведения Outlook, которая инструкции SQL не поддерживает.

Библиотека ADO поставляется в составе Office XP и Office 2000, в то время как DAO поставлялась с ранними версиями Office, включая Office 97, а также другими VBA приложениями. Если на вашем компьютере не установлена ни одна из этих библиотек объектов, вы можете загрузить их с Web-узла компании Microsoft, что, помимо всего прочего, гарантирует наличие на вашем компьютере самых последних версий этих библиотек.

После того как вы установили необходимую библиотеку объектов баз данных, вы должны указать ее наличие в VBA-проекте, прежде чем сможете использовать ее объекты. Для того чтобы это сделать, воспользуйтесь командой Tools = References, чтобы открыть диалоговое окно References (см. главу 12), в котором вам следует добавить необходимые сведения.

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

Читайте также:  Заголовок таблицы на каждой странице в Microsoft Excel

Возможность связывания элементов управления с данными представляет собой ключевое отличие Access от большинства других VBA-приложений. Однако, если вы используете приложение, например Word, которое не содержит элементы управления соответствующих типов, вы можете добавить их, отдельно приобретя специальные наборы инструментальных средств ActiveX. В состав пакета Microsoft Office Developer входит большое количество элементов управления для связывания данных, которые можно использовать в любом приложении Office, а также целый ряд других инструментов и утилит, значительно упрощающих создание программ управления базами данных на VBA. Кроме того, свои разработки предлагают многие сторонние поставщики.

Базы данных. Изучаем SQL запросы за 20 минут

Именно поэтому мы подготовили краткий экскурс по основам SQL. Начнем же!

1. Создание таблицы

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

Создадим простую таблицу по имени month. Она состоит из 3 колонок:

  • id – Номер месяца в календарном году (целое число).
  • name – Название месяца (строка, максимум 10 символов).
  • days – Количество дней в этом месяце (целое число).

Вот как будет выглядеть соответствующий SQL запрос:

Также при создании таблиц целесообразно добавить первичный ключ для одной из колонок. Это позволит держать записи уникальными и ускорит запросы на выборку. Пусть в нашем случае уникальным будет название месяца (столбец name)

Строковые типы данных

Тип данныхОписание
CHARСтрока фиксированной длины, состоящая из 1-255 символов
TEXTМожет хранить не более 65 535 символов
Числовые типы данных

Тип данныхОписание
TINYINTМожет хранить числа от -128 до 127
SMALLINTДиапазон от -32 768 до 32 767
MEDIUMINTДиапазон от -8 388 608 до 8 388 607
INTДиапазон от -2 147 483 648 до 2 147 483 647
FLOATЧисло с плавающей точкой
Дата и время

Тип данныхОписание
DATEЗначения даты
DATETIMEЗначения даты и времени с точностью до минты
TIMEЗначения времени

2. Вставка строк

Теперь давайте заполнять нашу таблицу months полезной информацией. Добавление записей в таблицу производится через инструкцию INSERT . Есть два способа записи этой инструкции.

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

Этот способ записи прост, но небезопасен, поскольку нет гарантии, что по мере расширения проекта и редактировании таблицы, столбцы будут располагаться в том же порядке, что и ранее. Безопасный (и в тоже время более громоздкий) способ записи инструкции INSERT требует указания как значений, так и порядка следования столбцов:

Здесь первое значение в списке VALUES соответствует первому указанному имени столбца и т.д.

3. Извлечение данных из таблиц

Инструкция SELECT – наш лучший друг, когда мы хотим получить данные из базы данных. Она используется очень часто, так что отнеситесь к этому разделу очень внимательно.

Самый простое использование инструкции SELECT – запрос, который возвращает все столбцы и строки из таблицы (например, таблицы по имени characters):

Символ звездочка ( * ) означает, что мы хотим получить данные из всех столбцов. Так базы данных SQL обычно состоят из более чем одной таблицы, то требуется обязательно указывать ключевое слово FROM , следом за которым через пробел должно следовать название таблицы.

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

Кроме того, во многих случаях мы хотим, чтобы полученные результаты были отсортированы в определенном порядке. В SQL мы делаем это с помощью ORDER BY . Он может принимать опциональный модификатор – ASC (по-умолчанию) сортирующий по возрастанию или DESC , сортирующий по убыванию:

При использовании ORDER BY убедитесь, что оно будет последним в инструкции SELECT . В противном случае будет выдано сообщение об ошибке.

4. Фильтрация данных

Вы узнали, как выбрать из базы данных с помощью SQL запроса строго определенные столбцы, но что если нам нужно получить еще и определенные строки? На помощь здесь приходит условие WHERE , позволяющее нам фильтровать данные в зависимости от условия.

В этом запросе мы выбираем только те месяцы из таблицы month, в которых больше 30 дней с помощью оператора больше (>).

Операторы в условии WHERE

ОператорПроверка
=Равенство
<>Неравенство
!=Неравенство
Больше
>=Больше или равно
!>Не больше
BETWEENВхождение в диапазон
IS NULLПроверка на пустое значение

5. Расширенная фильтрация данных. Операторы AND и OR

Ранее мы использовали фильтрацию данных с использованием одного критерия. Для более сложной фильтрации данных можно использовать операторы AND и OR и операторов сравнения (=, , =,<>).

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

6. In/Between/Like

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

  • IN – служит для указания диапазона условий, любое из которых может быть выполнено
  • BETWEEN – проверяет, находится ли значение в указанном диапазоне
  • LIKE – ищет по определенным паттернам

Например, если мы хотим выбрать альбомы с поп и соул музыкой, мы можем использовать IN(“value1″,”value2”) .

Если мы хотим получить все альбомы, изданные между 1975 и 1985годами, мы должны записать:

7. Функции

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

  • COUNT() – возвращает количество строк
  • SUM() – возвращает общую сумму числового столбца
  • AVG() – возвращает среднее значение из множества значений
  • MIN() / MAX() – получает минимальное / максимальное значение из столбца

Чтобы получить самый последний год в нашей таблице мы должны записать такой SQL запрос:

8. Подзапросы

В предыдущем пункте мы научились делать простые расчеты с данными. Если мы хотим использовать результат от этих расчетов, нам не обойтись без вложенных запросов. Допустим, мы хотим вывести artist, album и release year для старейшего альбома в таблице.

Мы знаем, как получить эти конкретные столбцы:

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

Все, что нужно сейчас, – это объединить два запроса с помощью WHERE:

9. Объединение таблиц

В более сложных базах данных существует несколько таблиц, связанных друг с другом. Например, ниже представлены две таблицы о видеоиграх (video_games) и разработчиков видеоигр (game_developers).

Таблица video_games Таблица game_developers

В таблице video_games есть колонка разработчик (developer_id), но в ней содержится целое число, а не имя разработчика. Это число представляет собой идентификатор (id) соответствующего разработчика из таблицы разработчиков игр (game_developers), связывая логически два списка, что позволяет нам использовать информацию, хранящуюся в них обоих одновременно.

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

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

10. Алиасы

Если вы посмотрите на предыдущий пример, то вы заметите, что существуют две колонки называемые name. Это сбивает с толку, так что давайте установим псевдоним одного из повторяющихся столбцов, например, name из таблицы game_developers будет называться developer.

Мы также можем сократить запрос задав псевдонимы имен таблиц: video_games назовем games, game_developersdevs:

11. Обновление данных

Часто мы должны изменить данные в некоторых строках. В SQL это делается с помощью инструкции UPDATE . Инструкция UPDATE состоит из:

  • Таблицы, в которой находится значение для замены;
  • Имен столбцов и их новых значений;
  • Выбранные с помощью WHERE строки, которые мы хотим обновить. Если этого не сделать, то изменятся все строки в таблице.

Ниже приведена таблица tv_series с сериалами с их рейтингом. Однако, в таблицу закралась маленькая ошибка: хотя сериал Игра престолов и описывается как комедия, он на самом деле ей не является. Давайте исправим это!

Данные таблицы tv_series

12. Удаление данных

Удаление строки таблицы с помощью SQL – это очень простой процесс. Все, что вам нужно, – это выбрать таблицу и строку, которую нужно удалить. Давайте удалим из предыдущего примера последнюю строку в таблице tv_series. Делается это с помощью инструкции >DELETE

Будьте осторожными при написании инструкции DELETE и убедитесь, что условие WHERE присутствует, иначе все строки таблицы будут удалены!

13. Удаление таблицы

Если мы хотим, чтобы удалить все строки, но оставить саму таблицу, то воспользуйтесь командой TRUNCATE :

В случае, когда мы на самом деле хотим, чтобы удалить и данные, и саму таблицу, то нам пригодится команда DROP :

Будьте очень осторожны с этими командами. Их нельзя отменить!/p>

На этом мы завершаем наш учебник по SQL! Мы многое о чем не рассказали, но то, что вы уже знаете, должно быть достаточно, чтобы дать вам несколько практических навыков в вашей веб-карьере.

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