Импорт данных в sql из excel: Импорт данных из Excel в SQL Server или базу данных Azure — SQL Server

Импорт данных из множества Excel файлов с помощью SSIS пакетов

Время прочтения: 5 мин.

С помощью
функционала SQL Server Integration Services
(SSIS) можно быстро и эффективно
загрузить данные из большого количества Excel файлов, в то время как
использование стандартного инструментария импорта/экспорта данных является
достаточно трудоемким, т.к. необходимо использовать инструмент импорт/экспорта такое
количество раз, сколько Excel
файлов требуется загрузить.

Допустим, имеется 5 Excel файлов, которые необходимо импортировать в целевую таблицу MS SQL Server. В каждом из этих файлов имеется по 1 листу с информацией следующего вида:

С помощью данного скрипта создадим целевую таблицу для импорта:

CREATE TABLE [dbo].[SSISExcelImport]
(
	[ID клиента] int,
	[ID офиса] int,
	[Наименование файла] nvarchar(20)
)

После создания целевой таблицы, откроем Visual Studio и создадим новый проект SSIS.  Далее необходимо создать и настроить подключения к источникам. На вкладке «Control Flow» правой кнопкой мыши выберем создать «New Connection»

Выберем пункт Excel в списке «Connection manager type» в качестве входного источника данных.

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

После нажатия на кнопку «ОК» настроенное соединение будет доступно в области «Connection Managers»

Далее, в списке «Connection manager type» необходимо выбрать источник назначения «OLE DB»

В «OLE DB connection manager» необходимо указать свойства подключения к экземпляру СУБД MS SQL Server, после чего в области «Connection Managers» будут доступны настроенные соединения к входному и целевому источникам. Настроенные соединения мы будем использовать в задачах потока и управления данных разрабатываемого SSIS пакета.

Добавим элемент «Data Flow Task» для импорта данных из Excel в таблицу MS SQL Server.

Дважды щелкнув по данному элементу, мы переместимся на вкладку «Data Flow», и добавим элемент «Excel Source».

Дважды щелкнув
по источнику Excel в «Excel Source Editor»,
выберем лист с информацией для импорта. Листы с данными должны иметь
аналогичную структуру по всем Excel файлам.

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

Добавим элемент «OLE DB Destination» и соединим с элементом «Excel Source»:

Через двойной щелчок по элементу «OLE DB Destination» будет открыт редактор данного элемента, в котором необходимо указать соединение «OLE DB» и целевую таблицу, которые были созданы ранее.

Добавим
переменные для определения Excel файлов, которые будут обрабатываться в цикле FOR EACH.

  • Directory:
    путь к директории с Excel файлами;
  • Path:
    путь к текущему Excel файлу. В данную переменную будут подставляться пути к файлам
    папки во время перебора.

Выберем «Excel Connection Manager» и отобразим свойства, и далее выберем «Expressions»

После нажатия кнопки «OK», данная переменная будет доступна во вкладке «Property Expressions Editor».

В результате данных действий появится соответствующий символ «fx» в пункте «Excel Connection Manager» вкладки «Connection Managers»

Далее необходимо настроить контейнер «Foreach Loop Container» для импорта данных из Excel файлов.

Для этого необходимо дважды щелкнуть по элементу «Foreach Loop Container», откроется соответствующий редактор, на вкладке «Collection» требуется указать реквизиты с директорией Excel файлов и маску файлов, которые будут импортированы.

Таким образом реализован SSIS пакет для импорта большого количества Excel файлов.

Запустим пакет на исполнение.

Убедимся, что данные загружены в целевую таблицу MS SQL Server, и сверим с одним из Excel файлов, например Excel_Source_3

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

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

Data Import for SQL Server

EMS Data Import for SQL Server — это мощная программа для быстрого импорта данных в таблицы SQL Server из файлов MS Excel, MS Access, DBF, XML, TXT, CSV, MS Word, RTF, ODF, HTML. Программа предусматривает настраиваемые параметры импорта, такие, как исходные форматы для всех полей, конечные форматы данных SQL Server для выбранных полей, параметры фиксации данных, количество пропускаемых записей и многие другие.

