SQL-Ex blog

Імпорт даних із файлу Excel до бази даних SQL Server за допомогою Python

Є багато способів завантажити дані з 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 означає використання довірчого підключення.

Після аутентифікації створимо нову базу даних наступною командою:

CREATE DATABASE ExcelData;  
GO

Використовуйте цю команду SQLCMD для підтвердження створення бази даних:

SELECT name FROM sys.databases  
GO

Нижче показано висновок команди, який показує всі наявні бази даних цього екземпляра SQL Server.

Для перемикання на нову базу даних використовуйте таку команду:

Буде одержано підтвердження зміни контексту, як показано нижче:

Тепер ми можемо створити таблицю у цій базі даних.

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, вставте в нього нижченаведений код і змініть джерело даних на потрібне.

import sqlalchemy as sa
від 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, може читати дані Excel у програмі Python за допомогою функції pandas.read_excel().

Для простоти цієї демонстрації, збережемо файл Excel у папці проекту Visual Studio Code, щоб нам не довелося вказувати шлях.

Ми також будемо використовувати openpyxl як движок для читання файлів Excel.

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.

Зворотні посилання

Немає зворотних посилань

Коментарі

Показувати коментарі Як список | Деревоподібною структурою

Автор не дозволив коментувати цей запис

Як перенести дані з Excel у SQL?

Відкрити файл Excel за допомогою OleDbConnection або надбудови над Office і скопіювати в базі даних Sql Server за допомогою SqlBulkCopy .

У SQL Server є майстер Import Data.

Швидкий та брудний спосіб: з Excel до таблиці SQL Server можна скопіпастити шматок даних за умови збігу типів полів та їх кількості. Була тільки якась хитрість, AFAIR, в Excel-і потрібний спочатку таблиці порожній стовпець.

Але акуратніше і надійніше зробити так, як сказав @msi.

вибачаюсь за неповноту питання.справа в тому, що необхідно, щоб дані з excel-таблиці переносилися в таблицю sql кінцевим користувачем по натисканню кнопки на веб-сторінці (код написаний на asp.net.), але дякую Вам за відповідь.

Імпорт даних у SQL Server або базу даних Azure з Excel

Імпортувати дані з файлів Excel у SQL Server або базу даних SQL Azure можна кількома способами. Деякі методи дозволяють імпортувати дані за один крок безпосередньо із файлів Excel. Для інших методів необхідно експортувати дані Excel у вигляді тексту (CSV-файлу), перш ніж їх можна буде імпортувати.

У цій статті перелічені методи, що часто використовуються, і містяться посилання для отримання додаткових відомостей. Однак в ній не вказано повного опису таких складних інструментів і служб, як SSIS або Фабрика даних Azure. Додаткові відомості про рішення, що вас цікавить, див. у вказаних посиланнях.

Список методів

Існує кілька способів імпорту даних із Excel. Щоб використати деякі з цих засобів, необхідно встановити СЕРЕДОВИЩЕ SQL Server Management Studio (SSMS).

Для імпорту даних із Excel можна використовувати такі засоби:

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

Додаткові відомості див. в обмеженнях та відомих проблемах для завантаження даних у файли Excel або з неї.

Майстер імпорту та експорту

Імпортуйте дані безпосередньо з файлів Excel за допомогою майстра імпорту та експорту SQL Server.Ви також можете зберегти параметри у вигляді пакета СЛУЖБ SQL Server Integration Services (SSIS), який можна налаштувати та повторно використовувати пізніше.

1. У SQL Server Management Studio підключіться до екземпляра ядра СУБД SQL Server.
2. Розгорніть вузол Бази даних.
3. Клацніть правою кнопкою миші базу даних.
4. Виберіть "Завдання".
5. Виберіть Імпортувати дані або Експортувати дані:

Для отримання додаткових відомостей див. у наступних статтях:

Служби Integration Services (SSIS)

Якщо ви знайомі з SQL Server Integration Services (SSIS) і не хочете запускати майстер імпорту та експорту SQL Server, можна створити пакет служб SSIS, який використовує джерело Excel та призначення SQL Server у потоці даних.

Для отримання додаткових відомостей див. у наступних статтях:

Щоб навчитися створювати пакети SSIS, див. керівництво How to Create an ETL Package (Як створити пакет ETL).

OPENROWSET та пов'язані сервери

У базі даних SQL Azure неможливо імпортувати безпосередньо з Excel. Спочатку необхідно експортувати дані до текстового файлу (CSV).

Постачальник ACE (колишня назва – постачальник Jet), який підключається до джерел даних Excel, призначений для інтерактивного використання клієнта. Якщо ви використовуєте постачальник ACE у SQL Server, особливо в автоматизованих процесах або процесах, що виконуються паралельно, можуть з'явитися непередбачені результати.

Розподілені запити

