How to convert Byte[] into a BP collection?

[NilanshuSoni]

Hi All, i have implemented one solution to read the data from an excel file using the URL to the file located on web using DownloadData(URI) method of the C# language. The data is getting generated but it is in form of Byte[] array and i need to convert it into BP collection but not able to do it. can someone here help me in doing this?  

[BastiaanBezemer]

Do you have the actual data you need already in your Byte[] array, or the whole workbook as binairy data? If it is just the data you need, you can maybe try something like: My_data_string = System.Text.Encoding.ASCII.GetString(My_byte_array) I've never used this myself, so chances are that I misunderstood your question and/or point you in the wrong direction, but it is worty trying. ☺

[NilanshuSoni]

Hi,  Thanks for replying back. i have the whole workbook as byte[] array. I have used DownloadData() method to directly getting all the data from the workbook kept at an URI into the byte[] array. now i need to convert that byte[] array into the data table(blue prism collection).  excel workbook is having columns: Name, Amount and Date. Also, when i am writing the same binary data into another excel, i am getting the same data that was available in the initial excel.  

[BastiaanBezemer]

So if you have the whole workbook in the byte array.... The easiest option is first saving it into a file with System.IO.File.WriteAllBytes(Your_Save_As_path, Your_Byte_Array) And then you can do the needed actions an manipulations with The MS Excel VBO With probably (after opening) the action Get worksheet as collection (fast)

[NilanshuSoni]

Thanks, but that is something I know. there is a requirement to not keep any file on the system for loading data. I need to directly download data through code and convert it into a collection and use it in the process on run time.

[BastiaanBezemer]

That's a pretty insane requirement.... I would try to re-negotiate that: Even windows and office apps itself often store temp files when working on remote files.... Can't you get the Data in an online google spreadsheet ? Then with API calls you can read out the data relatively easy...

[NilanshuSoni]

it will be a kind of security breach keeping real time customer data on google spreadsheet even for a fraction of second. if i could use excel in any way, i would have used it on local machine.

[NilanshuSoni]

Can some one from Blue prism technical team comment on this? 

[OscarAagren]

A bit late reply - but if you need to read it in memory I would do it in C#. To load an excel workbook from a .Net memorystream I think you need a third party library, such as https://www.nuget.org/packages/ClosedXML/. Convert the byte array to a memory stream and initiate the XLWorkbook by passing it, such as: XLWorkbook excelWorkbook = new XLWorkbook(stream) You then need to iterate through it and create a DataTable which then can be assigned to a native Blue Prism collection. Again, I would recommend saving to a temporary file and using bastiaan's approach from above.

[AndreyKudinov]

If file is xlsx (openxml), you can unzip it in memory and read parse xml format yourself or use openxml sdk or many other 3rd party libs. If it is old excel format, then some libs still work afaik, but your mileage may vary.