Data Import for SQL Server включает в себя мастер настройки для визуальной установки параметров для различных файлов и консольную утилиту для быстрого импорта данных в таблицы SQL Server.

Ключевые особенности

  • Импорт из 10 наиболее популярных форматов: MS Excel, MS Access, DBF, XML, TXT, CSV, MS Word, RTF, ODF, HTML
  • Импорт данных в одну или несколько таблиц или представлений из нескольких баз данных
  • Использование команд пакетной вставки и нативного доступа к серверу для осуществления импорта с максимальной скоростью
  • Несколько режимов импортирования — Вставить все, Вставить новые, Вставка или Обновление и другие
  • Поддержка UNICODE
  • Настраиваемые параметры импорта для каждого исходного файла
  • Сохранение всех параметров импорта активной сессии в файл конфигурации
  • Атоматическое создание структуры таблицы
  • Консольная утилита для быстрого импорта данных с помощью файла конфигурации
  • Мощный модуль настройки внешнего вида системы
  • Поддержка Amazon RDS for SQL Server

При покупке Вы получите также:

  • БЕСПЛАТНАЯ подписка на 1 год Сопровождения!
  • БЕСПЛАТНЫЕ Минорные и Мажорные обновления в период действия Обслуживания!
  • БЕСПЛАТНАЯ неограниченная техническая поддержка в период действия Сопровождения!
  • Разумные расценки на продление Сопровождения – всего от 35% в год!
  • Скидки при покупке двух и более лицензий одного продукта
  • Скидки на покупку сопутствующих продуктов
  • Гарантия возврата денег в течение 30 дней

Скриншоты продукта

Сопоставление данных таблиц

Установка базового формата данных

Выбор формата данных для полей

Определение режима импорта

Настройка общих параметров

Выбор сценариев

Data Import for SQL Server

Начните работу с Data Import for SQL Server

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

Скачать бесплатную пробную версию

Есть вопросы?

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

Сопутствующие продукты

Data Export for SQL Server

Экспортируйте данные из таблиц SQL Server в любой из 20-ти наиболее популярных форматов

Скачать
Подробнее

SQL Manager for SQL server

Упростите и автоматизируйте процесс разработки баз данных SQL Server

Скачать
Подробнее

SQL Management Studio for SQL Server

Комплексное решение для администрирования и разработки баз данных SQL Server

Скачать
Подробнее

Как импортировать данные из файла Excel в базу данных SQL Server

Есть много способов импортировать данные из файла Excel в базу данных SQL Server, используя:

  • Службы интеграции SQL Server (SSIS)
  • функции OPENROWSET и OPENDATASOURCE
  • связанных серверов SQL Server
  • мастер импорта и экспорта SQL Server

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

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

Первой страницей, которая появляется при запуске мастера импорта и экспорта SQL Server, является страница приветствия:

На этой странице показано только краткое введение в мастер импорта и экспорта SQL Server.

Нажмите кнопку Next , чтобы продолжить. Следующей страницей мастера импорта и экспорта SQL Server является страница выбора источника данных:

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

В раскрывающемся списке Источник данных выберите поставщика Microsoft Excel :

Как видите, в списке раскрывающегося списка Источник данных нет поставщика Microsoft Excel. На это есть несколько причин. Первая причина может заключаться в том, что Microsoft Office не установлен. Но нет необходимости устанавливать Microsoft Office (Microsoft Excel), чтобы увидеть поставщика Microsoft Excel в раскрывающемся списке Источник данных.

Чтобы увидеть поставщика Microsoft Excel в списке, установите Microsoft Access Database Engine 2016 Redistributable. Последняя версия Microsoft Access Database Engine может открывать более раннюю версию Excel, поэтому убедитесь, что у вас установлена ​​последняя версия.

Распространяемый компонент Microsoft Access Database Engine 2016 поставляется в двух версиях:

  • AccessDatabaseEngine. exe — 32-разрядная версия.
  • AccessDatabaseEngine_X64.exe — 64-разрядная версия.

Теперь, когда установлен Microsoft Access Database Engine 2016 Redistributable, мы должны увидеть поставщика Microsoft Excel в списке, но, к сожалению, поставщик Microsoft Excel не отображается в списке раскрывающегося списка Источник данных.

