Технология Excel для ODI.

Перевод статьи Excel technology in Oracle Data Integrator. В переводе использованы скриншоты из оригинальной заметки.

---

Работа с данными Excel в ODI нелегка, плюс именованные диапазоны данных (named ranges) должны быть созданы в файле Excel. Это происходит по причине того, что существующая технология ODI работает на основе преобразования именованных диапазонов в таблицы.

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

Конфигурационные изменения, которые я имею ввиду, были опробованы в ODI 10.3 и java 1.4. Сделать необходимо следующее.

Конфигурация ODI.

1. Необходим JDBC драйвер XLSQL Excel. Этот драйвер бесплатен и может быть загружен отсюда - http://sourceforge.net/projects/xlsql/
2. Добавьте этот драйвер в ODI. Чтобы сделать это скопируйте файл под названием xlSQL_Y7.jar (имя зависит от версии драйвера) в папку с драйверами (обычно это папка OraHome\oracledi\drivers). Также убедитесь что переменная classpath содержит путь к этому драйверу в файле odiparams.bat
3. Добавьте новый скриптовый язык для работы с этим драйвером в соответствующую папку (обычно это OraHome\oracledi\lib\scripting). Чтобы сделать это скопируйте следующие файлы:
   
   • commons-cli-1.0.jar
   • commons-logging.jar
   • crimson.jar
   • hsqldb.jar
   • jaxp.jar
   • jconfig.jar
   • jmxri.jar
   • jxl.jar
   • mysql-connector-java-3.0.10-stable-bin.jar
   Убедитесь, чтобы classpath также указывал на папку со скопированными файлами.
4. Закройте все программы ODI и перезапустите агентов.

Подключение Excel файла.

1. Запустие Менеджер Топологий.
2. Создайте новый дата сервер для технологии Microsoft Excel.
3. Используйте строку com.nilostep.xlsql.jdbc.xlDriver для драйвера.
4. Укажите путь к вашему Excel файлу в ссылке. Формат для ссылки: jdbc:nilostep:excel:folderpath

5. Создайте логическую схему и соедините с физической схемой через контекст.

Получить структуру файла.

1. Откройте дизайнер.
2. Добавьте модель.
3. Во вкладке свойств задайте технологию Microsoft Excel.
4. Во вкладке реверса выберите стандартный способ реверса.
5. Во вкладке выборочного реверса получите все таблицы. Таблицы будут созданы в виде Имя_Файла.Имя_Страницы.
6. Нажмите кнопку "Реверс" и получите эти таблицы в дереве модели.
7. Задайте типы данных для колонок полученных таблиц.

Получение данных Excel.
Модули знаний включенные в ODI работают с указанным драйвером достаточно хорошо. Возникла только одна проблема при загрузке данных из Excel в таблицу БД sql сервера. Суть проблемы в том, что имена таблицы и колонок в выражении select должны быть в двойных кавычках.
Чтобы исправить это выполните следующее:

1. Дуплицируйте модуль знаний LKM SQL to SQL.
2. Откройте новый модуль на редактирование и установите Microsoft Excel как технологию источника.

3. Измените шаг Load data и введите следующий код:
   select <%=snpRef.getPop("DISTINCT_ROWS")%>
   <%=snpRef.getColList("", "\u0022[COL_HEADING]\u0022", ",\n\t", "", "")%>
   from "<%=odiRef.getSrcTablesList("[TABLE_ALIAS]","")%>"
   where (1=1)
   <%=snpRef.getFilter()%>
   <%=snpRef.getJrnFilter()%>
   <%=snpRef.getJoin()%>
   <%=snpRef.getGrpBy()%>
   <%=snpRef.getHaving()%>

4. Создайте новый интерфейс. Перетащите ваши Excel таблицы из модели в область источников данных и таблицу - приемник данных.
5. Во вкладке Flow выберите измененный вами LKM модуль и нужный вам интеграционный модуль знаний.
6. Выполните интерфейс и загрузите данные из Excel в таблицу в базе данных.

Заключение.

После небольшой настройки и без дополнительных материальных затрат, получение данных Excel через ODI становится простым и удобным, ведь мы получаем возможность оперировать данными Excel абсолютно в том же духе что и данными из БД.

---

Примечания переводчика:
1. У меня на момент написания данной заметки не было возможности протестировать работоспособность предложенного подхода.
2. Драйвер для Excle JDBC существовал и по прежнему был бесплатен.

Из комментариев:

Вопрос: Спасибо, у меня получилось прочитать данные из Excel. Но теперь мне нужно сделать обратную операцию - использовать Excel таблицу как целевую в интерфейсе. Но когда я попробовал сделать это, у меня появилась следующая ошибка:
0 : null : java.sql.SQLException: not supported Можно ли использовать драйвер xlSql для таких операций?
Ответ: Чтобы вставить данные в Excel файл при помощи этого драйвера я использовал следующий код на Java. Этот код вставляет последовательно одну строку за другой.

try {
String driver = "com.nilostep.xlsql.jdbc.xlDriver";
Driver d = (Driver) Class.forName(driver).newInstance();
String protocol = "jdbc:nilostep:excel";
String database = System.getProperty("user.dir");
String url = protocol + ":" + database;
Connection con = DriverManager.getConnection(url);
Statement stm = con.createStatement();

String sql = "DROP TABLE \"demo.xlsqly7\" IF EXISTS;"
+ "CREATE TABLE \"demo.xlsqly7\" (v varchar);";
stm.execute(sql);

// Для версии драйвера Y7 мы сгенерируем добавление 7000 строк.
for (int i = 0; i < 7000; i++) { sql = "INSERT INTO \"demo.xlsqly7\" VALUES ('xlSQL Y7 – NiLOSTEP');"; stm.execute(sql); }

Вопрос: Привет. У меня не выходит просмотреть данные Excel через команду модели View Data. Ошибка: com.borland.dx.dataset.DataSetException error code: BASE+66
com.borland.dx.dataset.DataSetException: Table not found: HOJA1 in statement [select * from func0510011.Hoja1]
Ответ: Испанская версия использует Hoja1 как стандартное название для страницы Excel файла. Измените его на имеющееся у вас наименование.

Вопрос: Спасибо за заметку! У меня есть проблема - я не могу даже сделать реверс Excel файлов в модели. Несмотря на то, что тест в топологии проходит, после реверса я не вижу ни одной таблицы. А мне нужно загружать Excel файлы на Unix платформе так что хочу избежать использования ODBC драйверов.
Ответ: Одной из причин может быть формат Excel файла. Попробуйте сохранить ваши данные в один из более старых форматов.

Вопрос: У меня были проблемы с описываемым драйвером, но со следующей версией драйвера - xlSQL_Y8.jar все заработало. Да, я подключил его к ODI 11g!