How to create datasource for excel in cognos

[Gnani]

Hi,
I have one burning issue which is as follows..

I need an excel which has 10 columns along with data. my client needs this data in cognos reports.
Have an idea how to do it. I created Datasource in SysDSN in ODBC for Excel Driver and path has been selected. then created new datasource in cognos. here ODBC is selected as DB type. tested the connection successfully.
But when I import this Datasource in FM, no columns got imported.
how can i get the columns and data from excel sheets.
Pls suggest what should i do here....

Regards,
...Gnani...

[Gnani]

Sorry...
second line--->"I have an excel which has 10 columns along with data."
instead of "I need an excel which has 10 columns along with data."


Regards,
Gnani..

[MFGF]

Hi,

If you want to be able to read data from your Excel spreadsheet via ODBC, you will need to select the area you want to be able to access, and define a name for it (Insert -> Name -> Define)

This will then appear as a "table" when you access your Excel ODBC connection from within Cognos 8.

MF.

[Gnani]

MF,

""you will need to select the area you want to be able to access, and define a name for it (Insert -> Name -> Define)""

where can i define it..? Can u tell me in detail...?


...Gnani...

[MFGF]

MF,

""you will need to select the area you want to be able to access, and define a name for it (Insert -> Name -> Define)""

where can i define it..? Can u tell me in detail...?


...Gnani...

Within your excel workbook, use your mouse to lassoo the area you want to be able to access, then on the menubar click on Insert -> Name -> Define, type in the desired name, press Add then OK.  Save your excel workbook.

[Gnani]

MF,
Thanks a lot........
I am very happy with your solution, it works.

...Gnani...

[Gnani]

Hi,
There is another problem while testing data in FM from excel via ODBC,

eg:
I am importing prodid, prodname, description.
Values for prodid column are 101,102,103,1FH,GHD
1FH, GHD values are not getting imported in FM.

Values for prodname are prod1,prod2,prod3,7004,7005.
7004,7005 values are not getting imported in FM.

Cognos takes data type of the columns dynamically based on its data in excel. It took "Float 64" for prodid, "Character Length 16" for prodname and description columns.
Can any one help me to overcome this problem.

...Gnani...

[tupac_rd]

http://www-01.ibm.com/support/docview.wss?rs=3442&context=SS9RTN&q1=%2bexcel+%2bdatasource&uid=swg21352663&loc=en_US&cs=utf-8&cc=us&lang=all

HTH
2pac

[Gnani]

Tupac..!
Ur links explains cause for this problem. but doesn't give any solution.

In my excel sheet, values are alphanumerics, alpha, numerics.
For 1st column, first 20 rows are alphanumerics , next 20 rows are alpha and last 20 rows are numerics (numbers only)..
last 20 rows are not displaying in the report, blanks are being taken for this rows.

Any help would be appreciated.


Regards,
...Gnani...

[tupac_rd]

Can you change the order of the data, i.e instead of all 1st 20 rows being alphanumeric, can you make the 1st 8 rows to include both alphanumeric data and numeric data.....

[Gnani]

I am not sure....
alphanumeric includes characters and numbers.

Although first 8 row values contains characters and numbers, last 20 numeric rows are not getting retrieved.


...Gnani...

[tupac_rd]

So does your 1s 8 rows of data have alphanumeric data as well as only numeric data

For eg.

row1     ABC1234    -->  alphanumeric
row2     1234567     -->  numeric data
and so on..

[Gnani]

yeah......tried with the same but no result.  :(


...Gnani...