Это связано с тем, что вы можете запустить неправильную версию мастера импорта и экспорта SQL Server. Например, установлен AccessDatabaseEngine.exe и запущена 64-разрядная версия мастера импорта и экспорта SQL Server. В этом случае, чтобы увидеть провайдер Microsoft Excel в списке выпадающего списка Источник данных, запустите 32-разрядную версию Мастера импорта и экспорта SQL Server, и провайдер Microsoft Excel появится в списке:

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

В поле Путь к файлу Excel введите местоположение файла Excel или используйте кнопку Обзор , чтобы перейти к местоположению:

В раскрывающемся списке Excel версии выберите версию Excel, которая использует исходную книгу. В нашем случае это версия Microsoft Excel 2016:

Последней опцией на этой странице является флажок В первой строке есть имена столбцов:

По умолчанию эта опция включена. Он обрабатывает первые строки источника данных как имена столбцов:

Но если этот параметр включен и источник данных не содержит имен столбцов, мастер импорта и экспорта SQL Server добавит имена столбцов, начиная с имени 9.0025 F1 для имени первого столбца:

Если этот параметр отключен, а источник данных содержит имена столбцов, мастер импорта и экспорта SQL Server обрабатывает эти столбцы как первую строку данных:

Теперь, когда все настроено на странице Choose a Data Source мастера импорта и экспорта SQL Server, нажмите кнопку Next , чтобы продолжить.

После нажатия кнопки «Далее» может появиться следующее предупреждающее сообщение:

Операция не может быть завершена.

Дополнительная информация:

Поставщик «Microsoft.ACE.OLEDB.16.0» не зарегистрирован на локальном компьютере. (Системные данные)

Как правило, это предупреждающее сообщение появляется, когда мастер импорта и экспорта SQL Server запускается через SQL Server Management Studio (SSMS), которая является 32-разрядным приложением, и запускается 32-разрядная версия мастера импорта и экспорта SQL Server, но вы установили 64-разрядную версию Microsoft Access Database Engine 2016 Redistributable.

Есть два решения для решения этой проблемы:

  • Первый — запустить 64-разрядную версию мастера импорта и экспорта SQL Server через меню «Пуск».
  • Второе решение — установить 32-разрядную версию Microsoft Access Database Engine 2016 Redistributable и продолжить использование мастера импорта и экспорта SQL Server через SSMS, но сначала необходимо удалить 64-разрядную версию Microsoft Access Database Engine 2016 Redistributable, в противном случае при запуске инсталляционного пакета появится следующее предупреждающее сообщение:

Примечание . Распространяемый компонент Microsoft Access Database Engine 2016 можно установить в тихом режиме.

Откройте окно командной строки и выполните следующее:

Для 32-битной версии

C:\Users\<имя_пользователя>\Downloads\AccessDatabaseEngine.exe /quiet

Для 64-битной версии

C:\Users\<имя_пользователя>\Downloads\AccessDatabaseEngine_X64.exe /quiet

Следующая страница мастера импорта и экспорта SQL Server — это страница выбора места назначения:

На этой странице определите место назначения, куда будут помещены данные из источника данных (файл Excel). В нашем случае местом назначения будет база данных SQL Server.

В раскрывающемся списке Destination выберите поставщика, который может подключаться к базе данных SQL Server.

Один из провайдеров, который может подключаться к SQL Server:

  • Поставщик данных . NET Framework для SqlServer
  • Поставщик Microsoft OLE DB для SQL Server
  • Собственный клиент SQL Server 11.0

В этом случае собственный клиент SQL Server 11.0 будет выбран из списка Destination :

От имени сервера 9В поле со списком 0026 выберите экземпляр SQL Server:

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

В раскрывающемся списке База данных выберите базу данных, в которую будут помещены данные из источника данных (файл Excel):

Или создайте новую базу данных в качестве места назначения для данных из источника данных.

Для этого нажмите кнопку New и в диалоговом окне Create Database задайте параметры для новой целевой базы данных SQL Server:

Когда все настроено на странице Choose a Destination, нажмите кнопку Next , чтобы продолжить.

На странице Specify Table Copy or Query определите, как данные из источника данных будут скопированы в место назначения:

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

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

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

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

