book
Article ID: KB0090639
calendar_today
Updated On: 01-14-2017
Description
Resolution:
BW can't read MS Excel file if it is in Microsoft Format (in other word if they are not in Plain Text format),
but there are some solution:
1. You can save as Excel file as .xml or .csv file, and then read the xml/csv file and map to the target data.
If you can find third party command tools to do the conversion, you may launch the command from "External Command" activity in a BW process.
2. If you use Excel Java APIs like JExcelAPI (http://www.andykhan.com/jexcelapi/) or POI (http://jakarta.apache.org/poi/hssf/index.html),
you can create a Java Code/Method to extract data using those APIs.
3. You may also retrieve information from Excel spreadsheets through ODBC.
First, you set up the spreadsheet as a System DSN through ODBC to point to the file.
In the spreadsheet, you have to define the range of the columns that you are accessing, which becomes your query.
To do this:
highlight the area that you want to retrieve, then from the “Insert” menu, choose “Name” and then “Define”.
Give the range a name, and then use that in your JDBC query.
For example, it may look like “Select * from myrange”.
The one thing to remember is that if you are writing to the spreadsheet, in the ODBC setting, you need to make sure that you uncheck the “Read Only” parameter.
Issue/Introduction
Can BW read MS Excel files?