1. Introduction to Microsoft Excel Connector
Microsoft Excel Connector is an Add-On Connector in Informatica Cloud that let you read data from a Microsoft Excel source and integrate data with other applications, databases, and flat files.
Microsoft Excel Connector supports files created in Excel 2007 version onwards.
The following operations can be performed using Excel Connector
- Read Excel file as a Source Object.
- Read data from individual worksheets present inside an Excel file.
The Microsoft Excel connector currently supports only read operations.
2. Microsoft Excel Connector Properties
Create a Microsoft Excel connection to connect to a Microsoft Excel source and read data from it. The Microsoft Excel source can be used in Mappings, Mapping tasks and Synchronization tasks.
As Microsoft Excel connector is an Add-On Connector, it needs to be installed by the administrator. Then create a Microsoft Excel Connection on the Connections page.
The following properties needs to be configured in a Microsoft Excel Connection
- Runtime Environment: The name of the runtime environment where you want to run the tasks.
- Folder URI: The directory that contains the Microsoft Excel file. The Microsoft Excel file must be located on the same machine on which the Secure Agent runs.
- TreatFirstRowAsHeader: Indicates whether the first row in the file is a header row.
- Filename: The name of the Microsoft Excel file. The file name must have the .xlsx extension.
3. Microsoft Excel Connector Mapping Demo
Consider a scenario where you have to read an Excel file Employees.xlsx as source. The excel file has two sheets named “Employees” and “Departments” as shown below.
Follow below steps to read data from the Excel file in Informatica Cloud.
1. Create a Microsoft Excel Connection.
Specify the location of file and filename in the connector. Select TreatFirstRowAsHeader value as true as shown below.
2. Create a new mapping in Data Integration. Select the Microsoft Excel connection as source connection.
Under the source object, all the worksheets present in the excel file configured in the connection are listed. Select any one of the required sheet as source.
The below image shows data integration listing the worksheets Employees and Departments present in the Employees.xlsx file under the source object.
If you wanted to read data from multiple worksheets of the excel file, create a separate source transformation for each.
3. In the target transformation, configure a csv file as target object.
4. Validate, Save and Run the mapping.
When you run the mapping, the Secure Agent writes the data to the flat file based on the fields in the source object.
4. Microsoft Excel Connector Limitations
Below are the limitations of the Microsoft Excel Connector
- Since the connector requires the filename to be configured within it, a new connection needs to be created for every file you wanted to read.
- It is not possible to read files with dynamic file names. Ex: The files appended with date timestamp.
- The Excel connector is not supported in target transformation. Hence it is not possible to create an excel file as a target in IICS using the excel connector.
5. Conclusion
The Microsoft Excel connector provides the ability to read data from multiple worksheets from an excel file. If you requirement is also the same and looking for a no-code solution, the excel connector would be a great choice.
Unfortunately the Microsoft Excel connector do not support write operations currently. But as per Informatica Network, the feature request is in the pipeline (No ETA mentioned).
Subscribe to our Newsletter !!
Related Articles:
I need to read files with dynamic file names. Ex: The files appended with date timestamp but all the files have same structure and load them to tables using IICS job. Can you please help me on this?
You can use Indirect Loading Method !!