The unstructured data stage in DataStage allows to read and write Excel files in DataStage. It was added in the latest version of DataStage. Earlier the Excel files were required to convert into CSV or delimited files but that not any more.
- Excel 97-2003 OLE2 (.xls),
- Excel 2007-2010 OOXML (.xlsx)
Designing Job with Unstructured stage:
To Create Excel file:
- double-click the Unstructured Data stage.
- Stage type > Document type >Excel
- Set write mode to create file
- Click configure
- Set write method to Specific File and File Update mode to Overwrites
- In the properties section, define column header = column name and adjust width: yes
- Click OK and Compile the job.
To read Excel sheet as a source:
- Design the job as Sequential stage ? substructure stage
- Double-click Unstructured Data stage.
- Specify the full path of Excel sheet
- set the Range Expression field like Sheet1!A6: K8
- configure the column header to the first row of the data range
- .Click import and then OK
- In the Output -> column specify the data type for columns