Import Excel Source

shankar.singu

DW Fresher

Reputation: +6/-0
Offline

Offline

Posts: 16

Import Excel Source

« on: May 19, 2010, 10:35:53 am »

Hi,

I have to use MS excel as source. I have successfully imported in source(Designer) and have done mapping. But not able to understand how to configure in Session like userid, pwd and connection string etc for SOURCE EXCEL FILE.(workflow)

Can any one pls give me the steps with an example?

Regards,

Sankar. S


	Logged

shanilr

DW Fresher

Reputation: +3/-0
Offline

Offline

Posts: 6

Re: Import Excel Source

« Reply #1 on: May 19, 2010, 11:02:02 am »

Hi,

I believe since you are able to successfully import the excel file as a source, you have created a ODBC connection for the excel file. Since Informatica treats the excel file as a relational Database you need to provide the same ODBC name and the parameters in Workflow as well.

Thanks
Shanil.


	Logged

Whoa

DW Fresher

Reputation: +0/-0
Offline

Offline

Posts: 1

Re: Import Excel Source

« Reply #2 on: May 19, 2010, 11:02:32 am »

I had these steps lying around in my notes, although I am not sure where I got them -

Steps for creating an ODBC connection to Excel -

Creating a worksheet.
- Select the required rows to be read into PowerCenter.
- Choose Insert -> Name -> Define and give the range a name then click OK.
- Save the worksheet.
Creating the ODBC connection.
- System DSN -> Microsoft Excel Driver (*.xls).
- Configure and select workbook.
Importing into Designer.
- Sources -> Import From Database.
- ODBC data source must match (2.) above.
- Leave username, password and ownername blank.
- Click Connect
- Expand the worksheet name and select the range created in (1.) above.
Creating ODBC connection in Workflow Manager
- In Workflow Manager go to Connections -> Relational -> New... -> ODBC
- Enter a name for the connection.
- Username=pmnulluser
- Password=pmnullpasswd
- Connect string=
- Use this connection in session mapping for source.

p.s. - Please note that sheet names with spaces can be problematic.


	Logged

shankar.singu

DW Fresher

Reputation: +6/-0
Offline

Offline

Posts: 16

Re: Import Excel Source

« Reply #3 on: May 19, 2010, 05:45:15 pm »

Hi,
Thanks for your reply. But could you pls clarify the following doubt

4.   Creating ODBC connection in Workflow Manager
o   In Workflow Manager go to Connections -> Relational -> New... -> ODBC
o   Enter a name for the connection.
o   Username=pmnulluser
o   Password=pmnullpasswd
o   Connect string=
o   Use this connection in session mapping for source.
---------------------------------------------------------------
Pls tell me what is the connect String value, for example if I give connect string value “ExcelConnectString”.

Doubt:

1.   How Workflow manager will pick up my excel sheet since I have not given my excel file name.
a.   I have given only User Name, PWD and Connect String.
b.   In DESIGNER, we are using DSN where we will tell which excel file but in workflow we cannot say like that. Am I right?

2.   In case the connect String (ExcelConnectString) should be created in ODBC? because while creating ODBC DSN (in control panel) we will select which excel file.

Don’t hesitate to correct/scold  me if my doubt is silly. Pls help me on this?

Regards,

Sankar. S


	Logged

Whoever

Administrator

Reputation: +34/-0
Offline

Offline

Posts: 124

Re: Import Excel Source

« Reply #4 on: May 20, 2010, 09:59:06 am »

Quote from: shankar.singu

Pls tell me what is the connect String value, for example if I give connect string value “ExcelConnectString”.

Connect String should be the name of the ODBC DSN you created.

Quote from: shankar.singu

1. How Workflow manager will pick up my excel sheet since I have not given my excel file name.
a.   I have given only User Name, PWD and Connect String.
b.   In DESIGNER, we are using DSN where we will tell which excel file but in workflow we cannot say like that. Am I right?

2.   In case the connect String (ExcelConnectString) should be created in ODBC? because while creating ODBC DSN (in control panel) we will select which excel file.

Correct, You cannot specify the file name/path in designer, you do it when you create the ODBC DSN, like you said.

It's the DSN that contains information about the file path/name.
And when you provide connect string as the ODBC DSN, it gets pointed towards the file you intended.


	Logged

If most people said what’s on their minds, they’d be speechless.

shankar.singu

DW Fresher

Reputation: +6/-0
Offline

Offline

Posts: 16

Re: Import Excel Source

« Reply #5 on: May 20, 2010, 11:48:13 am »

Dear All,

Still I didn’t get the answer for my question.

Its very simple that there are two DSN.

1.   In designer, we will create DSN while import excel source. (lets say control panel->ODBC->pick excel driver and create it. Here we will define which excel file is the source. Assume that I have created DSN here “DESIGNER_ODBC_DSN”

Designer part completed. Now I am moving to Workflow manager.

2.   In workflow manager, we have to create DSN,
Connections -> Relational -> New... -> ODBC

Pls tell me now that this new workflow ODBC name should be “DESIGNER_ODBC_DSN” which we created in step 1.(above). Or it can be anything?

Could you help me on this still I am getting error that unable to connect source
3.   Incase any one is having step by step (or some related) document , could you pls send to me mail id : [email protected]
Undecided