Как импортировать данные из Excel в sql
Обновлено: 04.07.2024
До того, как я основал SQL Spreads (надстройка Excel для импорта и обновления данных SQL Server из Excel), я много лет работал консультантом по бизнес-аналитике, используя инструменты бизнес-аналитики Microsoft, такие как SQL Server, SSIS, Reporting. Службы, Excel и т. д.
Я обнаружил, что, работая над разными проектами, я, как правило, хватаюсь за множество полезных вещей, которые могу использовать снова и снова. Одна из вещей, которую я повторно использую почти в каждом проекте, — это возможность копировать и вставлять данные из Excel в таблицу SQL Server.
Это очень простой и удобный способ быстрого импорта данных в таблицу SQL Server. Например, заполнение новой таблицы измерений, добавление некоторых тестовых данных или ввод любых других данных, которые вам нужны, чтобы быстро попасть в таблицу в SQL Server. Вероятно, это самый простой способ вставки данных из Excel в SQL Server, но его часто упускают из виду.
В этой статье я расскажу об основах копирования и вставки данных из Excel в SQL Server за несколько простых шагов. Я также расскажу о некоторых особых случаях и методах, которые полезно знать.
Если вы много импортируете или обновляете данные из Excel в SQL Server или у вас есть пользователи, которые любят Excel, но не привыкли к инструментам базы данных, взгляните на надстройку SQL Spreads Excel, которую я с которыми я работаю последние несколько лет с тех пор, как оставил работу консультанта по бизнес-аналитике. Это значительно упрощает выполнение задач импорта из Excel в SQL Server!
Другие инструменты и методы
Для предприятий, использующих базы данных Microsoft SQL Server, необходимость вставки данных из Excel в SQL Server является постоянной задачей. Существует ряд инструментов и методов для выполнения этой задачи, каждый из которых имеет свои преимущества и разный уровень сложности.
- Мастер импорта SQL Server — инструмент импорта на основе мастера внутри SQL Server Management Studio. Идеально подходит для одноразового импорта, когда у вас есть документ Excel, который необходимо импортировать в таблицу в SQL Server. Плюсы включают гибкость и множество настроек для точной настройки импорта. Самый большой недостаток заключается в том, что каждый раз, когда вам нужно импортировать данные, вам нужно запускать дюжину диалогов мастера с множеством настроек. Дополнительные сведения о мастере импорта SQL Server доступны здесь.
- SSIS — это нефтяной танкер для перемещения данных между различными источниками. Вы можете выполнять почти любую задачу, какую хотите, но вам потребуется много времени, чтобы начать работу, и еще больше времени потребуется на поддержку и изменение решения в будущем. Плюсы включают хорошую универсальность и множество доступных функций; главный недостаток — это время, которое вам придется потратить на изучение инструмента. Дополнительные сведения о службах SSIS доступны здесь.
- Утилита BCP — инструмент на основе командной строки, который предлагает огромное количество настроек — если вы программист, это инструмент для использования. Дополнительную информацию об утилите BCP можно найти здесь.
Это отличные инструменты для перемещения данных, особенно если вам нужно импортировать данные из Excel в SQL Server ежедневно или еженедельно. Недостаток заключается в том, что инструменты требуют выполнения ряда действий для настройки и требуют некоторого времени для настройки.
Преимущество метода "копировать и вставить", описанного ниже, заключается в том, что он очень быстрый и работает "из коробки", используя стандартные приложения Excel и SQL Server Management Studio. Он также может обрабатывать таблицы со столбцами идентификаторов и обрабатывать несколько тысяч строк данных с разумным временем загрузки.
ПРОФЕССИОНАЛЬНЫЙ СОВЕТ
Метод, описанный в этой статье, — отличный способ быстро вставить данные из Excel в SQL Server с помощью SQL Server Management Studio.
Если вы хотите не использовать SQL Server Management Studio, а вместо этого вставлять и обновлять данные в SQL Server непосредственно из Excel, я настоятельно рекомендую вам взглянуть на решение для управления данными SQL Spreads.
SQL Spreads Документами Excel можно легко поделиться с коллегами, не являющимися техническими специалистами, которые любят Excel, но не знакомы с Management Studio, поэтому они сами обновляют данные в SQL Server — непосредственно из Excel.
Как импортировать данные из Excel в SQL Server — пошаговые инструкции
Помните: всегда начинайте с копирования и вставки одной строки данных из Excel в SQL Server. Это необходимо для проверки отсутствия несоответствий между вашими данными из Excel и таблицы SQL Server (например, количества столбцов) и того, что ваши данные в Excel соответствуют типам данных в таблице SQL Server. Подробнее см. в разделе «Советы и рекомендации» ниже.
Как импортировать данные из Excel в таблицу SQL Server со столбцом идентификаторов
Тот же метод можно использовать для копирования и вставки данных в таблицы с автоматически увеличивающимся столбцом ID (столбец идентификаторов).
Здесь следует иметь в виду, что при копировании данных из Excel в SQL Server необходимо также включить дополнительный крайний левый пустой столбец в Excel.
Выполните следующие действия, чтобы скопировать и вставить данные из Excel в SQL Server, используя таблицу с автоматически увеличивающимся столбцом идентификаторов:
Копирование и вставка данных из Excel в представления SQL Server
Метод копирования и вставки также работает, когда вы импортируете данные из Excel в SQL Server в представление, а не в таблицу. Единственное требование состоит в том, что представление должно содержать данные только из одной таблицы.
В представление в SQL Server, содержащее данные из нескольких соединенных таблиц, нельзя вставлять новые строки, но можно обновлять данные, если вы обновляете только столбцы, происходящие из одной и той же базовой таблицы.
Советы по копированию данных из Excel на сервер SQL
Проверка ваших данных — начните с одной строки данных
Если данные, которые вы копируете из документа Excel, не соответствуют типам данных столбцов в таблице SQL Server, вставка данных будет отменена, и вы получите предупреждающее сообщение. Это произойдет для каждой строки, которую вы вставляете из Excel в SQL Server. Если вы вставите 500 строк из Excel с неправильным количеством столбцов, вы получите одно предупреждающее сообщение для каждой вставленной строки.
Чтобы избежать этого, хитрость заключается в том, чтобы начать копировать только одну строку данных и вставлять ее в таблицу SQL Server. Если вы получаете предупреждающее сообщение о неверных типах данных, вы можете исправить несоответствие и повторять процедуру копирования и вставки, пока все ваши столбцы Excel не впишутся в таблицу в SQL Server. Когда все столбцы совпадут, выберите оставшиеся строки и вставьте их все в таблицу SQL Server за один шаг.
Вставка значений NULL из Excel в таблицу SQL Server
Если в таблице SQL Server есть столбцы, допускающие значения NULL, и вы хотите вставить значение NULL в таблицу, просто введите текст NULL в ячейку Excel, а затем скопируйте и вставьте данные из Excel в SQL-сервер:
Значения NULL будут вставлены в таблицу SQL Server:
Таблицы с вычисляемыми столбцами
Для таблиц SQL Server, содержащих вычисляемые столбцы, вы можете вставить данные из Excel в эту таблицу, просто оставив данные для вычисляемого столбца в Excel пустыми, а затем скопировав и вставив данные из Excel в таблицу SQL Server.
Как получить имена столбцов из таблицы SQL Server в Excel
Когда вы готовите данные в Excel для импорта в существующую таблицу SQL Server, полезно иметь заголовки столбцов и несколько строк образцов данных в качестве ссылки в Excel.
Существует способ, позволяющий скопировать существующие данные из SQL Server в Excel и включить имена столбцов таблицы в качестве имен заголовков.
Выполните следующие действия, чтобы также включить имена столбцов при копировании нескольких строк данных из таблицы SQL Server в Excel:
- В SQL Server Management Studio найдите свою базу данных и разверните папку Tables.
- Щелкните правой кнопкой мыши имя таблицы и выберите третий вариант — «Выбрать первые 1000 строк».
- Выберите строки для копирования в Excel, удерживая нажатой кнопку CTRL и нажимая номера строк слева.
- Выбрав строки, щелкните правой кнопкой мыши одну строку и выберите параметр "Копировать с заголовками":
- Перейдите в Excel и вставьте данные в ячейку. Заголовки из таблицы в SQL Server теперь будут добавляться первой строкой:
- Сначала скопируйте данные из Excel, а затем вставьте их в таблицу SQL Server, используя пункт меню База данных > Таблица >Редактировать первые 200 строк.
- Всегда начинайте с копирования и вставки одной строки данных для проверки типов данных.
- Для таблиц SQL Server со столбцом идентификаторов добавьте дополнительный пустой крайний левый столбец перед копированием из Excel.
- С помощью копирования и вставки из Excel в SQL Server можно вставить до нескольких десятков тысяч строк данных.
- Чтобы перенести заголовки столбцов SQL Server в Excel, щелкните правой кнопкой мыши таблицу в SQL Server и выберите Копировать с заголовками.
- Не забывайте, что этот метод также отлично работает при подключении к удаленному рабочему столу!
- Операция не может быть завершена.
Дополнительная информация:
Провайдер Microsoft.ACE.OLEDB.12.0 не зарегистрирован на локальном компьютере. (System.Данные) - Копировать данные из одной или нескольких таблиц или представлений.
- Напишите запрос, чтобы указать данные для передачи
- На экране "Выберите место назначения" выберите базу данных назначения:
- В окне "Указать копию таблицы или запрос":
- Для простоты выберите "Копировать данные из одной или нескольких таблиц или представлений" и нажмите "Далее".
- Создайте новую базу данных с именем EmployeesDB
- Выполните следующий запрос
- Создайте новую книгу в MS Excel
- Перейдите на вкладку ДАННЫЕ.
- Кнопка «Выбрать из других источников».
- Выберите из SQL Server, как показано на изображении выше.
- Введите имя/IP-адрес сервера. Для этого руководства я подключаюсь к локальному хосту 127.0.0.1
- Выберите тип входа. Поскольку я нахожусь на локальном компьютере и у меня включена аутентификация Windows, я не буду указывать идентификатор пользователя и пароль. Если вы подключаетесь к удаленному серверу, вам нужно будет предоставить эти данные.
- Нажмите кнопку "Далее".
- Выберите EmployeesDB из раскрывающегося списка.
- Нажмите на таблицу сотрудников, чтобы выбрать ее.
- Нажмите кнопку "Далее".
Импорт Excel в SQL Server на удаленном компьютере
При работе с базами данных SQL Server на удаленном компьютере, где вы подключаетесь к удаленному компьютеру с помощью подключения к удаленному рабочему столу, вы по-прежнему можете использовать тот же метод копирования и вставки для перемещения данных из Excel на локальном компьютере в SQL Server. базу данных на удаленном компьютере.
Если вы не можете копировать и вставлять данные в свой SQL Server при подключении с помощью подключения к удаленному рабочему столу, сначала убедитесь, что копирование и вставка включены для подключения к удаленному рабочему столу:
Если вы по-прежнему не можете копировать и вставлять данные между Excel на локальном компьютере и SQL Server на удаленном сервере базы данных, уточните у администратора сервера, включена ли функция копирования и вставки для подключения к удаленному рабочему столу на сервере.
Производительность Excel для SQL Server
Копирование и вставка данных из Excel в SQL Server — это действительно простой способ импорта данных из Excel в базу данных SQL Server.Один из недостатков заключается в том, что это не самый быстрый метод, если вам нужно вставить большие объемы данных, например несколько сотен тысяч строк данных или больше.
Чтобы получить представление об ограничениях производительности, я провел несколько тестов на своем локальном компьютере i7 с 8 ГБ ОЗУ, на котором были установлены Microsoft Excel и SQL Server.
У меня были следующие результаты: копирование данных из Excel с 10 столбцами смешанных типов данных в SQL Server заняло около 2 секунд для 100 строк, около 30 секунд для 1000 строк и около 10 минут для 20 000 строк.
Итак, я бы сказал, что ограничение на использование функции копирования и вставки составляет от нескольких тысяч до нескольких десятков тысяч строк данных. Если вам нужно выполнить импорт из Excel в SQL Server с большим количеством данных, то более подходящим является один из других методов, упомянутых ранее.
Требования и ограничения
Для копирования и вставки данных из Excel в SQL Server вам потребуются установленные Microsoft Excel и SQL Server Management Studio. Вы можете скачать бесплатную версию SQL Server Management Studio здесь.
Копирование данных из Excel в SQL Server отлично работает для нескольких тысяч строк данных с разумным временем загрузки.
Метод копирования и вставки подходит как для таблиц с бизнес-ключами, так и для таблиц с автоматически увеличивающимися ключами ID.
Обратите внимание, что метод копирования и вставки предназначен только для вставки новых данных. Если вам нужно обновить существующие данные в таблице в SQL Server, вам нужно будет использовать либо внешний инструмент, такой как SQL Spreads, либо вставить данные во временную таблицу, а затем написать небольшой SQL-запрос, который обновит вашу таблицу с помощью вставленные данные.
Метод копирования и вставки, описанный в этой статье, требует, чтобы вы были знакомы с SQL Server Management Studio.
Если у вас есть коллеги, которые не привыкли к SQL Server Management Studio,
но любят Excel, я хотел бы порекомендовать вам взглянуть на надстройку SQL Spreads для Excel.
Используя SQL Spreads, вы можете позволить своим конечным пользователям, не имеющим навыков работы с базами данных, самостоятельно обновлять и управлять данными в SQL Server непосредственно из Excel.
Резюме — вставка данных из Excel в SQL Server
Если вам нужно быстро и легко импортировать данные из Excel в SQL Server, отлично подойдет функция копирования и вставки. Этот процесс прост, не требует специальных знаний или инструментов и может использоваться в сценариях с несколькими десятками тысяч строк данных.
Метод копирования и вставки также отлично подходит для таблиц с автоматически увеличивающимся ключом идентификации — просто не забудьте добавить дополнительный пустой столбец слева от столбцов данных, как описано выше.
Его также можно использовать при работе с Excel на локальном компьютере и SQL Server на удаленном компьютере с использованием подключения к удаленному рабочему столу.
Из Excel в SQL Server — краткий справочник:
Йоханнес Окессон
Последние 15 лет работал в сфере бизнес-аналитики.
Основатель SQL Spreads — решения по управлению данными
для импорта, обновления и управления
данными SQL Server из Excel.
Импорт данных в базу данных SQL играет важную роль при работе с SQL-серверами. Существуют различные методы и инструменты для облегчения ввода данных в базу данных SQL. В этой статье объясняется, как импортировать данные в базу данных SQL из файла Excel двумя способами:
Импорт данных в базу данных SQL с помощью мастера импорта и экспорта данных SQL Server
SQL Server Management Studio позволяет пользователям импортировать данные из различных источников данных, которые будут описаны в этой главе.
При запуске SQL Server Management Studio открывается окно "Подключиться к серверу". Выберите имя сервера и тип аутентификации, укажите учетные данные и нажмите кнопку «Подключиться»:
Когда SSMS подключена к выбранному экземпляру SQL Server, щелкните правой кнопкой мыши нужную базу данных и выберите «Задачи» > «Импорт данных» в подменю «Задачи»:
Это действие откроет окно мастера импорта и экспорта SQL Server. Первый шаг этого процесса дает нам краткий обзор того, что делает мастер. Он разработан, чтобы помочь пользователям импортировать и экспортировать данные между многими популярными форматами данных, включая базы данных, электронные таблицы и текстовые файлы. Нажав кнопку Далее, мастер перейдет к следующему шагу:
Этап «Выбор источника данных» будет следующим в процессе импорта данных в базу данных SQL с помощью этого мастера. На этом шаге будет выбран источник, из которого будут скопированы данные.
В раскрывающемся списке Источник данных выберите Microsoft Excel в качестве источника. Раздел ниже будет изменен с помощью параметров, следующих за выбранным источником данных. В разделе настроек подключения к Excel путь к файлу Excel будет выбран нажатием на кнопку Обзор:
Данные из файла Excel dbo.Export_data будут использоваться для импорта в нужную базу данных:
Выбрав источник данных, нажмите кнопку "Далее", чтобы продолжить. Может появиться следующее предупреждающее сообщение:
Это предупреждающее сообщение обычно появляется в 64-разрядной операционной системе в сочетании с 32-разрядной версией SQL Server Management Studio. Чтобы обойти эту проблему, закройте SSMS, перейдите в меню «Пуск», найдите и откройте 64-разрядную версию мастера импорта и экспорта SQL Server:
Как и в 32-разрядной версии, будет представлен один и тот же приветственный шаг с точно такими же шагами. Когда все ранее упомянутое в статье установлено, в окне «Выберите источник данных» нажмите кнопку «Далее».
Выберите место назначения. Следующим шагом будет выбор места назначения, в которое будут скопированы данные. В раскрывающемся списке «Назначение» выберите «Собственный клиент SQL Server 11.0»:
При выборе места назначения раздел под раскрывающимся списком «Назначение» изменится автоматически. Здесь необходимо установить имя сервера, тип аутентификации и базу данных. Нажмите кнопку «Далее», когда все указано:
Двигаясь дальше, на шаге "Указать копию таблицы или запрос" доступны две опции:
В этом случае будет выбран параметр Копировать данные из одной или нескольких таблиц или представлений. Нажмите кнопку «Далее», чтобы продолжить процесс импорта данных в базу данных SQL:
Следующим в этом мастере является шаг Выбор исходных таблиц и представлений. Это позволяет пользователям выбирать одну или несколько таблиц и представлений для копирования данных. Для этой статьи выбрана таблица dbo.Export_data. Кроме того, он позволяет пользователям редактировать сопоставления, нажимая кнопку «Редактировать сопоставления», и просматривать, как импортированные данные будут выглядеть в базе данных, нажимая кнопку «Предварительный просмотр». Нажмите «Далее», чтобы продолжить процесс импорта данных в базу данных SQL:
На этапе "Сохранить и запустить пакет" пользователь может выбрать, сохранять ли пакет SSIS. Параметр по умолчанию — «Выполнить немедленно». Нажмите кнопку «Далее», чтобы продолжить процесс импорта:
И последнее, но не менее важное: завершение работы мастера — это последний шаг в обработке данных импорта в базе данных SQL. Здесь приводится сводка вариантов, сделанных в процессе импорта данных. Убедитесь, что все в порядке, и нажмите «Готово», чтобы завершить процесс:
Отображается сообщение «Выполнение выполнено успешно» с кратким статусом выполненной операции. Нажмите «Закрыть», чтобы выйти из мастера:
Целевая база данных будет заполнена вновь созданной таблицей и данными, как показано ниже:
Импорт данных в базу данных SQL через ApexSQL Pump
ApexSQL Pump — это инструмент перекачки базы данных, который позволяет пользователям легко экспортировать или импортировать данные в базу данных SQL и обратно.
При запуске ApexSQL Pump будет показано окно «Новый проект». На вкладке Источник данных выберите Сервер, тип аутентификации и базу данных. В этой статье будет использоваться база данных AW2019. Нажмите «Далее», чтобы продолжить:
На вкладке «Действие» будет выбрано действие «Импорт». Нажмите OK, чтобы подключиться к целевой базе данных:
В главном окне появится главное окно с таблицами и представлениями из целевой базы данных:
Чтобы импортировать данные в базу данных SQL, нажмите кнопку «Управление» на вкладке «Главная»:
Откроется окно Управление импортом. Нажмите кнопку «Добавить» на вкладке «Формат», чтобы добавить внешний файл для импорта:
Появится окно «Добавить источник импорта», в котором пользователи могут выбрать между базой данных или источником данных «Файл» для импорта. Параметр «Файл» будет проверен для целей этой статьи. Если флажок установлен, появится опция «Файл» с кнопкой «Обзор папки»:
Нажмите на эту опцию, чтобы выбрать файл для импорта данных в базу данных SQL. В этом случае будет выбран файл Excel dbo.Excel_data. Нажмите Открыть, чтобы продолжить:
Нажмите "ОК", чтобы закрыть окно "Добавить источник импорта":
После добавления источника импорта в правой части окна «Управление импортом» появятся дополнительные параметры для импортируемого источника данных. В нашем случае это параметры файла Excel.
В разделе «Импорт по» параметр «Строки» будет отмечен по умолчанию, а в разделе «Заголовок» будет установлен флажок «Первая строка в диапазоне». Окно предварительного просмотра показывает, как импортированные данные будут выглядеть в режиме реального времени. Когда все настроено, нажмите OK, чтобы продолжить:
Представление вернется к основной сетке, где столбцы из выбранной таблицы будут использоваться для сопоставления столбцов из импортированного файла, в данном случае будет использоваться таблица dbo.Customer:
Следующий шаг — перейти в окно настроек в правой части главного окна.
В раскрывающемся списке Сопоставление выберите ранее выбранный файл Excel dbo.Export_data. Затем в разделе «Таблица» нажмите «Создать новую» и в поле ниже введите имя новой таблицы. Наконец, в разделе «Режим импорта» выберите параметр «Вставить новый»:
Двигаясь дальше, в основной сетке перейдите к выбранной таблице и в раскрывающемся списке Сопоставление столбцов выберите, какие столбцы будут парными, например, столбец Customer_ID с ID столбец в импортированном источнике файла:
Когда все столбцы из импортированного источника сопоставлены с соответствующим столбцом из выбранной таблицы, нажмите кнопку «Обработка» на вкладке «Главная»:
Откроется окно сводки задания, при нажатии на кнопку Импорт запустится процесс импорта данных в базу данных SQL:
Откроется окно Просмотр результатов со всеми результатами. Это окно также позволяет пользователям экспортировать результаты или создавать отчеты, как показано ниже:
Вернувшись в SSMS, выполните команду Select Top 1000 Rows, щелкнув правой кнопкой мыши в обозревателе объектов, чтобы убедиться, что данные импортированы в базу данных SQL успешно:
Хотите улучшить этот вопрос? Обновите вопрос, чтобы он был сосредоточен только на одной проблеме, отредактировав этот пост.
Закрыт 3 года назад.
У меня есть данные в файле Excel — на самом деле в формате XLSX, так как сейчас 2020 год. Мне нужно передать эти данные в SQL Server следующим образом:
специальный вариант использования – заполнение таблиц тестовыми данными или нечастая загрузка небольших объемов данных (скажем, Follow
2 ответа 2
Есть много статей о написании кода для импорта файла Excel, но это версия с ручным управлением/сокращением:
Если вам не нужно программно импортировать файл Excel с помощью кода, вы можете сделать это очень быстро с помощью меню в SQL Server Management Studio (SSMS).
Самый быстрый способ преобразовать файл Excel в SQL – воспользоваться мастером импорта:
Откройте SSMS (SQL Server Management Studio) и подключитесь к базе данных, в которую вы хотите импортировать файл.
Импорт данных: в SSMS в обозревателе объектов в разделе «Базы данных» щелкните правой кнопкой мыши целевую базу данных и выберите «Задачи», «Импорт данных». Появится мастер импорта (обычно можно просто нажать «Далее» на первом экране).
Следующее окно — «Выберите источник данных». Выберите Excel:
В раскрывающемся списке "Источник данных" выберите Microsoft Excel (этот параметр должен появиться автоматически, если у вас установлен Excel).
Нажмите кнопку "Обзор", чтобы выбрать путь к файлу Excel, который вы хотите импортировать.
Выберите версию файла Excel (97-2003 обычно подходит для файлов с расширением .XLS или 2007 для более новых файлов с расширением .XLSX)
Установите флажок "В первой строке есть заголовки", если ваш файл Excel содержит заголовки.
Выберите «Имя сервера», «Аутентификация» (обычно это ваше имя пользователя и пароль sql) и выберите базу данных в качестве места назначения. Нажмите «Далее».
'Выбрать исходные таблицы:' выберите рабочий лист (листы) из файла Excel и укажите целевую таблицу для каждого рабочего листа.Если у вас еще нет таблицы, мастер очень любезно создаст новую таблицу, которая соответствует всем столбцам из вашей электронной таблицы. Нажмите «Далее».
В этом руководстве мы собираемся импортировать данные из внешней базы данных SQL. В этом упражнении предполагается, что у вас есть работающий экземпляр SQL Server и базовые знания SQL Server.
В этом учебном пособии по Excel вы узнаете –
Сначала мы создаем файл SQL для импорта в Excel. Если у вас уже есть экспортированный файл SQL, вы можете пропустить следующие два шага и перейти к следующему шагу.
Как импортировать данные в Excel с помощью диалогового окна мастера
После подключения к серверу базы данных. Откроется окно, вам нужно ввести все данные, как показано на скриншоте
Откроется мастер подключения к данным, чтобы сохранить подключение к данным и завершить процесс подключения к данным сотрудника.
Как импортировать данные MS Access в Excel с помощью примера
Здесь мы собираемся импортировать данные из простой внешней базы данных на базе базы данных Microsoft Access. Мы импортируем таблицу продуктов в Excel. Вы можете загрузить базу данных Microsoft Access.
Читайте также: