topic RE: Convert Data type of Column Name in Product Forum

Convert Data type of Column Name

vinodchinthakin — Tue, 30 Nov 2021 09:31:00 GMT

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
------------------------------

RE: Convert Data type of Column Name

devneetmohanty07 — Tue, 30 Nov 2021 10:08:00 GMT

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
------------------------------

RE: Convert Data type of Column Name

vinodchinthakin — Tue, 30 Nov 2021 10:15:00 GMT

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
------------------------------

RE: Convert Data type of Column Name

devneetmohanty07 — Tue, 30 Nov 2021 11:39:00 GMT

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

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

RE: Convert Data type of Column Name

vinodchinthakin — Tue, 30 Nov 2021 11:54:00 GMT

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

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

RE: Convert Data type of Column Name

vinodchinthakin — Tue, 30 Nov 2021 12:25:00 GMT

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
------------------------------

RE: Convert Data type of Column Name

devneetmohanty07 — Tue, 30 Nov 2021 12:36:00 GMT

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

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

RE: Convert Data type of Column Name

vinodchinthakin — Tue, 30 Nov 2021 12:54:00 GMT

Perfect!. Thanks!

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

RE: Convert Data type of Column Name

devneetmohanty07 — Tue, 30 Nov 2021 13:45:00 GMT

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

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

RE: Convert Data type of Column Name

Ashis_KumarRay — Tue, 07 Dec 2021 17:12:00 GMT

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
------------------------------

RE: Convert Data type of Column Name

devneetmohanty07 — Tue, 07 Dec 2021 23:28:00 GMT

Hi Ashis,

Yes you are absolutely correct on the point that the values which got converted are in UTC format because of which you are facing this issue. Also, one thing to note is in VB .NET you only have the DateTime class. Technically speaking, even the Date values are treated as DateTime objects with time component values as 00:00:00 only which is why when we call the data type in VB .NET then it is by default mapped against the DateTime column in the collection.

My fair guess here is that Blue Prism internally has some mechanism of converting the DateTime objects into the the 'Date' data type at the Blue Prism end or they might be using some specific class for dates that I am not aware of yet.

Perhaps @Bruce Liu @ewilson or someone from the technical team can confirm which class type in VB .NET is specifically being used to map the data type 'Date' in Blue Prism.

Coming to one of the solution is probably something you have picked up correctly that we would require to convert the incoming date from UTC format. For implementing this I have modified by solution a bit as shown below:

First you need to create all this extra data items as input parameters for the action where you can supply the time difference in terms of hours, minutes and seconds. You can provide the default value as 0 (Initial Value). So tomorrow in case you want to work with UTC format simply no need to provide any values.

In the code stage also the following changes need to be done:

Here the major change is the commenting of the line: Output_Collection.ImportRow(row) and instead we are using a different looping logic altogether so that we can check the date data type and accordingly convert just that one column value to Local format instead of importing all the rows at once.

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

Dim newRow As System.Data.DataRow

For Each row as System.Data.DataRow In Input_Collection.Rows


     newRow = Output_Collection.NewRow()

For Each column as System.Data.DataColumn In Output_Collection.Columns

If column.DataType = GetType(Date) Then

               newRow(column.ColumnName) = CDate(row(column.ColumnName)) + New  TimeSpan(0,Time_Difference_In_Hours,Time_Difference_In_Minutes,Time_Difference_In_Seconds)

Else

newRow(column.ColumnName) = row(column.ColumnName)

End If

Next

Output_Collection.Rows.Add(newRow)

Next

Test Results:

Here I am converting UTC to IST so I have provided the time difference of +5:30, you can also provide negative values if your target time zone is behind the UTC time zone

You can see we got the proper datetime values now since we are considering them to be a part of our current Time Zone. Hope it helps you out!

------------------------------
----------------------------------
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

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

RE: Convert Data type of Column Name

ewilson — Wed, 08 Dec 2021 16:30:00 GMT

There's nothing super special going on behind the scenes within Blue Prism. As you can see from a Code stage, Blue Prism's Date, DateTime, and Time data items are all implemented using System.DateTime.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Convert Data type of Column Name

devneetmohanty07 — Wed, 08 Dec 2021 16:57:00 GMT

Thanks a lot for your response @ewilson. I had my doubts over this, but the implementation of Date from DateTime is something that is the same in VB .NET as well.

Let me rephrase my question a bit, the part that I am not able to understand is that when we try to add any columns to a data table from code stage using: Output_Collection.Columns.Add(column.ColumnName, GetType(Date))

Blue Prism adds this column as a DateTime type of column at it's end. If you say that the data types DateTime, Date and Time are all implemented using System.Date.DateTime, then how does it distinguishes that whether date, date time or time type of data is added using Columns.Add method.

Can you perhaps help to share any sample code where we can see the logic to add any Date or Time column apart from DateTime column via code stage if possible ?

------------------------------
----------------------------------
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

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

RE: Convert Data type of Column Name

ewilson — Wed, 08 Dec 2021 20:12:00 GMT

I've been tinkering around a bit with this. If you look at the schema of the underlying DataTable for a Blue Prism Collection defined with a Column of type Date what you'll find in an extended property. This extended property seems to be what BP uses to map DateTime to Date and Time types.

When you create a column dynamically there's a property on the DataColumn type called ExtendedProperties. In theory, you should be able to add this custom property to that property collection and voila. 🤷‍♂️

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Convert Data type of Column Name

Finlay_lucaFinl — Wed, 08 Dec 2021 20:32:00 GMT

Once we created using external data, systematically numeric columns are taken to as data type objects instead of int or float, creating numeric tasks not possible. We will pass any Python, Numpy, or Pandas datatype to vary all columns of a dataframe thereto type, or we will pass a dictionary having column names as keys and datatype as values to vary the type of picked columns.

Here function empowers us to be express the data type you need to have. It's extremely adaptable i.e you can attempt to go from one type to some other.

Attention geek! Strengthen your foundations with the Course and learn the basics.

Sean Murphy Team

Re: Convert Data type of Column Name

Jaychlo — Wed, 07 Aug 2024 14:24:20 GMT

Hi Vinod.

I'm curious as to how you used a loop and calc stage to change the Data Type of your column. I need to do this myself. It could be any column that needs to be changed and it could be a different column every time.
Any help would be appreciated.

Thanks
JB

Re: RE: Convert Data type of Column Name

Jaychlo — Mon, 12 Aug 2024 14:44:41 GMT

Hi Devneet,

I've tried using your code below to update a data type in my collection but I'm getting the error below:

Description: Compiler error at line 20: 'Output_Collection' is not declared. It may be inaccessible due to its protection level.

Any suggestions and how I can correct this please?