Импорт из excel
Олег Соловьев 15.07.2010
Руководство попросило импортировать данные из excel в хранилище. Я сразу подумал про три самых популярных метода:
- воспользоваться Import Wizard: File → Import Data,
- назначить библиотеку на excel-файл с помощью ODBC-драйвера,
- положиться на операторы infile и input в data step.
Но все эти методы хороши, если данные в excel хранятся в виде таблицы. Т.е. в первой строке указаны имена столбцов, а в последующих –- данные.
В моем случае на одном excel-листе было несколько блоков с данными. Их, конечно, можно было вручную преобразовать к нескольким таблицам, но при редактировании excel-файла вручную ошибки неизбежны. Да и при следующем импорте пришлось бы повторять операцию заново.
Оказывается, SAS позволяет считывать с excel-листа произвольные прямоугольные области. Для этого в операторе filename нужно указать путь к excel-файлу, добавить опцию DDE и указать прямоугольную область в виде: <верхняя строка><левый столбец>:<нижняя строка><правый столбец>. Например:
options NOXSYNC; X 'c:data.xls'; filename data dde "Excel|[data.xls]Sheet1!R1C1:R2C5";
На шаге данных область из excel-файла можно считать построчно в несколько переменных:
data work.excel; infile data; input var1 var2 var3 var4 var5; run;
или по одной ячейке
data work.excel; infile data; input @@ var; put var; run;
При импорте данных учитывается какой формат наложен на ячейки excel. Например, если формат указывает отображать только целую часть числа, то импортирована будет только целая часть. Также обратите внимание, что у числовых значений символ разделителя – «.» а не «,». В противном случае SAS выдаст сообщение об ошибке.
После написания программы мне остается только следить, чтобы в очередном присланном excel-файлe данные размещались в тех ячейках, которые “зашиты” в программе. Вся процедура выглядит так: сохраняю очередной файл в специальном каталоге и добавляю в имя файла текущую дату (дату загрузки данных в ХД), чтобы облегчить поиск при расследовании ошибок.
Далее открываю программу, редактирую оператор X c именем файла и запускаю программу. Через пару секунд данные из файла excel попадают в хранилище.
Excel - это те грабли, на которые должен наступить каждый уважающий себя SAS-разработчик!
А чем вам не нравиться excelxp.map?
Вот WHS скрипт, который преобразует обычный Excel-евский файл в Excel XML формат (bold надо убрать, там глюк и с италиком, кажется тоже):
Const xlXMLSpreadsheet = 46
Set args = WScript.Arguments
Dim path
Set objShell = CreateObject(”Wscript.Shell”)
path = objShell.CurrentDirectory & “\”
Set objExcel = CreateObject(”Excel.Application”)
objExcel.Visible = True
objExcel.DisplayAlerts = False
objExcel.Workbooks.Open(path & args(0))
Set xBook = objExcel.ActiveWorkbook
Set xSheets = xBook.Sheets
For Each wb In xSheets
wb.Select
wb.Cells.Select
objExcel.Selection.Font.Bold = False
Next
xBook.SaveAs path & args(0) & “.xml”, xlXMLSpreadsheet, False, False
objExcel.Workbooks.Close
objExcel.Quit
и далее
%LET _path=z:/qu/zzz/;
%LET _src_xml=test.xml;
FILENAME tpl_xml “&_path/&_src_xml”;
FILENAME map “&_path/excelxp.map”;
LIBNAME tpl_xml XML XMLMAP=map ACCESS=READONLY;
PROC SQL;
SELECT MAX(WorksheetIndex) INTO:_sheets
FROM tpl_xml._exceldata
;
QUIT;
%PUT &_sheets;
PROC SQL NOPRINT;
CREATE TABLE work.test2 AS
SELECT *
FROM tpl_xml._exceldata
WHERE WorksheetIndex=1
;
QUIT;
а дальше - простор для творчества и заголовки, форматы и т.п. нам не помеха
Метод Олега покороче будет
В случае использования excelxp.map есть свои плюсы - можно не заботится о размерах получаемых блоков, так как в результате получится датасет, в котором каждая ячейка представлена отдельной строкой, с полями что-то типа (row, cell, type, format, value), что дает простор для анализа и группирования. У меня вообще “любовь” к excelxp.map пришла из задачи по перегону огромного количества excel-ских бухгалтерских форм в SAS, со всякими заголовками, сносками, примечаниями и т.п.
Т.е. подход хорош (ИМХО) для решения определенного класса задач, например таких, которые требуют предварительной очистки данных.
Не знаю, как в случае с DDE, а у нас при массовом вводе данных импортом основная проблема - автоопределение типа столбца и его длинны. Вот.
Можно напрямую из Excel записывать в SAS данные или сохранять Excel таблицы в Access и вызывать SAS хранимый процесс для считывания данных уже из Access таблиц.
Я рассматриваю Excel как средство визуализации данных из SAS с помощью вызова SAS хранимых процессов.
Пользователям Excel понятен. Тем более, что можно из Excel в SAS передавать параметры работы ХП.