Importing data from excel including cell type

[NiekGroebbé]

Hi, I'm trying to read an excel file into a collection, which works fine, but the data type of the field in the collection is not the same as in the excel file. The specific example: - Use of "Get Worksheet As Collection" from business object "MS Excel VBO" - Excel opened has a single column with numbers in it (confirmed by Ctrl-A + setting the cell type to "Number" followed by a save, previous to opening it with Blue Prism) - Data (specific values) from excel are put into collection, however, the field type is always "Text" Please advise. How do I make sure the collection fields are the same data type as the cells in the excel or at least the included header? Simply using a ToNumber would obviously make the text of each row a number, but not the column itself. Regards, Niek

[John__Carter]

Hi Niek - I think Get Worksheet As Collection will always return text values and you'll need to cast the data after reading. Try setting up a second collection with the datatypes you want, and then simply use a calculation stage with [Coll1] as the expression and Coll2 as the Store In variable. If the Coll1 data is 'clean' the calculation stage will perform the cast automatically. However, if any of the Excel number fields are blank this 'cast whole collection' idea won't work - in this situation you'll need to use a loop to transfer individual values from Coll1 to Coll2. If your collections are large then you could devise your own code stage - a BP collection is represented as a .Net datatable in a code stage, making looping through rows and columns is quite easy.

[NiekGroebbé]

Hi John, Ended up prematurely defining the columns with their respective types in the collection. This populates the columns accordingly with the values in excel. I was hoping for an exact copy of the column types from excel, but unfortunately this is not possible.

[JivankumarKedar]

From my experience, even though the data type is changed I am able to perform the operations as needed. for eg. I have amount column in excel which somehow got casted to Text in BP collection. But still I am able to perform Sum operation on it. If your case doesnt scrictly need the particular data types be retained, this should be fine.