– You need to have Excel installed on Informatica Server. – The ranges you defined in the Excel file (step 1.a) appear as separate table names when you import them in Informatica PowerCenter. If you require a specific datatype (numeric, date…), you need to assign the datatype in Excel before importing the spreadsheet (you can also modify it in Informatica source definition). – If you don’t assign datatypes to columns of data in Excel, the Designer imports each column as VARCHAR. It could cause problTo import a source definition, a read permission on the database object is required in the client machine. As you probably know, in order to indicate some MS Access file or Excel file as an ODBC source, you need to follow: Administrative Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose either 'Excel Files' or 'MS Access Database' from the list -> Press 'Configure' -> finally choose the file (MS Access or Excel) as ODBC source. Please bear in mind the following considerations: – Do not write sheet names or column names with spaces. In Source connection define the new relational connection created. Select the source file and click on OK.Į) Create workflow. Details: Write SQL query in Command Text and Click OK. Go to Source Analyzer and select ‘Import from Database.,’.Ĭ) Select the ODBC data source name created in step 3.c and click on Re-connect (don’t write any Username and Password). This article contains a complete example of Microsoft Excel and CSV data import to. Go to Repository and select ‘Import Objects…’.ī) Open Informatica Power Center Designer. Include relational source in the Source Qualifier transformation of the mapping:Ī) Open Informatica PowerCenter Designer. Connect string = Excel Name defined in step 3.aĤ. Go to Connection and select ‘Relational…’ and click on ‘New’. Create Relational connection in Informatica Workflow Manager: To import the excel file you need to configure connectivity with the PowerCenter Client:Ī) Open Informatica Power Center Workflow Manager.
#Odbc excel column type driver
Create ODBC DSN: it has to be created in the location where PowerCenter client and server are installedĪ) Go to Start > Settings > Administrative Tools > Datasources (ODBC) (in Windows 64 bits you must use ODBC Data Source Administration tool located in \Windows\SysWOW64\odbcad32).Ĭ) Click on Add and choose Microsoft Excel Driver (*.xls) and click on Finish button.ĭ) Write a Data Source Name, select the Excel or Access version and define the localization of the file through ‘Select Workbook…’ button and click OK.ģ. Which steps do we have to follow to load this data from these files to our Data Warehouse? How are the connections created? Where do these files have to be located? How should we configure Informatica? In this article I will explain the way to load data from Excel files using Informatica Power Center running on Windows.Ī) Select the required rows to be read into PowerCenter.Ĭ) Type the name of the range and select the range on “Refers to:” space.ĭ) Save the spreadsheet, Normally this kind of sources are saved in a specific folder (\Informatica\PowerCenter8.1.1\server\infa_shared\SrcFiles).Ģ. Informatica treat Excel and Access file sources as a relational database, not as a flat file. The most common sources are tables, views or synonyms, but for storing small sets of data, companies usually use flat files, MS Excel files or MS Access. Informatica Power Center can use different sources of data.