This article is a guide on how to Unload data from EXCEL file system to target relational database using Informatica.

Excel as a Source

Follow the instructions below on how to extract data from excel file

  • Define Range in Excel:

    File Name: EXCEL_FILE

    Path: PowerCenter8.6.1\server\infa_shared\SrcFiles [File is on ETL Server]

    Define Excel Range

    A range should be defined containing the data in the excel workbook like below and then save the workbook.

    Define Excel Range Name

    This defined name “DEPT” will be treated as relational table by informatica.

  • System DSN creation:

    DSN Name: DSN_EXEL_FILE

    Define System DSN

    Workbook selection in DSN

    Select the Workbook

    Excel File

  • Importing data definition of Excel in Source Analyzer:

    Since Informatica treats MS Excel file as a database, Import from Database needs to be selected.

    Informatica Source Analyzer

    System DSN created above should be selected. No username/password is required here.

    Connect DSN

    Import Table

    DEPT needs to be selected and OK should be clicked to import the source definition. After import Data types and Precision can be changed accordingly.

    Table Definition

    [N.B.: range name that is defined in excel is the table name]

    Mapping needs to be created with the imported source.

    Mapping Designer

  • Creation of Connection in Workflow Manager:

    A relational ODBC type connection should be created.

    Relational Connection

    Session level Connection should be created with below details:

    Username: PmNullUser

    Pwd: PmNullPasswd

    Connection String:

    Connection Object Definition

    Session Properties

    And create a workflow to run the mapping.

    Workflow

Limitations:

  1. Manual intervention is required to select the range in Excel.
  2. Different DSN and Connection should be made for different excel workbook.
  3. Need to have Microsoft Excel Driver under PowerCenter8.6.1\ODBC5.2

NOTE: Excel 2007 Table as Source

Create Table/ Define Name in MS Excel 2007:-

  • Create the data set in MS Excel sheet 2007

    Excel 2007

  • Select the data set

    Excel 2007 data set

  • Go to Insert and then Table tab.

    Excel 2007 Table

  • Create the table and then Name the table

    Excel 2007 Table

  • Finally Save the sheet in 2007 format.

    Excel 2007 Table as Source


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • PowerCenter SOA Components

    PowerCenter has a Service-Oriented Architecture that provides the ability to scale services and share resources across multiple machines. Let us know more about the components and services associated with Powercenter.

  • Informatica Dynamic Lookup Cache

    A LookUp cache does not change its data once built. But what if the underlying table upon which lookup was done changes the data after the lookup cache is created? Is there a way so that the cache always remain up-to-date even if the underlying...

  • CDC Implementation using Flatfile

    This article shows how to use a flatfile to implement Change data Capture. Suppose we want to maintain the last extraction date in a flatfile, based on that value we want to capture the changed data of our business table.

  • Calling C executable from Java Transform

    We are going to do is, to call C++ Executable from Informatica, using Passive Java Transform and capture the output of the C++ using Java and write the result to corresponding target column.

  • How to get Informatica Repository Information from Metadata

    Some metadata views can be very handy to get Informatica Repository Information. Know How.

  • Aggregation with out Informatica Aggregator

    Since Informatica process data on row by row basis, it is generally possible to handle data aggregation operation even without an Aggregator Transformation. On certain cases, you may get huge performance gain using this technique!

  • Informatica Tuning - Step by Step Approach

    This is the first of the number of articles on the series of Data Warehouse Application performance tuning scheduled to come every week. This one is on Informatica performance tuning.

  • When to use Informatica Stored Procedure Transformation

    There are loads of mis-information spreaded across Internet on good use-cases of Informatica Stored Procedure transformation. Exactly where do you use this transformation? This article finds out.

  • How to Tune Performance of Informatica Joiner Transformation

    Joiner transformation allows you to join two heterogeneous sources in the Informatica mapping. You can use this transformation to perform INNER and OUTER joins between two input streams. For performance reasons, I recommend you ONLY use JOINER...

  • Informatica Performance Tuning - Complete Guide

    This article is a comprehensive guide to the techniques and methodologies available for tuning the performance of Informatica PowerCentre ETL tool. It's a one stop performance tuning manual for Informatica.