Convert Data type of Column Name

[vinodchinthakin]

Hi

I have a Collection with Columns A,B,C as Text Data Type. I want to convert Column B as Number Data Type.
I am using Calc stage within a loop which is taking more time when collection is huge. Do we have any other solution for this ?

Thanks

------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Hi Vinod,

Create a collection with the same fields but make the field type of B as number type. Use a calculation stage and assign the original collection to this collection. No loops are required.

For example:

I used a collection called Coll1 with A,B and C field as text and some values as well as shown below.


I used a defined collection call "Coll2" with the fields A,B and C where B is of number type and it has no values.


I just used a calculation stage to store the value of "Coll1" into Coll2" and automatically all the values will get type casted in column B from text to number as shown below:



Values in Coll2:



NOTE : Make sure that all values in field B of Coll1 are proper Numeric values otherwise you will get an exception.




------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
------------------------------

[vinodchinthakin]

Thanks Devneet Mohanty for your time in responding my query. I have already implemented this solution for collections with less no. of columns.
Let me elaborate my complete query, If I have a collection of 40-50 Columns, I can't go with above solution.
Do you suggest any other solution. Like Coding?

Thanks

------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07 · Accepted solution]

Thanks for your response, if you have a large collection in that case you can extend your Collection manipulation action with a custom action having three inputs: Input Collection, Field Name (text) and Field Type(text) where Field Type can have the possible values such as "Number","Flag","Text" or "Date".

Use the following code:

For Each column As System.Data.DataColumn In Input_Collection.Columns
     If Field_Name.Equals(column.ColumnName) Then
              If CStr(Field_Type).ToUpper.Equals("TEXT") Then
                    Output_Collection.Columns.Add(column.ColumnName,GetType(String))
              ElseIf CStr(Field_Type).ToUpper.Equals("NUMBER") Then
                    Output_Collection.Columns.Add(column.ColumnName,GetType(Integer))
              ElseIf CStr(Field_Type).ToUpper.Equals("FLAG") Then
                    Output_Collection.Columns.Add(column.ColumnName,GetType(Boolean))
              ElseIf CStr(Field_Type).ToUpper.Equals("DATE") Then
                    Output_Collection.Columns.Add(column.ColumnName,GetType(Date))
              Else
                    Throw New Exception("Invalid File Type Provided")
              End If
       Else
              Output_Collection.Columns.Add(column.ColumnName,column.DataType)
       End If
Next
For Each row as System.Data.DataRow In Input_Collection.Rows
       Output_Collection.ImportRow(row)
Next


Please find the below screenshots for more reference where we are transforming the 'Age' column from 'Text' to 'Number' :





Code Stage Parameters:






------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[vinodchinthakin]

Thanks Devneet Mohanty.
I will try your code and will let you know.



------------------------------
vinod chinthakindi
------------------------------

[vinodchinthakin]

Hi Devneet Mohanty
Your Code works great. But I have a problem.
In my Case some values will be null then while executing code its throwing an error,

Do we have any solution to handle null Values while converting to Number

Thanks

------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Ideally Vinod, there should not be any null values coming in an Integer column. Technically the error is correct, better thing  is to first either remove these null values or replace the null values with 0 before using this code. Such changes generally should not be handled within the code itself as it is more of a business requirement related change. Try to keep the code reusable for every kind of general scenario.

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[vinodchinthakin]

Perfect!. Thanks!

------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Awesome 🙂

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[Ashis_KumarRay]

Hi Devneet,

I have used the above code stage to convert a column from "Text" to "Date". When I open the Output collection, I found that the text column became "Date Time" and also values are not correct.

E.g. one row value was "01/04/2021" as "Text". After the conversion the value became "31/03/2021 23:00:00" (I guess it follows the UTC time)

Please let me know how to resolve this issue. I only need that column in Date format with same data.

Thanks
Ashis

------------------------------
Ashis Kumar Ray
RPA Developer
TCS
Europe/London
------------------------------