Excel as a data source to create universe

[Former Member]

Hi

I am trying to create a universe from Excel.Steps i followed are

1) Saved the Excel file in my desktop

2) Created ODBC connection by selecting Microsoft Excel Driver(*.xls) from System DSN, Gave DSN Name by selecting the Excel file from Work book.

3)Opened the Designer-> Parameters->new connection->Generic ODBC Data base Source->ODBC Drivers->Selected the DSN from drop down list and gave Connection name-> Tested the connection and it is responding.

4) Copied the Excel Table from the table browser and created Class and Objects.

When i try to see the data from the dimension object properties i am getting the following error.

Exception: DBD, [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression ''ALL Duplicates$'.Supplier Partner Master Name'.State: 37000

Does anyone have idea where i am doing wrong?

Thanks in Advance

[Former Member]

Hi Ravi,

Have you tried using it WebI or DeskI, I have had problems looking at table data values in the universe, but the WebI report still works,

Regards

Alan

[Former Member]

Hi Alan,

Thanks for the reply, I tried both in deski and Webi but still i am getting the below error.

Database error:[Microsoft][ODBC Drive Management] Data source name not found and no default driver specified.

I tested with other Excel sheets with other Universes but still the same problem.

My database is SQL Server 2005 and BOXir2(SP2), Any thing to do with my database?

Thanks

Ravi

[Former Member]

Hi Ravi,

Is your designer on your BO server? If not then you will need to set up the odbc on the BO server as well.

Also, the location of your excel file is important. If it is on a network drive, then you have to be sure that the service has the appropriate access.

Regards

Alan

[Former Member]

Alen,

My designer is not on the server, when i tried to save excel file on the Server it is saving as Unknown file because MSOffice is not installed on the Server.So i am trying on the Client machine.

Location of my excel file is on my destop.It is on Network drive.Can you please guide me how to check the access?

Thanks

Ravi

[former_member212749]

Hi Ravi,

I guess you need to have an power user rights in the system that you have the excel and could you please let us know if you had created a System DSN or File DSN or a User DSN for ODBC connection.You should create a System DSN.

Try to connect from the server machine create the universe there with the excel and DSN.

Regards

Prashant

[Former Member]

Hi Prashant,

I am logging as an Administrator, so i think i have enough rights.I created System DSN in the ODBC connection.

My problem to create Excel file on the Server is MS Office is not installed on the server so when i save Excel file on the server it is showing as an Unknown file.

Thanks

Ravi

[Former Member]

Hi Ravi,

Even though you do not have Excel on the server you will be okay as long as you have a ODBC drivers for Excel on the server. If you do create the ODBC connection, as you did on your local machine, pointing to the server location of the file. The BO server should then be able to see the file.

Regards

Alan

[Former Member]

Thanks Guys for all your support.

The issue was resolved now, The problem is Column names have spaces and hypen "-" in between them which is not supported at Universe Level.

If i make column names as one word then i am able to get the data at the universe.

Thanks

Ravi