Import Excel data

[Former Member]

Hi all.

We need to compare Business Objects data with data stored in a spreadsheet.

I've found that I can read in the spreadsheet data with the Rich Client. However, this is a spreadsheet on my local PC.

And of course if I upload the Report to Webi, it can't see the spreadsheet on my PC.

What I would like to do is to be able to open the spreadsheet vi a URL on our network drives.

And then for clients to be able to run and refresh the data via the standard WEBI interface.

Is there any way this can be done?

Thanks

Malcolm

[Former Member]

I am not sure if I understand your problem correctly. Please ignore if it didn't work out.

You can create the Universe on top of excel file and then you can create a web-I report. With the above process you can publish

the web-I report developed on top of Excel file on the BO server. But, the overhead is maintenance of the file. Here, you have to

save the file on the server and make the updations to it when needed so.

Regards,

Rohit

[Former Member]

Rohit,

Thanks for the suggestion - but we are trying to do this without the overhead of creating a universe specifically for the Excel file. In fact, this technique will probably need to be used on several different Excel files. As usual in any large organisation, various data items finish up being stored in Excel rather than a "proper" (database) solution, as it's quick and easy - but then I get the job of trying to report on a combination of the main data in the universe and these external data items. As I said, I've found that I can do it in the Rich Client on my PC, but so far I've only got it working against a locally stored Excel file, which is no use for publishing the report. What I hope to do is to make the report available through the normal Business Objects server, which means that the Excel file has to be made available to it - presumably through a URL link rather than a disk address.

Secondly - I hope that Webi will support the report with this query even though I can't create it through Webi? I'm quite happy to create the report in Rich Client and export it to the server. That is a maintainable routine, but me having to run the report(s) regularly from my desktop isn't!

Thanks,

Malcolm

[Former Member]

UPDATE!

=======

I've just discovered I can store a spreadsheet in the Business Objects repository.

I would have thought that there must be a way to reference this as the data source for an Excel query in my report,

but I don't know what the syntax would be.

Additionally, as I have to develop the report in the Rich Client on my desktop, how can this reference the spreadsheet in the repository?

I feel I'm getting so close - can anyone help please?

Thanks,

Malcolm

[Former Member]

Was this ever resolved?

Can the users create an ad hoc report that uses data from a universe as well as an Excel file?

this would be a one time run with an excel file supplied by the user at design and run time.

Thanks

C. Schmidt

[Former Member]

Charles,

After a lot of fiddling around, I can get the report in Rich Client to run with the spreadsheet data and Universe data in separate queries and merge the results. I had a lot of problems - the spreadsheet data defaults to dimension, and mostly needs to be measure or detail, and when the spreadsheet changed slightly, it totally broke the report - I think it references the imported data by column and not name, so when an extra column was added, all my variables based on it seemed to reference the wrong data item.

The problem I was posting about however was trying to find a common location for the spreadsheet so that Rich Client can access it (it only seems to like addresses like D:/Myfiles/Sheet1.xls) and also so that Webi can find it - and that's obviously not in that format. Webi would run with the query created, but obviously can't find the file to do a data refresh.

So at the moment, I'm stuck with a manual process on my PC and then email the spreadsheet results to our clients, which is not nice!