WebIntelligence Report’s Save As CSV Has a Problem?

Posted on by 55 Comments

Like a good obedient BO disciple I switched from using BO 6.5’s “full client” (now called Desktop Intelligence) to using BOXI’s Web Intelligence tool (also called WebI). To Business Objects’ credit, they have put much of Desktop Intelligence’s functionality in to WebI. But there is one disorienting difference.

BOXI Web Intelligence “Save to CSV” Is Different

My recollection tells me that if I exported a BO 6.5 Full client report to CSV that I received a CSV file with the same content that I would get through a Microsoft Excel export. Sure formatting, such as colors, column width, font, etc. were lost, but the order of the columns and even the report level variables in the table were exported to CSV from full client. Maybe, I a wrong about this (please point this out if I am), but that is the way I and some colleagues remember it.

BOXI Web Intelligence (and perhaps BO 6.x Web Intelligence) also has the ability to export to a CSV file, but this export behaves very differently. This export behaves much like the BO 6.x “View Data” or the “Data Cube” functionality. The BOXI WebI CSV export is a dump of the exact results of the first query of the report just as they were received from the database.

WebI CSV is a Query Result Export, NOTHING MORE

When you export a WebI report to CSV you may not get what you expect. The results should be identical to those you would get if you took the query and ran it directly against the database. This may be disappointing to some because it does not include any report-level aggregation, calculations, sorting, breaks, character formatting, or column ordering. HOWEVER, if a report developer is debugging a query or a universe object this output can be invaluable. For example, it can quickly uncover issues (such as Cartesian products) that dimensions can hide in a table. There are many uses for this output.

Why Does WebI’s CSV Have This Limitation?

I don’t claim to have inside information, but I can guess at two reasons for this limitation.

  1. Report Development Debugging: I know I already mentioned this, but I really want people to use the CSV export to understand more about their query and report. This previously perceived limit is actually advantageous functionality in the right hands.
  2. Discouraging ETL Use of Business Objects: If CSV were to output the aggregation and calculated columns of a report it would be readily used by end users as an ETL tool (Extract, Transform, Load). Some users still use it this way, but large scale use would explode if CSV exports were more than query dumps.

    3. What Can I Do If I Want a CSV Export of My WebI Report Table’s Data?

    There is almost always a workaround. If you must have your table’s data in CSV format then the solution is simple: export to Excel, open Excel output, save to CSV. You’re welcome!

Enjoyed this post? Share it!

 

