Как загрузить в MS SQL Server данные из Excel
Для импорта данных Вам необходима консоль администратора MS SQL Server Managment Studio, которая является компонентой при установке экземпляра MS SQL Server.
Рассмотрим самый простой способ загрузки — через средство экспорта/импорта данных из сторонних форматов. Наиболее распространенная необходимость — это загрузка из файла Excel (.xlsx, .xls).
Для того, чтобы загрузить данные в MS SQL Server из Excel необходимо:
- Открыть консоль MS SQL Server Managment Studio
- Подключиться к серверу:
3. На БД, в которую будет производится загрузка данных, нажать правой кнопкой мыши и выбрать пункт Import Data
4. В появившемся окне SQL Server Import and Export Wizard нажать кнопку Next
5. В следующем окне необходимо в поле Data Source выбрать из списка значение Microsoft Excel, в поле Excel fale path указать путь к файлу Excel, в поле Excel version выбрать версию файла Excel (.xlsx — соответствует MS Excel 2007, .xls — более ранние версии Excel)
Рис.2 — MS SQL Server — Import Data — choose data source
* Если в файле Excel таблица оформлена с шапкой (наименованием столбцов), то необходимо установить галку First row has column names.
Нажать на кнопку Next.
6. В следующем окне необходимо проверить доступа к экземпляру MS SQL Server и БД, в которую будут импортироваться данные
*Есть возможность создать новую БД с помощью кнопки New
Нажать на кнопку Next
7. В следующем окне необходимо выбрать пункт Copy data from one or more tables or views
Нажмите кнопку Next
8. В следующем окне необходимо выбрать листы в файле Excel, которые вы хотите импортировать (загрузить). Например, Лист 1
- Есть возможность предварительного просмотра результата загрузки (кнопка Preview)
Нажать на кнопку Next
9. В следующем окне нажмите кнопку Finish. В случае успешной обработки появится окно:
Нажмите на кнопку Close
Теперь данные из таблицы Excel загружены в БД test MS SQL Server
Импорт данных из 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), в одну таблицу, для Вашего удобства.
| Действие / Настройка | Импорт 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.0 | Microsoft.ACE. OLEDB.12.0 и Excel 12.0 | Microsoft.ACE. OLEDB.12.0 и Excel 12.0 |
| Включение распределённых запросов на SQL Server | Да | Да | Да |
Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую пройти наши онлайн-курсы по T-SQL, на которых Вы «с нуля» научитесь работать с SQL и программировать на T-SQL в Microsoft SQL Server.
На сегодня это все, удачи Вам в освоении языка T-SQL!
Импорт данных SQL в Excel
Импорт данных 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’, ‘Петров В.И.’)
Добавилась одна строка.
Импорт данных SQL в Excel
Со стороны Excel действий будет гораздо меньше. Переходим на вкладку «Данные», нажимаем кнопку «Получение внешних данных», «Из других источников», «С сервера SQL Server».
Как было написано выше, нужно указать имя сервера SQL — BLACKPRINCE, и выбрать проверку подлинности — Windows. Нажать «Далее».
В следующем окне выберем базу данных SHOPS и отметим галкой таблицу Shops. Если нужно выбрать несколько таблиц — отметить галкой соответствующую настройку. Нажать «Далее».
В следующем окне Excel предложит сохранить файл для подключения к базе данных с настройками. Нажать «Готово».
Теперь можно выбрать расположение загруженной таблицы и указать необходимые настройки. Я пока указывать их не буду, просто нажму «ОК». Таблица будет размещена в ячейку A1.
Цель достигнута! Таблица из базы данных перекочевала в Excel и теперь над ней можно совершать привычные действия.
В следующих уроках мы поговорим о более сложных манипуляциях с запросами к базам данных .
Как отконвертировать данные из Excel в БД Oracle
В ремя от времени случается сталкиваться с задачами , для решения которых не всегда бываешь готов. Ну это в том смысле , что и задачка-то иногда бывает для знатоков пустяковой , но конкретно в твоем контексте требует некоторой известной квалификации. Это я к чему ? Да вот намедни потребовалось залить данные из таблички Excel в базу данных Oracle. Для опытных пользователей Oracle такая манипуляция скорее всего — тьфу , выеденного яйца не стоит. Но тем , кто впервые имеет дело с Oracle — проблемка .
Короче , с теми , кто столкнулся с вышеописанной
ситуацией переноса ( к онвертирования) данных из Excel в БД Oracle,
готов поделиться таким нехитрым опытом.
Итак , нужно было залить данные Excel по кадровому составу некой фирмы приблизительно такого содержания .
Да , тут нужны еще вот какие пояснения. База данных Oracle для случая нашего примера была не слишком большой по размеру, не слишком сложной и доступна для ограниченного количества пользователей. Поэтому, в контексте нашего примера, на компьютере была установлена облегченная версия Oracle Database 11g Release 2 Express Edition
Следующим вопросом был выбор оболочки для собственно управления этой самой облегченной версией. К счастью, и тут нашлось решение в виде Oracle SQL Developer
Прим. Процедура скачки упомянутых выше продуктов вполне обычная — заводите учетку на сайте http://www.oracle.com/ и качайте себе на здоровье. Правда, скорость отдачи ну очень медленная. Гораздо проще и быстрее забрать эти вещи с территории известных зон. Там они есть 😉
Однако, ‘вернемся к нашим баранам’ — то бишь загрузки Excel-данных в Oracle. Предположим, что вы вполне успешно проинсталлировали Oracle Express Edition и Oracle SQL Developer ( строго говоря , это тема отдельного разговора). Переходим к непосредственно к работе с последним.
Прим. Имейте в виду — если у вас на компьютере установлен Антивирус Касперского , то есть вероятность при запуске sqldeveloper’ a получить такое сообщение :
Выход из создавшейся коллизии заключается в разрешении в настройках Касперского активности для модулей sqldeveloper ‘а. Или уж совсем лобовое решение — просто на время отключить защиту( но это совсем в крайнем случае)
  |
