How to connect to MSAcess / MS Excel Databases from informatica.

• In the beginning of my Admin Tenure I always used to get terrible whenever such issues comes up with the requests like want to read the data from MS Excel or MS Access. Because there are no native drivers available to connect to Windows based Applications and Databases from Informatica when the PowerCenter is built on Unix and Linux Operating Systems.

• Data which exist in the windows apps and database will be in the 32 bit format in order to protect the intellectual property rights. There are compatibility issues when we have PowerCenter Server built on 64 Bit machine. Data has to be converted from 32 bit to 64 bit or Visa Versa.

• Following are the recommended method in case you want to read/write data from/to Windows based apps and data bases.You can use either one of them based on your convenience.

1. Convert the MS Excel to XML and use the XML source to read the data.
2. Remove the header and footer convert it to the .csv file and read the .csv file as Flat File.
3. Use Data Direct Sequel Link ODBC drivers and read the MS Excel data directly Use Microsoft Excel Driver (*.xlsx) at PowerCenter.
4. Another preferred method which recommended by vendor would to use the Power Channel server installed on windows side.
5. Install the 32 bit Power Center server on 32 Bit Operating System so that you can read 32 bit data kept in windows apps and data bases.