55 thoughts on “WebIntelligence Report’s Save As CSV Has a Problem?”

  1. A couple of related notes:

    For some of our users, saving as Excel is not actually an option. As we are still on Excel 2003, they run smack up against the 65K row limit. Under 6.5, they were able to use the Full Client to function as a poor man’s ETL system to populate local databases. Trying to do the same in WebI has exposed a host of difficulties. While I know this is not how WebI is *intended* to be used, is it really that unusual a case that it is not accounted for?

    Why can we not schedule reports to a CSV or other text format?

  2. I feel your pain. It seems that BO could allow Administrators to enable text export for scheduled jobs. 🙁

    By the way, even if every one of your users had Excel 2007 you would still be limited to 65K rows on Excel output. Last I talked with BO Engineers on this, the Excel file generator is custom code developed by BO and it is not impacted by what version you may install on the server or on the client. Also, they indicated that leaving this limit at 65K might be an intentional anti-ETL choice that BO makes.

    We’ll see.

  3. hello all,
    some of our users are suffering with this problem.
    they are used to exporting to access and now they have a 2 stage process which they claim is time consuming.

    if anyone manages to find a quick solution then please let me know

    regards

    yuen

  4. Sorry Yuen,

    According to various Business Objects engineers this is by design and there is no way to do it.

    HOWEVER, if you can build your aggregation and formulas into your SQL (or the SQL of your report’s objects) then you may be able to make the actual query result useful in its native form and therefore the CSV export from WebI would be just right for their needs. BTW, building this into your SQL can often be a best practice that will improve accuracy and performance for your users.

    Please comment back with your thoughts.

    Good Luck!
    Julian

  5. Our report is actually not very big. Excel output is only about 50 kb, and also the number of rows is less then 200. But the users want the report in csv. But when we try to export, we get the error msg saying

    ‘Max character file size limit exceeded. The document is too large to be processed by the server. Contact your BusinessObjects administrator. (Error: WIS 30272) (Error: INF )” ‘

    Not sure what could we do? Any ray of light?

    Thanks

  6. I can think of one explanation, but it may not fit your case…

    Remember that Excel output is WYSIWYG. Therefore, it contains all of the aggregation of your report. Therefore, if your report is aggregating a lot of data then it is entirely reasonable that the CSV export could exceed your binary file size limit. For example, what if you pull back 500,000 rows of micro transactions for 5 different products, but in your report you aggregate all of those 500K rows to 5 lines of data. Then of course your Excel output file will be very small, but your CSV file would be HUGE. You need to take a look at how many records are being returned by your report’s query/queries.

    Please update let us know if this tip helps. Good luck.

  7. Also, I have downloaded the data of 160000 rows in the excel sheet, it had downloaded automatically in multiple sheets in excel. My Version is 2003 and Webi 3.1 version.

  8. Interesting observation Suresh. It would appear that maybe in XI 3.1 Business Objects has found a workaround to the limit and has started splitting the output across multiple sheets.

    Since the Excel file is created by the server I am curious to know if it is creating an Excel 2007 *.xlsx file and perhaps your Excel 2003 version is splitting those 160,000 rows across multiple sheets. Can you please comment on this? Thanks!

  9. Well I can confirm what Suresh said
    I am running Webi 3.1 and I set the “Binary Stream Maximum Size (MB):” parameter for the webi server to 1000 and then ran a report that gives over 500.000 records.

    An export to Excel gave me 8 sheets in 1 workbook (Eace sheet with 65k rows or export to csv and then into Excel2007 gave me one sheet with 500.000 lines.

    So until BO give’s us an updated Excel export module this is a workaround

    Regards Svavar

  10. Svavark thank you for confirming that. Very interesting. I had not noticed this. I like the workaround. I suppose it is implemented this way to be backward compatible with Excel 2003 as well. Not bad. Now if it just had two options, “Export to Excel 2003 or lower” and “Export to Excel 2007” then we would all be even happier. I will write a short article about this to give it more visibility. Thanks Suresh and Svavark!

  11. Pingback: Business Objects Creates Excel Readable Files - Note True Excel - CSV Export | Business Objects Tips

  12. Hi..
    I am new to Webi…
    how do i go about
    Java Code functionality:
    1) Look for a Excel file at a specified location on BO Server
    2) Now Read the Excel file and capture each (Section ) details and create a new Excel file with the data of this Section alone.
    3) Rename the New excel file names
    4) Now all the new excel files created are Zipped

    I have created the java code.. I need a way to carry out the same thing in bo sdk…
    Please help…

  13. Hi,

    Is there any way to convert a BO webi document to Excel report via .net application and prompt the excel to the user?

    Thanks

  14. Is there any way to convert a BO webi document to Excel report via .net application and prompt the excel to the user?

    Please provide more details. I found at least two very different meanings to this question. Do you want to prompt the user to save to Excel? Do you want the user to be able to open an Excel document and then refresh the data in the spreadsheet when prompted with something like “Would you like to refresh the data”? Both are possible. 1st one would involve SDK or at least a modification to InfoView. The 2nd one would use LiveOffice (easier) or just stick some VBA/SDK code in an Excel report (harder but cheaper).

  15. In saving report to .CSV format using BO 6.5, how is it actually done?
    Is it restricted with access rights? I am trying to save a BO 6.5 to .CSV, but I can’t seem to see an option for that.

    Thanks.

  16. I don’t want to make you wait until I can dust off my DeskI of BO 6.5 Full Client, so I am going to say this one from memory…

    Click on the data cube and there you will find an export option. This will export the data exactly as it came from the database without any report formatting or aggregation.

    Please confirm that this is valid memory. Thanks!

  17. Yes.

    Thanks Julian. 🙂

    I have another concern though.
    This time with BOXI.
    Because with BOXI, InfoView, when you save the reports in .CSV format, all the queries behind the report are retrieved.
    How would you limit the output to only a particular query that you would want to see the result?
    Like in BO 6.5, if you only need results from Query 1, you could export that query only to excel, how could we do that in BOXI InfoView?

    Thanks again!

  18. As far as I know this is not possible. If this were a very important requirement then you might build some kind of macro for processing the output as you want it to be, or you could even have prompts created that would be used to turn off the output of the other queries (making them return no rows) and turn on only the query you want to retrieve. Crazy ideas, but I have seen it all and the business requirements that drive ’em.

  19. Hi there,

    I’ve came across this page here when doing a search on how to export WebI 3.0 into csv or txt format.

    In fact, is there such a way to export to txt format directly from the WebI reports without having to go through Excel?

    Hope my question is clear enough.
    Thanks.

  20. Hi ccool,

    Yes, in XIR2 and BO XI 3.1 (I just confirmed both) just open the report in the regular HTML viewer (not the Java Report Panel for editing). With the report open go to Document > Save to my computer as > CSV. You may also select “CSV (with options)” in order to select the text qualifier, column delimiter, or character set.

    Best Regards, Julian

  21. Thanks a lot for the input, Julian.

    I’m very very new in BO and thus, I need a lot of guidance as I find that BO resources are quite limited. Thank goodness we have a blog like this which I think is quite resourceful.

    Now, if I were to schedule my WebI report to CSV format, are we able to do so? I only found WebI, Excel and PDF under the Output Format when I go to Schedule>Output Format and Destination.

    Thanks in advance.

  22. Hi ccool,

    Thanks for the feedback! Unfortunately, it is impossible to schedule to CSV from WebI. This is by design, according to my various conversations with BO engineers. This prevents people from using WebI reports for ETL (Extract, Transform, Load) purposes and encourages them to keep using it for business intelligence reporting only.

    Sorry, Julian

  25. I see that we can schedule a webi doc to csv format in XI3.1 SP2 via InfoView or the CMC. I would like to do the same using the SDK. But in the SDK code we dont see the option for csv. We are using boesdk_java_apiRef_12_en.zip. Is this the latest version of the Java SDK?

    -apa

  26. Mr. Abdalla,
    I think that you are correct, you have the latest Java SDK API Reference; however, the “12” stands for BO XI 3.X and therefore, it has most likely not been updated with the patch releases. Since the CMC uses the SDK I highly suspect that the API for scheduling with CSV delivery is possible. Good luck finding the documentation though.

    If you have SAP-BO Support maintenance, you could open a Customer Message (a.k.a. case) with them and demand that they provide this information. However, I tried to do this for Query Builder once and I was shot down, which led me to search out everything I could get my hands on and write and publish my own documentation.

  27. This string is very interesting…I am an IT neophyte, so take pity on my question…Can anyone recommend a text book or books that will cover this scenario?

    I would like to schedule an automatic refresh of 4 separate queries in Desktop Intelligence 11.5.0.0 every night where the query date ranges represent a 120 day rolling period and import the results into an Access database to replace 4 corresponding tables.

    Any suggestions?

  28. I can’t think of a single book that would tell you exactly how to do that. I think I know how. I have done plenty of rolling data range reports (using universe-level conditions based on the system date). As for loading to a DB, you would need to probably deliver Excel or CSV output (XI 3.1 SP2) to a file share and have some batch process pick it up and load it to your database.

  29. Julian,
    If I use excel 2007 and BOXIR2 still the 65K row limit is there is any work around for XIR2 please let me know

    Satya

  30. Sorry Satya, the component which produces the Excel output has nothing to do with anything installed on your computer, it is a DLL/class which BO has created. Until they alter it, you will not see this limit exceeded.

    I can tell you that in BO XI 3.1 they now force output of over 65K rows onto additional sheets in the Excel workbook, but in order to retain compatibility with all versions of Excel and OpenOffice Calc each sheet is limited to 65K rows. I don’t know if XI 3.0 includes this.

  31. When I export from BOXI in csv format, the resulting file has blank rows. I am selecting export options isolate report/page section & isolate report/page section + do not export group sections for same result. how can i eliminate the blank rows please

  32. Hi Maurie, are you using Desktop Intelligence or Web Intelligence? Either way, I don’t recall ever seeing blank rows in my CSV exports, but then again, I never really have used the CSV export functionality very much.

  33. Hi,
    I am using the BO XI R2, I have a doubt here can I open the reports developed in Deskii in my Webii? If yes please explain the process or is there any third party tool to do the same.

  34. Hi Sudhir, to be precise, WebI cannot open DeskI reports. However, InfoView has an HTML viewer for DeskI reports that displays them in a web browser. You cannot edit the report, but you can view it. This of course, will not run any macros. There is also a report conversion tool that will convert DeskI reports to WebI, it is pretty good, but not perfect.

  35. Thanks a lot. I have a another doubt I have to merger my webii report with an external data source(csv file) through webii. can i do the same? if not then through deskii can i do it?

  36. Hi Sudhir, I don’t think you can do that with WebI (unless you can get Designer attached to it). I have not gotten creative enough with DeskI to be able to answer for DeskI. Sorry.

  37. Hi,

    I am using BOXI R2 and am trying to export a report to EXCEL. I know that the component which produces the Excel output is a DLL/class which BO has created but is there any other way to export if it’s more than 65 thousand rows. BOXI R2 does not have a CSV option

  38. Sorry Tiffany, there is no way to exceed the 65K Excel export limit. There is a CSV export option in my XI R2 system, but it is a raw data dump and includes no report level formatting, aggregation, etc.

  39. Hi Sudhir, In XIR2 you can merge data from personal data files and data from universe/other source. But only tool for this is DeskI. In XI 3.1 this can be done also with WebI Rich Client. Br, Erno

  40. Hi,
    We are using 2.0 and my main user runs a WEBI report that exceeds 65K records and wants to play with the output in Excel 2007. Obviously the 65K limit prevents the use of the Save as Excel. When she runs and saves a CSV on her desktop the output gets cut off at 18000 records, the same happens if I save the output directly on the server where the software is installed but when I run it locally and save as CSV I get all 77K records. Any idea what is causing this 18K issue?
    Thanks!

  41. Hi Martin, I am not aware of any CSV export row limit, but I have seen this kind of strangeness on Excel once. After we installed a FP it went away, and it also went away when we increased that user’s access or ran the same export as Administrator. The only other thing that could impact such an export is the binary files size limit set on the WebI server, but I think if this triggers it just raises and error (not truncates the data).

  42. Thanks Julian.
    Actually I think I have narrowed down the problem to .Net version of Infoview.
    Running the report in the Java version works fine. Normally we use Java but they use .Net for single sign on in AD. And the limit is 1.5 MB. I assume a FP corrects it but which one?

  43. Hi Martin, I couldn’t tell you which FixPack, you would have to read the “fixed issues” of each one. I can tell you that if you apply a FixPack you will need to generate new web applications from it for both your Java and .NET web application servers.

  45. Hi Julian,

    Hope you are fine. I have two questions

    1)A report is running for 20 mins for the past two days. The next day the report is taking 50 mins to run. The report has no prompts. In the report side I have not made any changes. How to debug this?
    Where the problem could be? why suddenly report takes more time to execute?

    2)A report has 7 columns and 50 rows of data. In one row alone in the last three columns there is #Multivalue error.But the all the rest of the cells are with data. How that on one particular row for the last three columns the #Multivalue error can occur? How to approach and resolve this?

    I request you to post on what is #Multivalue error and in what situations it occur and how to resolve it?

    Regards,
    Ramanathan S

  46. Hi Ram, Sorry for my delay, my day job has kept me very busy lately.

    1) Have you tested the report’s SQL directly against the database to see what the performance is. It is possible that the troubles with performance are completely database related. Changes to indexes, out-of-date table stats, and many other things can cause queries to reduce in performance.

    2) The #Mulitvalue error is a most annoying error. Usually what happens is that your report is trying trying to aggregate databased on dimensions that do not completely uniquely identify a row of data. It also happens when using merged dimensions and one is using a dimension of a query and not the merged dimension. I suggest you export your data to CSV and take a close look at all records that provide data to the row throwing the error.

    I hope to expound on these topics in an article soon.

  47. I am looking for a WebI report saved to .csv to have end of line markers at the end of a record. I am finding that End of Line marker is not provided when I save WebI report to a .csv file.

  48. Hi Brian, if you are not seeing the end-of-line markers that you need in the CSV output there is not much you can do. Of course, I suspect that you are trying to use a BO report to perform ETL actions and this is usually a business intelligence “no-no”. The only thing I can think of is to have some kind of macro or small application that could put the markers there for you. You also, might try reporting this to BO to see if they might classify it as a bug or future enhancement (neither will result in a prompt solution for you though). I’d love to hear anyone else’s ideas on this.

  49. Hi,
    I need to save a WebI query into a .csv file without any text delimiter. WebI obliges me to choose text delimiters. Is there a way to do this? I’m using BO XI R2.
    Thanks.

  50. Save as CSV does actually save a dump of all your queries, not just the first query. It displays them one after the other so if you scroll down, you will see the results from the other queries.

Leave a comment

Your email address will not be published. Required fields are marked *