Загрузить excel в 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 в 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!
Как загрузить в 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 в MS SQL
Добрый день.
Знаю что много таких вопросов и большую часть из них я просмотрел не найдя ответа.
Задача такая:
Запускается программа, выбираем файл exel (много данных >2000) и данные из него записываем в таблицу БД
как это сделать ?
Импорт данных из Excel в БД SQL
Доброе время суток! Помогите народ, диплом горит :help::help::help: Как реализовать перенос данных.
Импорт данных из Excel в MS SQL через приложение WinForms
Всем привет. В приложение С# WinForms нужно добавить возможность импорта данных из Excel в MS SQL.
Импорт from EXCEL to SQL
Добрый день уважаемые форумчане) Столкнулся с проблемой импорта в sql serve 2012. Суть ее такова.
Импорт из Excel в MS SQL
мой вопрос уже отображается в теме, но сформулирую его еще раз: у меня есть файл ехеля и мне нужно.
Смотрел sqlbulkcopy, и другие варианты везде обрывочная информация ни одного живого примера.
Решил изобрести велосипед:
1) считываю все их EXEL
2) данные запихиваю в dataset
3) bp dataset пытаюсь инсертить в БД но тут вопрос
//q,w,e столбцы в бд nvarchar(50) null
Q1,W1,E1 — столбцы в datagrid от куда беру значения
ошибок не показывает, в базу не инсертит в чем может быть проблемма ??
Добавлено через 1 минуту
на всякий случай как считываю из EXEL
Случайно формой этой не делитесь ? хотелось бы глянуть как импорт должен работать по нормальному
Добавлено через 6 минут
добавил для каждой строки SetAdded
теперь хоть какойто результат есть, а именно :
1) Prov > «Сообщение об ошибке не предусмотренно, код результата DB_E_ERRORSOCCURRED(0*80040e21)»
2)Prov > Произошли ошибки во время выполнения многошаговой операции OLE db . Работа не выполнена
3)Prov > Произошли ошибки во время выполнения многошаговой операции OLE db . Работа не выполнена
4) Prov > Поставщик .SqlClient не зарегистрирован на данном компьютере ( хотя другое приложение на этом компьютере только его и использует все работает)
с таким кто нибудь сталкивался ?
 | Old.rar (19.3 Кб, 69 просмотров) |
Добавлено через 2 минуты
так и не разобрался с ошибками которые писал раньше.
По советам решил использовать sqlbulkcopy
(нашел хороший пример по которому делал https://code.msdn.microsoft.com/offi. sheet-2b7ca7cf)
вот мой код:
только есть некоторые моменты которые не понимаю
1) в exel файле 1 стока это заголовки которые он не должен читать , должен читать сразу со 2 строки но на деле 2 строку пропускает и читает 3 строку
2) Бывает выскакивает ошибка «неожиданный формат файла»/»не верный формат » в чем может быть проблема?
(бывает скопирую файл ничего в нем не меняя и начинается подобные проблемы приходиться открывать менять формат ячеек или строки удалять добавлять )
Добавлено через 16 минут
«на проекте -> добавить существующий элемент ->и все три файла выбрал?»
сделал так добавились три файла,
стою на файле SetServer.cs нажимаю SHIFT+F7 чтоб открылась визуальная часть , а она пустая просто голая форма
Добавлено через 2 часа 43 минуты
«внешняя таблица не имеет предполагаемый формат » — вот точное название ошибки
Загрузка...
detector