WebI: Working with Excel-Files as datasource
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
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
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
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
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?
Answering to myself, will be included in BO 4.3 🙁
Hi Gilles,
You could check for "Add Objects to the Folder" security rights.
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.