В современном мире очень важно иметь возможность беспрепятственно передавать данные между различными системами. Когда дело доходит до управления данными в реляционной системе баз данных, такой как SQL Server, одной из распространенных задач является импорт данных из внешних источников, таких как электронные таблицы Excel. Excel — это широко используемый инструмент для управления данными и их анализа, и может быть полезно импортировать данные из Excel в SQL Server для различных целей, таких как хранение данных, создание отчетов и анализ. Существуют различные методы импорта данных Excel в SQL Server, каждый из которых имеет свои преимущества и ограничения.
В этой статье мы обсудим наиболее часто используемые методы импорта данных Excel в SQL Server и предоставим примеры запросов T-SQL для выполнения операции импорта.
Методы импорта данных Excel в SQL Server
Использование мастера импорта SQL Server
Мастер импорта и экспорта SQL Server — это мощный инструмент, который можно использовать для импорта данных Excel в SQL Server. Мастер предоставляет удобный интерфейс, который проведет вас через процесс импорта данных. Вот следующие шаги для использования «Мастера импорта и экспорта»:
1. Щелкните базу данных правой кнопкой мыши и выберите Задачи -> Импорт данных.
2. Выберите «Microsoft Excel» в качестве источника данных в диалоговом окне мастера импорта. Теперь перейдите к файлу Excel, из которого вы хотите импортировать данные, и выберите лист Excel, содержащий данные, которые нужно импортировать. Затем нажмите «Далее».
3. Выберите «Поставщик Microsoft OLE DB для SQL Server» в качестве места назначения в диалоговом окне «Назначение». Теперь введите имя сервера и данные аутентификации для базы данных SQL Server, в которую вы хотите импортировать данные. Затем выберите базу данных и таблицу, в которую вы хотите импортировать данные.
4. Сопоставьте столбцы из источника Excel с соответствующими столбцами в целевой таблице.
5. Нажмите «Далее», чтобы просмотреть данные и настроить любые дополнительные параметры, такие как обработка ошибок и столбцы идентификаторов.
6. Нажмите «Готово», чтобы сохранить конфигурацию и импортировать данные в SQL Server.
Использование команд T-SQL
Вы можете использовать команды T-SQL для импорта данных Excel в SQL Server с помощью функции OPENROWSET. Функция OPENROWSET позволяет считывать данные из внешнего источника, например файла Excel, и вставлять их в таблицу на сервере SQL. Чтобы импортировать данные Excel в SQL Server с помощью команд T-SQL, выполните следующие действия:
1. Создайте новую таблицу в SQL Server, которая соответствует структуре листа Excel, из которого вы хотите импортировать данные.
Например: если на листе Excel есть столбцы «Имя», «Номер телефона», «Поток», «Место работы» и «Место работы», создайте таблицу со столбцами «Имя», «Номер телефона», «Поток», «Место работы» и «Место работы».
СОЗДАТЬ ТАБЛИЦУ dbo.sheet2$ (Имя VARCHAR(50),
Номер телефона VARCHAR(20),
Поток VARCHAR(50),
КомпанияРазмещена VARCHAR(50),
Место работыVARCHAR(50)
)
2. Напишите команду T-SQL в новом окне запроса, которая использует функцию OPENROWSET для чтения данных из файла Excel. Вставьте его в созданную вами таблицу. Вот пример команды, которую вы можете использовать:
ВСТАВЬТЕ В dbo.sheet2$ (имя, номер телефона, поток, компания, место работы)ВЫБЕРИТЕ Имя, Номер телефона, Поток, CompanyPlaced, JobLocation
ОТ OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;База данных=[C:\Users\Somdeb\Desktop\Students.xls];HDR=ДА',
'ВЫБЕРИТЕ * ИЗ [лист2$]')
Выход:
Имя Номер телефона Поток Компания Размещено Место работы1 Арнаб Дас 9876543210 Engineering Infosys Kolkata
2 Рия Патель 8765432109 Медицина Больницы Аполлона Мумбаи
3 Advait Pal 7654321098 Law TATA Group Delhi
4 Анджали Сингх 6543210987 Arts Wipro Limited Ченнаи
3. Выполните команду T-SQL, чтобы импортировать данные из листа Excel в таблицу SQL Server.
Примечание: Перед выполнением команды T-SQL вам может потребоваться включить параметр Ad Hoc Distributed Queries на вашем SQL Server.
sp_configure 'показать дополнительные параметры', 1;ПЕРЕКОНФИГУРИРОВАТЬ;
ИДТИ
sp_configure «Специальные распределенные запросы», 1;
ПЕРЕКОНФИГУРИРОВАТЬ;
ИДТИ
Использование импорта плоского файла
Еще один простой способ импорта данных в SQL Server — использование мастера «Импортировать неструктурированный файл» в SQL Server Management Studio. Этот метод удобен, когда у вас есть большой файл с фиксированным форматом или форматом с разделителями, например файл CSV или файл с разделителями табуляцией. Вот шаги для импорта данных с помощью мастера «Импорт неструктурированного файла»:
1. Щелкните правой кнопкой мыши базу данных, в которую вы хотите импортировать данные, и выберите «Задачи» -> «Импортировать плоский файл» в контекстном меню.
2. Перейдите к местоположению файла CSV или Excel, который вы хотите импортировать. Вы также можете выбрать формат плоского файла и просмотреть данные. Укажите имя таблицы в поле «Новое имя таблицы». Указав параметры, нажмите «Далее», чтобы продолжить.
3. Вы можете предварительно просмотреть данные, которые будут импортированы, на экране «Предварительный просмотр данных» и при необходимости изменить сопоставления между исходным и целевым столбцами. Указав параметры, нажмите «Далее», чтобы продолжить.
4. Просмотрите сводку процесса импорта на экране «Сводка» и нажмите «Готово», чтобы завершить импорт.
5. После завершения импорта вы можете просмотреть импортированные данные в целевой таблице базы данных.
Просто запустите оператор SELECT для таблицы, в которую вы импортировали данные, чтобы подтвердить импорт файла Excel или CSV.
ВЫБЕРИТЕ * ИЗ dbo.familyCSV;Выход:
Члены семьи Возраст Профессия1 Аджай 42 Инженер
2 Саяны 38 Домохозяйка
3 Рохит 24 Фрилансер
4 Обряд 11 Студент
Заключение
Импорт данных из Microsoft Excel в SQL Server — это обычная задача, которую можно выполнить с помощью различных методов, включая службы SSIS, мастер импорта и экспорта и команды T-SQL. Каждый метод имеет свои преимущества и недостатки. Лучший метод для вашей ситуации зависит от различных факторов, таких как размер и сложность данных, частота импорта, а также ваши технические навыки и ресурсы. Следуя шагам, описанным в этой статье, вы сможете успешно импортировать данные Excel в SQL Server.