Как загрузить данные в sql из excel: Импорт данных из Excel в SQL

Содержание

Ничего не найдено для Import Excel In Ms Sql Server%23Shag 1 Proveryaem Nalichie Provaydera Microsoft

Linux

Всем привет! В этой заметке я покажу, как можно исправить ситуацию, когда после успешной

Windows

Привет! Сегодня мы рассмотрим новый инструмент управления серверами – Windows Admin Center. Я расскажу,

Новости

Приветствую всех посетителей сайта Info-Comp.ru! Я хочу поздравить Вас с наступающим новым 2020 годом!

Функционал в операционной системе Windows Server расчет и улучшается от версии к версии, ролей

Программы

В данной статье мы рассмотрим, как можно организовать коллекцию дисков у себя на компьютере,

Сеть

Данный материал посвящен эталонной сетевой семиуровневой модели OSI. Здесь Вы найдете ответ на вопрос

Ничего не найдено для Import Excel In Ms Sql Server%23Shag 4 Vypolnyaem Sql Zapros Obraschenie

Windows

Всем привет! Сегодня я расскажу о том, как можно удалить шифрованный EFI раздел с

Windows

Сейчас я Вам расскажу о бесплатной и очень простой программе, которая умеет создавать образы

Windows

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

Windows

Всем привет! Сейчас мы с Вами рассмотрим два варианта установки и удаления файлового сервера

Другое

Сегодня я хочу поделиться своим отзывом о планшетном компьютере Texet TM-9751 HD, иными словами,

Microsoft SQL Server

Приветствую Вас на сайте Info-Comp.ru! Сегодня мы приступим к рассмотрению полнотекстовых запросов в СУБД

Ничего не найдено для Import Excel In Ms Sql Server%23Shag 6 Vypolnyaem Sql Zapros Obraschenie

Microsoft SQL Server

Microsoft уже прекратил поддержку операционной системы Windows Server 2003 вслед за этим планируется завершить

Microsoft SQL Server

Изучение Transact-SQL продолжается и на очереди у нас функции ранжирования ROW_NUMBER, RANK, DENSE_RANK и

Для того чтобы Ваш домашний компьютер оставался в рабочем состоянии, а личные данные были

Linux

Всем привет! Материал сегодня будет посвящен рассмотрению процесса установки серверной операционной системы Linux Ubuntu

Microsoft SQL Server

Всем привет! Сегодня я максимально просто, специально для начинающих, попытаюсь рассказать Вам о том,

Windows

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

Import Excel столбцов электронной таблицы в базу данных SQL Server

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

Существуют ли какие-либо простые варианты кода?

sql-server

excel

sql-server-2008

import

etl

Поделиться

Источник

user47206

17 декабря 2008 в 20:54

15 ответов

После подключения к базе данных Sql Server 2005 в окне объекта Explorer щелкните правой кнопкой мыши базу данных, в которую вы хотите поместить таблицу import. Выберите Задачи -> Import Данных.
Это простой инструмент и позволяет вам ‘map’ входящих данных в соответствующую таблицу.
Вы можете сохранить сценарии для повторного запуска при необходимости.

Поделиться

Tejas

09 июня 2010 в 01:25

Microsoft предлагает несколько методов:

SQL Server Услуги По Преобразованию Данных (DTS)
Microsoft SQL Server 2005 Integration Services (SSIS)
SQL Server связанных серверов
SQL Server распределенные запросы
ActiveX объекты данных (ADO) и поставщик Microsoft OLE DB для SQL Server
ADO и поставщик Microsoft OLE DB для Jet 4.0

Если мастер (DTS) не работает (а я думаю, что должен), вы можете попробовать что-то вроде этого http://www.devasp.net/net/articles/display/771.html, который в основном предлагает сделать что-то вроде

INSERT INTO [tblTemp] ([Column1], [Column2], [Column3], [Column4])

SELECT A.[Column1], A.[Column2], A.[Column3], A.[Column4]
FROM OPENROWSET 
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Excel.xls;HDR=YES', 'select * from [Sheet1$]') AS A;

Поделиться

inspite

17 декабря 2008 в 21:02

Это может показаться далеким путем, но вы можете посмотреть на использование Excel для генерации кода INSERT SQL, который вы можете вставить в анализатор запросов для создания своей таблицы.

Хорошо работает, если вы не можете использовать мастера, потому что файл excel не находится на сервере

Поделиться

Nick Kavadias

22 декабря 2008 в 02:41

Вы могли бы использовать OPENROWSET, что-то вроде:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
  'Excel 8.0;IMEX=1;HDR=NO;DATABASE=C:\FILE.xls', 'Select * from [Sheet1$]'

Просто убедитесь, что путь-это путь на сервере, а не на вашей локальной машине.

Поделиться

SqlACID

17 декабря 2008 в 21:03

go
sp_configure 'show advanced options',1  
reconfigure with override  
go  
sp_configure 'Ad Hoc Distributed Queries',1  
reconfigure with override  
go
SELECT * into temptable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Documents and Settings\abhisharma\Desktop\exl\ImportExcel2SQLServer\ImportExcel2SQLServer\example.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

select * from temptable

Поделиться

abk

14 июня 2011 в 10:35

Другой вариант-использовать VBA в Excel и написать макрос для анализа данных электронной таблицы и записи их в SQL.

Один из примеров здесь: http:/ / www.ozgrid.com / forum/showthread. php?t=26621&page=1

Sub InsertARecord() 
Dim cnt As ADODB.Connection 
Dim rst As ADODB.Recordset 
Dim stCon As String, stSQL As String 
Set cnt = New ADODB.Connection 
Set rst = New ADODB.Recordset 

stCon = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=JOEY" 
cnt.ConnectionString = stCon 
stSQL = "INSERT INTO MyTable (Price)" 
stSQL = stSQL & "VALUES (500)" 

cnt.Open 
rst.Open stSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText 

If CBool(rst.State And adStateOpen) = True Then rst.Close 
Set rst = Nothing 
If CBool(cnt.State And adStateOpen) = True Then cnt.Close 
Set cnt = Nothing 

End Sub

Поделиться

Cyrus Loree

03 сентября 2011 в 17:22

Под ‘the wiz’ я предполагаю, что вы говорите о «SQL Server Import и Мастере экспорта». (Я тоже довольно новичок, поэтому не понимаю большинства вопросов, не говоря уже о большинстве ответов, но думаю, что этот я понимаю). Если да, то не могли бы вы взять электронную таблицу или ее копию, удалить столбцы, которые вы не хотите импортировать, а затем использовать мастер?

Я всегда находил возможность делать с ним то, что мне нужно, и я нахожусь только на SQL Server 2000 (не знаю, чем отличаются другие версии).

Edit: на самом деле я смотрю на него сейчас и, кажется, могу выбрать, какие столбцы я хочу сопоставить с какими строками в существующей таблице. На экране «выбор исходных таблиц и представлений» я проверяю используемую таблицу данных, выбираю ‘Destination’ и нажимаю кнопку » Редактировать…». Оттуда вы можете выбрать столбец Excel и столбец таблицы для его сопоставления.

Поделиться

Jeff Keslinke

17 декабря 2008 в 21:09

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

Как проверить и Import Excel электронную таблицу в базу данных SQL Server:

http://leansoftware.net/en-us/help/excel-database-tasks/worked-examples/how-to-import-excel-spreadsheet-to-sql-server-data.aspx

Поделиться

Richard Briggs

05 февраля 2014 в 22:53

Microsoft Access — это еще один вариант. У вас может быть локальная база данных Access на вашем компьютере, в которую вы помещаете электронные таблицы excel (доступны мастера) и ссылаетесь на таблицы базы данных SQL Server через ODBC .

Затем вы можете создать запрос в access, который добавляет данные из таблицы Excel в таблицы SQL Server.

Поделиться

Troy

12 февраля 2010 в 19:40

Поделиться

kajo

21 июня 2012 в 10:27

Поделиться

Such

21 мая 2013 в 08:55

Прежде всего, попробуйте 32-битную версию Мастера Import. Это показывает гораздо больше поддерживаемых форматов import.

