December 06, 2006, 01:22 AM
OPALTOSHReadin Excel
Can someone please share their experience of reading Excel Workbooks with WebFOCUS. What do you have to do to the Excel data to enable access to it via ODBC from WebFOCUS.
I'mm using 7.1.3 on Windows.
December 06, 2006, 04:14 AM
Tony AA quick overview -
1. Need to define the cells as an ODBC name.
- In Excel, open the spreadsheet you want to report upon and
highlight the range of cells that will be used as a table (you must highlight
headers and ALL data).
- Click the Insert menu and select NAME, use NEW to create a name for the
range of cells and press enter.
- The name you entered appears in the drop-down list for the name box (upper
left-hand) portion of Excel screen.
2. If you already have the ODBC datasource defined, then the ODBC data adapter
will list it on the web console. You can then select CREATE SYNOYNM for that
datasource and will get a list of the cell names you've defined. If not, the
ODBC datasource can be defined by the following:
- Windows Control Panel -> Administrative Tools -> Data Sources (ODBC)
- From the ODBC administrator window, Click on System DSN. Click on ADD
Highlight the Microsoft Excel Driver(*.xls) and click FINISH.
- Enter the DATA SOURCE NAME. It is recommended to have only one word.
- Click on Select WORKBOOK to enter the location of the spreadsheet.
3. Now use the web console to create the synonym
- Choose the ODBC data source, and select the Excel datasource as the sub
source.
- Create Synonym displays the cell range names you assigned as data table
available for creation.
Obviously you need to define it as an ODBC datasource to the Reporting Server you intend to use.
Good luck
T
December 06, 2006, 08:13 AM
RichHTony has preety much broken it down for you, but here are the links to the ibi tech docs on how to do this. Just remember to highlight the cells you want.
How to create ODBC data source for reading Excel files?
http://techsupport.informationbuilders.com/sps/20802073.htmlHow to Access Data from an Excel Spreadsheet via WebFOCUS
http://techsupport.informationbuilders.com/tech/wbf/wbf_tcn_068.htmlDecember 07, 2006, 12:17 AM
OPALTOSHThanks Tony and Rich,
That should have read 'Reading Excel'.
Thanks for the detail. It is the information I was looking for.
Greg