Работа с данными Excel в ODI нелегка, плюс именованные диапазоны данных (named ranges) должны быть созданы в файле Excel. Это происходит по причине того, что существующая технология ODI работает на основе преобразования именованных диапазонов в таблицы.
Мы можем изменить стандартную конфигурацию чтобы работать с данными Excel так же легко, как это происходит с данными других технологий.
Конфигурационные изменения, которые я имею ввиду, были опробованы в ODI 10.3 и java 1.4. Сделать необходимо следующее.
Конфигурация ODI.
- Необходим JDBC драйвер XLSQL Excel. Этот драйвер бесплатен и может быть загружен отсюда - http://sourceforge.net/projects/xlsql/
- Добавьте этот драйвер в ODI. Чтобы сделать это скопируйте файл под названием xlSQL_Y7.jar (имя зависит от версии драйвера) в папку с драйверами (обычно это папка OraHome\oracledi\drivers). Также убедитесь что переменная classpath содержит путь к этому драйверу в файле odiparams.bat
- Добавьте новый скриптовый язык для работы с этим драйвером в соответствующую папку (обычно это 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
- Закройте все программы ODI и перезапустите агентов.
Подключение Excel файла.
- Запустие Менеджер Топологий.
- Создайте новый дата сервер для технологии Microsoft Excel.
- Используйте строку com.nilostep.xlsql.jdbc.xlDriver для драйвера.
- Укажите путь к вашему Excel файлу в ссылке. Формат для ссылки: jdbc:nilostep:excel:folderpath
- Создайте логическую схему и соедините с физической схемой через контекст.
Получить структуру файла.
- Откройте дизайнер.
- Добавьте модель.
- Во вкладке свойств задайте технологию Microsoft Excel.
- Во вкладке реверса выберите стандартный способ реверса.
- Во вкладке выборочного реверса получите все таблицы. Таблицы будут созданы в виде Имя_Файла.Имя_Страницы.
- Нажмите кнопку "Реверс" и получите эти таблицы в дереве модели.
- Задайте типы данных для колонок полученных таблиц.
Получение данных Excel.
Модули знаний включенные в ODI работают с указанным драйвером достаточно хорошо. Возникла только одна проблема при загрузке данных из Excel в таблицу БД sql сервера. Суть проблемы в том, что имена таблицы и колонок в выражении select должны быть в двойных кавычках.
Чтобы исправить это выполните следующее:
- Дуплицируйте модуль знаний LKM SQL to SQL.
- Откройте новый модуль на редактирование и установите Microsoft Excel как технологию источника.
- Измените шаг 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()%> - Создайте новый интерфейс. Перетащите ваши Excel таблицы из модели в область источников данных и таблицу - приемник данных.
- Во вкладке Flow выберите измененный вами LKM модуль и нужный вам интеграционный модуль знаний.
- Выполните интерфейс и загрузите данные из 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!
Комментариев нет:
Отправить комментарий
Примечание. Отправлять комментарии могут только участники этого блога.