Для успешного запроса рабочего листа необходимо добавить $ в конец имени листа и скобки вокруг имени листа ( [BusinessEntity$] ), иначе могут появиться следующие предупреждающие сообщения:

Оператор не может быть проанализирован.

Дополнительная информация:

Ядру базы данных Microsoft Access не удалось найти объект «BusinessEntity». Убедитесь, что объект существует и что вы правильно написали его имя и путь. Если «BusinessEntity» не является локальным объектом, проверьте подключение к сети или обратитесь к администратору сервера. (Ядро базы данных Microsoft Access)

Или это:

Оператор не может быть проанализирован.

Дополнительная информация:

Синтаксическая ошибка в предложении FROM. (Ядро базы данных Microsoft Access)

Если выбран переключатель Копировать данные из одной или нескольких таблиц или представлений , при нажатии кнопки Далее будет показана страница Выбор исходных таблиц и представлений:

На этой странице все рабочие листы для файла Excel (ImportData. xlsx) будут перечислены в столбце Source . В списке Источник выберите листы, из которых вы хотите импортировать данные в базу данных SQL Server, установив флажок рядом с именем листов. Выбранные рабочие листы появятся в столбце Destination :

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

Как можно заметить, значок в столбце «Назначение» для поля BusinessEntity$ отличается от полей «Таблица1» и «Таблица2». Это связано с тем, что таблица в базе данных SQL Server, выбранная в качестве места назначения, уже существует, и для полей Table1 и Table2 будут созданы новые таблицы.

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

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

Найдено 1 преобразование неизвестного типа столбца Вам разрешено только сохранять пакет

После установки соответствующих столбцов и типов данных нажмите кнопку Next , появится страница «Сохранить и запустить пакет» мастера импорта и экспорта SQL Server:

Нажмите кнопку Далее , если вы хотите импортировать данные из файла Excel в базу данных SQL Server, но если хотите сохранить пакет служб SSIS для последующего использования, нажмите кнопку Сохранить пакет служб SSIS.

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

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

Если ошибка возникает в процессе импорта данных из файла Excel в базу данных SQL Server, Messages… слово появится в столбце Message для действия, которое не удалось:

При нажатии на Messages…, появится диалоговое окно View Report с подробной информацией об ошибке:

Эта ошибка:

– Проверка (ошибка)

Сообщения

  • Ошибка 0xc0202049: Задача потока данных 1: Ошибка вставки в столбец только для чтения «numb».
    (Мастер импорта и экспорта SQL Server)
  • Ошибка 0xc0202045: поток данных, задача 1: проверка метаданных столбца не удалась.
    (Мастер импорта и экспорта SQL Server)
  • Ошибка 0xc004706b: Задача потока данных 1: «Назначение — test1» не прошла проверку и вернуло статус проверки «VS_ISBROKEN».
    (Мастер импорта и экспорта SQL Server)
  • Ошибка 0xc004700c: Задача потока данных 1: Один или несколько компонентов не прошли проверку.
    (Мастер импорта и экспорта SQL Server)
  • Ошибка 0xc0024107: поток данных, задача 1: при проверке задачи возникли ошибки.
    (Мастер импорта и экспорта SQL Server)

Как правило, он появляется, когда в целевой таблице есть столбец IDENTITY. Чтобы решить эту проблему, вернитесь на страницу «Выбор исходных таблиц и представлений», выберите таблицы, у которых есть свойство удостоверения, и нажмите кнопку 9. 0025 Кнопка «Редактировать сопоставления» . В диалоговом окне Transfer Settings установите флажок Enable identity insert :

Кроме того, еще одна распространенная проблема, которая может возникнуть при импорте данных из источника данных в целевые таблицы SQL Server, — это проблема ограничений FOREIGN KEY. В приведенной ниже ошибке показаны две таблицы назначения test1 и test2. Таблица test2 ссылается на таблицу test1:

— Копирование в [dbo].[test1] (Ошибка)