Тут для начала вам придется создать соединение с правами пользователя SYSTEM (по умолчанию пароль adm). Исходная диспозиция имеет приблизительно сл. вид :
Комментарий к рисунку слева. Командой
CREATE USER CHIEF IDENTIFIED BY boss создаем пользователя CHIEF с паролем boss. Такой пользователь нам понадобиться, чтобы создать, в терминах Oracle схему для работы с данными.
  |
Идем далее. Командой CONNECT CHIEF/boss попробуем установить соединение :
Нет, ошибка — потерпели неудачу. Но ничего страшного . Командой GRANT CREATE SESSION TO CHIEF разрешим пользователю CHIEF устанавливать соединения :
Вот теперь все нормально :
Прим. Удалить пользователя CHIEF можно командой DROP USER CHIEF CASCADE (только сначала хорошенько подумайте, а то шефу ваша идея может не очень-то и понравиться :- ) :
На следующем шаге нужно будет создать соединение .
. ну скажем, с именем MyConn приблизительно сл. образом :
Для тестирования параметров соединения жмем кнопку Test (см. рис. выше) . Если все прошло в штатном режиме, должны увидеть наше вновь созданное соединение :
На рисунке выше выбрав в контекстном меню Import Data переходим к нашей главной задаче — импорту данных из таблички Excel (собственно, все что было изложено ранее — это всего лишь предварительная подготовка).
На следующей форме оцениваем данные в режиме предварительного просмотра:
Указываем имя вновь созданной таблицы (Sotrudniki для случая нашего примера) :
Теперь нужно сделать выбор полей для переноса — берем все :
На следующей форме жмем, само-собой, Finish .
. и , опаньки, получаем такое сообщение :
Аварийное сообщение User does not have privileges to allocate an extent in the specified tablespace указывает на то, что у пользователя нет прав перераспределять выделенное пространство . ‘Лечиться’ такая беда командой GRANT UNLIMITED TABLESPACE to CHIEF :
Увы, придется всю манипуляцию по импорту Excel-таблички проделать заново .
Если все было сделано правильно, то в конце мастера импорта должны увидеть сообщение с ледующего содержания:
А окончательно достигнутый результат должен выглядеть таким образом :
Структуру (описание полей) нашей вновь созданной таблицы можно просмотреть и откорректировать на вкладке Columns :
Теперь можно работать с табличкой Sotrudniki. Например, составить такой нехитрый запрос Select * from Sotrudniki Where Zarplata > 40000 , который будет выводить всех сотрудников, у которых зарплата выше 40 000 неких у.е. :
И нет ничего сложного в том, чтобы добавить нового сотрудника командой INSERT :
Результат будет следующим :
Ну вот, пока кажется все. Всем удачной работы с программными продуктами от Oracle !