Excel sql: Passing Dynamic Query Values from Excel to SQL Server

SQL VBA Excel

Работа с внешними источниками данных

Материалы по работе с внешними источниками данных на примере Excel и SQL.

Рассмотрим способы передачи данных между Excel и внешней базой данной на SQL сервере с помощью ADO.

Задача первая. Подключаемся к внешней базе данных.

Для начала надо подключиться к внешней базе данных. Подключение возможно если на компьютере установлен драйвер. Список установленных драйверов для подключения к базам данных на компьютере под управлением Windows:

Панель управления\Все элементы панели управления\Администрирование\Источники данных (ODBC)

Проверить подключение к базе данных можно простым способом. Создаем пустой файл (например, «текстовый документ.txt»), затем изменяем имя и расширение на .udl (например, «connect.udl»). Двойной клик мышкой по новому файлу, далее приступаете к настройке и проверке подключения к базе данных. После того, как удалось настроить корректное подключение к базе данных, сохраняем файл «connect. udl». Открываем файл «connect.udl» обычным текстовым редактором (например, блокнотом), и видим в строке подключения все необходимые параметры. Про подключение к внешним базам данных можно посмотреть на ресурсе ConnectionStrings .

Теперь возвращаемся к нашему VBA для Excel. В редакторе VBA подключаем последнюю версию библиотеки:

 Microsoft ActiveX Data Objects Library

Пример кода:

Sub TestConnection()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "" 'Параметры строки подключения
cn.Open   'Открываем подключение
cn.Close   'Закрываем подключение
Set cn = Nothing   'Стираем объект из памяти
End Sub

Задача вторая. Загружаем данные из внешней базы данных на SQL сервере в Excel.

После того, как мы установили подключение к внешней базе данных можно приступать к чтению данных и выводу в Excel. Здесь потребуется знание языка запросов SQL. В результате выполнения SQL запроса к нам возвращается некая таблица с данными в объект RecordSet. Далее из объекта RecordSet можно выгружать данные непосредственно на лист или в сводную таблицу.

Пример кода простой процедуры:

Sub LoadData()

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
Set rst = New ADODB.Recordset

cn.ConnectionString = "" 'Параметры строки подключения
cn.Open

rst.Open "SELECT TOP 10 * FROM <таблица>", cn 'SQL-запрос, подключение

ActiveSheet.Range("A1").CopyFromRecordset rst 'Извлекаем данные на лист

rst.Close
cn.Close

Set rst = Nothing
Set cn = Nothing

End Sub

Для удобства работы. Предлагаю создать собственный класс «tSQL» для работы с базой данных.  У класса будет одно свойство:

Public ConnectionSring As String

Для чтения данных напишем метод SelectFrom с параметрами TableName и ws. TableName — это имя таблицы, откуда будем считывать данные и ws — лист Excel, куда будем записывать данные.

Public Sub SelectFrom(TableName As String, ws As Worksheet)

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim SQLstring As String
Dim i As Long

Set cn = New ADODB.Connection
Set rst = New ADODB.Recordset
SQLstring = "SELECT * FROM " & TableName
ws.Cells.Clear

cn.ConnectionString = ConnectionSring
cn.Open

rst.Open SQLstring, cn

For i = 1 To rst.Fields.Count
 ws.Cells(1, i) = rst.Fields(i - 1).Name
Next i
ws.Range("A2").CopyFromRecordset rst

rst.Close
cn.Close

Set rst = Nothing
Set cn = Nothing
SQLstring = Empty
i = Empty

End Sub

Пример использования класса tSQL в процедуре

Sub mySQL()
Dim ts As tSQL
Set ts = New tSQL

ts.ConnectionSring = '<Строка подключения>
ts.SelectFrom "Название таблицы", ActiveSheet

Set ts = Nothing
End Sub

Задача третья. Загружаем данные из Excel во внешнюю базу данных.

Для записи данных напишем метод InsertInto с параметрами TableName. rHead и rData. TableName — это имя таблицы, куда будем добавлять данные;  rHead — диапазон ячеек, с указанием полей; rData — диапазон ячеек с данными, которые будем добавлять.

Public Sub InsertInto(TableName As String, rHead As Range, rData As Range)

Dim cn As ADODB.Connection
Dim SQLstring As String
Dim SQLstringH As String
Dim SQLstringV As String
Dim i As Long
Dim j As Long

Dim arrHead()
Dim arrData()

arrHead = rHead.Value
arrData = rData.Value
Set cn = New ADODB.Connection
cn.ConnectionString = ConnectionSring
cn.Open

SQLstringH = "INSERT INTO " & TableName & "("
For j = LBound(arrHead, 2) To UBound(arrHead, 2)
 SQLstringH = SQLstringH & " " & arrHead(1, j)
 If j < UBound(arrHead, 2) Then
 SQLstringH = SQLstringH & ","
 Else
 SQLstringH = SQLstringH & ")"
 End If
Next j
SQLstringH = SQLstringH & " VALUES("

For i = LBound(arrData, 1) To UBound(arrData, 1)
 For j = LBound(arrData, 2) To UBound(arrData, 2)
 SQLstringV = SQLstringV & " " & arrData(i, j)
 If j < UBound(arrHead, 2) Then
 SQLstringV = SQLstringV & ","
 Else
 SQLstringV = SQLstringV & ") "
 End If
 Next j
 SQLstring = SQLstringH & SQLstringV
 SQLstringV = Empty
 cn. Execute SQLstring
Next i
cn.Close

Set cn = Nothing
SQLstring = Empty
i = Empty
j = Empty
SQLstring = Empty
SQLstringH = Empty
SQLstringV = Empty
Erase arrHead
Erase arrData

End Sub

Пример использования класса tSQL в процедуре

Sub mySQL()
Dim ts As tSQL
Set ts = New tSQL

ts.ConnectionSring = '<Строка подключения>
ts.InsertInto "Название таблицы", Range("B1:D1"), Range("B8:D300")

Set ts = Nothing
End Sub

Задача четвертая. Управляем внешней базой данных из Excel

Рекомендую использовать запросы в основном для чтения данных из внешней БД. Можно записывать данные в таблицы внешней БД.
Но крайне не желательно использовать Excel для управления внешней базой данных, лучше использовать стандартные средства разработки.

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

Data from Excel to SQL 

 http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba. htm

Получние данных для Excel с помощью запроса к базе данных

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

Для MS SQL

Sub Get_MSSQL_Data()
 Dim db As ADODB.Connection
 Dim rs As ADODB.Recordset
 Dim sqlStr As String
 Set rs = CreateObject("ADODB.Recordset")
 Set db = New ADODB.Connection
 db.Open _
  "DRIVER={SQL Server};SERVER=SName;UID=UserName;PWD=Password;DATABASE=DBName"
 
 sqlStr = "SELECT Count(*) as cnt FROM [DBName].[DB].[Table]"
 rs.Open sqlStr, db
 
 While Not rs.EOF
    str1 = rs.Fields("cnt").Value
    rs.MoveNext
    Wend
 rs.Close
 db.Close
End Sub

Sub Get_MSSQL_Data()
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqlStr As String
Set rs = CreateObject(«ADODB.Recordset»)
Set db = New ADODB. Connection
db.Open _
«DRIVER={SQL Server};SERVER=SName;UID=UserName;PWD=Password;DATABASE=DBName»

sqlStr = «SELECT Count(*) as cnt FROM [DBName].[DB].[Table]»
rs.Open sqlStr, db

While Not rs.EOF
str1 = rs.Fields(«cnt»).Value
rs.MoveNext
Wend
rs.Close
db.Close
End Sub

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

"DRIVER={SQL Server};SERVER=SName;UID=UserName;PWD=Password;DATABASE=DBName"

«DRIVER={SQL Server};SERVER=SName;UID=UserName;PWD=Password;DATABASE=DBName»

Teradata ODBC Driver

на следующий вариант для Teradata:

"Provider=Teradata;DBCName=DbcName;Database=DBName;Uid=UserName;Pwd=Password"

«Provider=Teradata;DBCName=DbcName;Database=DBName;Uid=UserName;Pwd=Password»

OLE DB Provider for Oracle

на следующий вариант для Oracle:

"Provider=OraOLEDB. Oracle;Data Source=DWH;User ID=UserName;Password=Password;"

«Provider=OraOLEDB.Oracle;Data Source=DWH;User ID=UserName;Password=Password;»

Другие варианты

User-defined type not defined

Для того, чтобы при выполнении кода не возникало ошибки «Compile error: User-defined type not defined»:

Нужно зайти в Tools->Reference и выбрать следующие пункты:

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

sql запрос из excel « NC’s Blog

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

6 мая, 2009

Posted by nikpro |
Администрирование и Настройка | ActiveX, ADO, ado select excel, adodb подключение к sql server vb, DSN, Администрирование, импорт в ms sql, импорт данные из excel в datasource tabl, импорт excel в mssql, импорт excel в sql server 2005, перенос данных из sql в excel, получение данных из excel в access, пример подключения к sql visual basic, программа для переноса excel в .mdf, программа для переноса excel в .sql, excel в mssql, from openrowset excel, insert into import from excel, Microsoft OLE, Microsoft SQL Server, MS Office Exel, ms sql импорт из excel связанный сервер, ms sql связанные сервера xls excel, MS Windows, mssql excel получить имена столбцов, создать поставщик microsoft jet ole db, строка подключения к sql server, строка подключения excel, ODBC, open dts sql sever 2008, openquery, openquery excel, openquery sql server, select * from excel, SQL 2005, SQL 2008, sql запрос из excel, sql мастер импорта данных, SQL Server, Visual Basic, xls перевести данные в sql, xls select |
12 комментариев

Экспорт данных из базы oracle в excel – Мои IT-заметки

Классическая задача – есть база с данными. Данные эти заказчик желает лицезреть в виде красиво оформленных экселек заданного вида. Мало того, что форматирование шаблонов не подлежит обсуждению – в них, для полного счастья, натыканы не только эксельные формулы но и vba-макросы. А сервер может быть как на виндах, так и на никсах, на которых эксели и вовсе не предусмотрены. Как бы это все половчее организовать?

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

1 вариант: кросс-платформенно, какая база – не принципиально, на сервер ничего офисо-подобного ставить не надо.

Данные дергаем чем удобней – расписывать это не буду, зависит от ситуации. Для формирования отчетности в excel можно воспользоваться библиотеками:

на Java:

JExcelApi,  пример использования – http://www.vogella.de/articles/JavaExcel/article.html
Apache POI  примеры использования подробно расписаны на сайте. Хотя вот тоже неплохой пример на русском.

на Perl:

Используя пакет Excel-Writer-XLSX (бывшие Spreadsheet::WriteExcel и Spreadsheet::ParseExcel) подробнейшие примеры использования поставляются вместе с пакетом.

Из минусов подобного подхода – когда последний раз пробовал – не удалось корректно отображать всю информацию в диаграммах, vba-содержимое файлов-шаблонов страдало. Возможно, сейчас эти недочеты уже подправлены. В любом случае, для большинства отчетов вышеупомянутых библиотек будет более чем достаточно.

2 вариант: воткнуть куда-нибудь Open Office и при необходимости использовать OOO SDK, например, через жаву. Официальные сниппеты валяются тут. Т.е. схема взаимодействия следующая – сервер получает запрос на формирование отчета, он подготавливает данные и перенаправляет их на сторонний сервис на том же сервере или другом. Там используя OpenOffice создается excel файл и возвращается основному серверу, который уже возвращает их клиенту. Из минусов – опен офис таки тяжеловат, на перекачивание данных туда-сюда будет уходить некоторое время. Из плюсов – проблем совместимости содержимого эксель-шаблонов не встречал – макросы, формулы, форматирование – работает все.  Ну и опять же кроссплатформенно получается – благо опен офис вполне себе фурычит на никсах, виндах, да маках.

