Как загрузить данные из excel в oracle sql developer

Импорт данных c Excel в Oracle

Коллеги, мне необходимо просто загрузить данные с excel в oracle (с таблицы в таблицу), как это сделать? Можно ли обойтись без различных даталоудеров? Возможно как то через sql developer (использую его). Подскажите пожалуйста.

94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Импорт в Oracle из Excel
Здравствуйте! Подскажите пожалуйста. Как можно импортьровать БД набитые в Excel в Oracle?

Импорт таблиц oracle в excel
Добрый день. Есть такая задача: есть таблица с информацией. Требуется на ее основе формировать файл.

Экспорт и импорт данных oracle 7
Здравствуйте. Занимаюсь переносом таблиц БД Оракл из одной базы в другую. За экспорт отвечает.

Из Excel экспорт в базу данных Oracle
Есть таблица Exel. Как наиболее быстро можно вставить в таблицу Oracle данные из Exel? Заранее.

Экспорт содержимого из анализа и информационной панели

Вы можете экспортировать содержимое из анализа и информационной панели.

• Экспорт результатов анализа
• Экспорт информационных панелей и их страниц
• Советы по экспорту

Экспорт результатов анализа

Результаты анализа можно экспортировать в различные форматы, включая данные и форматирование в форматах Microsoft Office Excel, Adobe PDF и CSV, а также в различные форматы, предназначенные только для данных (т. е. без форматирования).

Например, можно экспортировать анализ «Контроль ассортимента», чтобы поставщики могли посмотреть результаты в приложении Microsoft Excel.

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

1. Откройте результаты анализа для редактирования.
2. Для экспорта данных и форматирования нажмите Экспорт данного анализа , затем Форматированный и выберите формат вывода.
3. Чтобы экспортировать только данные, нажмите Экспорт данного анализа , затем Данные и выберите формат вывода.

Экспорт информационных панелей и их страниц

Информационную панель целиком или ее отдельную страницу можно экспортировать в Microsoft Excel 2007+. При экспорте содержимого информационной панели в Microsoft Excel состояние панели (например, приглашения или переходы по иерархии) не изменяются.

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

1. Откройте информационную панель или страницу информационной панели, которые требуется экспортировать.
2. На панели инструментов страницы «Информационная панель» нажмите кнопку Параметры страницы , выберите Экспорт в Excel , а затем – Экспортировать текущую страницу или Экспортировать всю информационную панель .

• В книге Excel каждая страница помещается на отдельный лист.
• Каждому листу присваивается имя соответствующей страницы информационной панели.

Советы по экспорту

Вот несколько советов по экспорту данных из анализов, информационных панелей и страниц информационной панели.

