Reading date using readcell throwing System.FormatException:

Help
, ,
1

Hi All,

I am trying to read date using readcell (this is the requirement so, didn’t used read range).
is has two dates 01/09/2019 and 15/09/2019. It is reading 01/09/2019 fine. But, it is throwing exception on 15/09/2019.

RemoteException wrapping System.FormatException: 15/09/2019 is not a valid value for DateTime. —> RemoteException wrapping System.FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
at System.ComponentModel.DateTimeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
— End of inner exception stack trace —
at System.ComponentModel.DateTimeConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
at UiPath.Excel.Activities.ExcelReadCell.SetResult(AsyncCodeActivityContext context, Object result)
at UiPath.Excel.Activities.ExcelInteropActivity`1.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

Please let me know how can I read this only using dd/mm/yyyy as I cant change input excel file.

2

Hi @Shruti_Rastogi

Use row(“Column name”).ToString=CDate(row(“ColumnName”).ToString(“dd/MM/yyyy”)

Check this

Thanks
Ashwin S

3

I m using read cell not read range.

4

This exception is happening on the Read Cell activity or is it happening in some Assign activity?
The dates are separate right? In one cell you have 01/09/2019, and in the second 01/09/2019, or do you have both value in one cell?

5

It is happening in read cell which is under excel application scope. Also, ye the dates are in different cells. Its just that the date in excel is mention in dd/mm/yyyy format and uipath is reading it in mm/dd/yyyy format( as 15 is not a month in calendar that’s why throwing error)
the date which uipath is throwing error is 15/09/2019. which is in dd/mm/yyyy format

6

What type is the variable that you are putting the value? You can put a String then later convert it with

DateTime.ParseExtract(StringValue, “mm/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture)

7

Hey @Shruti_Rastogi

Yes it is due to format mismatch.

As you said you can’t change excel then i would advice to use Read Range Activity to Read entire data and take your cell by using datatable row index and column and then you can convert your datetime string to either datetime datatype or Different Datetime string format as per your needs.

Regards…!!
Aksh

8

if I put it in astring…then, throwing this error StringConverter cannot convert from System.DateTime.

9

image

This is my file, I was using read cell as I need to read vertical table(which I don’t know how to do, that’s why using read cell as I was required to get liitle info). let me know, How I can read this horizontal table, extracting the place, the two dates in separate variables.

Thanks in advance

10

@Shruti_Rastogi Every time start Date cell will be same or different ?

11

layout of excel is fixed,only the date values will change.

12

@Shruti_Rastogi Than you can use read cell activity

13

i know bt it is throwing error as mentioned above, which was the main query

14

Hi @Shruti_Rastogi,

What is output variable type of the read cell activity. If it is DateTime type, then try change it to string and read. Later if needed you can convert it into date format as required.

Regards,
Sasikumar K

1 Like
15

Hey @Shruti_Rastogi

Sorry for delayed response. was busy with some other stuff.
See attached sample for your reference.
Transpose Datatable -Shruthi Sample.xaml (14.0 KB)

it will give you datatable like -
image

so you can easily get your data by using row and column index.

Regards…!!
Aksh

1 Like
16

I am doubted because just about column there is string data i.e. Lyon so column type should not be Datetime Datatype. Just a guess. :slight_smile:

Regards…!!
Aksh

17

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.