3  вариант: Аналогично варианту 2, однако, при необходимости создать документ эксель, оракловый сервер обращается к вашему самописному сервису на винде, где установлен эксель, предоставляя данные. Для формирования задействован “родной” sharp. Из минусов – таки нужен ексель и винда. Из плюсов – проблемы совместимости исключены.

Оракло-специфичные:

4 вариант: если оракл версии 11 – самый оптимальный – использовать Oracle Bi Publisher – о том как преобразовать пользовательские шаблоны к вашим данным он позаботиться сам. Ваша задача адаптировать их с помощью встроенных средств внести соответствующие изменения в механизм формирования отчетов для клиентов.

5 вариант: если версия оракла не позволяет воспользоваться возможностями Bi Publisher, а скупость – использовать готовые пакеты для этих целей  – придется ваять таки ручками на pl/sql. Снипеты водятся в интернетах. (http://akdora.wordpress.com/2009/02/06/how-to-write-excel-via-plsql-and-save-the-file-to-a-directory/, http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:728625409049, PL/SQL ExcelDocumentType)

Как использовать операторы SQL в MS Excel | Small Business

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

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

Щелкните «Данные» на ленте Excel. Щелкните значок «Из других источников» в разделе «Получить внешние данные». В раскрывающемся меню выберите «Из Microsoft Query».

Щелкните тип источника данных в окне «Выбор источника данных».Щелкните и включите параметр «Использовать мастер запросов для создания / редактирования запросов», а затем щелкните «ОК». Сначала появится окно «Подключение к базе данных», а через несколько секунд откроется окно браузера «Выбор файла базы данных».

Найдите папку и файл для вашей базы данных или файла источника данных. Выделите имя файла источника данных и нажмите «ОК». На экране появится окно мастера запросов.

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

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

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

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

Нажмите «ОК». Excel отображает таблицу с извлеченными записями, которые возвращаются мастером запросов в результате базового SQL-запроса источника данных.

Просмотрите запрос SQL, чтобы увидеть, как код извлекает данные из внешнего источника данных. Щелкните «Существующие подключения» на вкладке «Данные». Щелкните значок «Запрос от типа источника данных» в разделе «Подключения в этой книге» окна «Существующие подключения». На экране появится окно импорта данных.

Щелкните кнопку «Свойства». В окне «Свойства подключения» щелкните вкладку «Определение». Найдите текст в текстовом поле команды. Там появляется собственный код SQL-запроса. Для SQL-запроса, который извлекает записи из внешней таблицы с меткой «Personal_Contacts», код будет выглядеть примерно так: SELECT tbl_Personal_Contscts.ID, tbl_Personal_Contacts.Contact_Name, tbl_Personal_Conts.Phone_Number, tbl_Personal_Contscts.Email_Address Users FROM \ Name C: \ Documents \ Database1.accdb .tbl_Personal_Contacts tbl_Personal_Contacts

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

Excel SQL AddIn — создание SQL-запросов в Excel (MS Query)

Загрузка …

SQL (MS Query) — это НАТУРАЛЬНАЯ функция Excel . Так зачем использовать надстройку? Что ж … Microsoft хорошо поработала, сделав использование SQL в Excel как можно сложнее и неудобнее с помощью вкладки ленты Data . Следовательно, создание MS QueryTables или выполнение простых команд SQL, таких как UPDATE, неясно и трудно использовать ежедневно. Здесь входит надстройка Excel SQL.

СКАЧАТЬ

Надстройка SQL

Excel — это простая в использовании слабосвязанная база данных . Мы склонны забывать об этом. Часто многие обращаются к макросам VBA, сложным сводным таблицам или просто сложным формулам Excel для решения задач, которые в противном случае были бы простыми, если бы мы посмотрели на Excel с точки зрения базы данных SQL. Почему нет? Многие из вас, вероятно, не знают, что Excel на самом деле поддерживает запросы SQL ! Если вы все еще не понимаете возможностей — читайте дальше, вас ждет угощение. Использование SQL в Excel позволит обрабатывать запросов данных за секунды по сравнению с кодом VBA, который может обрабатывать данные даже за час! Чтобы сделать SQL более доступным в повседневных задачах, я создал эту надстройку Excel SQL, которая в основном заменяет несколько громоздкий мастер From Microsoft Query , доступный на вкладке ленты данных .
Итак, давайте сразу перейдем к этому добру…

Введение

Начнем с представления элементов управления ленты:
Лента надстройки SQL
Надстройка SQL Excel добавляет дополнительную вкладку ленты на ленту Excel. Это надстройка (полностью VBA) без заблокированных функций, которая упрощает процесс создания пользовательских SQL-запросов (MS Query) на основе драйверов данных OLEDB. Более того, надстройка SQL оснащена предопределенными быстрыми SQL-запросами для определенных заданий, например. например, объединение листов или сравнение листов (различие — поиск различий).

Важно помнить — Все SQL-запросы, созданные с помощью надстройки SQL, будут работать БЕЗ надстройки!

Запрос SQL SELECT

Надстройка поддерживает несколько драйверов OLEDB. Могут быть выполнены следующие типы запросов:

1. Запустить SQL из Excel (Jet.OLEDB) (xls) — позволяет запускать SQL-запросы к данным Excel в более старом формате, например Excel 2003 (также на основе данных из других листов или книг Excel)
2. Запуск SQL из Excel (Ace. OLEDB) (xlsx, xlsm, xlsb) — позволяет запускать SQL-запросы к данным Excel в новом формате файлов Excel, например. Excel 2007 и выше (также на основе данных в других таблицах или книгах Excel)
3. Запускать SQL из MS SQL (SQLOLEDB) — позволяет запускать SQL-запросы к данным MS SQL (из баз данных MS SQL в
   таблицы запросов Excel)
4. Выполнить SQL (UPDATE или INSERT) в текущем файле Excel (Ace.OLEDB) — позволяет запускать SQL-запросы UPDATE или INSERT в текущей книге e.грамм. добавить дополнительные строки из строк SELECT или UPDATE на лист на основе данных из других листов или книг

Объединить листы

Эта функция позволяет объединить любое количество рабочих листов (путем множественного выбора) в один объединенный рабочий лист. Условие состоит в том, что на всех листах должны быть одинаковые столбцы. Надстройка SQL создаст SQL-запрос, который можно обновить в любое время.

Пример

Нажмите кнопку Объединить рабочие листы , чтобы открыть следующую пользовательскую форму:
SQL AddIn: Объединение рабочих листов
Затем укажите диапазон вывода , в котором вы хотите создать запрос, выберите рабочие листы, которые хотите объединить, и нажмите Объединить .

Найдите различия между листами

Эта функция позволяет сравнивать 2 листа, если их столбцы идентичны (и имена заголовков идентичны).

Пример

Нажмите кнопку Найти различия между рабочими листами , чтобы открыть следующую пользовательскую форму:
SQL AddIn: Diffing 2 рабочих листа (поиск различий)
Укажите выходной диапазон , в котором вы хотите создать запрос, выберите 2 листа, которые вы хотите сравнить .Затем выберите ключевой столбец, в котором необходимо соединить 2 листа / сделать перекрестную ссылку, и щелкните Diff .

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

Ссылка на внешний лист Excel

Чтобы импортировать (и связать) внешний рабочий лист, просто щелкните Связать внешний рабочий лист Excel . Появится следующая форма:
Надстройка SQL: импорт внешнего листа Excel
Укажите выходной диапазон , в котором вы хотите создать запрос, укажите путь к файлу Excel (или щелкните . .. , чтобы открыть диалоговое окно файла) и нажмите Загрузить рабочие листы . Затем выберите рабочий лист, который вы хотите импортировать, и нажмите Импорт .

Ссылка на лист внешнего доступа

Чтобы импортировать (и связать) внешнюю таблицу доступа, просто щелкните Связать внешнюю таблицу доступа . Появится следующая форма:
SQL AddIn: импорт внешней таблицы Access
Укажите выходной диапазон , в котором вы хотите создать запрос, укажите путь к файлу базы данных Access (или щелкните ... , чтобы открыть диалоговое окно файла) и нажмите Загрузить таблицы . Затем выберите таблицу, которую вы хотите импортировать, и нажмите Import .

Обновление / редактирование запросов

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

Щелкните правой кнопкой мыши таблицу запроса, чтобы изменить / обновить:
Обновление или редактирование запроса SQL

Нажмите Обновить , чтобы обновить (перезагрузить) запрос, или Изменить запрос , чтобы изменить запрос SQL или изменить свойства запроса.

Примеры SQL в Excel

Хотя и сильно недооценен, SQL — мощный инструмент , особенно в Excel. Все эти функции встроены в Excel, надстройка просто упрощает жизнь при создании запросов MS. Предполагая, что у вас установлена ​​надстройка SQL, давайте поиграемся с некоторыми базовыми примерами…

Hello World в SQL

Начнем с первого примера. Предположим, у нас есть простой лист, содержащий 2 столбца — Col1 и Col2. Если вы хотите создать запрос, который будет фильтровать только те элементы, в которых значение Col2 равно «Name1», выполните следующие действия:

Нажмите кнопку Выполнить и заполните форму:

Выполнить SQL из MS Excel: Шаг 1

Нажмите Выполнить запрос , чтобы запустить запрос и вставить результаты:

Запустить SQL из MS Excel: Шаг 2

Другие примеры

Excel SQL, пример 1: загрузка данных из внешнего файла Excel

ВЫБРАТЬ * ИЗ `C: Data.xlsx`.`Sheet1 $ `

ВЫБРАТЬ * ИЗ `C: Data. xlsx`.`Sheet1 $`

Excel SQL, пример 2: объединение двух внешних файлов
Поиск записей, повторяющихся в обеих книгах

ВЫБЕРИТЕ s1.Name ИЗ `C: Data1.xlsx`.`Sheet1 $` как s1
INNER JOIN `C: Data2.xlsx`.`Sheet1 $` as s2 ON s1.Name = s2.Name

ВЫБРАТЬ s1.Имя FROM `C: Data1.xlsx`.`Sheet1 $` as s1 INNER JOIN `C: Data2.xlsx`.`Sheet1 $` as s2 ON s1.Name = s2.Name

Excel SQL, пример 3: Объединение внутреннего рабочего листа с внешним файлом Excel
Давайте объединим данные из Sheet2 с данными из Sheet1 из внешнего файла Excel

ВЫБРАТЬ * ИЗ `C: Data.xlsx`.`Sheet1 $`
СОЮЗ ВСЕ
ВЫБРАТЬ * ИЗ [Sheet2 $]

ВЫБРАТЬ * ИЗ `C: Data.xlsx`.`Sheet1 $ ` UNION ALL SELECT * FROM [Sheet2 $]

Excel SQL, пример 4: Обновление рабочего листа с использованием ЛЕВОГО ВНЕШНЕГО СОЕДИНЕНИЯ
Допустим, у нас есть главный рабочий лист с данными [Sheet1] и другой рабочий лист [Sheet2], где мы храним только обновленные записи. Как обновить основной рабочий лист? Вот такой простой:

ВЫБЕРИТЕ S1.A, Iif (IsNull (S2.A), S1.B, S2.B)
FROM [Sheet1 $] как S1
LEFT OUTER JOIN [Sheet2 $] как S2 НА S1.А = S2.A

SELECT S1.A, Iif (IsNull (S2.A), S1.B, S2.B) FROM [Sheet1 $] как S1 LEFT OUTER JOIN [Sheet2 $] as S2 ON S1.A = S2 .A

Обзор: Excel SQL

Я предпочитаю использовать SQL в Excel, а не писать любой макрос VBA, если это возможно. Причины этого следующие:

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

.

• Быстрая разработка — написание тех же функций в VBA часто занимает много времени и тестирование
• Меньше ошибок — SQL-запрос менее подвержен обычным ошибкам VBA при условии, что структура данных существенно не изменится
• Производительность — драйвер Jet. Oledb намного эффективнее любого написанного кода VBA с циклами.Попробуйте любой простой пример

Загрузить надстройку Excel SQL

Чтобы получить одноразовую ссылку для загрузки на указанный адрес электронной почты , перейдите к оплате, нажав кнопку Купить сейчас ниже. Заранее благодарим вас за приобретение набора и поддержку новых интересных сообщений и инструментов от AnalystCave!

Есть вопросы или проблемы с покупкой / загрузкой надстройки? Пишите прямо мне на analystcave (at) gmail.com !

Как импортировать данные из файла 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.

Нажмите кнопку Далее , чтобы продолжить. Следующая страница мастера импорта и экспорта 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.

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

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

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

В поле со списком 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, установив флажок рядом с названием листов. Выбранные листы появятся в столбце Destination :

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

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

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

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

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

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

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

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

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

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

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

Эта ошибка:

— проверка (ошибка)

Сообщения

• Ошибка 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» Результат: 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:

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

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

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

Сообщения

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

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

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

Ссылки:

Марко — инженер-механик, любит играть в баскетбол, настольный футбол и слушать рок-музыку.Он интересуется кодом SQL, разработкой PHP, методами HTML и CSS.

В настоящее время работает в ApexSQL LLC в качестве инженера по продажам программного обеспечения, помогает клиентам с любыми техническими проблемами и обеспечивает контроль качества для бесплатных надстроек ApexSQL Complete, ApexSQL Refactor и ApexSQL Search.

Посмотреть все сообщения Marko Zivkovic

Последние сообщения Marko Zivkovic (посмотреть все)

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

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

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

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

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

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

Зарегистрируйтесь для бесплатного вебинара

Дата: 28 ноября 2020 г. (сб)
Время: с 11:00 до 12:30 (IST / GMT +5: 30)

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

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

Данные в Excel

Здесь у нас есть выборка из 15 записей из тех больших данных, которые есть у вас в EXCEL.

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

как импортировать данные из Excel на сервер SQL

Прежде чем начать процесс, перейдите в SQL Management Studio и решите, в какую базу данных вы собираетесь импортировать эти данные.

Шаг 1:

Перейдите в меню «Пуск», найдите SQL-сервер и затем щелкните «Импорт и экспорт данных SQL Server 2016» на своем компьютере.

SQL Server 2016 Импорт и экспорт

Шаг 2:

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

Выберите источник данных в Excel

Мастер импорта и экспорта SQL-сервера

Выберите источник данных

Шаг 3:

Выберите пункт назначения.В раскрывающемся списке выберите SQL Server Native Client 11.0 и нажмите Далее.

Выберите пункт назначения

Шаг 4:

Выберите базу данных, в которой вы хотите создать таблицу. В нашем случае мы выбрали Funky Tshirts Pvt. ООО

Выбрать базу данных в пункте назначения

Шаг 5:

Выберите «Копировать данные из одной или нескольких таблиц или представлений», а затем щелкните ДАЛЕЕ.

Копирование данных SQL Server

Шаг 6:

Выберите лист, на котором присутствуют ваши данные.Нажмите кнопку «Далее. В вашем файле Excel может быть несколько листов, поэтому убедитесь, что вы выбрали правильный.

SQL Server

Шаг 7:

Теперь выберите вариант «Запустить немедленно» и нажмите ДАЛЕЕ.

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

Шаг 8:

Все проверьте и только потом нажмите Готово.

Шаг 9:

Успешное выполнение SQL Server

Статус выполнения.

Таким образом, мы научились импортировать данные из Excel на SQL-сервер.

Шаг 10:

Теперь нам нужно убедиться, что наши данные правильно импортированы в SQL из Excel.

SQL-запрос

Запустите запрос Select * from tablename и проверьте.

Шаг 11:

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

Обозреватель объектов SQL

В SQL Management Studio укажите на таблицу и щелкните правой кнопкой мыши имя таблицы.

Затем нажмите «Переименовать» и дайте таблице новое имя. В данном случае мы дали название «Продажи».

Переименовать запрос SQL

Теперь запустите запрос Select с новым именем таблицы.

Зарегистрируйтесь для бесплатного вебинара

Дата: 28 ноября 2020 г. (сб)
Время: с 11:00 до 12:30 (IST / GMT +5: 30)

Как экспортировать данные из SQL в Excel

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

Анализировать эти данные с помощью SQL, безусловно, можно, но требуется много навыков SQL.

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

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

Есть 2 способа экспортировать данные из SQL в Excel.

1. Выполните тот же процесс, что и при импорте данных из Excel в SQL, просто поменяйте местами Источник и Назначение.Раньше источником был Excel, а местом назначения был SQL, теперь источником будет SQL, а местом назначения будет Excel.
2. Другой возможный способ — это простая копипаста. Сначала просто запустите запрос select * from tablename . Вы получите вывод как полные данные этой таблицы.

   SQL в Excel

В правом верхнем углу щелкните правой кнопкой мыши и выберите «Выбрать все».
Затем снова щелкните правой кнопкой мыши и выберите «Копировать с заголовками».
Наконец, простая вставка в Excel.

Импорт таблицы SQL из Excel

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

Создайте новую таблицу базы данных SQL из электронной таблицы Excel за девять шагов.

Вы можете легко импортировать таблицу Microsoft SQL Server из электронной таблицы Excel,
с помощью мастера импорта и экспорта SQL Server . (Вы также можете использовать Импорт
и Мастер экспорта в
экспортировать данные из таблицы SQL Server в электронную таблицу Excel.)

Вы можете использовать мастер в SQL Server Standard, Enterprise, Developer или Evaluation.
редакции.

1. Введите данные в электронную таблицу Excel

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

Небольшая демонстрационная таблица Excel, готовая для передачи в базу данных SQL

2. Запустите мастер импорта и экспорта SQL

.

• Затем в Windows запустите мастер импорта и экспорта на Пуск / Все
  Программы / Microsoft SQL Server 2008 / Импорт и экспорт данных.
• Откроется страница Приветствие . Щелкните Далее .

Мастер импорта и экспорта SQL в меню «Пуск» Windows

3. Выберите электронную таблицу Excel в качестве источника данных

.

• В раскрывающемся списке Источник данных на странице Выбор источника данных выберите
  Microsoft Excel .
• В поле Путь к файлу Excel укажите путь к файлу электронной таблицы Excel.
• Выберите версию в раскрывающемся списке Excel версия .
• Убедитесь, что Первая строка содержит имена столбцов. отмечен флажком.
• Щелкните Далее .

4. Выберите базу данных SQL в качестве назначения

• В раскрывающемся списке Назначение примите настройку по умолчанию SQL Server
  Собственный клиент 10.0 .
• В раскрывающемся списке Server name введите имя сервера. Пример
  является удаленным сервером, поэтому были указаны IP-адрес и порт сервера.
• Выберите тип Аутентификация . Пример — удаленный сервер, поэтому SQL Server
  требуется аутентификация с использованием имени пользователя и пароля.
• В раскрывающемся списке База данных выберите или введите имя базы данных.
• Щелкните Далее .

5. Укажите, как копировать данные

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

МОЖЕТ ЕСТЬ SQL?

DBASE IZ GETDB (‘db / demo.db ‘)

FUNNAHS IZ DBUCKET (& DBASE &, «МОГУ ЛИ Я, ПОЖАЛУЙСТА, ПОЛУЧИТЬ * ВСЕ НА ЛОЛИТЕ»)

IM IN UR FUNNAHS ITZA TITLE

VOTEZ IZ & TITLE # ups & — & TITLE # downs &

6. Выберите исходные таблицы и представления

.

• В этом примере работают настройки по умолчанию, как показано.
• В столбце Назначение можно указать другое имя для таблицы.
  в базе данных SQL, если хотите.
• Щелкните Предварительный просмотр , чтобы увидеть, как ваши данные будут отображаться в целевой таблице.
• Вы можете нажать Изменить сопоставления , чтобы изменить способ назначения данных в месте назначения
  table, но в этом примере это не обязательно, так как вы ввели данные
  в электронную таблицу Excel самостоятельно.
• Щелкните Далее .

7.Запускаем «Пакет»

Внизу страницы Run Package появляется следующее сообщение: In SQL
Server Express, Web или Workgroup, вы можете запустить пакет, который используется для импорта и экспорта.
Мастер создает, но не может сохранить. Чтобы сохранить пакеты, созданные мастером, вы
необходимо обновить до SQL Server Standard, Enterprise, Developer или Evaluation.
«Пакет» — это все настройки, которые вы настроили до сих пор.В
коммерческие версии SQL Server, вы можете сохранить пакет для повторного использования, чтобы
не нужно вводить все настройки при следующем запуске мастера. В
Express (бесплатная) версия Microsoft SQL Server 2008 Management Studio ,
вы должны повторно вводить все настройки каждый раз, когда вы запускаете SQL Server Import и
Мастер экспорта.

8.Убедитесь, что пакет успешно выполнен.

• Щелкните Отчет , чтобы просмотреть полезную информацию о процессе передачи данных.
• Щелкните Закрыть .

9. Просмотрите новую таблицу в SQL Server Management Studio

.

• Просмотрите новую таблицу, открыв Microsoft SQL Server 2008 Management Studio
  at Пуск / Все программы / Microsoft SQL Server 2008 / Импорт и экспорт данных.

Новая таблица, dbo.Sheet1 $ , в Microsoft SQL Server Management Studio

Вы также можете использовать Мастер импорта и экспорта для
экспортировать данные из таблицы в SQL Server в электронную таблицу Excel.

Пожалуйста, присылайте любые предложения или комментарии об этой странице на номер , отзывы на 66pacific.com .

Получение данных между Excel и SQL Server с использованием ODBC

Зачем использовать ODBC?

Достаточно легко вставить данные из Excel в SQL Server или наоборот, из любой другой базы данных ODBC в любую другую, используя PowerShell.Самым важным направлением, конечно же, является переход от Excel к SQL Server. Это быстрее, чем автоматизация Excel, и вы можете сделать это, не требуя копии Excel. Он более аккуратный, чем SSIS, и более универсален. Однако самое важное — это то, что вы можете агрегировать данные перед отправкой. Можно провести большую фильтрацию и агрегирование данных, прежде чем они попадут на SQL Server, поскольку вы можете превратить существующую книгу Excel в реляционную базу данных для бедняков или даже создать ее. В этой статье будет показано, как это делается.

Мне всегда немного неловко говорить об ODBC. Это технология «когда-то и будущее», разработанная до своего времени, но теперь показывающая свою ценность для обработки больших объемов данных, несмотря на ее причуды, плохую документацию и слабую поддержку. Если вы используете драйвер ODBC, ваша книга Excel превратится в небольшую реляционную базу данных на основе SQL. Листы или области на листах становятся таблицами. Конечно, некоторых функций не хватает, но вы можете выполнять соединения между таблицами, фильтровать строки по вкусу, выполнять агрегирование и некоторые манипуляции со строками.Это означает, что вам нужно втягивать гораздо меньше данных в SQL, потому что вы можете сделать большой выбор и предварительную обработку, прежде чем данные попадут где-то рядом с SQL-сервером. Если, например, вам нужны только сумма, количество и отклонение показаний за день, то зачем вам импортировать больше, чем эти агрегированные цифры? Даже если вы это сделаете, эти агрегации, выполненные с исходными данными, можно использовать в качестве «согласованной» проверки того, что вы безошибочно отправили все данные в их конечный пункт назначения.

Я также предпочитаю использовать ODBC и последовательный считыватель данных для чтения данных из Excel или любого другого источника ODBC, потому что это быстро; и мне нравится использовать библиотеку массового копирования для вставки данных ODBC «читателя» в таблицу SQL Server, потому что это очень быстро, поэтому мы будем ее использовать. Когда вам нужно вставить большое количество больших таблиц, скорость имеет значение.

Драйвер ODBC Excel (ACE)

ODBC был задуман как способ упростить подключение к определенному источнику данных, например к реляционной базе данных, текстовому файлу, документу данных (например,грамм. XML), веб-данные или электронная таблица

В настоящее время самым современным в ODBC для Access и Excel является распространяемый компонент Microsoft Access Database Engine 2010, который можно загрузить здесь. Сюда входят более популярные драйверы OLEDB, которые также хорошо работают в PowerShell. Эти драйверы позволяют получить доступ к ряду файлов данных через SQL, как если бы они были реляционной базой данных. Форматы включают Access, CSV, с разделителями, DBase и Excel

.

При разработке на 64-разрядном настольном компьютере общего назначения вы, скорее всего, наткнетесь на очень глупую путаницу с Microsoft.Microsoft рекомендует установить 32-разрядную версию Office 2010 даже на 64-разрядных компьютерах, поскольку многие из распространенных надстроек Office не запускались в 64-разрядной среде Office. Этот совет стал «передовой практикой». Если вы, как и большинство из нас, используете 64-битную оболочку PowerShell, вам необходимо использовать 64-битную версию драйверов. Если на вашем компьютере установлен только 32-разрядный Office, то у него уже будут 32-разрядные драйверы, которые не будут видны для 64-разрядной оболочки PowerShell и не будут работать.Вы не можете установить 64-битные драйверы, если у вас уже есть 32-битные драйверы, и я не думаю, что вы можете добиться чего-то хорошего, удалив 32-битные драйверы. Неа. Все три (или четыре, если вы включаете Visual Studio) должны быть 64-разрядными. Я так понимаю, что один из выходов из этой ловушки 22 — сначала установить 64-разрядные драйверы ODBC / OleDB для Office 2010, а затем (32-разрядный) Office, но есть более эффективное исправление, включающее настройку реестра. Смотрите здесь полную разочаровывающую историю.

Драйвер ODBC Excel в ACE работает с последними форматами электронных таблиц Excel до 2010, а также со старыми.Я подозреваю, что последняя версия будет работать с Office 2013, хотя я еще не пробовал.

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

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

Если, например, вам нужны данные в диапазоне от C3 до L8, вы должны использовать оператор

В ODBC, если вы указали, скажем, строку 8 в качестве конца таблицы, вы можете выбрать только строки до строки 8, даже если вы вставили больше строк сверх этого предела, как позволяет ODBC.Если вы используете некоторые разновидности, такие как старый механизм базы данных MDAC «JET», то вы не можете добавлять новые строки за определенные пределы диапазона, в противном случае вы получите сообщение Exception: «Cannot expand named range»

Если вы хотите определить свою таблицу как находящуюся между столбцами C и L, начиная со строки 3 вы должны использовать

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

Страшный соединительный шнур

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

В настоящее время игнорируя расширенные настройки свойств, для данных Microsoft Office Access установите для строки подключения значение

.

Для данных Excel используйте

Для данных dBASE используйте

Для текстовых данных используйте

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

Эти расширенные свойства актуальны только для используемого вами драйвера. Они не всегда надежны и плохо документированы Microsoft. Упомяну только самое необходимое.

Драйвер должен знать, содержит ли первая строка таблицы имя столбца. «HDR = Да»; указывает, что первая строка содержит имена столбцов, а не данные. Фактически он будет использовать только первые 64 символа заголовка. «HDR = Нет» обрабатывает первую строку как данные, но затем столбцы получают имя F1 и далее, и вы хотите присвоить им псевдонимы в своих операторах SQL, чтобы дать им осмысленные имена столбцов.

Excel ODBC не поддерживает подробное определение схемы таблиц. (Драйверы Text и Access, напротив, делают). Драйвер ODBC Excel будет пытаться разобраться в данных, которые он находит, тестируя их, чтобы увидеть, какой тип данных он может использовать для результата. Он делает это путем тестирования ряда строк перед импортом, и вы можете указать, сколько строк он проверяет, прежде чем определять тип данных столбца, используя MaxScanRows в расширенных свойствах. По умолчанию это значение равно 8.Вы можете указать любое значение от 1 до 16 для 1–16 строк. Вы также можете установить значение 0, чтобы поиск выполнялся по всем существующим строкам перед определением типа данных, но это замедляет работу.

Это нормально для реляционной таблицы, но Excel часто имеет смешанные типы в столбце. Поставщик ODBC попытается вернуть данные основного типа, но для остальных будет возвращать значения NULL, которые не будут преобразованы. Если два типа одинаково смешаны в столбце, поставщик выбирает числовое значение вместо текста, и вы теряете весь текст.Кроме того, он будет оценивать длину символьного типа данных в столбце из первых строк, и если длина первых строк меньше 255 символов, он усекает все последующие данные до 255 символов, даже если значения ячеек ниже длиннее.

Установив режим импорта (IMEX = 1). Вы можете принудительно преобразовать смешанные данные в текст, но только тогда, когда он обнаружит смешанные значения в проверяемых строках.

Вы также можете открыть книгу Excel в режиме только для чтения, указав ReadOnly = true ; По умолчанию атрибут Только для чтения имеет значение false, поэтому вы можете изменять данные в своей книге.Однако это заблокирует доступ ко всей книге, пока вы не закроете соединение.

Давай попробуем.

Чтобы вы могли убедиться в этом сами, я предоставил книгу Excel, которая представляет собой старую базу данных PUBS, которая раньше распространялась с SQL Server и Sybase. Это означает, что вы можете использовать SQL из старых примеров, использующих PUBS, и посмотреть, что работает. Все, что вам нужно сделать, это немного преобразовать версию SQL Server, слегка изменив имена таблиц, чтобы сообщить драйверу, что вам нужен весь рабочий лист с этим именем ($ — это разделитель между именем рабочего листа и спецификацией диапазона)

Итак, давайте соберем очень простую тестовую установку, чтобы опробовать ее в PowerShell.Будьте осторожны, я установил это в режиме чтения-записи, поэтому он будет обновлять вашу электронную таблицу в некоторых случаях (CUD). Чтобы поиграть, вам нужно будет загрузить мою версию базы данных PUBS для Excel и изменить путь к файлу Excel.

Все эти работы

Внутренние стыки

Левое или правое внешнее соединение

Выражения с использованием столбцов

Простое выражение GROUP BY

Более сложная агрегация с предложением ORDER BY и предложением WHERE

Строковые функции

СОЮЗ и СОЮЗ ВСЕ

Можно продолжать и продолжать; работают даже подзапросы, но я думаю, что заметил, что в этом драйвере ODBC Excel гораздо больше возможностей, чем просто средство для извлечения необработанных данных.То же самое и с драйвером TEXT для OLEDB. Он тоже все это сделает. Чтобы соответствовать минимальному синтаксису ODBC, драйвер должен иметь возможность выполнять CREATE TABLE, DELETE FROM (поиск), DROP TABLE, INSERT INTO, SELECT, SELECT DISTINCT и UPDATE (поиск). Операторы SELECT могут иметь предложения WHERE и ORDER BY. ACE работает немного лучше этого, поскольку даже текстовый драйвер позволяет SELECT INTO, а операторы SELECT позволяют GROUP BY и HAVING.

Создание электронной таблицы

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

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

Изучение метаданных Excel

Вы можете узнать, какие типы данных доступны для любого источника ODBC, используя метод OdbcConnection.GetSchema (string) .

Что с моим подключением дает только типы данных LOGICAL, CURRENCY, NUMBER, VARCHAR и DATETIME . Полезнее ..

… который дает вам список доступных рабочих листов.Полный список, если хотите посмотреть на них —

.

Хм. Это начинает больше походить на базу данных. С Columns MetadataCollection вы можете узнать столько, сколько вы когда-либо хотели знать о данных, доступных в электронной таблице, поэтому, если вы хотите прочитать все рабочие листы прямо в SQL Server, это широко открытый Цель.

Создание рабочих листов

Возвращаясь к базе данных PUBS Excel, давайте создадим таблицу людей и заполним ее авторами и продавцами.Это нужно сделать за три глотка, так как водителю, кажется, не нравится идея делать пакетную обработку, и он дает отпор, когда я пытаюсь СОЕДИНЯТЬ два результата.

Вы обнаружите, что таким образом вы можете без проблем ОБНОВИТЬ, ВСТАВИТЬ и УДАЛИТЬ данные . Если вы подключите электронную таблицу к базе данных SQL Server, то сможете получить массу удовольствия, копируя целые базы данных в электронные таблицы и обратно. Мы с Робин показываем, как это сделать здесь.

Проблема в создаваемой вами книге.Независимо от того, назовете ли вы его XLS или XSLX, он создает электронную таблицу XLSX в последней заархивированной форме Office Open XML. Проблема в том, что с моей версией драйвера я могу заставить Excel читать его только с типом файла XLS, поскольку он сообщает об ошибке, если вы пытаетесь открыть его как файл .XLSX. Я подозреваю, что драйвер ODBC не был так хорошо протестирован Microsoft.

Получение данных в SQL Server из Excel с помощью PowerShell

Теперь что насчет использования PowerShell для копирования данных, возможно, отфильтрованных, отсортированных и агрегированных, в SQL Server с помощью PowerShell и ODBC.В этом направлении мы можем сэкономить много времени, используя библиотеку BCP. Теперь опишем распорядок дня.

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

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

Я предоставил образцы данных, чтобы вам не приходилось копаться в поисках чего-то подходящего. Это некоторые климатические данные, которые удобны для проверки таких вещей, как преобразование даты.

Вы заметите, что, хотя вы можете отображать числа различными способами, существует только один способ их хранения в Excel, в типе данных NUMBER (другие типы данных в Excel: LOGICAL, CURRENCY, VARCHAR и ДАТА ).Поэтому мне пришлось указать точность числовых данных, что сложно, если у вас есть некоторые столбцы с целыми числами, а другие — с реальными десятичными данными с числами после десятичной точки (шкала). Помните, что эта процедура просто создает промежуточную таблицу, а не конечный пункт назначения. Все, что вам нужно сделать, это добавить свои собственные операторы для передачи данных в их окончательную таблицу с помощью CAST для правильного внутреннего типа данных!

Хорошо, а с реальными данными работает? Отправляйтесь в Информационный центр здравоохранения и социального обеспечения за некоторыми реалистичными данными в виде электронной таблицы. Я добавил некоторые данные, чтобы вам не приходилось заходить на сайт, чтобы подыгрывать, но гораздо лучше использовать последнюю версию этих данных с сайта.Я уверен, что мне не нужно рассказывать вам, как легко это сделать в сценарии через PowerShell.

Также

… и

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

Запись в Excel из SQL Server.

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

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

«Файл, который вы пытаетесь открыть, MyExcelFile.xls, имеет формат, отличный от указанного в расширении файла. Перед открытием файла убедитесь, что файл не поврежден и получен из надежного источника. Вы хотите открыть файл сейчас? »

Более серьезно, он жалуется, что числа в столбцах «отформатированы как текст». Оказывается, данные сохраняются в правильном формате, но при следующем открытии файла все столбцы возвращаются к varchar. Опытные пользователи ODBC привыкают к этим ошибкам, но если кто-нибудь знает способ обхода этой проблемы, я был бы признателен.

.