Getting the DataType of a Specific Column in a Data Table

[Luis_Fernando]

Hello Forum

I am trying to get the specific data type of the values of a column in a DataTable with this expression ( dt_CSV.Columns(“Amount”).DataType ) and every time I get System.String as result.

Everything started when I tried doing a filter data table to get only the values less than 41, and I kept getting 0 as result of the rowcount from the filtered table. That is when I realized that the error is that it seems that the values in the data table are all considered strings, even though there are numbers or dates.

Wierd thing is that when I check the values from the columns in the locals panels the system shows them as int32, double or date respectively, but when I try getting the value it reflects as string.

I must be doing something wrong and I’m scared because is most likely to be a little detail, yet I haven´t been able to capture.

Thanky you in advance for the attention, Regards.

[rodrigo.simao]

Hello @Luis_Fernando, try something along these lines:

1.	Check the Source Data: Ensure that the data source from which you’re loading the DataTable (dt_CSV) contains the correct data types for the “Amount” column. If the source data is formatted as strings, it will be loaded as strings into the DataTable.
2.	Data Type Mismatch: If the source data contains values that don’t match the expected data type (e.g., a numeric column containing non-numeric values like text), it can result in data type issues.
3.	Type Casting: When you filter a DataTable using the “Filter Data Table” activity, UiPath may implicitly treat the values as strings. You can explicitly convert the “Amount” column to the appropriate data type using the Convert.ToInt32 or Convert.ToDouble function in an expression. For example:

Convert.ToDouble(dt_CSV.Rows(0)(“Amount”).ToString())

3.	This will convert the string representation of a number to a double.
4.	Debugging: Use debugging activities like “Write Line” to display the data type of a specific cell value to verify its actual data type during runtime. This can help you pinpoint the issue.
5.	Check CSV Read Settings: If you’re loading data from a CSV file, make sure that you’re using the correct settings when reading the CSV to ensure that numbers are recognized as numbers.
6.	Check DataTable Schema: Double-check the schema of your DataTable (dt_CSV). Ensure that the “Amount” column is defined with the correct data type.
7.	Handling Dates: If your DataTable contains date values, you might need to handle date conversions separately using DateTime.Parse or DateTime.ParseExact depending on the date format.
8.	Column Naming: Ensure that the column name (“Amount”) is spelled correctly and matches the case used in the DataTable. Column names are case-sensitive.

Cheers!

[postwick (Paul Ostwick)]

It’s normal for everything to be a string. If you need something not to be a string column, then use Add Data Column to add an int32 (or whatever you need) column to the datatable.

Then For Each through the datatable and assign CurrentRow(“integer column name”) = CInt(CurrentRow(“string column name”).ToString)

Now you’ll have all the values in an actual integer column to be able to sort, filter, etc.

[Yoichi]

Hi,

If you get dt_CSV using ReadCSV activity, datatype of column will be always String type (as you already checked)

And if you try to filter the datatable by 41 or less using FilterDataTable , it won’t work due to datatatype.

So, one of solutions, LINQ where method will help you.

dtFiltered = dt_CSV.AsEnumerable().Where(Function(r) Double.TryParse(r("Amount").ToString,New Double) AndAlso Double.Parse(r("Amount").ToString)<41).CopyToDataTable()

This assumes there is one or more rows in the filtered datatable.

Regards,

[Luis_Fernando]

Hello @rodrigo.simao, Thank you very much for your clear response.

I checked everything you asked with the Excel file but nothing seemed to give me an answer. Read CSV. doesn´t have as many settings available and any of them is about the matter.

I tried doing the conversion as you suggest in the Filter Data Table, The thing is that it looks like I can´t use that expression where I enter the column I filter, have you try this before? Maybe you could show an example.

Besides that I know there are multiple other ways to achieve the result, but for me is weird to see that the activities does not identify the data types when reading a Table. Later on, on one of the feedback of the topic by Yoichi said that Read CSV always brings the columns datatypes as strings, the weird thing is that it is happening even when using Read Range for Excel files.

Once again Thank you for your attention.

[supermanPunch (Arpan)]

Hi @Luis_Fernando ,

An Alternate would be to use Excel Application Scope Read Range activities, we get the DataColumn types as Object and I believe the Filtration for the Datatable with Filter Datatable activity does work as tested below :

[Luis_Fernando]

Hello @supermanPunch, Thank you for your response
Did you mean the Use Excel File scope? What configuration did you use for the Preserve Format property?

[supermanPunch (Arpan)]

@Luis_Fernando

Did you mean the Use Excel File scope?

@Luis_Fernando ,

This is also possible, but it is with Modern. The showcased example was with Classic Excel activities. PreserveFormat Property was False.

To Enable Classic activities, we could Click on Filter and Select Classic (In Place of Modern) :

[Luis_Fernando]

Hello @postwick @supermanPunch @Yoichi, Thank you so much for sharing your feedback.

@Yoichi I certainly didn’t know about the default column type when reading a CSV. I thought Studio would have identified datatype accordingly, I guess I was wrong. Also, I thought the same thing when using an Excel file, but it seems that it never captures the data types no matter what Preserve Format configuration you use.