Сообщения

  • Ошибка 0xc0047022: задача потока данных 1: код ошибки служб SSIS DTS_E_PROCESSINPUTFAILED. Метод ProcessInput для компонента «Destination 1 — test2» (79) завершился с ошибкой с кодом 0xC0209029 при обработке ввода «Destination Input» (92). Идентифицированный компонент вернул ошибку из метода ProcessInput. Ошибка связана с компонентом, но она является фатальной и приведет к остановке выполнения задачи потока данных. Перед этим могут быть опубликованы сообщения об ошибках с дополнительной информацией о сбое.
    (Мастер импорта и экспорта SQL Server)
  • Информация 0x402090df: поток данных, задача 1: начата последняя фиксация для вставки данных в «Destination — test1».
    (Мастер импорта и экспорта SQL Server)
  • Информация 0x402090e0: поток данных, задача 1: окончательная фиксация для вставки данных в «Destination — test1» завершена.
    (Мастер импорта и экспорта SQL Server)

— Копирование в [dbo].[test2] (Ошибка)

Сообщения

  • Ошибка 0xc0202009: задача потока данных 1: код ошибки служб SSIS DTS_E_OLEDBERROR. Произошла ошибка OLE DB. Код ошибки: 0x80004005.
    Доступна запись OLE DB. Источник: «Собственный клиент Microsoft SQL Server 11.0». Hresult: 0x80004005 Описание: «Выполнение инструкции было прекращено».
    Доступна запись OLE DB. Источник: «Собственный клиент Microsoft SQL Server 11.0». Результат: 0x80004005. Описание: «Не удается вставить значение NULL в столбец «No_id» таблицы «ImportData.dbo.test2». столбец не допускает пустых значений. ВСТАВИТЬ не удается».
    (Мастер импорта и экспорта SQL Server)
  • Ошибка 0xc0209029: задача потока данных 1: код ошибки SSIS DTS_E_INDUCEDTRANSFORMFAILUREONERROR. «Назначение 1 — test2.Inputs [Вход назначения]» не удалось, поскольку произошел код ошибки 0xC020907B, а расположение строки ошибки в «Назначение 1 — test2.Inputs [Вход назначения]» указывает на сбой за ошибкой. Произошла ошибка в указанном объекте указанного компонента. Перед этим могут быть опубликованы сообщения об ошибках с дополнительной информацией о сбое.
    (Мастер импорта и экспорта SQL Server)

Чтобы решить эту проблему, отключите ограничение для ссылочной таблицы (test2), выполнив следующий код в SSMS:

ИЗМЕНИТЬ ТАБЛИЦУ test2 NOCHECK CONSTRAINT ALL

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

ИЗМЕНИТЬ ТАБЛИЦУ test2 С ПРОВЕРКОЙ ПРОВЕРИТЬ ОГРАНИЧЕНИЕ ВСЕ

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

– проверка (предупреждение)

Сообщения

  • Предупреждение 0x802092a7: поток данных, задача 1: может произойти усечение из-за вставки данных из столбца потока данных «AddressLine1» длиной 255 в столбец базы данных «AddressLine1» длиной 60.
    (Мастер импорта и экспорта SQL Server)
  • Предупреждение 0x802092a7: Задача потока данных 1: усечение может произойти из-за вставки данных из столбца потока данных «AddressLine2» длиной 255 в столбец базы данных «AddressLine2» длиной 60.
    (Мастер импорта и экспорта SQL Server)
  • Предупреждение 0x802092a7: Задача потока данных 1: усечение может произойти из-за вставки данных из столбца потока данных «Город» длиной 255 в столбец базы данных «Город» длиной 30.
    (Мастер импорта и экспорта SQL Server)
  • Предупреждение 0x802092a7: Задача потока данных 1: усечение может произойти из-за вставки данных из столбца потока данных «AddressLine1» длиной 255 в столбец базы данных «AddressLine1» длиной 60.
    (Мастер импорта и экспорта SQL Server)
  • Предупреждение 0x802092a7: Задача потока данных 1: может произойти усечение из-за вставки данных из столбца потока данных «AddressLine2» длиной 255 в столбец базы данных «AddressLine2» длиной 60.
    (Мастер импорта и экспорта SQL Server)
  • Предупреждение 0x802092a7: Задача потока данных 1: усечение может произойти из-за вставки данных из столбца потока данных «Город» длиной 255 в столбец базы данных «Город» длиной 30.
    (Мастер импорта и экспорта SQL Server)
  • Предупреждение 0x80049304: поток данных, задача 1: предупреждение: не удалось открыть глобальную общую память для связи с библиотекой производительности; счетчики производительности потока данных недоступны. Чтобы решить эту проблему, запустите этот пакет от имени администратора или на системной консоли.
    (Мастер импорта и экспорта SQL Server)

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