Імпортуйте дані безпосередньо з файлів Excel у SQL Server за допомогою функції Transact-SQL OPENROWSET або OPENDATASOURCE . Така операція називається розподілений запит.

У базі даних SQL Azure неможливо імпортувати безпосередньо з Excel. Спочатку необхідно експортувати дані до текстового файлу (CSV).

Перед виконанням розподіленого запиту необхідно включити параметр Ad Hoc Distributed Queries у конфігурації сервера, як показано нижче. Для отримання додаткових відомостей див. розділ "Конфігурація сервера: нерегламентовані розподілені запити".

sp_configure 'show advanced options', 1; RECONFIGURE;

У наведеному нижче прикладі коду дані імпортуються з листа Excel Sheet1 у нову таблицю бази даних за допомогою OPENROWSET.

USE ImportFromExcel;

Нижче наведено той самий приклад з OPENDATASOURCE.

USE ImportFromExcel;

Щоб додати імпортовані дані в існуючу таблицю, а не створювати нову, використовуйте синтаксис INTO .

Для використання даних Excel без імпорту використовуйте стандартний синтаксис SELECT .

Додаткові відомості про розподілені запити див. у наступних статтях:

1 Розподілені запити, як і раніше, підтримуються в SQL Server, але документація цієї функції не оновлюється.

Пов'язані сервери

Крім того, можна налаштувати постійне підключення від SQL Server до файлу Excel як до пов'язаному серверуУ прикладі нижче дані імпортуються з аркуша Excel Data на існуючому зв'язаному сервері EXCELLINK в нову таблицю бази даних SQL Server з ім'ям Data_ls.

USE ImportFromExcel; GO SELECT * INTO Data_ls FROM EXCELLINK

Ви можете створити зв'язаний сервер із SQL Server Management Studio (SSMS) або запустити системну процедуру sp_addlinkedserver , як показано в наступному прикладі.

DECLARE @RC INT; DECLARE @server NVARCHAR(128); DECLARE @srvproduct NVARCHAR(128); DECLARE @provider NVARCHAR(128); DECLARE @datasrc NVARCHAR(4000); DECLARE @location NVARCHAR(4000); DECLARE @provstr NVARCHAR(4000); DECLARE @catalog NVARCHAR(128); -- Set parameter values ​​SET @server = 'EXCELLINK'; SET @srvproduct = 'Excel'; SET @provider = 'Microsoft.ACE.OLEDB.12.0'; SET @datasrc = 'C:\Temp\Data.xlsx'; SET @provstr = 'Excel 12.0'; EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog;

Для отримання додаткових відомостей про пов'язані сервери див. наступні статті:

Додаткові приклади та відомості про пов'язані сервери та розподілені запити див. у статті:

Необхідні компоненти

Щоб використовувати інші методи, описані на цій сторінці ( BULK INSERT оператор, засіб bcp або Фабрика даних (Azure), спочатку необхідно експортувати дані Excel у текстовий файл.

Збереження даних Excel у вигляді тексту

В Excel виберіть "Файл" | Збережіть як та виберіть текст (розділювача табуляції) (*.txt) або CSV (розділювачі-коми) (*.csv) як тип цільового файлу.

Якщо ви хочете експортувати кілька аркушів з книги, виберіть кожний аркуш і повторіть процедуру. Команда Зберегти як експортує лише активний лист.

Щоб оптимізувати використання імпорту, зберігайте аркуші, які містять лише заголовки стовпців та рядки даних. Якщо збережені дані містять заголовки сторінок, порожні рядки, нотатки тощо, при імпорті даних можуть з'явитися непередбачені результати.

Майстер імпорту неструктурованих файлів

Імпортуйте дані, збережені як текстові файли, виконавши інструкції на сторінках майстра імпорту неструктурованих файлів.

Як описано раніше в розділі "Попередні вимоги", необхідно експортувати дані Excel у вигляді тексту, перш ніж використовувати майстер імпорту неструктурованих файлів для імпорту.

Щоб отримати додаткові відомості про майстра імпорту неструктурованих файлів, див. у розділі Майстер імпорту неструктурованих файлів у SQL.

Команда BULK INSERT

BULK INSERT — це команда Transact-SQL, яку можна виконати у SQL Server Management Studio. У наведеному нижче прикладі дані завантажуються з файлу Data.csv з роздільниками-комами в існуючу таблицю бази даних.

Як описано раніше в розділі попередніх вимог, необхідно експортувати дані Excel у вигляді тексту, перш ніж використовувати BULK INSERT для імпорту. BULK INSERT Неможливо зчитувати файли Excel безпосередньо. BULK INSERT За допомогою команди можна імпортувати файл CSV, що зберігається локально або в сховище BLOB-об'єктів Azure.

USE ImportFromExcel; GO BULK INSERT Data_bi FROM 'C:\Temp\data.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'); GO