• При экспорте данных объемом более миллиона строк узнайте у администратора, какое максимальное число строк вы можете экспортировать.
• По умолчанию параметр Подавление значений в диалоговом окне Свойства столбца на вкладке Формат столбца определяет, будут ли повторяться ячейки строк и столбцов в таблицах или сводных таблицах при экспорте в Excel (вместо обязательного повторения). Не подавляйте значения при экспорте в Excel, если пользователи таблиц Excel хотят работать с данными.
  • Если для параметра Подавление значений задано значение Подавить , ячейки строк и ячейки столбцов не повторяются. Например, в таблице со значениями «Год» и «Месяц» значение «Год» отображается для значений «Месяц» только один раз. Такое подавление значений полезно для упрощения данных представлений в таблицах Excel.
  • Если для параметра Подавление значений задано значение Повторить , ячейки срок и ячейки столбцов повторяются. Например, в таблице со значениями «Год» и «Месяц» значение «Год» повторяется для всех значений «Месяц».

  How to import data from Excel to PL/SQL Developer

  I need to compare data from tables in Oracle with data from tables in excel, how can I import data from excel. How can I import data from excel to test table in oracle pl/sql dev?

  152k 11 11 gold badges 62 62 silver badges 123 123 bronze badges
  asked May 26, 2022 at 11:27
  Andrey Romanov Andrey Romanov
  69 1 1 silver badge 7 7 bronze badges

  In the title you write you are using «PL/SQL Developer» but in the body you say «oracle pl/sql dev»; the problem with this is that «PL/SQL Developer» is a third-party client application developed by Allround-Automations and is nothing to do with Oracle whereas Oracle’s client application is called «SQL Developer» (No «PL/» in the name). Which are you using as there will be different solutions for each client application?

  May 26, 2022 at 12:00

  I sometimes add a column in excel containing a formula that compose an INSERT statement from the values of that row. This way I just need to copy and paste the whole column into sqlplus, sqldeveloper or any other tool, which allows to run sql-scripts.

  May 26, 2022 at 18:02

  Please clarify your specific problem or provide additional details to highlight exactly what you need. As it’s currently written, it’s hard to tell exactly what you’re asking.

  May 27, 2022 at 9:28

  2 Answers 2

  if you have 0ver 100 k data or upper maybe almost a million data , i suggest to change data type of your excel to csv file,then now your going to import to pl/sql : 1. go to tools and choose text importer 2. then browse csv file and upload (data from textfile) 3. go to next tab and choose user and your table (data to oracle) 4. make sure your field suitable with your csv data 5. then import, if you find «ora» error make sure your varchar fit the data

  answered Nov 8, 2022 at 8:10
  11 1 1 bronze badge

  There are two options:

  Option I:

  • Create test table with no. of columns and data type matching with excel data.
  • Open the test table in PL/SQL developer in edit mode. You can do this by selecting rowid in SELECT statement or writing the SELECT with ‘for update’. For example:

  SELECT t.*, t.rowid FROM test t; OR SELECT t.* FROM test t for update; 

  • Click on the lock ICON to keep it in pressed status for opening the fetched rows for editing.
  • Now copy the data range from excel and in PL/SQL developers SQL result grid, select the empty column and paste the copied data. (Note: you would need to add one empty column in the beginning of your first data column and include that blank column as well while copying data. This blank column is a placeholder for the serial no. column in PL/SQL developer’s result grid. Otherwise your first data column will be eaten up by the serial no. column of result grid. 🙂 ).
  • Click on green tick to commit the data to database.

  Option II: In excel you can compose INSERT query referencing data cells with help of Excel’s concatenation (using & ) and execute all INSERT in PL/SQL developer.

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

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

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

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

  Существует несколько способов импорта данных из Excel. Для использования некоторых из этих инструментов может понадобиться установка SQL Server Management Studio (SSMS).

  Для импорта данных из Excel можно использовать следующие средства:

  Сначала экспортировать в текст (SQL Server и база данных SQL)	Непосредственно из Excel (только в локальной среде SQL Server)
  Мастер импорта неструктурированных файлов	мастер импорта и экспорта SQL Server
  Инструкция BULK INSERT	Службы SQL Server Integration Services
  BCP	Функция OPENROWSET
  Мастер копирования (Фабрика данных Azure)
  Фабрика данных Azure.

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

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

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

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

  

  1. В SQL Server Management Studio подключитесь к экземпляру SQL Server Компонент Database Engine.
  2. Разверните узел Базы данных.
  3. Щелкните базу данных правой кнопкой мыши.
  4. Выберите Задачи.
  5. Выберите Импортировать данные или Экспортировать данные:

  

  Дополнительные сведения см. в следующих статьях:

  • Запуск мастера импорта и экспорта SQL Server
  • Приступая к работе с простым примером мастера импорта и экспорта

  Службы Integration Services (SSIS)

  Если вы работали с SQL Server Integration Services (SSIS) и не хотите запускать мастер импорта и экспорта SQL Server, создайте пакет SSIS, который использует в потоке данных источник «Excel» и назначение «SQL Server».

  Дополнительные сведения см. в следующих статьях:

  • Источник 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 в конфигурации сервера, как показано в примере ниже. Дополнительные сведения см. в статье ad hoc distributed queries Server Configuration Option (Параметр конфигурации сервера «ad hoc distributed queries»).

  sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'ad hoc distributed queries', 1; RECONFIGURE; GO 

  В приведенном ниже примере кода данные импортируются из листа Excel Sheet1 в новую таблицу базы данных с помощью OPENROWSET .

  USE ImportFromExcel; GO SELECT * INTO Data_dq FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]); GO 

  Ниже приведен тот же пример с OPENDATASOURCE .

  USE ImportFromExcel; GO SELECT * INTO Data_dq FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0'). [Sheet1$]; GO 

  Чтобы добавить импортированные данные в существующую таблицу, а не создавать новую, используйте синтаксис INSERT INTO . SELECT . FROM . вместо синтаксиса SELECT . INTO . FROM . из предыдущих примеров.

  Для обращения к данным Excel без импорта используйте стандартный синтаксис SELECT . FROM . .

  Дополнительные сведения о распределенных запросах см. в следующих статьях:

  • Распределенные запросы (распределенные запросы по-прежнему поддерживаются в SQL Server 2019 г. (15.x), но документация по этой функции не была обновлена.)
  • OPENROWSET
  • OPENDATASOURCE

  Связанные серверы

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

  USE ImportFromExcel; GO SELECT * INTO Data_ls FROM EXCELLINK. [Data$]; GO 

  Вы можете создать связанный сервер из 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; 

  Дополнительные сведения о связанных серверах см. в следующих статьях:

  • Создание связанных серверов
  • OPENQUERY

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

  Предварительное требование — сохранение данных Excel как текст

  Чтобы использовать другие методы, описанные на этой странице (инструкцию BULK INSERT, средство BCP или фабрику данных Azure), сначала экспортируйте данные 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 см. в следующих статьях:

  • Массовый импорт данных с помощью инструкции BULK INSERT или OPENROWSET(BULK. )
  • BULK INSERT

  Средство BCP

  BCP — это программа, которая запускается из командной строки. В приведенном ниже примере данные загружаются из файла Data.csv с разделителями-запятыми в существующую таблицу базы данных Data_bcp .

  Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать BCP для их импорта. BCP не может считывать файлы Excel напрямую. Используется для импорта в SQL Server или базу данных SQL из текстового файла (CSV), сохраненного в локальном хранилище.

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

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

  Дополнительные сведения о BCP см. в следующих статьях:

  • Массовый импорт и экспорт данных с помощью программы bcp
  • Программа bcp
  • Подготовка данных к массовому экспорту или импорту

  Мастер копирования (ADF)

  Импортируйте данные, сохраненные как текстовые файлы, с помощью пошаговой инструкции мастера копирования Фабрики данных Azure (ADF).

  Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать фабрику данных Azure для их импорта. Фабрика данных не может считывать файлы Excel напрямую.

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

  • Мастер копирования фабрики данных
  • Руководство. Создание конвейера с действием копирования с помощью мастера копирования фабрики данных.

  Фабрика данных Azure

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

  Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать фабрику данных Azure для их импорта. Фабрика данных не может считывать файлы Excel напрямую.

  Дополнительные сведения об использовании этих источников и приемников Фабрики данных см. в следующих статьях:

  • Файловая система
  • SQL Server
  • База данных SQL Azure

  Сведения о копировании данных с помощью фабрики данных Azure см. в следующих статьях:

  • Перемещение данных с помощью действия копирования
  • Руководство. Создание конвейера с действием копирования с помощью портала Azure

  Распространенные ошибки

  Microsoft.ACE.OLEDB.12.0″ не зарегистрирован

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

  Полный текст ошибки.

  Msg 7403, Level 16, State 1, Line 3 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been 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 create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for 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" cannot be loaded in-process on a 64-bit SQL Server. 

  Поставщик OLE DB «Microsoft.ACE.OLEDB.12.0» для связанного сервера «(null)» сообщил об ошибке.

  Не удалось проинициализировать объект источника данных поставщика OLE DB «Microsoft.ACE.OLEDB.12.0» для связанного сервера «(null)».

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

  Полный текст ошибки.

  Msg 7399, Level 16, State 1, Line 3 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error. 

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

  Дальнейшие действия

  • Приступая к работе с простым примером мастера импорта и экспорта
  • Импорт данных из Excel или экспорт данных в Excel с помощью служб SQL Server Integration Services (SSIS)
  • Программа bcp
  • Перемещение данных с помощью действия копирования
  Похожие публикации:

  1. Как найти человека вконтакте по номеру телефона
  2. Как поменять цвет папок в mac os
  3. Как сбросить honor 20 pro до заводских настроек
  4. Почему 1с не удаляет помеченные объекты