9.1 release of Information server has Native Excel Access – A New stage that provides direct Excel read capabilities on all platforms with a rich set of features to handle ranges, multiple worksheets, unstructured text and more.
Prior to Information Server 9.1 release, it was difficult to access an Excel work sheet directly from a DataStage job. One would have to access Excel file via ODBC – this approach requires creating an ODBC connection to the Excel file on a Datastage server machine and use an ODBC stage in Datastage. The main disadvantage is that it was impossible to do this on an Unix machine.
But the New Unstructured Stage provides….
- 3-Step Configuration – enter file name, select data range and setup column name mapping
- Native Excel File Support – reads data from any supported OS (Windows, Linux, Unix)
- XLS Version Variants – includes both Excel 97-2003 OLE2 (.xls) and 2007 OOXML (.xlsx) file format
- Simplified Access – no need to define ODBC DSN for each excel file
- Password Protected Files – read data from password encrypted files
- Multiple File Support – ability to simultaneously process multiple files in different nodes
- Multiple Sheet Support – extract data from multiple sheets at a time
- Runtime Column Propagation – define column metadata
- Excel Field Extraction – ability to extract row numbers, comments, hyperlinks, formulas, document Properties, etc…