Excel: Copying Number to Text

[Vinodh_KannanKr]

Hello,

I am using MS Excel VBOs GetWorkSheetAsCollection action to get data from an excel file into a collection. The excel file has long numbers which are coming in fine in the excel as the column format is number. But when using the GetWorkSheetAsCollection it gets copied into a text column and gets converted into exponential notation.

For example: 5023030911480000 comes in as 5.02303E+15

Appreciate if any workarounds can be suggested. TIA

------------------------------
Vinodh Kannan Krishnan Vice President
AVP
State Street Corporation
America/New_York
------------------------------

[Mukeshh_k · Accepted solution]

Hello @Vinodh Kannan Krishnan -

I have replicated your query around such long entries with format of the column kept as Number and have posted the solution for this please follow below approach when dealing with such requirements :

Collection Fields are autoset  to "Text" by default - In order to change a field of the collection from Text to let's say Number in this case - You would have to change it via code stage, Please follow below approach : 

Use the Collection Manipulation Object - Add an Action with Code Stage:

Inputs:  Collection = CollectionIn and Column Name

Ouputs: Collection = CollectionOut

Code for changing the Column Type from Text to Number (I have attached the txt file for the code below: 

Results: Your column is now set to Number Type with Exact Data as on Excel, let me know if you face any difficulties implementing this. 

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS England, United Kingdom, GB
------------------------------