Accessing an Excel spreadsheet
As much as we might want all data to reside in databases, a large amount of data still resides in other formats. In this recipe, we will look into how to read data from an Excel spreadsheet, as this can present some unique challenges.
Getting ready…
To connect to an Excel spreadsheet, ODI requires two components: the ODBC driver provided by Microsoft and the ODI ODBC/JDBC bridge that ships with ODI.
Note
If you are using Excel on a Windows 64-bit platform, you will have to make sure that you have a 64-bit version of the ODBC driver. You can download the proper drivers from the Microsoft support website. The 64 bit ODBC driver for Office 2010 is available at the Microsoft download center at this URL: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255
Before getting started, we will need an Excel spreadsheet. To make things simple, you can create a spreadsheet with the following data:
|
EMPNO |
ENAME |
JOB |
HIREDATE |
DEPTNO |
|---|---|---|---|---|
|
10 |
J Tanake... |
