Using Excel as ODBC datasource

[fml55555]

Hi Gurus,

I am trying to connect Excel as a datasource to framework manager. Till now...

An ODBC connection has been made on the server machine (Lets say the name of the document is abc.xls and the table is called aaa)

An ODBC connection on the local machine has been made (Say the name of the document is xyz.xls and the table is called aaa)

I can see the abc.xls as a datasource when I launch the metadata wizard in FM.

Questions:

Is it the right method use the abc.xls Datasource to import the metadata to FM (I have already tried it and got the following error)

"QE-DEF-0285 The logon failed.

QE-DEF-0323 The DSN(ODBC)/ServiceName is invalid. Either the DSN is missing or the host is inaccessible.

RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
RI_Premium_Plan

UDA-SQL-0031 Unable to access the "RI_Premium_Plan" database. Check that the connection parameters to the database are configured correctly. For example, ensure that the data source connection contains the signon information, such as a password, to connect to the database.
[Microsoft][ODBC Driver Manager] Cannot update, database or object is read only

UDA-SQL-043 Underflying database detected an error during processing an sql request
UDA-SQL-0532 Data Source is not accessible: "RI_Premium_Plan".
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

What am I doing wrong?

[MFGF]

It looks to me like you don't have the ODBC connection set up correctly on your local machine? I would expect the ODBC names on both machines to be the same and to point to the same physical Excel file?

MF.

[navissar]

What am I doing wrong?

Uhm. I'd say the first thing is the choice of board, wouldn't you, Sir Muppet?
Not to be nitpicky, but people might roam the administration/framework board who may have seen this issue before...

[fml55555]

Hi MFGF,

Thank you so much for the response and I apologize for posting this in the incorrect forum. Just to make things clear,

I do not understand what you mean by pointing to the same excel file. I though there were two copies of the same excel files (one on server and one on local machine) with the same name.

should I name the name the document as xyz.xls as well (on my machine). What about the named areas in the excels do they have to be the same as well.

Which is the excel sheet I should use to create the datasource in the FM?

[MFGF]

Hi MFGF,

Thank you so much for the response and I apologize for posting this in the incorrect forum. Just to make things clear,

I do not understand what you mean by pointing to the same excel file. I though there were two copies of the same excel files (one on server and one on local machine) with the same name.

should I name the name the document as xyz.xls as well (on my machine). What about the named areas in the excels do they have to be the same as well.

Which is the excel sheet I should use to create the datasource in the FM?

No worries about getting the wrong forum - it was easy for me to move the thread to a more suitable place :)

Normally I would expect the text file to be on a mapped network drive visible to both the server and the FM client machine. You would need the exact same ODBC data source (System DSN) defining on both machines. It will probably work using separate copies of the file if both ODBC names are the same, I guess.

Make sure you have created the ODBC using the 32-bit ODBC Administration tool on each machine. If you're unsure, go to C:\Windows|Syswow64 and run odbcad32.exe from there. Make sure the definition is on the System DSN tab in each, too.

Also make sure that both copies of the Excel file have the same named range defined.

Cheers!

MF.

[fml55555]

Hi MFGF,

Thank you so much for all the help. It has worked and I am able to get to run the metadata wizard and the columns showup in the FM. However, when I test any of them, this error message shows up

This query contains an error and can not be executed.

It is recommended that you view the query feedback on the "Query Information" tab.

RQP-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-28'.
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.
UDA-SQL-0043 The underlying database detected an error during processing the SQL request.
UDA-SQL-0043 The underlying database detected an error during processing the SQL request.
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'RI_Premium_Plan'. Make sure the object exists and that you spell its name and the path name correctly.

[MFGF]

Hi MFGF,

Thank you so much for all the help. It has worked and I am able to get to run the metadata wizard and the columns showup in the FM. However, when I test any of them, this error message shows up

This query contains an error and can not be executed.

It is recommended that you view the query feedback on the "Query Information" tab.

RQP-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-28'.
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.
UDA-SQL-0043 The underlying database detected an error during processing the SQL request.
UDA-SQL-0043 The underlying database detected an error during processing the SQL request.
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'RI_Premium_Plan'. Make sure the object exists and that you spell its name and the path name correctly.

So the connection can be made but testing produces errors that the columns can't be found?

Try with a single file on a shared network drive. Does this fix the issue?

MF.

[fml55555]

I will have to try it tomorrow as the shared drive (server) exists offshore and I would have to take ask someone to do it for me as I do not have the permission  :-\. I feel it would take some time to get all the relevant accesses. In the meantime are there any other ways this can be achieved?

[CognosPaul]

Try using the external data functionality in Report Studio. This will essentially go through the process of uploading your excel sheet into a temporary table, create the join to your existing table, and republish as a new package.

[fml55555]

Thanks for the suggestion paul. But here at the shop they have already disabled that function "for security reasons" ???