excel and impromptu

[slaforce]

Can an excel spreadsheet be a data source for impromptu?

 

[griffindm]

Yep. Just use the MS ODBC driver for Excel. It's a little tricky to setup, has lousy documentation, and has some issues, but other than that it works fine.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[slaforce]

I was able to connect using the ODBC driver - but when I try to create a new catalog - it will not retrieve any tables. How does impromptu tried excel worksheets?

 

[griffindm]

1) Make sure the driver specifies the correct version of Excel and the correct path and filename for the Excel spreadsheet.
2) The spreadsheet should have a named range, usually DATABASE, that contains short headers. The first several rows should contain representative data for the contents of the column, not nulls.
2) Create a Logical Database in Impromptu under the ODBC area.

With these three items taken of, you should be able to create a catalog that points to that logical database in Impromptu. You should see a table called DATABASE that contains columns of the type determined by the first rows found in the spreadsheet.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[slaforce]

Thanks Dave -

I'm getting closer. I see the table (spreadsheet) now, but still no columns. I made sure I have no nulls in the columns. What do you mean by a named range that contains short headers?

Thanks so much for the help

 

[griffindm]

I'm not sure if you can break the driver by having unreasonably long column names or not, so I suggested having column names that you would consider 'typical' for database column schemas, i.e. no spaces in names, length of 15 char or less, just to be on the safe side. The named range become the 'table' as seen by the ODBC driver.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com