WebI: Working with Excel-Files as datasource

[Former Member]

Sometimes the users have the problem, that they want to create a report with data, which are not in a database. Often this data are individual data, that are stored in excel.

With BO 4.1 you can use an excel file as datasource in the webi report. For this, the following steps are necessary:

• upload the excel file
  • go to the BI Lauchpad - register Documents and go to the folder, where you want to store the file
  • then New and  "local Document"
  • now search the file on your local client an upload the file
• create a new data provider
  • Data Access - "new data provider"
  • From Excel and select the excel file from the BI-Portal
  • Configure the data provider
  • Run Query

now you can work with the data in the report.

After the report is created, often the problem is, that the excel file is not up to date and the content must be refreshed. If the user tries to upload the new excel file the same way as described, than the old file can not be overwritten. If you make an upload under a different name, you must create a new data provider and all variables in the report has errors.

The question is, how to update the uploaded excel file. You can do this the following way:

• go to the file in the BI-Portal
• right click on the file and click on "organize"
• than you have the option "Replace File"

Now the file is refreshed.

Note: Yoiu can move the report and excel file, but if you copy both files, the excel data provider doesn't work because the CUID changes and the copied report has the connection to the old excel file

Hope this helps

Andreas

[Former Member]

Hi Andreas,

Thank you so posting.This is very helpful. I uploaded the excel to public folder and created a webi against it. Then I followed to Replace File with an updated measure. But data doesn't change on my WebI....Do you have any idea why it doesn't get refreshed? Do we need to go to the Input Files Store to update it again? If that is the case, the Replace File feature really doesn't make a lot of difference.

Thanks,

Kan

[Former Member]

If you refresh the datasource in WebI, only the data will be refreshed. If you have changes in the structure (additional column) you must go to the Query Panel and go to "edit settings" under Query definition.  Also the Query Properties must set to refreshable.

Hope, that helps

Andreas

[Former Member]

Hi Andreas hope you can clarify below

1. how to combine an existing webi report and excel (both have same no.of columns and same column names)

2. i have a macro generating the above excel for me, what do i need to do if the macro doesnt result in correct excel

3. how to refresh the incremental data in the webi report as soon as excel file changes with new data. (in my case a macro runs on excel we get everyday)

Your help would be greatly appreciated

Thanks in advace, Kris

[gbe]

I know this blog is quite old.

We have upgraded to BO 4.2 which bundles with the new Fiori Launchpad /BOE/BILaunchpad

I don't see anymore the 'Replace file' option in the organize menu. Is there a specific permission for that?

[gbe]

Answering to myself, will be included in BO 4.3 😞

[former_member618074]

Hi Gilles,

You could check for "Add Objects to the Folder" security rights.

[former_member749011]

Hello,

I am using Excel as one of the datasource in my webi report and use it to combine data from another webi report. We are on BO 4.3 When I do data preview with Excel as datasource in BOBJ, I get datasource is not valid. Click help for more information". error message. What is the reason for this error? Are there any special characters in excel that needs clean-up before this can be used in webi?

Thanks.

[batwoman]

Hello, I know this is quite an old post, however I am suddenly having trouble with a report that has 2 data providers from SAP and an Excel spreadsheet.  It has worked for years, but now I am getting this error.  Its a pretty generic one. It happens when I choose run and it says refreshing the document. Instead of bringing up the filters, I get the error below. I have changed spreadsheets etc and nothing seems to work.

We are using SAP BusinessObjects BI Platform 4.3 Support Pack 3 Patch 7 Version : 14.3.3.4727.7

Error

An internal error occurred while calling 'processDPCommandsEx' API. (Error: ERR_WIS_30270)