Microsoft Office Exel вже досить давно став ключовою програмою при перенесенні різних баз, тк практично будь-яка навіть дуже стара база працює з Exel і перевести в нього даний НЕ состовляет ніяких проблем. Це покрокове керівництво описує різні способи імпорту даних з листів Microsoft Excel в бази даних Microsoft SQL Server.
Вимоги перед початком
У наведеному нижче списку перераховані рекомендовані обладнання, програмне забезпечення, мережева інфраструктура, а також необхідні пакети оновлень:
У ряді розділів даної статті передбачається, що користувач має достатні знання в наступних областях:
- Служби перетворення даних
- Пов'язані сервери і розподілені запити
- Розробка об'єктів ADO на Visual Basic
У прикладах, наведених у цій статті, імпорт даних Excel виконується за допомогою наступних функцій:
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.
Використання пов'язаного сервера
Наступна програма імпортує дані з робочого листа «Customers» пов'язаного сервера Excel «EXCELLINK» в нову таблицю SQL Server з ім'ям XLImport1:
При використанні OPENQUERY джерела може бути переданий наскрізний запит:
'SELECT * FROM [Customers $]')
Використання розподілених запитів
Якщо встановлювати існуюче підключення до книги Excel як пов'язаний сервер небажано, дані можуть бути імпортовані з використанням функцій OPENDATASOURCE або OPENROWSET. У наступних прикладах коду також проводиться імпорт даних з робочого аркуша Excel «Customers» в нові таблиці SQL Server:
Використання ADO і SQLOLEDB
Синтаксис «розподілених запитів», наведений у розділі Використання розподілених запитів, може бути використаний також в додатку ADO для імпорту даних Excel в SQL Server, якщо для підключення до SQL Server використовується Microsoft OLE DB для SQL Server (SQLOLEDB).
Для роботи такого прикладу програми на Visual Basic 6.0 потрібно додавання посилання на проект в об'єкти даних ActiveX (ADO). Цей приклад демонструє використання функцій OPENDATASOURCE і OPENROWSET для підключення SQLOLEDB.
Використання 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:
Для роботи такого прикладу програми на Visual Basic 6.0 потрібно додавання посилання на проект в ADO. У прикладі показаний імпорт даних Excel в SQL Server через підключення ADO з використанням постачальника даних Jet 4.0.
Для імпорту даних Excel в інші бази даних Microsoft Access, бази даних індексного-послідовного методу доступу (ISAM) або бази ODBC також можуть використовуватися конструкції, підтримувані постачальником даних Jet.
Усунення несправностей
Корисні посилання:
Використання Excel зі зв'язаними серверами SQL Server і розподіленими запитами
При використанні ADOX для отримання стовпців таблиці Access стовпці упорядковуються за алфавітом
При використанні DAO OpenRecordset даними в Excel присвоюється значення NULL