Чтобы убедиться, что данные из файла Excel импортированы в базу данных SQL Server, перейдите в SSMS, найдите базу данных, в которую импортируются данные, и перечислите все данные из таблиц:

  • Импорт данных из Excel в SQL Server или базу данных SQL Azure
  • Как импортировать/экспортировать данные в SQL Server с помощью мастера импорта и экспорта SQL Server
  • Как запрашивать данные Excel с помощью связанных серверов SQL Server
  • Импорт и экспорт данных с помощью мастера импорта и экспорта SQL Server
  • Автор
  • Последние сообщения

Марко Живкович

Марко ака «Живко» — старший аналитик программного обеспечения из Ниша, Сербия, специализирующийся на SQL Server и MySQL, а также клиентских технологиях, таких как SSMS, Visual Studio и VSCode. Он имеет большой опыт в области обеспечения качества, эскалации/решения проблем и евангелизации продукта.

Он является плодовитым автором авторитетного контента, связанного с SQL Server, включая ряд «платиновых» статей (1% лучших с точки зрения популярности и участия). Его работы охватывают ряд тем по MySQL и SQL Server, включая удаленные/связанные серверы, импорт/экспорт, LocalDB, SSMS и многое другое.

В свободное время Живко любит баскетбол, настольный футбол (настольный футбол) и рок-музыку.

Подробнее о Марко на LinkedIn

Просмотреть все сообщения Марко Живковича

Последние сообщения Марко Живковича (посмотреть все)

Как импортировать данные в базу данных SQL из Excel

Импорт данных в базу данных SQL играет важную роль при работе с SQL-серверами. Существуют различные методы и инструменты для облегчения ввода данных в базу данных SQL. В этой статье объясняется, как импортировать данные в базу данных SQL из файла Excel двумя способами:

  • SQL Server Management Studio (мастер импорта и экспорта данных SQL Server)
  • Насос ApexSQL

Импорт данных в базу данных SQL с помощью мастера импорта и экспорта данных SQL Server

SQL Server Management Studio позволяет пользователям импортировать данные из различных источников данных, которые будут описаны в этой главе.

При запуске SQL Server Management Studio будет открыто окно Connect to Server . Выберите сервер имя и тип аутентификации , введите учетные данные и нажмите кнопку Кнопка подключения :

Когда SSMS подключена к выбранному экземпляру SQL Server, щелкните правой кнопкой мыши нужную базу данных и перейдите к пункту Tasks > Import data из подменю Tasks :

Это действие откроет окно мастера импорта и экспорта SQL Server . Первый шаг этого процесса дает нам краткий обзор того, что делает мастер. Он разработан, чтобы помочь пользователям импортировать и экспортировать данные между многими популярными форматами данных, включая базы данных, электронные таблицы и текстовые файлы. Нажав на Кнопка Next мастер перейдет к следующему шагу:

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

В раскрывающемся списке Источник данных выберите Microsoft Excel в качестве источника. Раздел ниже будет изменен с помощью параметров, следующих за выбранным источником данных. В Параметры подключения к Excel , путь к файлу Excel будет выбран нажатием кнопки Обзор :

Данные из файла Excel dbo.Export_data будут использоваться для импорта в нужную базу данных:

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

  • Операция не может быть завершена.
    Дополнительная информация:
    Поставщик «Microsoft.ACE.OLEDB.12.0» не зарегистрирован на локальном компьютере. (Системные данные)

Это предупреждающее сообщение обычно появляется в 64-разрядной операционной системе в сочетании с 32-разрядной версией SQL Server Management Studio. Чтобы обойти эту проблему, закройте SSMS, перейдите в меню Start , найдите и откройте 64-битную версию Мастера импорта и экспорта SQL Server :

