Best way to append reports created as .xls files

[pranavred]

Every 10 minutes the bot runs, a report is generated as .xls file. We want to combine these reports into a single file and use that for a dashboard. What's the best way to consolidate these reports?    Now we're planning to add more bots which do the same, but our final output should still be a single file connected to dashboard with all consolidated reports. Is it advisable to use .csv instead of .xls? We are also thinking of writing the report to a database and appending to it from all different robots. But that increases our dependency. Any advice is appreciated.  Thanks,  Pranav

[AmiBarrett]

Programatically, CSVs are generally easier to work with than an XLS, but it can be done. There are two problems with this unfortunately. 1) There's no caching. As the file gets longer and longer, so will the page load, significantly moreso than a DB call would. 2) If multiple robots are writing to a single file, you could have a file lock error, or (more likely) a case of data loss. Say bot A opens the file, then bot B opens it. Bot A appends and saves, then so does bot B. Since bot B appended to the original file and overwrote what was on disk, the data added by bot A is lost. You could get around point 2 by collecting multiple files and looping through them all, but this may also increase load time. If you instead dump the results to a DB, you get caching as well as the benefit of simultaneous connections/writes. Likewise, it would probably be a lot easier to query different data if you want to make multiple types of reports. For example, we have a custom table to house our results and generate ten different types of reports based off of that data.

[BenKirimlidis]

Hi Pranavred, We tried a similar approach to a similar problem but we kept encountering issues.  I'd recommend making DB calls instead of using excels/CSVs. -Multiple robots writing to the same file will present issues, if the file is locked you may have to attempt to open the file mutliple times and code in wait stages into the process adding inefficiencies as the robot is sitting there with nothing to do. -Using a CSV or XLS and trying to interact with it doesn't scale as easily,the more robots, the higher the liklihood of locks and extended waits occuring. -Adding in wait stages is more inefficient in terms of run time but it also adds to the development time. -Also depending on who has access to the fodlers the files are stored in can present issues if a human user moves the file or open it without clsoing out of it.  Same problem occurs if a BOT opens the file but the process terminates for whatever reason, the file is still locked on that run time resourse. -Just adding a DB call stage or an Alert with the requisite data means you can query your own DB or a mirror of the BP DB server easily whenever you want to know what the BOT has been doing.  This is a much more scalable solution as well as AmiBarrett pointed out above as you can have one or many bots interacting wih the DB at once. -In addition, you can adjust the MI by changing your SQL queries without having to do a new release in BP which is a big bonus depending on how your company does releases. -Recommend getting the bot to log the run log number as a parameters so you can distuinguish items run in one batch vs another. I'm new to the forum, so apologies if answering your post in this way is against etiquette (i.e. recommending a different solution instead of helping you improve your proposed method).  

[pranavred]

Thanks Amibarrett and Ben_M31. I was shying away from DB because we will need this data only for a day and we were planning to delete the CSVs. It makes sense to use the DB since I saw issues with files getting corrupt.  Thanks again for the responses. Ben_M31, different solutions are very helpful, especially if you've faced similar problems 🙂