I did an experiment with all the options available and the only two results I got for the datatypes of the columns were either Object or String. When getting Object the filtering does work.

What I don’t understand about all of this is that days ago I had a similar discussion but working with DateTime values, and the result was that when working with columns that had a DateTime value, depending on the Preserve Format option you used the result of the final datetime format values of the column were different. You can see details here (Read Range stays pending & Date conversión issue - #5 by Luis_Fernando)

So this leaves me with these questions

• How is it that when reading the Excel file and storing it in the output data table the datetime values are changed from the original format and when asking for the datatype the result is either string or object?

• Is not possible that when reading an Excel file I can obtain the datatypes corresponding to the content of each column? When I check the Data Table information from the locals panel when debugging it seems to capture the correct data types for the values. Is this not relevant at all when manipulating the data?

These are the results of every configuration of the Preserve Format and the result of the columns DataType and whether the filtering works or not

Read Range Workbook
Preserve Format Configuration = True
Columns DataTypes = System.Objects
Result = Filtering of Doubles doesn’t work

Read Range Workbook
Preserve Format Configuration = False
Columns DataTypes = System.Objects
Result = Filtering of Doubles does work

As you can see for the Read Range Workbook the DataTypes does not change, but the result of the filtering does based on the Preserve Format configuration, That makes me really confused and question everything . Am I asking the wrong question here? , Is the DataType not the issue?

Use Excel File
Preserve Format Configuration = Null
Columns DataTypes = System.String
Result = Filtering of Doubles doesn’t work

Use Excel File
Preserve Format Configuration = Default
Columns DataTypes = System.String
Result = Filtering of Doubles doesn’t work

Use Excel File
Preserve Format Configuration = RawValue
Columns DataTypes = System.Object
Result = Filtering of Doubles does work

Use Excel File
Preserve Format Configuration = DisplayValue
Columns DataTypes = System.String
Result = Filtering of Doubles doesn´t work

PD:

@Yoichi I found your LINQ Query very useful and I might use it for this automation or any other., Thank you very much.

---

Thank you all, hopefully we can keep discussing the matter, Best Regards.

Luis Fernando Pazos, The Fisrt Venezuelan RPA Developer

[Yoichi]

Hi,

If some column’s datatype is object, each value of the column has own datatype.
For example, Let’s assume there is datatime type value in the worksheet (and it’s recognized as datetime by Excel)
When we read it using ReadRange activity without PreserveFormat, the column datatype will be Object and each data has information for type: DateTime.

So we can filter it using FilterDataTable with datetime type like set New DateTime(2023,9,8) .

If we read it with PreserveFormat, the column datatype will be Object and each data has information for type: String.
In this case, we can filter it using String type, and we cannot use < or > due to restriction of Filter DataTable.

Hope this helps you.

Regards,

[Luis_Fernando]

Yeah it did help @Yoichi
The only major question that still remains for me, is that even when using the Preserve Format and obtaining the data as Strings DataTypes, DateTime values are still modified from their original format in the source file.

This kind of goes a bit off from the original inquiry but is still related to this topic and the topic I shared in my last reply.

[Yoichi]

@Luis_Fernando

DateTime values are still modified from their original format in the source file.

Can you share specific sample as file (ideally whole the project including xaml and xlsx )? It’s no problem if dummy data.

Regards,

[Luis_Fernando]

Here it is…
DateDemo.zip (11.4 KB)

You can always check this topic where I shared my results with every Preserve Format configuration on the Date Format.

Read Range stays pending & Date conversión issue

Hello @Parvathy, Thank you very much for your response. I have to admit I feel pretty embarrassed , Yesterday was a complicated day and I guess I still forget the basics and check the properties of the activities very carefully. I guess I have no trouble consulting the forum every time I need something. Yet with the answers on the topic I started testing the different configurations available mostly for the Excel activities (That worked better for my case), an…

There is also a Workflow called Filtering that examples the filtering with DataTable from Read Range Workbook where both Preserve Format throws System.Object DataType but the filtering only works when the configuration is false. I think you already addressed this issue on your last reply, but I’m not sure if I got you quite alright. Do you mean that when Preserve Format is True the whole column DataType is Object but every value individually is treated as a string? where in false are treated as objects? If that’s so, you can ignore that workflow

Thank you once again

[Yoichi]

Hi,

Thank you for sharing.
First of all, as the date data in the sheet is set as DateTime for OS settings, it’s displayed vary style in each environment. For example, it’s displayed as the following (yyyy/MM/dd style.)

If you use ReadRangeExcel or ReadRangeX with PreserveFormat, the result will be change in each environment. in your envirnment, it will be dd/MM/yyyy style. However yyyy/MM/dd in my environment.
On the other hands, as ReadRangeWorkbook usually uses invariant culture, it read the data as MM/dd/yyyy style with preserve format.

If you set explicitly the data as date time format which does not depend OS setting, it will work as you expect.

Do you mean that when Preserve Format is True the whole column DataType is Object but every value individually is treated as a string?

Mostly, yes. As I mentioned each object data has information for type, the data has information for String if preserveFormat is true.

If you want to check the above, please use the following expression, for example.

dt_data.Rows(0)(1).GetType().ToString()

Hope this helps you.

Regards,