Фон:
Все зависит от установки вашего офиса (runtimes Engine).

Если у вас не установлен Office 2007 или более поздняя версия, мастер Import (32-разрядная версия) позволяет использовать только файлы import Excel 97-2003 (.xls).

Если у вас установлены Office 2010 и geater (поставляется также в 64 Bit, не рекомендуется), мастер Import также поддерживает файлы Excel 2007+ (.xlsx).

Обзор времени выполнения см. В разделе ‘Microsoft.ACE.OLEDB.12.0’ поставщик не зарегистрирован на локальном компьютере

Поделиться

Bernhard

14 января 2016 в 08:10

Excel + SQLCMD + Perl = exceltomssqlinsert

и вы можете использовать свой Excel как frond-end для MSSQL db … обратите внимание на усеченную таблицу в начале каждого сгенерированного файла sql insert …

Поделиться

Yordan Georgiev

23 апреля 2011 в 19:55

Я использовал DTS (теперь известный как SQL server Import и мастер экспорта). Я использовал этот учебник , который отлично работал для меня даже в Sql 2008 и excel 2010 (14.0)

Я надеюсь, что это поможет

-Д

Поделиться

Diego C.

08 марта 2012 в 20:55

Мастер import действительно предлагает эту опцию. Вы можете либо использовать опцию, чтобы написать свой собственный запрос для данных в import,либо использовать опцию копировать данные и использовать кнопку «Edit Mappings», чтобы игнорировать столбцы, которые вы не хотите import.

Поделиться

DCNYAM

17 декабря 2008 в 21:01

Немножко всего .. из жизни администратора ms sql server

Несколько примеров работы из MS SQL Server с таблицами формата Excel(.xls,.xlsx):

1)С
использованием функции OPENROWSET или с OPENDATASOURCE:

SELECT * FROM OPENROWSET(‘MSDASQL’,

‘Driver={Microsoft Excel Driver (*.xls)};

DBQ=[C:\gr_otchet.xls]’, ‘SELECT * FROM [Sheet1$A8:D10000]’

Пример для OPENDATASOURCE
из BOL:

SELECT * FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0’,

‘Data
Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0’)…[Sheet1$] ;

Одна из
распространенных проблем, это отсутствие
драйверов под х64 платформу, или установка х32 битных под х64 систему. Например,
драйверов Microsoft.Jet.OleDB нет 64 битных,в этом случае можно использовать другие драйвера, к примеру
Microsoft.ACE.OLEDB.12.0.

Не забудьте только про
Примечание из BOL:

Функция
OPENROWSET или OPENDATASOURCE может быть использована
для доступа к удаленным данным из источников данных OLE DB только в том случае,
если для заданного поставщика параметр реестра DisallowAdhocAccess явно
установлен в 0 и включен параметр Ad Hoc Distributed Queries расширенной
настройки. Если эти параметры не установлены, поведение по умолчанию запрещает
нерегламентированный доступ.

Если параметр
Ad Hoc Distributed Queries выключен, то об будет информационное сообщение. Включение
параметра осуществляется через хранимую процедуру sp_configure.

sp_configure ‘Ad Hoc Distributed Queries’, 1;

RECONFIGURE;

2) Второй спосб через Linkedserver
и ODBC драйвер.

1-ый способ
хорошо, когда необходимо использовать разово, для частого и широко
использования лучше использовать технологию связанного сервера(Linked Server)

Для этого
необходимо установить на сервере MS SQL Server ODBC драйвер а для Excel, затем создать источник
данных( Администрирование ->Источники данных)

Указывает имя источника данных
,версию Excel и сам
файл.

Сохраняем источник.

После этого создаем связанный
сервер LinkedServed (связанный
сервер):

Указываем имя нашего связанного сервера и имя созданного нами
раннего ODBC источника.

Сохраняем.

Теперь можно выполнять запросы к
нашему связанному серверу, к примеру:

select * from openquery(excel,’select * from [sheet1$]’) – получение всех данныз из экселя

select * from openquery(excel,’select * from
[Sheet1$A10:D2]’) – получение данных диапозона $A10:D2

select * from openquery(excel,’select * from
[Sheet1$A10:D]’) – получение данных диапозона с A10:D до
конца файла.

3) Еще одни,
способ, когда необходим импорт разово, то можно использовать «SQL Server Import and Export Wizard»:

Выделяем БД, правая кнопка, Задачи, Выбираем пункт Импорт
или Экспорт:

Выбираем источник данных, наш
файл Excel, версию Excel-я. :

Выбираем куда
копировать данные, указываем таблицу назначение .

После этого
можно пакет запустить немедленно или его сохранить для дальнейшего
использования.

4) Кстати, 4 способ, это как раз создание пакета SSIS в Microsoft Visual Studio, результатом которого
так же будет пакет, похожий на то, что было создано в варианте 3

Делается он
просто

Выбирается Элемент потока
управления

Затем выбирается источник и
сервер назначения:

В источнике соединений создается
новое соединение с нашим файлом Excel,
в Назначение указываем наш MS SQL Server, указываем таблицу,
сопоставляем столбцы:

После этого сохраняем пакет, и
его запускаем.

Пакет создали и должен работать.

Удачи .

Импорт данных из Excel в SQL Server

Microsoft Office Exel уже довольно давно стал ключевой программой при переносе разных баз, тк практически любая даже очень старая база работает с Exel и перевести в него данный не состовляет ни каких проблем.
Это пошаговое руководство описывает различные способы импорта данных из
листов Microsoft Excel в базы данных Microsoft SQL Server.

Требования перед началом

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

Экземпляр Microsoft SQL Server 7.0, Microsoft SQL Server 2000, Microsoft SQL Server 2005 или Microsoft SQL Server 2008
Microsoft Visual Basic 6.0 для примеров объектов ADO, использующих Visual Basic

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

Службы преобразования данных
Связанные серверы и распределенные запросы
Разработка объектов ADO на Visual Basic

Описание

В примерах, приведенных в данной статье, импорт данных Excel выполняется с помощью следующих функций:

Службы преобразования данных SQL Server (DTS)
Службы интеграции Microsoft SQL Server 2005 (SSIS)
Связанные серверы SQL Server
Распределенные запросы SQL Server
Поставщик объектов данных ActiveX(ADO) и Microsoft OLE DB для SQL Server
Поставщик ADO и Microsoft OLE DB для Jet 4.0

Примеры

Import или Append

В примерах команд SQL, используемых в статье, показаны запросы Create
Table для импорта данных Excel в новую таблицу SQL Server с
использованием конструкций SELECT…INTO…FROM. При сохранении ссылок на
объекты-источники и получатели выражения, приведенные в примерах, могут
быть преобразованы в запросы Append с использованием конструкций INSERT
INTO…SELECT…FROM.

Использование DTS или SSIS

Для импорта данных Excel в таблицы SQL Server могут быть использованы
мастер импорта служб преобразования данных (DTS) SQL Server или мастер
импорта и экспорта SQL Server. При работе с мастером и выборе исходных
таблиц Excel помните, что имена объектов Excel со знаком доллара ($)
являются именами листов (например Лист1$), а имена объектов без знака
доллара являются названиями именованных диапазонов Excel.

Использование связанного сервера

Для упрощения запросов книга Excel может быть настроена как связанный
сервер в SQL Server. Для получения дополнительных сведений щелкните
приведенный ниже номер статьи базы знаний Майкрософт: 306397 (http://support.microsoft.com/kb/306397/RU/ )

Следующая программа импортирует данные из рабочего листа
«Customers» связанного сервера Excel «EXCELLINK» в новую таблицу SQL
Server с именем XLImport1:

←**

SELECT * INTO XLImport1 FROM EXCELLINK…[Customers$]

**→

При использовании OPENQUERY источнику может быть передан сквозной запрос:

←**

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,

‘SELECT * FROM [Customers$]’)

**→

Использование распределенных запросов

Если устанавливать существующее подключение к книге Excel как
связанный сервер нежелательно, данные могут быть импортированы с
использованием функций OPENDATASOURCE или OPENROWSET. В следующих
примерах кода также производится импорт данных из рабочего листа Excel
«Customers» в новые таблицы SQL Server:

←**

SELECT * INTO XLImport3 FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0’,

‘Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0’)…[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,

‘Excel 8.0;Database=C:\test\xltest.xls’, [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,

‘Excel 8.0;Database=C:\test\xltest.xls’, ‘SELECT * FROM [Customers$]’

**→

Использование ADO и SQLOLEDB

Синтаксис «распределенных запросов», приведенный в разделе Использование распределенных запросов,
может быть использован также в приложении ADO для импорта данных Excel в
SQL Server, если для подключения к SQL Server используется Microsoft
OLE DB для SQL Server (SQLOLEDB).

Для работы следующего примера программы на Visual Basic 6.0
требуется добавление ссылки на проект в объекты данных ActiveX (ADO). В
этом примере показано использование функций OPENDATASOURCE и OPENROWSET
для подключения SQLOLEDB.

on Visual Basic

←**

Dim cn As ADODB.Connection

Dim strSQL As String

Dim lngRecsAff As Long

Set cn = New ADODB.Connection

cn.Open «Provider=SQLOLEDB;Data Source=<server>;» & _

«Initial Catalog=<database>;User ID=<user>;Password=<password>»

‘Import by using OPENDATASOURCE.

strSQL = «SELECT * INTO XLImport6 FROM » & _

«OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0’, » & _

«‘Data Source=C:\test\xltest.xls;» & _

«Extended Properties=Excel 8.0′)…[Customers$]»

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print «Records affected: » & lngRecsAff

‘Import by using OPENROWSET and object name.

strSQL = «SELECT * INTO XLImport7 FROM » & _

«OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, » & _

«‘Excel 8.0;Database=C:\test\xltest.xls’, » & _

«[Customers$])»

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print «Records affected: » & lngRecsAff

‘Import by using OPENROWSET and SELECT query.

strSQL = «SELECT * INTO XLImport8 FROM » & _

«OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, » & _

«‘Excel 8.0;Database=C:\test\xltest.xls’, » & _

«‘SELECT * FROM [Customers$]’)»

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print «Records affected: » & lngRecsAff

cn.Close

Set cn = Nothing

**→

Использование ADO и Поставщика данных Jet

В примере из предыдущего раздела при импорте из Excel в SQL для связи с получателем использовались ADO и поставщик SQLOLEDB. Для подключения к источнику Excel можно воспользоваться поставщиком данных OLE DB для Jet 4.0.

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

[Полный путь к базе данных Microsoft Access].[Название таблицы]
[Название ISAM;Строка подключения ISAM].[Название таблицы]
[ODBC;Строка подключения ODBС].[Название таблицы]

В этом разделе для создания подключения ODBC к базе данных SQL Server используется третий формат. Может использоваться имя источника данных (DSN) ODBC или строка подключения без определения DSN:

←**

DSN:

[odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

Подключение без определения DSN:

[odbc;Driver={SQL Server};Server=<server>;Database=<database>;

UID=<user>;PWD=<password>]

**→

Для работы следующего примера программы на Visual Basic 6.0
требуется добавление ссылки на проект в ADO. В примере показан импорт
данных Excel в SQL Server через подключение ADO с использованием
поставщика данных Jet 4.0.

←**

Dim cn As ADODB.Connection

Dim strSQL As String

Dim lngRecsAff As Long

Set cn = New ADODB.Connection

cn.Open «Provider=Microsoft.Jet.OLEDB.4.0;» & _

«Data Source=C:\test\xltestt.xls;» & _

«Extended Properties=Excel 8.0»

‘Import by using Jet Provider.

strSQL = «SELECT * INTO [odbc;Driver={SQL Server};» & _

«Server=<server>;Database=<database>;» & _

«UID=<user>;PWD=<password>].XLImport9 » & _

«FROM [Customers$]»

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print «Records affected: » & lngRecsAff

cn.Close

Set cn = Nothing

**→

Для импорта данных Excel в другие базы данных Microsoft Access, базы
данных индексно-последовательного метода доступа (ISAM) или базы ODBC
также могут использоваться конструкции, поддерживаемые поставщиком
данных Jet.

Устранение неполадок

Помните, что объекты Excel, имена которых содержат знак доллара ($),
являются листами (например, Лист1$), другие объекты являются
именованными диапазонами Excel.
В некоторых ситуациях, особенно если источник данных Excel
представлен именем таблицы, а не запросом SELECT, столбцы
таблицы-получателя SQL Server переупорядочиваются по алфавиту.Для
получения дополнительных сведений о проблеме в работе поставщика данных
Jet щелкните следующий номер статьи базы знаний Майкрософт:
299484 (http://support.microsoft.com/kb/299484/RU/ )
Обнаружив, что в столбцах Excel содержатся числовые и текстовые
данные, поставщик данных Jet выбирает «доминирующий» тип данных и
возвращает NULL вместо значений другого типа.Для получения
дополнительных сведений об обходе этой проблемы щелкните следующий номер
статьи базы знаний Майкрософт:
194124 (http://support.microsoft.com/kb/194124/RU/ )

—————————————————-

Полезные ссылки:

306397 (http://support.microsoft.com/kb/306397/RU/ )

Использование Excel со связанными серверами SQL Server и распределенными запросами

299484 (http://support.microsoft.com/kb/299484/RU/ )

При использовании ADOX для получения столбцов таблицы Access столбцы упорядочиваются по алфавиту

194124 (http://support.microsoft.com/kb/194124/RU/ )

При использовании DAO OpenRecordset данным в Excel присваивается значение NULL

Используйте возможности SQL для создания запросов в Excel и напрямую к таблицам Excel

Порой таблицы Excel постепенно разрастаются настолько, что с ними становится неудобно работать. Поиск дубликатов, группировка, сложная сортировка, объединение нескольких таблиц в одну, т.д. — превращаются в действительно трудоёмкие задачи. Теоретически эти задачи можно легко решить с помощью языка запросов SQL… если бы только можно было составлять запросы напрямую к данным Excel.

Инструмент XLTools «SQL запросы» расширяет Excel возможностями языка структурированных запросов:

Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам
Автогенерация запросов SELECT и JOIN
Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
Создание запросов в интуитивном редакторе с подстветкой синтаксиса
Обращение к любым таблицам Excel из дерева данных

Перед началом работы добавьте «Всплывающие часы» в Excel

«SQL запросы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel

– пробный период дает 14 дней полного доступа ко всем инструментам.

Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами

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

Выделите диапазон данных На вкладке «Главная» нажмите Форматировать как таблицу Примените стиль таблицы.
Выберите таблицу Откройте вкладку «Конструктор» Напечатайте имя таблицы.
Напр., «КодТовара».
Повторите эти шаги для каждого диапазона, который планируете использовать в запросах.
Напр., «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.
Готово, теперь эти таблицы будут служить реляционной базой данных и готовы к SQL запросам.

Как создать и выполнить запрос SQL SELECT к таблицам Excel

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

Нажмите кнопку Выполнить SQL на вкладке XLTools Откроется окно редактора.
В левой части окна находится дерево данных со всеми доступными таблицами Excel.
Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).
Выберите целые таблицы или конкретные поля.
По мере выбора полей, в правой части редактора автоматически генерируется запрос SELECT.
Внимание: редактор запросов SQL автоматически подсвечивает синтаксис.
Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
Нажмите «Выполнить» Готово!

Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel

XLTools использует стандарт SQLite. Пользователи, владеющие языком SQLite, могут создавать самые разнообразные запросы:

LEFT JOIN – объединить две и более таблиц по общему ключевому столбцу
ORDER BY – сортировка данных в выдаче запроса
DISTINCT – удаление дубликатов из результата запроса
GROUP BY – группировка данных в выдаче запроса
SUM, COUNT, MIN, MAX, AVG и другие операторы

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

Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

Вы можете объединить несколько таблиц Excel в одну, если у них есть общее ключевое поле. Предположим, вам нужно объединить несколько таблиц по общему столбцу «КодТовара»:

Нажмите Выполнить SQL на вкладке XLTools Выберите поля, которые нужно включить в объединённую таблицу.
По мере выбора полей, автоматически генерируется запрос SELECT и LEFT JOIN.
Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
Нажмите «Выполнить» Готово! Объединённая таблица появится в считанные секунды.

Источник Excel — Службы интеграции SQL Server (SSIS)

15.05.2018
4 минуты на чтение

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) Среда выполнения интеграции SSIS в фабрике данных Azure

Источник Excel извлекает данные из листов или диапазонов в книгах Microsoft Excel.

Режимы доступа

Источник Excel предоставляет четыре различных режима доступа к данным для извлечения данных:

Таблица или представление.
Таблица или представление, указанное в переменной.
Результаты оператора SQL. Запрос может быть параметризованным запросом.
Результаты оператора SQL, хранящиеся в переменной.

Источник Excel использует диспетчер соединений Excel для подключения к источнику данных, а диспетчер соединений указывает файл книги для использования.Дополнительные сведения см. В разделе Диспетчер подключений Excel.

Источник Excel имеет один стандартный вывод и один вывод ошибок.

Конфигурация источника Excel

Вы можете установить свойства через конструктор служб SSIS или программно.

Диалоговое окно Advanced Editor отражает все свойства, которые могут быть установлены программно. Для получения дополнительных сведений о свойствах, которые можно установить в диалоговом окне Advanced Editor или программно, щелкните один из следующих разделов:

Для получения информации о циклическом просмотре группы файлов Excel см. Цикл по файлам и таблицам Excel с помощью контейнера цикла по каждому элементу.

Редактор исходного кода Excel (страница диспетчера подключений)

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

Примечание

Свойство CommandTimeout источника Excel недоступно в редакторе исходного кода Excel , но его можно установить с помощью расширенного редактора .Дополнительные сведения об этом свойстве см. В разделе «Источник Excel» настраиваемых свойств Excel.

Статические параметры

Диспетчер подключений OLE DB
Выберите существующий диспетчер подключений Excel из списка или создайте новое подключение, щелкнув Новый .

Новый
Создайте новый диспетчер подключений с помощью диалогового окна Диспетчер подключений Excel .

Режим доступа к данным
Укажите метод выбора данных из источника.

Значение	Описание
Таблица или вид	Извлеките данные из рабочего листа или именованного диапазона в файле Excel.
Имя таблицы или имя представления переменная	Укажите имя рабочего листа или диапазона в переменной. Информация, связанная с данной: Использование переменных в пакетах
Команда SQL	Получите данные из файла Excel с помощью запроса SQL.
Команда SQL из переменной	Укажите текст SQL-запроса в переменной.

Предварительный просмотр
Предварительный просмотр результатов с помощью диалогового окна Просмотр данных . Предварительный просмотр может отображать до 200 строк.

Динамические параметры режима доступа к данным

Режим доступа к данным = таблица или представление

Имя листа Excel
Выберите имя листа или именованный диапазон из списка доступных в книге Excel.

Режим доступа к данным = имя таблицы или переменная имени представления

Имя переменной
Выберите переменную, которая содержит имя рабочего листа или именованного диапазона.

Режим доступа к данным = команда SQL

Текст команды SQL
Введите текст SQL-запроса, создайте запрос, щелкнув Построить запрос , или перейдите к файлу, содержащему текст запроса, нажав Обзор .

Параметры
Если вы ввели параметризованный запрос с помощью? в качестве заполнителя параметра в тексте запроса используйте диалоговое окно Set Query Parameters для сопоставления входных параметров запроса с переменными пакета.

Построить запрос
Используйте диалоговое окно «Построитель запросов » для визуального построения SQL-запроса.

Обзор
Используйте диалоговое окно Открыть , чтобы найти файл, содержащий текст SQL-запроса.

Анализировать запрос
Проверить синтаксис текста запроса.

Режим доступа к данным = команда SQL из переменной

Имя переменной
Выберите переменную, содержащую текст SQL-запроса.

Редактор исходного кода Excel (страница столбцов)

Используйте страницу Столбцы диалогового окна редактора исходного кода Excel , чтобы сопоставить выходной столбец с каждым внешним (исходным) столбцом.

Опции

Доступные внешние столбцы
Просмотрите список доступных внешних столбцов в источнике данных. Вы не можете использовать эту таблицу для добавления или удаления столбцов.

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

Выходной столбец
Укажите уникальное имя для каждого выходного столбца. По умолчанию используется имя выбранного внешнего (исходного) столбца; однако вы можете выбрать любое уникальное описательное имя. Указанное имя будет отображаться в конструкторе служб SSIS.

Редактор исходного кода Excel (страница вывода ошибок)

Используйте страницу вывода ошибок диалогового окна редактора исходного кода Excel , чтобы выбрать параметры обработки ошибок и задать свойства столбцов вывода ошибок.

Опции

Ввод или вывод
Просмотрите имя источника данных.

Столбец
Просмотрите внешние (исходные) столбцы, выбранные на странице диспетчера подключений диалогового окна Редактор исходного кода Excel .

Ошибка
Укажите, что должно произойти при возникновении ошибки: игнорировать сбой, перенаправить строку или отказать компоненту.

Связанные темы: Обработка ошибок в данных

Усечение
Укажите, что должно произойти при усечении: игнорировать сбой, перенаправить строку или отказать компоненту.

Описание
Просмотрите описание ошибки.

Установите это значение для выбранных ячеек.
Укажите, что должно произойти со всеми выбранными ячейками при возникновении ошибки или усечения: игнорировать сбой, перенаправить строку или отказать компоненту.

Применить
Применить параметр обработки ошибок к выбранным ячейкам.

Связанное содержимое

Загрузка данных из или в Excel с помощью служб интеграции SQL Server (SSIS)
Назначение Excel
Диспетчер подключений Excel

Запустите мастер импорта и экспорта SQL Server — службы интеграции (SSIS)

18.11.2019
4 минуты на чтение

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) Среда выполнения интеграции SSIS в фабрике данных Azure

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

Важно

В этом разделе описано только, как запустить мастер . Если вы ищете что-то еще, см. Связанные задачи и контент.

Вы можете запустить мастер:

Предварительное условие — Установлен ли мастер на вашем компьютере?

Если вы хотите запустить мастер, но на вашем компьютере не установлен Microsoft SQL Server, вы можете установить мастер импорта и экспорта SQL Server, установив SQL Server Data Tools (SSDT). Дополнительные сведения см. В разделе Загрузка средств данных SQL Server (SSDT).

Примечание

Чтобы использовать 64-разрядную версию мастера импорта и экспорта SQL Server, необходимо установить SQL Server. SQL Server Data Tools (SSDT) и SQL Server Management Studio (SSMS) — это 32-разрядные приложения, которые устанавливают только 32-разрядные файлы, включая 32-разрядную версию мастера.

Меню Пуск

Запустите мастер импорта и экспорта SQL Server из меню «Пуск».

В меню Пуск найдите и разверните Microsoft SQL Server 20xx .
Выберите один из следующих вариантов.
- SQL Server 20xx Импорт и экспорт данных (64-разрядная версия)
- SQL Server 20xx Импорт и экспорт данных (32-разрядная версия)
Запустите 64-разрядную версию мастера, если вы не знаете, что для вашего источника данных требуется 32-разрядный поставщик данных.

Командная строка

Запустите мастер импорта и экспорта SQL Server из командной строки

В окне командной строки запустите DTSWizard.exe из одного из следующих расположений.

Запустите 64-битную версию мастера, если вы не знаете, что вашему источнику данных требуется 32-битный поставщик данных.

SQL Server Management Studio (SSMS)

Запустите мастер импорта и экспорта SQL Server из SQL Server Management Studio (SSMS)

В SQL Server Management Studio подключитесь к экземпляру ядра СУБД SQL Server.
Развернуть Базы данных .
Щелкните базу данных правой кнопкой мыши.
Укажите на Задачи .
Выберите один из следующих вариантов.

Если у вас не установлен SQL Server или у вас есть SQL Server, но не установлена SQL Server Management Studio, см. Раздел Загрузка SQL Server Management Studio (SSMS).

Visual Studio

Запустите мастер импорта и экспорта SQL Server из Visual Studio с SQL Server Data Tools (SSDT)

В Visual Studio с SQL Server Data Tools (SSDT) с открытым проектом служб Integration Services выполните одно из следующих действий.

В меню Project щелкните SSIS Import and Export Wizard .
— или —
В обозревателе решений щелкните правой кнопкой мыши папку SSIS Packages и выберите Мастер импорта и экспорта SSIS .

Если у вас не установлена Visual Studio или у вас есть Visual Studio, но не установлены инструменты данных SQL Server, см. Раздел Загрузка инструментов данных SQL Server (SSDT).

Получить мастера

Получение помощи во время работы мастера

Подсказка

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

Что дальше?

Когда вы запускаете мастер, первая страница — это Добро пожаловать в мастер импорта и экспорта SQL Server .Вам не нужно предпринимать никаких действий на этой странице. Дополнительные сведения см. В разделе Добро пожаловать в мастер импорта и экспорта SQL Server.

Связанные задачи и содержание

Вот еще несколько основных задач.

Импорт из Excel или экспорт в Excel с SSIS — SQL Server Integration Services (SSIS)

29.06.2018
13 минут на чтение

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) Среда выполнения интеграции SSIS в фабрике данных Azure

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

В следующих разделах содержится информация, необходимая для успешного использования Excel с SSIS, а также для понимания и устранения распространенных проблем:

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

Вы можете импортировать данные из Excel или экспортировать данные в Excel с помощью SSIS с помощью одного из следующих инструментов:

Получите файлы, необходимые для подключения к Excel

Прежде чем вы сможете импортировать данные из Excel или экспортировать данные в Excel с помощью SSIS, вам, возможно, придется загрузить компоненты подключения для Excel, если они еще не установлены.Компоненты подключения для Excel по умолчанию не устанавливаются.

Загрузите последнюю версию компонентов подключения для Excel здесь: Microsoft Access Database Engine 2016 Redistributable. Последняя версия компонентов может открывать файлы, созданные в более ранних версиях Excel.

Примечания к загрузке и установке

Убедитесь, что вы загрузили Access Database Engine 2016 Redistributable , а не Microsoft Access 2016 Runtime .
Если на компьютере уже установлена 32-разрядная версия Office, необходимо установить 32-разрядную версию компонентов. Вы также должны убедиться, что вы запускаете пакет SSIS в 32-битном режиме или запускаете 32-битную версию мастера импорта и экспорта.
Если у вас есть подписка на Microsoft 365, вы можете увидеть сообщение об ошибке при запуске установщика. Ошибка указывает на то, что вы не можете установить загружаемый файл одновременно с компонентами Office, запускаемыми нажми и работай.Чтобы обойти это сообщение об ошибке, запустите установку в тихом режиме, открыв окно командной строки и запустив файл .EXE, который вы загрузили с переключателем / quiet . Например:
C: \ Users \ <имя_пользователя> \ Downloads \ AccessDatabaseEngine.exe / quiet
Если у вас возникли проблемы с установкой распространяемого пакета 2016, установите вместо него распространяемый компонент 2010 отсюда: Распространяемый компонент Microsoft Access Database Engine 2010. (Excel 2013 не распространяется.)

Укажите Excel в качестве источника данных

Первый шаг — указать, что вы хотите подключиться к Excel.

в SSIS

В SSIS создайте диспетчер подключений Excel для подключения к исходному или целевому файлу Excel. Есть несколько способов создать диспетчер соединений:

В области Диспетчеры соединений щелкните правой кнопкой мыши и выберите Новое соединение . В диалоговом окне Добавить диспетчер подключений SSIS выберите EXCEL , а затем Добавить .
В меню SSIS выберите Новое соединение . В диалоговом окне Добавить диспетчер подключений SSIS выберите EXCEL , а затем Добавить .
Создайте диспетчер соединений одновременно с настройкой источника Excel или назначения Excel на странице Диспетчер подключений редактора исходного кода Excel или редактора назначения Excel .

В мастере импорта и экспорта SQL Server

В мастере импорта и экспорта на странице Выберите источник данных или Выберите место назначения выберите Microsoft Excel в списке Источник данных .

Если вы не видите Excel в списке источников данных, убедитесь, что у вас запущен 32-разрядный мастер. Компоненты связи с Excel обычно представляют собой 32-разрядные файлы и не отображаются в 64-разрядном мастере.

Файл Excel и путь к нему

Первое, что нужно предоставить, — это путь и имя файла Excel.Вы предоставляете эту информацию в редакторе диспетчера подключений Excel в пакете SSIS или на странице Выберите источник данных или Выберите место назначения мастера импорта и экспорта.

Введите путь и имя файла в следующем формате:

Для файла на локальном компьютере: C: \ TestData.xlsx .
Для файла в общей сетевой папке: \\ Sales \ Data \ TestData.xlsx .

Или нажмите Обзор , чтобы найти электронную таблицу с помощью диалогового окна Открыть .

Важно

Не удается подключиться к файлу Excel, защищенному паролем.

Excel версия

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

Выберите версию Microsoft Excel, которая использовалась для создания файла, или другую совместимую версию.Например, если у вас возникли проблемы с установкой компонентов подключения 2016, вы можете установить компоненты 2010 и выбрать Microsoft Excel 2007-2010 в этом списке.

Вы не сможете выбрать более новые версии Excel в списке, если у вас установлены только более старые версии компонентов подключения. Список Excel версии включает все версии Excel, поддерживаемые SSIS. Наличие элементов в этом списке не означает, что необходимые компоненты подключения установлены.Например, Microsoft Excel 2016 отображается в списке, даже если вы не установили компоненты подключения 2016.

Первая строка имеет имена столбцов

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

Если вы отключите этот параметр, поскольку исходные данные не содержат имен столбцов, мастер использует F1, F2 и т. Д. В качестве заголовков столбцов.
Если данные содержат имена столбцов, но вы отключили этот параметр, мастер импортирует имена столбцов как первую строку данных.
Если данные не содержат имен столбцов, но вы включили этот параметр, мастер использует первую строку исходных данных в качестве имен столбцов. В этом случае первая строка исходных данных больше не включается в сами данные.

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

Рабочие листы и диапазоны

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

Рабочий лист. Чтобы указать лист, добавьте символ $ в конец имени листа и добавьте разделители вокруг строки, например [Sheet1 $] . Или найдите имя, которое заканчивается символом $ в списке существующих таблиц и представлений.
Именованный диапазон. Чтобы указать именованный диапазон, укажите имя диапазона, например MyDataRange . Или найдите имя, которое не заканчивается символом $ в списке существующих таблиц и представлений.
Без названия. Чтобы указать диапазон ячеек, которые вы не назвали, добавьте символ $ в конец имени листа, добавьте спецификацию диапазона и добавьте разделители вокруг строки — например, [Sheet1 $ A1: B4] .

Чтобы выбрать или указать тип объекта Excel, который вы хотите использовать в качестве источника или назначения для данных, выполните одно из следующих действий:

в SSIS

В SSIS на странице диспетчера подключений редактора исходного кода Excel или редактора назначения Excel выполните одно из следующих действий:

Чтобы использовать рабочий лист или именованный диапазон , выберите Таблица или просмотрите в качестве режима доступа к данным .Затем в списке Имя листа Excel выберите рабочий лист или именованный диапазон.
Чтобы использовать безымянный диапазон , который вы указываете вместе с его адресом, выберите SQL-команду в качестве Режим доступа к данным . Затем в поле текста команды SQL введите запрос, как в следующем примере:
```
  ВЫБРАТЬ * ИЗ [Sheet1 $ A1: B5]
  
```

В мастере импорта и экспорта SQL Server

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

Когда вы импортируете из Excel, выполните одно из следующих действий:
- Чтобы использовать рабочий лист или именованный диапазон , на странице Укажите копию таблицы или страницу запроса выберите Копировать данные из одной или нескольких таблиц или представлений .Затем на странице Выбор исходных таблиц и представлений в столбце Источник выберите исходные рабочие листы и именованные диапазоны.
- Чтобы использовать безымянный диапазон , который вы указываете вместе с его адресом, на странице Укажите копию таблицы или страницу запроса выберите Написать запрос, чтобы указать данные для передачи . Затем на странице Provide a Source Query введите запрос, аналогичный следующему примеру:
```
  ВЫБРАТЬ * ИЗ [Sheet1 $ A1: B5]
  
```
Когда вы экспортируете в Excel, выполните одно из следующих действий:
- Чтобы использовать рабочий лист или именованный диапазон , на странице Выбор исходных таблиц и представлений в столбце Назначение выберите целевые листы и именованные диапазоны.
- Чтобы использовать безымянный диапазон , который вы указываете вместе с его адресом, на странице Выбор исходных таблиц и представлений в столбце Назначение введите диапазон в следующем формате без разделителей: Лист1 $ A1: B5 . Мастер добавляет разделители.

После выбора или ввода объектов Excel для импорта или экспорта вы также можете выполнять следующие действия на странице Выбор исходных таблиц и представлений мастера:

Проблемы с типами данных

Типы данных

Драйвер Excel распознает только ограниченный набор типов данных.Например, все числовые столбцы интерпретируются как двойные (DT_R8), а все строковые столбцы (кроме мемо-столбцов) интерпретируются как 255-символьные строки Unicode (DT_WSTR). SSIS сопоставляет типы данных Excel следующим образом:

Числовой — с плавающей запятой двойной точности (DT_R8)
Валюта — валюта (DT_CY)
Логическое — логическое (DT_BOOL)
Дата / время — datetime (DT_DATE)
String — строка Unicode, длина 255 (DT_WSTR)
Memo — текстовый поток Unicode (DT_NTEXT)

Преобразование типов данных и длины

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

Вот несколько примеров преобразований, которые могут потребоваться:

Преобразование между строковыми столбцами Unicode Excel и строковыми столбцами не-Unicode с определенной кодовой страницей.
Преобразование между 255-символьными строковыми столбцами Excel и строковыми столбцами разной длины.
Преобразование между числовыми столбцами Excel с двойной точностью и числовыми столбцами других типов.

Подсказка

Если вы используете мастер импорта и экспорта и вашим данным требуются некоторые из этих преобразований, мастер настраивает необходимые преобразования за вас. В результате, даже если вы хотите использовать пакет SSIS, может оказаться полезным создать исходный пакет с помощью мастера импорта и экспорта. Позвольте мастеру создавать и настраивать диспетчеры соединений, источники, преобразования и места назначения.

Проблемы с импортом

Пустые строки

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

Если над вашими данными есть пустые строки, вы не можете запросить данные как рабочий лист. В Excel вам нужно выбрать диапазон данных и присвоить ему имя, а затем запросить именованный диапазон вместо рабочего листа.

Отсутствующие значения

Драйвер Excel считывает определенное количество строк (по умолчанию восемь строк) в указанном источнике, чтобы угадать тип данных каждого столбца. Когда кажется, что столбец содержит смешанные типы данных, особенно числовые данные, смешанные с текстовыми данными, драйвер решает в пользу большинства типов данных и возвращает нулевые значения для ячеек, содержащих данные другого типа.(В равной степени числовой тип выигрывает.) Большинство параметров форматирования ячеек на листе Excel, похоже, не влияют на определение этого типа данных.

Вы можете изменить это поведение драйвера Excel, указав режим импорта для импорта всех значений в виде текста. Чтобы указать режим импорта, добавьте IMEX = 1 к значению Расширенные свойства в строке подключения диспетчера соединений Excel в окне «Свойства».

Усеченный текст

Когда драйвер определяет, что столбец Excel содержит текстовые данные, драйвер выбирает тип данных (строка или памятка) на основе самого длинного значения, которое он выбирает.Если драйвер не обнаруживает значений длиннее 255 символов в строках, которые он выбирает, он рассматривает столбец как столбец строки из 255 символов, а не столбец памятки. Следовательно, значения длиннее 255 символов могут быть усечены.

Чтобы импортировать данные из мемо-столбца без усечения, у вас есть два варианта:

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

Распространяемые компоненты версии	Ключ реестра
Excel 2016	HKEY_LOCAL_MACHINE \ SOFTWARE \ WOW6432Node \ Microsoft \ Office \ 16.0 \ Access Connectivity Engine \ Engines \ Excel
Excel 2010	HKEY_LOCAL_MACHINE \ SOFTWARE \ WOW6432Node \ Microsoft \ Office \ 14.0 \ Access Connectivity Engine \ Engines \ Excel

Проблемы с экспортом

Создать новый файл назначения

в SSIS

Создайте диспетчер подключений Excel, указав путь и имя нового файла Excel, который вы хотите создать. Затем в редакторе назначения Excel для Имя листа Excel выберите Новый , чтобы создать целевой рабочий лист. На этом этапе SSIS создает новый файл Excel с указанным листом.

В мастере импорта и экспорта SQL Server

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

Экспорт в достаточно большой ассортимент

Когда вы указываете диапазон в качестве места назначения, возникает ошибка, если в диапазоне столбцов меньше , чем исходные данные.Однако, если в указанном диапазоне строк меньше , чем исходные данные, мастер продолжит запись строк без ошибок и расширит определение диапазона, чтобы оно соответствовало новому количеству строк.

Экспорт длинных текстовых значений

Прежде чем вы сможете успешно сохранить строки длиной более 255 символов в столбце Excel, драйвер должен распознать тип данных целевого столбца как memo , а не string .

Если существующая таблица назначения уже содержит строки данных, то первые несколько строк, которые выбираются драйвером, должны содержать по крайней мере один экземпляр значения длиной более 255 символов в столбце memo.

Связанное содержание

Дополнительные сведения о компонентах и процедурах, описанных в этой статье, см. В следующих статьях:

О SSIS

Диспетчер подключений Excel
Источник Excel
Назначение Excel
Цикл по файлам и таблицам Excel с помощью контейнера цикла по каждому элементу
Работа с файлами Excel с помощью задачи сценария

О мастере импорта и экспорта SQL Server

Подключение к источнику данных Excel
Начните с этого простого примера мастера импорта и экспорта

Другие статьи

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

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

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

В этой статье с помощью мастера импорта и экспорта SQL Server будут объяснены шаги по импорту данных из файла Excel в базу данных 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. Последняя версия ядра СУБД Microsoft Access может открывать более раннюю версию Excel, поэтому убедитесь, что у вас установлена последняя версия.

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

AccessDatabaseEngine.exe — 32-разрядная версия
AccessDatabaseEngine_X64.exe — это 64-битная версия

Теперь, когда установлен распространяемый компонент Microsoft Access Database Engine 2016, мы должны увидеть поставщика 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 добавит имена столбцов, начиная с имени F1 для имени первого столбца:

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

Теперь, когда все настроено на странице «Выбор источника данных» мастера импорта и экспорта SQL Server, нажмите кнопку Далее , чтобы продолжить.

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

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

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

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

Обычно это предупреждающее сообщение появляется, когда мастер импорта и экспорта SQL Server запускается через SQL Server Management Studio (SSMS), который является 32-разрядным приложением, и запускается 32-разрядная версия мастера импорта и экспорта SQL Server, но вы установили 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 / тихий

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

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

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

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

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

В поле со списком Server name выберите экземпляр SQL Server:

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

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

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

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

Когда все настроено на странице «Выбор места назначения», нажмите кнопку « Далее », чтобы продолжить.

На странице «Указать копию таблицы или запрос» определите, как данные из источника данных будут скопированы в место назначения:

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

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

Если на странице «Выбор места назначения» выбран вариант «Записать запрос для указания данных для передачи», то при нажатии кнопки « Далее » будет отображаться страница «Предоставить исходный запрос»:

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

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

Не удалось проанализировать инструкцию.

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

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

Или это:

Не удалось проанализировать инструкцию.

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

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

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

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

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

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

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

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

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

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

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

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

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

Если ошибка возникает в процессе импорта данных из файла Excel в базу данных SQL Server, в столбце «Сообщение» появится слово 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. Чтобы решить эту проблему, вернитесь на страницу «Выбор исходных таблиц и представлений», выберите таблицы, у которых есть свойство идентификации, и нажмите кнопку Изменить сопоставления .В диалоговом окне 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: Началась последняя фиксация для вставки данных в «Назначение — test1».
(мастер импорта и экспорта SQL Server)
Информация 0x402090e0: поток данных Задача 1: завершена последняя фиксация для вставки данных в «Назначение — test1».
(мастер импорта и экспорта SQL Server)

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

Сообщения

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

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

ALTER TABLE test2 NOCHECK CONSTRAINT ALL

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

ALTER TABLE test2 WITH CHECK CHECK CONSTRAINT ALL

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

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

Сообщения

Предупреждение 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: Предупреждение: не удалось открыть глобальную разделяемую память для связи с производительной DLL; счетчики производительности потока данных недоступны.Чтобы решить эту проблему, запустите этот пакет от имени администратора или на системной консоли.
(мастер импорта и экспорта SQL Server)

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

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

Как импортировать файл Excel в SQL Server?

Как импортировать файл Excel в SQL Server? — Переполнение стека

Присоединяйтесь к Stack Overflow , чтобы учиться, делиться знаниями и строить свою карьеру.

Спросил
4 года, 8 мес назад

Просмотрено
271k раз

Закрыто .Этот вопрос должен быть более конкретным. В настоящее время он не принимает ответы.

Хотите улучшить этот вопрос? Обновите вопрос, чтобы он фокусировался только на одной проблеме, отредактировав это сообщение.

Закрыт 2 года назад.

У меня есть данные в файле Excel — фактически в формате xlsx, поскольку сейчас 2020 год.Мое требование — передать эти данные в SQL Server следующим образом:

ad hoc, вариант использования — загрузка таблиц с тестовыми данными или нечастые загрузки небольших объемов данных (скажем, <3k строк), и
Воспроизводимым, надежным и, возможно, автоматизированным способом для производственной системы.

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

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

Создан 21 сен.

SpyderSpyder

3,09922 золотых знака2121 серебряный знак1414 бронзовых знаков

Есть много статей о написании кода для импорта файла Excel, но это ручная / сокращенная версия:

Если вам не нужно импортировать файл Excel программно с помощью кода, вы можете сделать это очень быстро с помощью меню в SQL Management Studio .

Самый быстрый способ перенести файл Excel в SQL — использовать мастер импорта:

Откройте SSMS (Sql Server Management Studio) и подключитесь к базе данных, в которую вы хотите импортировать файл.
Импорт данных : в SSMS в обозревателе объектов в разделе «Базы данных» щелкните правой кнопкой мыши целевую базу данных, выберите «Задачи », «Импорт данных ». Появится мастер импорта (обычно вы можете просто нажать «Далее» на первом экране).

Следующее окно: « Выберите источник данных », выберите Excel :
- В раскрывающемся списке «Источник данных» выберите Microsoft Excel (этот параметр должен появиться автоматически, если у вас установлен Excel).
- Нажмите кнопку «Обзор», чтобы выбрать путь к файлу Excel, который вы хотите импортировать.
- Выберите версию файла Excel (97-2003 обычно подходит для файлов с расширением.XLS или используйте 2007 для новых файлов с расширением .XLSX)
- Установите флажок «Первая строка имеет заголовки», если ваш файл Excel содержит заголовки.
- Нажмите «Далее».

На экране « Выберите назначение » выберите базу данных назначения :
Выберите «Имя сервера», Аутентификацию (обычно имя пользователя и пароль sql) и выберите базу данных в качестве места назначения. Нажмите «Далее.

В окне « Укажите копию таблицы или запрос »:
- Для простоты просто выберите «Копировать данные из одной или нескольких таблиц или представлений» и нажмите «Далее».
‘ Выбор исходных таблиц: ‘ выберите рабочие листы из файла Excel и укажите целевую таблицу для каждого рабочего листа. Если у вас еще нет таблицы, мастер любезно создаст новую таблицу, которая соответствует всем столбцам вашей электронной таблицы. Нажмите «Далее.

Нажмите Готово.

Создан 21 сен.

SpyderSpyder

3,09922 золотых знака2121 серебряный знак1414 бронзовых знаков

Вы также можете использовать OPENROWSET для импорта файла Excel на сервере sql.

  ВЫБЕРИТЕ * В Your_Table ИЗ OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
                        'Excel 12.0; База данных = C: \ temp \ MySpreadsheet.xlsx',
                        'ВЫБРАТЬ * ИЗ [$ данных]')

Создан 21 сен.

Прафулла

48333 серебряных знака1616 бронзовых знаков

язык-sql

Stack Overflow лучше всего работает с включенным JavaScript

Ваша конфиденциальность

Нажимая «Принять все файлы cookie», вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

Принимать все файлы cookie

Настроить параметры

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Далее на шаге Укажите копию таблицы или Запрос доступны два варианта:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Из раздела Import by опция для Rows будет отмечена по умолчанию, а в разделе Header будет отмечена опция First row in range .Окно Preview показывает, как импортированные данные будут выглядеть в реальном времени. Когда все настроено, нажмите OK , чтобы продолжить:

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

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

В раскрывающемся списке Mapping выберите ранее выбранный файл Excel dbo.Export_data . Затем в разделе Таблица щелкните опцию Create new и в поле ниже введите имя новой таблицы. Наконец, в разделе Import mode нажмите на Insert new option:

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

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

Откроется окно сводки задания, при нажатии на кнопку Импорт будет запущен процесс импорта данных в базу данных SQL:

Откроется окно Просмотр результатов со всеми результатами.Это окно также позволяет пользователям Экспортировать результаты или Создать отчет , как показано ниже:

Вернитесь в SSMS, выполните команду Select Top 1000 Rows , щелкнув правой кнопкой мыши в обозревателе объектов с по и убедитесь, что импортированные данные в базу данных SQL были успешными:

27 января 2021 г.

Как вставить данные из Excel в SQL Server

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

Фон
Другие инструменты и методы
Как вставить данные из Excel в SQL Server — пошаговые инструкции
Вставить данные в таблицу SQL Server со столбцом идентификаторов
Скопировать и вставить данные из Excel в SQL Server Представления
Советы, когда копирование данных из Excel на SQL-сервер
Excel на SQL Server на удаленном компьютере
Excel на производительность SQL Server
Требования и ограничения
Сводка — вставка данных из Excel в SQL Server
Excel в SQL Server — краткий справочник

Фон

До того, как я основал SQL Spreads (надстройку Excel для использования Excel для обновления данных в SQL Server), я много лет работал консультантом по бизнес-аналитике, используя инструменты бизнес-аналитики Microsoft, такие как SQL Server, SSIS, Reporting Services, Excel. , так далее.

Я обнаружил, что, работая над разными проектами, я часто вспоминаю несколько важных вещей, которые я могу использовать снова и снова. Одна из тех вещей, которые я повторно использую почти в каждом проекте, — это возможность копировать и вставлять данные из Excel в таблицу в SQL Server.

Это действительно простой и удобный способ быстро вставить данные в таблицу в SQL Server — например, заполнить новую таблицу измерений, добавить некоторые тестовые данные или ввести любые другие данные, которые вам нужно быстро поместить в таблицу в 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. Плюсы включают гибкость и множество настроек для точной настройки импорта. Самый большой недостаток заключается в том, что вам нужно запускать дюжину диалогов Мастера с множеством настроек каждый раз, когда вам нужно импортировать данные. Более подробную информацию о мастере импорта можно найти здесь.
SSIS — это нефтяной танкер для перемещения данных между разными источниками — вы можете выполнять практически любую задачу, которая вам нравится, но вам нужно будет потратить много времени, чтобы начать работу, и еще больше времени потребуется для поддержки и изменения решение по линии.К плюсам можно отнести универсальность и множество доступных функций; главный минус — это время, которое вам придется потратить на изучение инструмента. Более подробную информацию о SSIS можно найти здесь.
Утилита BCP — инструмент на основе командной строки, который предлагает огромное количество настроек — если вы программист, это инструмент, который нужно использовать. Более подробную информацию об утилите BCP можно найти здесь.

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

Преимущество описанной ниже техники «копирования и вставки» заключается в том, что она действительно быстрая и работает сразу после установки с использованием стандартных Excel и SQL Server Management Studio. Он также может обрабатывать таблицы с идентификационными столбцами, и вы можете использовать его для нескольких тысяч строк данных с разумным временем загрузки.

Как вставить данные из Excel в SQL Server — пошаговые инструкции

Откройте SQL Server Management Studio и подключитесь к базе данных SQL Server.
Разверните папки Databases и Tables для таблицы, в которую вы хотите вставить данные из Excel.
Щелкните таблицу правой кнопкой мыши и выберите четвертый вариант — Изменить первые 200 строк .
Данные будут загружены, и вы увидите первые 200 строк данных в таблице.
Переключитесь в Excel и выберите строки и столбцы для вставки из Excel в SQL Server.
Щелкните выделенные ячейки правой кнопкой мыши и выберите Копировать .
Вернитесь в SQL Server Management Studio, прокрутите вниз до последней строки внизу и найдите строку со звездочкой в крайнем левом столбце.
Щелкните правой кнопкой мыши звездочку в заголовке столбца и выберите Вставить .
Ваши данные из Excel теперь вставлены в вашу таблицу в SQL Server!

Помните : всегда начинайте с копирования и вставки одной строки данных из Excel в SQL Server. Это необходимо для проверки того, что между вашими данными из Excel и таблицы SQL Server нет несоответствий (например, количество столбцов) и что ваши данные в Excel соответствуют типам данных в таблице SQL Server.См. Раздел «Советы и хитрости» ниже для получения более подробной информации.

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

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

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

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

Откройте SQL Server Management Studio и подключитесь к базе данных SQL Server.
Разверните папки Databases и Tables для таблицы, в которую вы хотите вставить данные Excel.
Щелкните правой кнопкой мыши имя таблицы и выберите Edit Top 200 Rows , четвертый вариант сверху.
Появится сетка с первыми 200 строками данных в таблице.
Переключитесь в Excel и выберите строки и столбцы для копирования. Не включайте строку заголовка.
Теперь, также не забудьте включить дополнительный пустой крайний левый столбец в свой выбор .
Затем щелкните правой кнопкой мыши выбранные ячейки и выберите Копировать .
Вернитесь в SQL Server Management Studio и выберите вкладку с 200 строками из таблицы.
Перейдите к последней строке внизу и найдите строку со звездочкой в крайнем левом столбце .
Щелкните правой кнопкой мыши звездочку и выберите Вставить .
Ваши данные из Excel теперь вставлены в вашу таблицу в SQL Server, и SQL Server автоматически создаст значения в столбце ID / key для вас:

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

Представленный метод копирования и вставки для вставки данных из Excel в SQL Server также отлично работает, когда вам нужно вставить данные в представление 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 Server. :

Значения 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 на удаленном компьютере

При работе с базами данных 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 вам потребуются установленные Microsoft Excel и SQL Server Management Studio.Вы можете скачать бесплатную версию SQL Server Management studio здесь.

Копирование данных из Excel в SQL Server отлично подходит для нескольких тысяч строк данных с разумным временем загрузки.

Метод копирования и вставки может использоваться как для таблиц с бизнес-ключами, так и для таблиц с автоматически увеличивающимися ID-ключами.

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

Сводка по

— вставка данных из Excel в SQL Server

Вставка данных из Excel в SQL Server с помощью функции копирования и вставки — действительно простой и удобный способ быстро получить данные в базе данных без необходимости запускать и настраивать какой-либо специальный инструмент. Функцию копирования и вставки можно использовать в сценариях, содержащих до нескольких десятков тысяч строк данных.

Метод копирования и вставки также отлично работает для таблиц с автоматически увеличивающимся ключом идентификации — просто не забудьте добавить дополнительный пустой столбец слева от столбцов данных, как описано выше.

Его также можно использовать при работе с Excel на локальном компьютере и SQL Server на удаленном компьютере с помощью подключения к удаленному рабочему столу.

Excel для SQL Server — краткий справочник:

Сначала скопируйте данные из Excel, а затем вставьте их в таблицу SQL Server, используя пункт меню База данных > Таблица > Изменить верхние 200 строк .
Всегда начинайте с копирования и вставки одной строки данных для проверки типов данных.
Для таблиц SQL Server со столбцом идентификаторов добавьте дополнительный пустой крайний левый столбец перед копированием из Excel.
Копирование и вставка из Excel в SQL Server может использоваться для вставки до нескольких десятков тысяч строк данных.
No related posts.

Ноябрь 2024
Пн	Вт	Ср	Чт	Пт	Сб	Вс
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

Как загрузить данные в sql из excel: Импорт данных из Excel в SQL — SQL Server

Ничего не найдено для Import Excel In Ms Sql Server%23Shag 1 Proveryaem Nalichie Provaydera Microsoft

Ничего не найдено для Import Excel In Ms Sql Server%23Shag 4 Vypolnyaem Sql Zapros Obraschenie

Ничего не найдено для Import Excel In Ms Sql Server%23Shag 6 Vypolnyaem Sql Zapros Obraschenie

Import Excel столбцов электронной таблицы в базу данных SQL Server

15 ответов

Похожие вопросы:

Немножко всего .. из жизни администратора ms sql server

Импорт данных из Excel в SQL Server

Импорт данных из Excel в SQL Server

Требования перед началом

Описание

Примеры

Import или Append

Использование DTS или SSIS

Использование связанного сервера

Использование распределенных запросов

Использование ADO и SQLOLEDB

Использование ADO и Поставщика данных Jet

Устранение неполадок

Используйте возможности SQL для создания запросов в Excel и напрямую к таблицам Excel

Перед началом работы добавьте «Всплывающие часы» в Excel

Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами

Как создать и выполнить запрос SQL SELECT к таблицам Excel

Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel

Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

Источник Excel — Службы интеграции SQL Server (SSIS)

В этой статье

Режимы доступа

Конфигурация источника Excel

Редактор исходного кода Excel (страница диспетчера подключений)

Статические параметры

Динамические параметры режима доступа к данным

Режим доступа к данным = таблица или представление

Режим доступа к данным = имя таблицы или переменная имени представления

Режим доступа к данным = команда SQL

Режим доступа к данным = команда SQL из переменной

Редактор исходного кода Excel (страница столбцов)

Опции

Редактор исходного кода Excel (страница вывода ошибок)

Опции

Связанное содержимое

Запустите мастер импорта и экспорта SQL Server — службы интеграции (SSIS)

В этой статье

Предварительное условие — Установлен ли мастер на вашем компьютере?

Меню Пуск

Запустите мастер импорта и экспорта SQL Server из меню «Пуск».

Командная строка

Запустите мастер импорта и экспорта SQL Server из командной строки

SQL Server Management Studio (SSMS)

Запустите мастер импорта и экспорта SQL Server из SQL Server Management Studio (SSMS)

Visual Studio

Запустите мастер импорта и экспорта SQL Server из Visual Studio с SQL Server Data Tools (SSDT) ​​

Получить мастера

Получение помощи во время работы мастера

Что дальше?

Связанные задачи и содержание

Импорт из Excel или экспорт в Excel с SSIS — SQL Server Integration Services (SSIS)

В этой статье

Получите файлы, необходимые для подключения к Excel

Примечания к загрузке и установке

Укажите Excel в качестве источника данных

в SSIS

В мастере импорта и экспорта SQL Server

Файл Excel и путь к нему

Excel версия

Первая строка имеет имена столбцов

Рабочие листы и диапазоны

в SSIS

В мастере импорта и экспорта SQL Server

Проблемы с типами данных

Типы данных

Преобразование типов данных и длины

Проблемы с импортом

Пустые строки

Отсутствующие значения

Усеченный текст

Проблемы с экспортом

Создать новый файл назначения

в SSIS

Запустите мастер импорта и экспорта SQL Server из Visual Studio с SQL Server Data Tools (SSDT)