Как импортировать данные из Excel в Oracle с помощью SQL Developer

Илья Дергунов

Илья Дергунов

Автор статьи. ИТ-специалист с 20 летним стажем, автор большого количества публикаций на профильную тематику (разработка ПО, администрирование, новостные заметки). Подробнее.

Задача. У вас есть файл Excel – и вы хотите, чтобы эти данные были помещены в таблицу. Я покажу вам, как это делается, и мы задокументируем каждый шаг с большим количеством иллюстраций.

Прочитав этот пост, вы будете готовы с уверенностью импортировать данные в существующую таблицу из Excel. Хотите создать новую таблицу из Excel? Мы тоже можем это сделать.

В нашем примере я буду использовать таблицу HR.EMPLOYEES для создания XLS-файла для нашего импорта. Мы будем использовать этот файл Excel для заполнения пустой копии таблицы EMPLOYEES в другой схеме.

Шаг 0: Пустая таблица Oracle и ваш файл Excel

У вас есть таблица Oracle и один или несколько файлов Excel.

Шаг 1: Выбираем "Импорт данных" по правому щелчку мыши

Шаг 2: Выберите свой входной файл (XLSX) и проверьте данные

Если вы уже запускали этот мастер раньше, вы можете выбрать файлы из предыдущих сеансов.

Когда вы выберете файл, мы захватим первые 100 строк для просмотра ниже. Этот "Предел строк предварительного просмотра" определяет, сколько строк вы можете использовать для проверки ИМПОРТА по мере прохождения мастера. Вы можете увеличить его, но это потребует больше ресурсов, так что не сходите с ума.

Кроме того, есть ли в вашем файле Excel заголовки столбцов? Хотим ли мы рассматривать их как строку к таблице? Скорее всего, нет. Если вы снимите флажок "Заголовок", имена столбцов станут новой строкой в вашей таблице – и, вероятно, не будут вставлены.

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

Шаг 3: Создайте сценарий или импортируйте автоматически

По мере прохождения шагов в мастере мы будем держать панель предварительного просмотра содержимого файла под рукой, так что вам не придется нажимать  alt+tab туда и обратно от Excel до SQL Developer.

Для этого упражнения будет использоваться метод "Вставки" (Insert). Каждая строка, обработанная в файле Excel, приведет к выполнению инструкции INSERT в таблице, в которую мы импортируем.

Если вы выберете "Вставить скрипт", мастер завершит работу скриптом ВСТАВКИ на вашем листе SQL. Это хорошая альтернатива, если вы хотите настроить SQL, или если вам нужно отладить/посмотреть, почему метод "Insert" не работает.

Шаг 4: Выберите столбцы Excel для импорта

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

У вас может быть файл Excel со 100 столбцами, но ваша таблица имеет только 30. Здесь вы даете команду SQL Developer, какие столбцы должны использоваться для импорта. Вы также можете изменить порядок столбцов, что может сделать следующий шаг немного проще.

Шаг 5: Сопоставьте столбцы Excel со столбцами таблицы

Левая панель представляет столбцы в файле XLS. Информация справа показывает, куда идут эти данные и как они будут обрабатываться в Oracle.

Если вы не обращаете внимания и просто позволяете Мастеру импорта делать всё по умолчанию, то сейчас самое время проснуться. Есть большая вероятность, что порядок столбцов файла Excel не будет соответствовать определению вашей таблицы. На этом шаге вы указываете SQL Developer-у, какие столбцы в электронной таблице совпадают с какими столбцами в таблице Oracle.

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

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

Давайте на секунду поговорим о форматах даты и времени.

О TIMESTAMP тоже. В файле Excel у вас, вероятно, будут некоторые поля даты и времени, которые вы хотите переместить в столбцы формата  DATE или TIMESTAMP. SQL Developer обрабатывает эти значения как строки – и ВАМ нужно сообщить SQL Developer формат DATE или TIMESTAMP, чтобы иметь возможность их преобразовать.

Давайте посмотрим на HIREDATE.

Это значение "ха – ха" никогда не войдет в качестве значения HIRE_DATE – если только вы не храните ДАТЫ в VARCHAR2, - и если вы делаете это, то делаете это НЕПРАВИЛЬНО. Всегда храните ДАТЫ в формате DATE!

Видите выпадающий селектор "Формат" (Format)? SQL Developer по умолчанию установил строку формата ДАТЫ в ‘DD-MON-RR’ – мы пытаемся угадать это на основе строк, которые мы рассматриваем в этом окне предварительного просмотра 100.

Если мы ошиблись в догадках или не смогли разобраться, вам нужно будет ввести это самостоятельно. Документы Oracle могут помочь вам определить правильную модель формата ДАТЫ. Если вы видите небольшое предупреждающее изображение рядом со значениями даты на панели данных, возможно, у вас неправильный формат.

Шаг 6: Проверьте свои настройки и ВПЕРЕД!

Кнопка "Готово" запустит магию.

Нажмите на кнопку "Готово" (Finish).

Если Мастер столкнется с какими-либо проблемами при выполнении вставок, вы увидите следующее:

Помните те забавные данные, которые я поместил в файл Excel? Теперь это создает проблемы.

Я собираюсь сказать "Да", чтобы игнорировать все ошибки. Но если вам нужна КАЖДАЯ отдельная строка – вам нужно сказать "Отмена", чтобы начать все сначала. Затем вы можете либо исправить свои данные в файле Excel, либо внести изменения в свою таблицу, чтобы данные соответствовали/работали.

Нажав ‘Да", мы доберемся до конца нашей истории и наших данных!

Шаг 7: Посмотрите, Что сработало, а Что нет

Во-первых, есть ли плохие новости?

Если были строки, отклоненные базой данных, мы увидим их сейчас.

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

Теперь давайте посмотрим на наши новые табличные данные!

Я люблю сладкий запах данных по утрам!

Обратите внимание на панель "Журнал" (Log). Там можно увиеть файл, с которым мы работали, и сколько времени потребовалось для загрузки данных.

Всё! Можно выдохнуть свободно. Импорт из файла Excel в таблицу базы данных Oracle завершен. В шапке статьи вы можете посмотреть видео инструкцию  - копию этого мануала на английском языке.

 

Вас заинтересует / Intresting for you:

Oracle и непроцедурный доступ ...
Oracle и непроцедурный доступ ... 7642 просмотров Antoni Tue, 21 Nov 2017, 13:32:50
Listener Oracle
Listener Oracle 16322 просмотров Stas Belkov Tue, 21 Nov 2017, 13:18:05
NULL в ORACLE и как с ним можн...
NULL в ORACLE и как с ним можн... 6259 просмотров Андрей Васенин Wed, 01 Jul 2020, 08:11:51
Язык SQL в Oracle
Язык SQL в Oracle 3418 просмотров Ирина Светлова Tue, 21 Nov 2017, 13:26:01
Войдите чтобы комментировать