Додаткові відомості та приклади SQL Server та База даних SQL Azure див. у наступних статтях:

Засіб масового копіювання (bcp)

Засіб bcp запускається із командного рядка. У наведеному прикладі дані завантажуються з файлу Data.csv з роздільниками-комами в існуючу таблицю бази даних Data_bcp .

Як описано раніше у розділі "Попередні вимоги", необхідно експортувати дані Excel у вигляді тексту, перш ніж використовувати bcp для його імпорту. Засіб bcp не може безпосередньо читати файли Excel. Використовується для імпорту SQL Server або бази даних SQL з текстового файлу (CSV), збереженого в локальному сховищі.

Для текстового файлу (CSV), що зберігається у сховищі BLOB-об'єктів Azure, використовуйте BULK INSERT або OPENROWSET . Приклад див. у розділі "Використання BULK INSERT" або OPENROWSET(BULK. ) для імпорту даних у SQL Server.

bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,

Додаткові відомості про bcp див. у наступних статтях:

Майстер копіювання (ADF)

Імпортуйте дані, збережені як текстові файли, за допомогою покрокової інструкції майстра копіювання Фабрики даних Azure (ADF).

Як описано раніше в розділі "Попередні вимоги", необхідно експортувати дані Excel у вигляді тексту, перш ніж використовувати завод даних Azure для його імпорту. Фабрика даних не може зчитувати файли Excel безпосередньо.

Щоб отримати додаткові відомості про майстра копіювання, див.

Azure Data Factory

Якщо ви вже працювали з фабрикою даних Azure і не хочете запускати майстер копіювання, створіть конвеєр з копіюванням з текстового файлу в SQL Server або Базу даних SQL Azure.

Як описано раніше в розділі "Попередні вимоги", необхідно експортувати дані Excel у вигляді тексту, перш ніж використовувати завод даних Azure для його імпорту. Фабрика даних не може зчитувати файли Excel безпосередньо.

Додаткові відомості про використання цих джерел та приймачів фабрики даних див. у наступних статтях:

Щоб дізнатися, як скопіювати дані за допомогою фабрики даних Azure, див.

Поширені помилки

Microsoft.ACE.OLEDB.12.0" не зареєстровано

Ця помилка виникає, оскільки постачальник OLEDB не встановлено. Встановіть його з поширеного компонента Microsoft Access ядро ​​СУБД 2016. Не забудьте встановити 64-розрядну версію, якщо Windows та SQL Server — 64-розрядні.

Повний текст помилки.

Msg 7403, Level 16, State 1, Line 3 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" не буде registered.

Неможливо створити екземпляр постачальника OLE DB "Microsoft.ACE.OLEDB.12.0" для зв'язаного сервера "(null)"

Ця помилка означає, що Microsoft OLEDB не налаштований належним чином. Щоб вирішити цю проблему, виконайте наступний код Transact-SQL:

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1; EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;

Повний текст помилки.

Msg 7302, Level 16, State 1, Line 3 Cannot створено за допомогою OLE DB provider "Microsoft.ACE.OLEDB.12.0" для linked server "(null)".

32-розрядний постачальник OLE DB "Microsoft.ACE.OLEDB.12.0" не може бути завантажений у процесі на 64-розрядну версію SQL Server.

Ця помилка виникає під час встановлення 32-розрядної версії постачальника OLD DB з 64-розрядною версією SQL Server. Щоб вирішити цю проблему, видаліть 32-розрядну версію та встановіть 64-розрядну версію постачальника OLE DB замість неї.

Повний текст помилки.

Msg 7438, Level 16, State 1, Line 3 The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" може бути завантажений в-процес на 64-bit SQL Server.

Постачальник OLE DB "Microsoft.ACE.OLEDB.12.0" для зв'язаного сервера "(null)" повідомив про помилку.

Ця помилка зазвичай вказує на проблеми з роздільною здатністю між процесом SQL Server і файлом. Переконайтеся, що обліковий запис SQL Server має дозвіл на повний доступ до файлу. Ми не рекомендуємо імпортувати файли з настільного комп'ютера.

Повний текст помилки.

Msg 7399, Level 16, State 1, Line 3 OLE DB provider "Microsoft.ACE.OLEDB.12.0" для linked server "(null)" reported an error. Provider did not give any information про the error.

Неможливо ініціалізувати об'єкт джерела даних постачальника OLE DB "Microsoft.ACE.OLEDB.12.0" для зв'язаного сервера "(null)"

Ця помилка зазвичай вказує на проблеми з роздільною здатністю між процесом SQL Server і файлом. Переконайтеся, що обліковий запис SQL Server має дозвіл на повний доступ до файлу. Ми не рекомендуємо імпортувати файли з настільного комп'ютера.

Повний текст помилки.

Msg 7303, Level 16, State 1, Line 3 Cannot initialize the source object data of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Пов'язаний контент