SQL-Ex blog
Импорт данных из файла Excel в базу данных SQL Server с помощью Python
Добавил Sergey Moiseenko on Суббота, 8 апреля. 2023
Есть много способов загрузить данные из Excel в SQL Server, но иногда полезно использовать те инструменты, которые вы знаете лучше всего. В этой статье мы рассмотрим как загружать данные из Excel в SQL Server с помощью Python.
Используемые инструменты
- Экземпляр SQL Server
- Python, версия 3.11.0.
- Visual Studio Code, версия 1.72.1.
- Windows 10 PC или Windows Server 2019/2022.
Установка базы данных — создание тестовой базы данных и таблицы
Имеется несколько способов создать базу данных и таблицы в SQL Server, но ниже мы пройдем через использование SQLCMD для создания базы данных, если вы не имеете SQL Server Management Studio или Azure Data Studio.
Откройте командую строку Windows или запустите новую терминальную сессию из Visual Studio Code, нажав CTRL + SHFT + `.
Для запуска SQLCMD используйте следующую команду sqlcmd -S -E , чтобы подключиться к SQL Server. Параметр -S указывает экземпляр SQL Server, а параметр -E означает использование доверительного подключения.
sqlcmd -S -E
После аутентификации создадим новую базу данных следующей командой:
CREATE DATABASE ExcelData;
GO
Используйте эту команду SQLCMD для подтверждения создания базы данных:
SELECT name FROM sys.databases
GO
Изображение ниже представляет вывод команды, который показывает все имеющиеся базы данных этого экземпляра SQL Server.
Для переключения на новую базу данных используйте следующую команду:
USE ExcelData;
GO
Будет получено подтверждение изменение контекста, как показано ниже:
Теперь мы можем создать таблицу в этой базе данных.
CREATE TABLE EPL_LOG(ID int NOT NULL PRIMARY KEY);
GO
Отлично! Вы создали таблицу с именем EPL_LOG и первичным ключом ID. Нам нужен только первый столбец, а программа загрузки создаст остальные столбцы на основе файла-источника.
Конфигурация ядра
Ядро помечает начальную точку вашего приложения SQLAlchemy. Ядро описывает пул соединений и диалект для BDAPI (Python Database API Specification), спецификацию в рамках Python для определения общих шаблонов использования для всех пакетов подключения к базам данных, которые в свою очередь взаимодействуют с указанной базой данных.
Для открытия нового терминала нажмите CTRL + SHFT + ` в Visual Studio Code.
Используйте следующую команду npm в окне терминала для установки модуля SQLAlchemy.
npm install sqlalchemy
Создайте файл Python с именем DbConn.py, вставьте в него нижеприведенный код и измените источник данных на требуемый. Это — ядро SQLAlchemy, которое взаимодействует с SQL Server через Python.
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
conn = urllib.parse.quote_plus(
‘Data Source Name=MssqlDataSource;’
‘Driver=;’
‘Server=POWERSERVER\POWERSERVER;’
‘Database=ExcelData;’
‘Trusted_connection=yes;’
)
try:
coxn = create_engine(‘mssql+pyodbc:///?odbc_connect=<>‘.format(conn))
print(«Passed»)
Запись в SQL Server
Мы будем использовать Pandas, который является быстрым, гибким и легким в использовании инструментом с открытыми кодами для манипуляции и анализа данных, встроенным в язык программирования Python. Python может читать данные Excel в программе Python, используя функцию pandas.read_excel().
Для простоты этой демонстрации, сохраним файл Excel в папке проекта Visual Studio Code, чтобы нам не пришлось указывать путь. Это позволит вам игнорировать параметр io (любая валидная строка пути) функции read_excel().
Мы будем также использовать openpyxl в качестве движка для чтения файлов Excel. Выполните следующую команду pip в окне терминала, чтобы установить openpyxl.
pip install pandas openpyxl
Создайте еще один файл с именем ExcelToSQL.py, содержащий код ниже. Этот код будет читать файл Excel и записывать в созданную ранее таблицу базы данных.
//ExcelToSQL.py
from pandas.core.frame import DataFrame
import pandas as pd
from DbConn import coxn
df = pd.read_excel(‘sportsref_download.xlsx’, engine = ‘openpyxl’)
except:
pass
print(«Failed!»)
else:
print(«saved in the table»)
print(df)
Теперь щелкнем кнопку Play в верхнем правом углу окна Visual Studio Code для выполнения скрипта. В терминале появится вывод данных.
Для проверки сохранения данных в базе откройте SSMS и выберите данные из таблицы. Вы можете также использовать SQLCMD для подключения к экземпляру и выполнения следующего кода.
USE ExcelData;
GO
SELECT * FROM EPL_LOG
Изображение ниже показывает какие данные сейчас находятся в базе данных.
Заключение
Python выполняет большую работу, действуя как посредник между Excel и SQL Server. Вы можете транслировать любые статичные данные Excel в более гибкий набор данных, перемещая его в базу данных, которая обладает большей доступностью и легче интегрируется с другим системами.
Перемещайте данные Excel в SQL Server данным способом. Поскольку pandas сохраняет данные в DataFrame, ими легко манипулировать и изменять перед занесением в базу данных SQL Server.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
SQL-Ex blog
Руководство: как автоматизировать импорт данных из Excel в базу данных, используя VBA
Добавил Sergey Moiseenko on Суббота, 30 сентября. 2023
- MS Access посредством ODBC, если данных не очень много (просто скопировать/вставить).
- Использовать мастер импорта и экспорта данных, если данных много
Тут предполагается, что ваши данные не представляют собой перекрестную (пивот) таблицу.
Установка
Начнем с создания макроса в Excel. На вкладке “Developer” ленты (ее может не быть по умолчанию; вы можете добавить ее из меню Options) щелкните кнопку “View Code” (для Windows можно использовать комбинацию клавиш Alt+F11).
В верхнем меню: Insert >> Module
На новой панели (где печатается код) создайте функцию public:
Public Function ImportData()
End
Если вы напечатаете “public function ImportData” нажмете Enter, остальное будет добавлено автоматически; т.е. будут добавлены круглые скобки и ключевое слово End.
Не забудьте сохранить ваш файл с расширением .xlsm, т.к. это книга с включенными макрокомандами (а не «обычная» книга).
Обзор концепции
Мы будем выполнять оператор INSERT в следующем формате:
INSERT INTO myTableName
(field1, field2)
VALUES
(row1Value1, row1Value2),
(row2Value1, row2Value2)
- Заголовок SQL (INSERT INTO myTableName)
- Динамически сгенерированные «values», представляющие собой конкатенацию отдельных строк данных из электронной таблицы.
Давайте напишем код!
Сначала нужно создать строку подключения. Я оставляю точное значение вам. Вы, конечно, должны убедиться, что пользователь имеет необходимые разрешения безопасности базы данных. В примере ниже используется Active Directory — поэтому он обнаруживает мой вход в Windows и не использует локальную учетную запись пользователя SQL.
Я подключаюсь через ODBC. Если на вашей машине не установлен драйвер, загрузите его.
В моем случае строка подключения выглядит так:
Const strDbConn As String = "server=myServerName;Database=myDatabaseName;Trusted_Connection=Yes;Driver="
Для этого я создал глобальную переменную, размещенную выше объявления функции, которое мы только что создали.
Глобальную переменную мы можем использовать и в других функциях.
У меня есть простая функция для выполнения SQL:
Public Function RunSQL(strDbConn As String, strSQL As String)
Dim cnn As Object
Dim rst As Object
Set cnn = CreateObject("ADODB.Connection")
cnn.Open strDbConn
Set rst = CreateObject("ADODB.Recordset")
rst.Open strSQL, cnn, 3, 1 '3 = Keyset, 1 = Pessimistic
Set rst = Nothing
End Function
Ошибка ADODB?
Этот код работает у меня без необходимости добавлять внешне ссылки. Но если вы получаете ошибку.
Раньше — возможно, это была предыдущая версия Excel — мне требовалось добавить ссылку, чтобы использовать код ADODB.
Вы можете сделать это в меню Tools >> References:
Согласно этой статье, вам нужно снять флажок опции Microsoft ActiveX Data Objects [x.x] Library.
Вставка данных с помощью SQL: заголовок
Нам нужно создать начальную часть оператора SQL, которая будет статичной (т.е. не будет изменяться).
Создадим функцию , которая возвращает жестко закодированный результат первой части оператора INSERT для заданной таблицы и имен полей:
Public Function GetInsertHeader() As String
Dim strHeader As String
strHeader = ""
strHeader = strHeader & " INSERT INTO myTableName ("
strHeader = strHeader & " [field_one]"
strHeader = strHeader & " ,[field_two]"
strHeader = strHeader & " ,[field_three]"
strHeader = strHeader & " )"
GetInsertHeader = strHeader
End Function
Конечно, вам нужно изменить имена таблицы и полей
Вставка данных с помощью SQL: сами данные
Нам нужен способ для динамической генерации values, которые будут вставляться в базу данных. Прежде чем создать SQL, нам нужен способ прохода по всем строкам в Excel сверху донизу. На первом шаге нужно определить, сколько строк у нас есть.
Я использую функцию ниже для получения числа строк в электронной таблице.
Я определяю тип LONG, поскольку Integer (целое) недостаточно велик для моего набора данных. Целое ограничено 32000 строками.
Public Function HowManyRows(sheetName As String) As Long
'Find out how many row there are
' Assumes the first column does NOT have a blank in it
Sheets(sheetName).Select
Range("A1").Select
Selection.End(xlDown).Select
Dim totalRows As Long
totalRows = ActiveCell.Row ' do NOT subtract one for the header
HowManyRows = totalRows
End Function
Проходя в цикле каждый ряд мы будем создавать строку, которая берет значения отдельных столбцов и добавляет их в оператор SQL. Код приведен ниже:
Public Function getSQLForSingleRow(sheet As String, rowNumber As Long) As String
' Не забудьте поменять форматирование, например, даты или числа
fieldWithText = ReplaceSingleQuote(Worksheets(sheet).Range("A" & rowNumber).FormulaR1C1)
fieldWithDate = Format(Worksheets(sheet).Range("B" & rowNumber).FormulaR1C1, "mm/dd/yyyy")
fieldWithNumbers = CDbl(Nz(Worksheets(sheet).Range("C" & rowNumber).FormulaR1C1))
Dim strSQL As String
strSQL = " ("
strSQL = strSQL & " '" & fieldWithText & "',"
strSQL = strSQL & " '" & fieldWithDate & "',"
strSQL = strSQL & " " & fieldWithNumbers & "" ' Кавычки не нужны, т.к. это число
strSQL = strSQL & " ) "
getSQLForSingleRow = strSQL
End Function
Я имею две вспомогательные функции; одну для текста, а другую для чисел. Если значение представляет собой текст, нам нужно заменить («экранировать») одиночную кавычку, для этого у меня есть такая функция:
Public Function ReplaceSingleQuote(str As String) As String
If Len(Trim(str)) > 0 Then
ReplaceSingleQuote = Replace(str, "'", "''")
Else
ReplaceSingleQuote = str
End If
End Function
Я также создал функцию nz() (как «null-нуль», которая встроена в некоторые языки программирования), которая будет заменять числовые значения на «0», при необходимости:
Function Nz(value As String) As Double
If IsNull(value) Or (value = "") Then
Nz = 0
Else
Nz = value
End If
End Function
Вот код, который проходит в цикле каждую строку электронной таблицы и генерирует оператор SQL:
Public Function ImportData(sheet As String)
Dim i As Integer
Dim strSQL As String
Dim totalRows As Long
totalRows = HowManyRows(sheet)
strInsertHeader = GetInsertHeader()
Dim strIndividualRows As String
strIndividualRows = ""
For i = 2 To totalRows
strIndividualRows = strIndividualRows & getSQLForSingleRow(sheet, i)
' добавить запятую в конце,
' которую удалим потом, если потребуется
strIndividualRows = strIndividualRows & ","
Next
strSQL = strInsertHeader & " VALUES " & strIndividualRows
strSQL = RemoveLastCharacterIfComma(strSQL)
Call RunSQL(strDbConn, strSQL)
Range("A1").Select
MsgBox "Complete"
End Function
- Вызываем функцию, которая передает имя электронной таблицы (лист) с данными, чтобы определить количество строк в наборе.
- Создаем переменную, которая содержит начало оператора SQL.
- Обходим в цикле каждую строку набора данных, создавая оператор INSERT. VALUES.
- В конце каждой строки данных добавляем запятую.
- Удаляем последнюю запятую в операторе.
- Выполняем SQL.
Public Function RemoveLastCharacterIfComma(str As String) As String
str = Trim(str)
If Right(str, 1) = "," Then
RemoveLastCharacterIfComma = Left(str, Len(str) - 1)
Else
RemoveLastCharacterIfComma = str
End If
End Function
Как обойти ограничение на импорт данных
Я столкнулся с тем, что при слишком длинных конкатенированных «значениях» Excel терпит крах. Выяснилось, что я должен ограничить скрипт импортирования до 500 строк одновременно.
Вот обновленный код:
Public Function ImportData(sheet As String)
Dim i As Long
Dim strSQL As String
Dim totalRows As Long
totalRows = HowManyRows(sheet)
strInsertHeader = GetInsertHeader()
Dim strIndividualRows As String
strIndividualRows = ""
For i = 2 To totalRows
strIndividualRows = strIndividualRows & getSQLForSingleRow(sheet, i)
' add comma at the end no matter what; remove last one after
strIndividualRows = strIndividualRows & ","
'Worksheets("Instructions").Range("I2").FormulaR1C1 = "Rows read in: " & i
' вставлять в базу данных, пока не станет слишком большой
If (i Mod 500) = 0 Then
strSQL = strInsertHeader & " VALUES " & strIndividualRows
strSQL = RemoveLastCharacterIfComma(strSQL)
Call RunSQL(strDbConn, strSQL)
strIndividualRows = ""
'Debug.Print "in the middle of importing " & i
End If
Next
' вставить в последний пакет
strSQL = strInsertHeader & " VALUES " & strIndividualRows
strSQL = RemoveLastCharacterIfComma(strSQL)
Call RunSQL(strDbConn, strSQL)
strIndividualRows = ""
'Debug.Print "last row " & i
Range("A1").Select
' Complete: clear out the status
' Worksheets("Instructions").Range("I1").Formula2R1C1 = ""
' Worksheets("Instructions").Range("I2").Formula2R1C1 = ""
'MsgBox sheet + " data imported"
End Function
Как можно увидеть, когда переменная, в которой хранится генерируемые значения, достигает 500 символов, в базу данных вставляются накопленные к тому времени данные. После завершения цикла вставляется оставшийся пакет.
Я оставил некоторый код закомментированным, который уведомляет пользователя о том, как движется процесс импорта.
В окончательном коде есть еще одна проверка для выполнения только последнего пакета кода при необходимости. Сейчас же, если вы пытаетесь импортировать количество строк, кратное 500, вы получите сообщение об ошибке.
Собираем все вместе
Чтобы сделать это более полезным, нам может понадобиться удалять существующие данные.
Хотя вы можете получить «дату последнего обновления» из набора данных, этот пример кода демонстрирует дополнительный оператор SQL для выполнения запроса UPDATE с целью указания даты импорта.
- Удаляет существующие данные.
- Вызывает функцию, которая импортирует данные.
- Выполняет запрос UPDATE для указания даты обновления данных.
Public Sub autoImport()
' Сначала удаляем существующие данные
Dim strSQL As String
strSQL = "DELETE FROM myTableName" ' Альтернативно можно использовать TRUNCATE
Call RunSQL(strDbConn, strSQL)
' Импорт новых данных
Call ImportData("tabWithData") ' Убедитесь, что передали имя листа, или получите его динамически
' Указываем, когда данные обновлялись; я использую таблицу "utility"
Dim theDate As String
theDate = InputBox("Enter date that the data for", "Data as of", Format(Now, "m/d/yyyy"))
strSQL = "UPDATE Utility SET value = '" & ReplaceSingleQuote(theDate) & "' WHERE description IN ('Last Updated,'Last Updated')"
Call RunSQL(strDbConn, strSQL)
Range("A1").Select
MsgBox "Data has been imported"
End Sub
Импорт данных из Excel или экспорт данных в Excel с помощью служб SQL Server Integration Services (SSIS)
В этой статье приводится информация о подключении, которую вы должны предоставить, и необходимые настройки подключения для импорта данных из Excel и экспорта данных в Excel с помощью SQL Server Integration Services (SSIS).
В следующих разделах содержится вся информация, которая потребуется для успешной работы с приложением Excel из служб SSIS, а также для понимания и устранения распространенных проблем:
- Средства, которые можно использовать.
- Файлы, которые вам потребуются.
- Сведения о подключении, которые необходимо предоставить, и параметры, которые необходимо настроить для загрузки данных в/из Excel с использованием SSIS.
- Укажите Excel в качестве источника данных.
- Укажите имя файла Excel и путь к нему.
- Выберите версию Excel.
- Укажите, содержит ли первая строка имена столбцов.
- Укажите лист или диапазон, который содержит данные.
- Известные проблемы и ограничения.
- Проблемы с типами данных.
- Проблемы с импортом.
- Проблемы с экспортом.
Средства, которые можно использовать
Вы можете импортировать и экспортировать данные в Excel с использованием SSIS при помощи любого из следующих инструментов.
- SQL Server Integration Services (SSIS) . Создайте пакет SSIS, настроив для него Excel в качестве источника или назначения с помощью диспетчера подключений Excel. (В этой статье не рассматривается создание пакетов SSIS.)
- Мастер импорта и экспорта SQL Server, основанный на SSIS. Дополнительные сведения см. в статьях Импорт и экспорт данных с помощью мастера импорта и экспорта SQL Server и Подключение к источнику данных Excel (мастер импорта и экспорта SQL Server).
Получение файлов, необходимых для подключения к Excel
Прежде чем импортировать или экспортировать данные в Excel с использованием SSIS, вам может потребоваться скачать компоненты подключения для Excel, если они еще не установлены. По умолчанию компоненты подключений для Excel не устанавливаются.
Используйте таблицу из статьи Не удалось использовать интерфейсы Access ODBC, OLEDB или DAO за пределами Office Click-to-Run, чтобы понять, нужны ли дополнительные компоненты для вашей среды.
Примечание. Системные драйверы Office поддерживаются только в определенных случаях, конкретные руководства см. в статье Рекомендации по автоматизации Office на стороне сервера.
Назначение 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: лист, именованный диапазон или неименованный диапазон ячеек, который задается с помощью адреса.
- Лист. Чтобы указать лист, добавьте в конец имени листа символ $ и окружите строку разделителями, например [Листt1$] . Также можно выполнить поиск имени, заканчивающегося символом $ , в списке существующих таблиц и представлений.
- Именованный диапазон Чтобы указать именованный диапазон, используйте имя диапазона, например Мой_диапазон. Также можно выполнить поиск имени, не заканчивающегося символом $ , в списке существующих таблиц и представлений.
- Неименованный диапазон Чтобы указать диапазон ячеек, которым не были заданы имена, добавьте символ $ после имени листа, добавьте спецификацию диапазона и окружите строку разделителями, например [Лист1$A1:B4] .
Чтобы выбрать или указать тип объекта Excel, который вы хотите использовать в качестве источника или назначения для данных, выполните одно из следующих действий:
В службах SSIS
В службах SSIS на странице Диспетчер соединений в редакторе источника Excel или редакторе назначения Excel выполните одно из следующих действий:
- Чтобы использовать лист или именованный диапазон, выберите Таблица или представление в разделе Режим доступа к данным. Затем выберите лист или именованный диапазон в списке Имя листа Excel.
- Чтобы использовать неименованный диапазон, который задается по адресу, выберите Команда SQL в разделе Режим доступа к данным. Затем в поле Текст команды SQL введите запрос, аналогичный представленному ниже примеру:
SELECT * FROM [Sheet1$A1:B5] В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта выполните одно из следующих действий:
- При импорте из Excel выполните одно из следующих действий:
- Чтобы использовать лист или именованный диапазон, на странице Выбор копирования таблицы или запроса выберите Скопировать данные из одной или нескольких таблиц или представлений. Затем на странице Выбор исходных таблиц и представлений в столбце Источник укажите исходные листы и именованные диапазоны.
- Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор копирования таблицы или запроса выберите Написать запрос, указывающий данные для передачи. Затем на странице Определение исходного запроса введите запрос, аналогичный показанному ниже:
SELECT * FROM [Sheet1$A1:B5]- Чтобы использовать лист или именованный диапазон, на странице Выбор исходных таблиц и представлений в столбце Назначение выберите целевые листы и именованные диапазоны.
- Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор исходных таблиц и представлений в столбце Назначение укажите диапазон в следующем формате без использования разделителей: Sheet1$A1:B5 . Мастер автоматически добавит разделители.
После выбора или ввода объектов Excel для импорта или экспорта вы также можете выполнить следующие действия на странице Выбор исходных таблиц и представлений мастера:
- Проверить сопоставления столбцов между источником и назначением, выбрав команду Изменить сопоставления.
- Выполнить предварительный просмотр данных, чтобы убедиться в правильности выбора, с помощью команды Предварительный просмотр.
Проблемы с типами данных
Типы данных
Драйвер Excel распознает только ограниченный набор типов данных. Например, все числовые столбцы воспринимаются как тип double (DT_R8), а все строковые столбцы (кроме столбцов типа memo) воспринимаются как строки в Юникоде длиной 255 символов (DT_WSTR). Службы SSIS сопоставляют типы данных Excel следующим образом:
- Числовой — с плавающей запятой двойной точности (DT_R8)
- Денежный — денежный (DT_CY)
- Логический — логический (DT_BOOL)
- Дата и время — datetime (DT_DATE)
- Строковый — строка в Юникоде длиной в 255 символов (DT_WSTR)
- Memo — текстовый поток в Юникоде (DT_NTEXT)
Преобразования типов данных и длины по умолчанию
В службах SSIS неявное преобразование типов данных не выполняется. В результате, возможно, потребуется использовать преобразование «Производный столбец» или «Преобразование данных» для явного преобразования данных Excel до их загрузки в назначение, отличное от Excel, либо для преобразования данных из источника, отличного от Excel, до их загрузки в назначение Excel.
Ниже приведены некоторые примеры преобразований, которые могут потребоваться:
- Преобразование между строковыми столбцами Excel в Юникоде и строковыми столбцами в формате с конкретными кодовыми страницами, отличными от Юникода.
- Преобразование между строковыми столбцами Excel длиной в 255 символов и строковыми столбцами другой длины.
- Преобразование между числовыми столбцами Excel с плавающей запятой двойной точности и числовыми столбцами других типов.
Если вы используете мастер импорта и экспорта и вам требуется выполнить некоторые из этих преобразований данных, мастер выполнит настройку необходимых преобразований автоматически. В результате, даже если вам требуется использовать пакет служб SSIS, часто бывает полезно создать первичный пакет с помощью мастера импорта и экспорта. В таком случае мастер создаст и настроит диспетчеры соединений, источники, преобразования и назначения автоматически.
Проблемы с импортом
Пустые строки
Если в качестве источника указан лист или диапазон, драйвер считывает непрерывный блок ячеек, начиная с первой непустой ячейки в верхнем левом углу листа или диапазона. В результате данные могут начинаться не со строки 1, однако в данных источника не должны присутствовать пустые строки. Например, пустые строки не допускаются между заголовками столбцов и строками данных, а также после названия в верхней части листа.
Если над данными располагаются пустые строки, вы не сможете выполнить их запрос в качестве листа. В Excel необходимо выбрать диапазон данных, присвоить ему имя и выполнить запрос к именованному диапазону, а не к листу.
Отсутствующие значения
Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанном источнике для определения типа данных каждого столбца. Если столбец содержит смешанные типы данных, особенно если числовые данные смешаны с текстовыми данными, драйвер принимает решение в пользу того типа данных, которого больше, и возвращает значения NULL в ячейки, содержащие данные другого типа. (В случае равенства преимущество получает числовой тип.) Большинство параметров форматирования ячеек в листе Excel не затрагивает это определение типа данных.
Можно изменить поведение драйвера Excel, указав режим импорта для импорта всех значений в виде текста. Чтобы указать режим импорта, добавьте IMEX=1 к значению расширенных свойств в строке соединения диспетчера соединений с Excel в окне «Свойства».
Усеченный текст
Когда драйвер определяет, что столбец Excel содержит текстовые данные, он выбирает тип данных (строковый или memo) на основании самого длинного значения. Если драйвер не обнаруживает значений длиннее 255 символов в выбираемых строках, он считает, что столбец является строковым с длиной 255 символов, а не столбцом типа memo. Поэтому значения длиннее 255 символов могут быть усечены.
Чтобы импортировать данные из столбца типа memo без усечения, можно воспользоваться любым из двух способов:
- Убедитесь, что столбец типа memo как минимум в одной из выбранных строк содержит значение длиной более 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, укажите имя этого файла и выберите Открыть.
Экспорт в достаточно большой диапазон
При указании диапазона в качестве назначения появится сообщение об ошибке, если диапазон содержит меньше столбцов, чем в данных источника. Если же указываемый диапазон содержит меньше строк, чем данные источника, мастер продолжит записывать строки без ошибки и расширит определение диапазона для соответствия новому числу строк.
Экспорт длинных текстовых значений
Чтобы успешно сохранять в столбцы Excel строки, имеющие длину более 255 символов, драйвер должен распознать тип данных целевого столбца как memo , а не как string.
- Если в существующей целевой таблице уже содержатся строки данных, то в столбце типа memo в первых нескольких строках, которые проверит драйвер, должен содержаться, по крайней мере, один экземпляр значения, имеющего длину более 255 символов.
См. также
Дополнительные сведения о компонентах и процедурах, описываемых в этой статье, см. в следующих статьях:
Импорт\Экспорт в или из Excel в MS SQL Server
—Установим драйвер Microsoft.ACE.OLEDB.12.0
—http://www.microsoft.com/en-us/download/details.aspx?id=13255
—После установки для 86x платформ
EXEC sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’AllowInProcess’, 1
GO
EXEC sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’DynamicParameters’, 1
GO—Создаем linked server для работы с Excel файлом
—Более полная докуметация
—http://msdn.microsoft.com/en-us/library/aa259589(v=sql.80).aspx
EXEC sp_addlinkedserver
@server = ‘ExcelServer’,
@srvproduct = ‘Excel’,
@provider = ‘Microsoft.ACE.OLEDB.12.0’,
@datasrc = ‘C:1231.xlsx’,
@provstr = ‘Excel 12.0;IMEX=1;HDR=YES;’—Выборка из Excel
—l1 — такое название я дал листу в excel файле
SELECT * FROM ExcelServer. [l1$]
SELECT * FROM OPENQUERY(ExcelServer, ‘SELECT * FROM [l1$]’)—Вставка данных в EXCEL
INSERT INTO OPENROWSET (‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0;Database=C:1231.xlsx;’,’SELECT * FROM [l1$]’)
SELECT 1, 2, 3—Столкнулся с проблемой:SQL Server заблокировал доступ к STATEMENT «OpenRowset/OpenDatasource» компонента «Ad Hoc Distributed Queries», поскольку он отключен в результате настройки конфигурации безопасности сервера. Использование «Ad Hoc Distributed Queries» может быть разрешено администратором при помощи хранимой процедуры sp_configure
—Решил так:
sp_configure ‘show advanced options’, 1;
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
RECONFIGURE;
GO—Более полная документация есть в этой статье
—-http://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htmНа этом все, видео можно увидеть на моем канале YouTube
P.S. BONUS :))
Прочтой пример выборки из excel файла:SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′, N’Excel 8.0;Database=C:detailстрой_ОООdet_day_1_part_1.xlsx’, ‘SELECT * FROM [detail$]’)