A couple weeks ago we received the best email question so far. Not only did it ask for more details with regards to one of the articles, but it provided insights worthy of making it its very own standalone article. Truthfully, I would like to publish it here to share the knowledge contained inside (this is always the best way to answer questions sent to us), but also to hopefully bring visibility to a very worthy question that you may be able to answer. Please read this one, it is good, and provide any comments or questions you might had in the comments below.
Our Guest Author wrote the following [minimal edits for context clarification from Julian are in brackets]:
Subject: [Excel] Format issues when Schedule a DESKI report in BOXIR2 SP3
We are trying to schedule a DESKI Report 3 times a week in Excel format to our clients. DESKI report has 1 data provider and 1 report tab only. We are on BOXIR2 SP3.
[A client] has an automated process, which reads the files we send and save it somewhere. They have a problem in reading Excel file which we send it through our BO scheduler. I tried to schedule DESKI document through InfoView using both SMTP and FTP [delivery] options.
From the InfoView -> scheduler
1. Excel format is generating Excel readable file — but they want exact Excel file.
2. Text file format is generating ‘Tab’ delimiter file
3. There is no option for CSV from scheduler [link to related article]
I am really kind of stuck in the middle (between clients, business users, and my manager). Clients want either an Exact Excel file or CSV file on a schedule basis. As there is no other way to schedule a report in CSV Format [link to related article], we have to write macros/scripts to do this. Which I am ready to do, [but] our management and team don’t want to do this workaround. [They want to] avoid macros as it will increase maintenance and users have to depend on IT for this.
New Knowledge Alert – Business Objects Cannot Create True Excel Files, only Excel Readable Files
I have opened a ticket with BO support and this is the answer I got from them.
Business Objects uses its own dll files for exporting a report to an excel format. The file created is not a native excel file and is simply excel readable.
Any file converted to excel using a tool other than excel will always create excel readable file which would be different (but not too much) from the native one created in Microsoft Excel.
Business Objects does not require Microsoft to be installed for this conversion to take place as it relies on its internal files for the conversion. BusinessObjects is designed only for creating the reports, though it can export the reports to the other formats.
But since all the exported files contain similar information such that Microsoft Excel can interpret and render the files, your module should be able to access it. The files exported from Business Objects would not differ greatly from an excel file created in Microsoft. I am not sure what these internal differences would be.
[The following response captures the client’s requirement/comments]:
I think we are stuck in this issue now because [BO] was intended to create an “Excel-readable” file, not create an Excel file. My Java module [is] trying to read an Excel file, not an Excel readable file. There [is] nothing wrong at both ends.
Unfortunately, the data file just cannot serve the purpose that I am looking for. We would need to find some other options now.
I am not sure if [BO] is able to create a “CSV” file? This is a general purpose type of file format and would not have any format issue in reading. Also, you did provide a TEXT file for my testing, but it’s not a fixed length TEXT file. Seems that tab was used as a delimiter in that text file. I wonder if you are able to create a text file without delimiter?
I know that we can save a report in CSV format also in a fixed length text format – through DESKTOP Intelligence
but is there a way to schedule this.
Is a schedule CSV option available in BOXIR3.1???
Please let me know if there is a way to schedule a DESKI report in CSV format.
Responses from BusinessObjectsTips.com’s Julian
The more I think about this I have to say that macros are the only solution that will meet the needs client, unless they can somehow do SDK integration of some sort and pull the data right off the report.
Another solution might be to create an Excel “converter” application. This could be a standalone Excel file with a macro in it that loads BO’s native Excel readable format and outputs a true Excel format file. Perhaps a standalone executable would be better because it would allow for scheduling (perhaps using a BO Program Object). I don’t really know how to create such an application, but this actually would not require any BO skills.
To the author, please share your progress with us all. To the rest of our audience please share your ideas and comments. Thanks!