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.

  • 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.

  • Stop Hardcoding- Follow Parameterization Technique

    This article tries to minimize hard-coding in ETL, thereby increasing flexibility, reusability, readabilty and avoides rework through the judicious use of Informatica Parameters and Variables.

  • Working with Informatica Flatfiles

    In this article series we will try to cover all the possible scenarios related to flatfiles in Informatica.

  • 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.

  • APEAR - A tool for automating Informatica Performance Tuning

    DWBIConcepts is launching APEAR – an Automated Performance Evaluation and Reporting tool for Informatica. As the name suggests, this tool will help you tune the performance of Informatica sessions fully automatically. Now don't waste your precious...

  • CDC Implementation using Informatica Variable

    This article explains the Change Data Capture mechanism using Informatica Mapping Variable. We can use the Informatica Mapping Variable to extract the CDC data without using any other custom table. Here it goes.

  • Informatica Excel Source

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

  • Informatica Join Vs Database Join

    In this yet another "DWBI Concepts' Original article", we test the performance of Informatica PowerCentre 8.5 Joiner transformation versus Oracle 10g database join. This article gives a crucial insight to application developer in order to take...

  • Informatica Reject File - How to Identify rejection reason

    When we run a session, the integration service may create a reject file for each target instance in the mapping to store the target reject record. With the help of the Session Log and Reject File we can identify the cause of data rejection in the...

  • Comparing Performance of SORT operation (Order By) in Informatica and Oracle

    In this "DWBI Concepts' Original article", we put Oracle database and Informatica PowerCentre to lock horns to prove which one of them handles data SORTing operation faster. This article gives a crucial insight to application developer in order to...