Как и в 32-битной версии, будет представлен тот же приветственный шаг с точно такими же шагами. Когда все ранее упомянутое в статье установлено, в окне Choose a Data Source нажмите кнопку Next .

Выберите место назначения. Далее будет шаг n, в котором будет установлено место назначения , куда будут копироваться данные. В раскрывающемся списке Destination выберите SQL Server Native Client 11.0 9.0026 :

При выборе адресата раздел под раскрывающимся списком Destination изменится автоматически. Здесь необходимо установить имя сервера , тип аутентификации , и базу данных . Нажмите кнопку Next , когда все указано:

Двигаясь дальше, на шаге Specify Table Copy или Query доступны два варианта:

  • Скопируйте данные из одной или нескольких таблиц или представлений
  • Напишите запрос, чтобы указать данные для передачи

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

Шаг Выбор исходных таблиц и представлений является следующим в этом мастере. Это позволяет пользователям выбирать одну или несколько таблиц и представлений для копирования данных. Для этой статьи dbo.Export_data Таблица выбрана. Кроме того, он позволяет пользователям редактировать сопоставления, нажав кнопку Изменить сопоставления , и посмотреть, как импортированные данные будут выглядеть в базе данных, нажав кнопку Предварительный просмотр . Нажмите Next , чтобы продолжить процесс импорта данных в базу данных SQL:

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

И последнее, но не менее важное: Complete the Wizard — это последний шаг в обработке данных импорта в базу данных SQL. Здесь приводится сводка вариантов, сделанных в процессе импорта данных. Убедитесь, что все в порядке, и нажмите Готово , чтобы завершить процесс:

Показывается сообщение «Выполнение успешно выполнено» с кратким статусом выполненной операции. Нажмите Закройте , чтобы выйти из мастера:

Целевая база данных будет заполнена вновь созданной таблицей и данными, как показано ниже:

Импорт данных в базу данных SQL через ApexSQL Pump

ApexSQL Pump — это инструмент перекачки базы данных, который позволяет пользователям легко экспортировать или импортировать данные в базу данных SQL и обратно.

При запуске ApexSQL Pump будет показано окно New project . В источнике данных , выберите Server , тип аутентификации , и базу данных . Для этой статьи будет использоваться база данных AW2019 . Нажмите Далее , чтобы продолжить:

На вкладке Action будет выбрано действие Import . Нажмите OK , чтобы подключиться к целевой базе данных:

В главном окне появится главное окно с таблицами и представлениями из целевой базы данных:

Чтобы импортировать данные в базу данных SQL, нажмите кнопку Manage на вкладке Home :

Откроется окно Управление импортом . Нажмите кнопку Добавить на вкладке Формат , чтобы добавить внешний файл для импорта:

Появится окно Добавить источник импорта , в котором пользователи могут выбрать между базой данных или файлом 9. 0026 источник данных для импорта. Параметр File будет проверен для целей этой статьи. Если флажок установлен, и появится опция File с кнопкой Browse for folder :

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

Нажмите OK , чтобы закрыть окно Добавить источник импорта :

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

В разделе Импорт по параметр для строк будет отмечен по умолчанию, а в разделе Заголовок будет отмечен параметр Первая строка в диапазоне . Окно Preview показывает, как импортированные данные будут выглядеть в режиме реального времени. Когда все настроено, нажмите OK , чтобы продолжить:

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

Следующим шагом будет переход в окно Settings в правой части главного окна.

В раскрывающемся списке Mapping выберите ранее выбранный файл dbo.Export_data Excel. Затем в разделе Таблица щелкните параметр Создать новый и в поле ниже введите имя новой таблицы. Наконец, в разделе Режим импорта щелкните параметр Вставить новый :

Двигаясь дальше, в основной сетке перейдите к выбранной таблице и из сопоставления столбцов 9В раскрывающемся списке 0026 выберите, какие столбцы будут сопряжены, например, столбец Customer_ID со столбцом ID в импортированном источнике файла:

Когда все столбцы из импортированного источника сопоставлены с соответствующим столбцом из выбранной таблицы, нажмите кнопку Process на вкладке Home :

Откроется окно сводки задания, при нажатии на кнопку Import 9.