Csv to DataTable fields typing

Hello,

I’m new with UI-Path. I used Read CSV to transform CSV to DataTable. As a result, all fields are string typed, that does not make it easy to use Filter activity on some dates or amounts.
What is the correct approach ?

  • Use another activity that read CSV and provide DataTable with typed fields, maybe with the help of a schema ?
  • Using classic CSV activity, and use LinQ to build anonymous types, but I saw the compiler doesnt support it, that and other things as let or tuples (probably compiler options and scope)
  • Using classic CSV activity, and use LinQ to transform DataRow to a strong type I defined in a custom .Net library i wrote and import ?
  • Using Excel to import CSV and hope the typing will be done at “opening time”. (but I dont know how to recover DataTable because of the Range I cant predict or now)
  • Another way ?

here the code I used to filter and compensate typing lack. not really what i hoped…

(from row in datatableCsv.AsEnumerable()

where DateTime.ParseExact(row.Field(“Date”),“dd/MM/yyyy”,System.Globalization.CultureInfo.CurrentUICulture) >= startDate
&& DateTime.ParseExact(row.Field(“Date”),“dd/MM/yyyy”,System.Globalization.CultureInfo.CurrentUICulture) < endDate.AddDays(1)
select row).CopyToDataTable()

Thank you for your help

@Frederic_DIDIER
welcome to the forum

You can have a look to the generate DataTable activity and its different configuration options. Maybe working with the auto detect column type will serve your needs.

However the datatable’s row itemArray (reflecting the column values of the row) is of datatype Object() (Object Array). So in the general work the indication to the specific datatype is needed.

About your statement have a look here (VB and the use of And instead of &&):

(from row in datatableCsv.AsEnumerable()
let pd = DateTime.ParseExact(row.Field("Date").toString,"dd/MM/yyyy",System.Globalization.CultureInfo.CurrentUICulture)
where  pd >= startDate.Date AND pd < endDate.AddDays(1).Date
select row).CopyToDataTable()

we can make it more compact with the let block
we do force the use of string for the date column
we do compare on the date part to compare on the entire day /range

with importing the namespace System.Globalization we can addtional shorten the statement to:
let pd = DateTime.ParseExact(row.Field(“Date”).toString,“dd/MM/yyyy”,CultureInfo.CurrentUICulture)

1 Like

Thank you for your kind answer.

Ok for importing namespace, that is “equivalent” to using statement in C# code.

“let” doesnt work, that is the first thing I tried :grinning:. Looks like UIpath doesnt support code syntax lesser than C# 5. I didnt find how to increase language version. My UI-Path version is 2020-10-2, it’s quite recent, so I didnt suspect any application version problem.

Generate DataTable activity seems perfect, but…

It didnt succeed in automatic type detection. activity seems use application CultureInfo that seems to be en-US, and csv dates are fr-FR style formated (dd/MM/yyyy). I didnt found a way to change language (that could not be solution for ages), and didnt found any way to provide parsing statements or schema to the activity.

Idealy, the CsvParser API embedded in a graphical component (activity) would be perfect, but it’s time to code Designers for the activity…

https://docs.uipath.com/activities/lang-fr/docs/generate-data-table

@Frederic_DIDIER
ok im working with VB and Back to Version 2018 when I started to work withUiPath i was working with LET in LINQs (Maybe a C# thing or other issue)

csv dates are fr-FR style formated (dd/MM/yyyy)

you can control it with pattern or with CultureInfo (e.g. New CultureInfo(“fr-FR”)

Idealy, the CsvParser API embedded in a graphical component (activity) would be perfect, but it’s time to code Designers for the activity…

can be done over the activity settings

have a look here:

maybe you share a csv with some sample rows and we can help you more directly based on this

I agree with the CultureInfo, I just did’nt find how to provide it to the Generate DataTable activity.

Nice example. Efficient too…

In fact I realize that the point is specifying the culture to any activity… for example, Web application, file parsing…

Thank you very much for your help

@Frederic_DIDIER
have a look here: