Rewrite Expression to Handle the error related to Date Column in Data table

[Sisay_Dinku (Sisay YDG)]

Hi team,
@Yoichi, @ppr, et al;
I have a process in place that converts Json data to Data table so that certain fields are updated in an application from the data coming from each of the columns in the data table. This process has been in production and working seamlessly using the following expression

io_dt_GlobalDataTable = JsonConvert.DeserializeObject(of DataTable)(in_qi_TransactionItem.SpecificContent(“JsonStringIs”).ToString)

Here is the json payload for your reference

However, we wanted to revise the code as there was additional column required to be added - the column is Date column. All the rows of this column are not always need to be populated with date data, sometimes the rows could be empty or null. Therefore, we want the expression rewritten to handle this situation.

I’ve tried to revise as follows.

io_dt_GlobalDataTable = JsonConvert.DeserializeObject(Of DataTable)(in_qi_TransactionItem.SpecificContent(“JsonStringIs”).ToString.Replace(“”“Date”“: “””“”, “”“Date”“: null”))

Which I was not successful and threw the following error

Can anyone help me rewrite the expression to handle this scenario?
If you need additional info, please let me know.

Thanks a lot!
Sisay

[Kancharla_Mahija]

It looks like the issue arises from trying to convert empty strings to DateTime objects, which causes the deserialization to fail. To handle this, Maybe you can use a custom JsonConverter that treats empty strings as null for DateTime fields.

[Yoichi]

Hi,

It seems data type of the column is DateTime and it doesn’t allow null or empty value.
As a workaround, how about either of the following?

• Set something datetime value which cannot be in actual such as 2199-1-1
  Then replace it to blank. In this case, it’s necessary to add a new column then copy theses values.

• Insert dummy row which has all empty value as the first row in the json.
  Then remove it from the datatable.
  (Because datatype of columns is determined from the first row data)

Regards

[Sisay_Dinku (Sisay YDG)]

Hi @Kancharla_Mahija -
Yes, you got the issue right. How do we do custom JsonConverter can you rewrite the expression I just posted in the initial post?

Thanks a lot

[Kancharla_Mahija]

try creating this custom converter using C# code

public class CustomDateTimeConverter : IsoDateTimeConverter
{
    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        if (reader.Value == null || string.IsNullOrEmpty(reader.Value.ToString()))
            return null;

        return base.ReadJson(reader, objectType, existingValue, serializer);
    }
}

then Use the Custom Converter in Your Deserialization:

var settings = new JsonSerializerSettings
{
    DateFormatHandling = DateFormatHandling.IsoDateFormat,
    DateTimeZoneHandling = DateTimeZoneHandling.Utc,
    NullValueHandling = NullValueHandling.Include,
    Converters = new List<JsonConverter> { new CustomDateTimeConverter() }
};

io_dt_GlobalDataTable = JsonConvert.DeserializeObject<DataTable>(in_qi_TransactionItem.SpecificContent("JsonStringIs").ToString(), settings);

Kindly mark as solution if it works

[Sisay_Dinku (Sisay YDG)]

Hi Yoichi -

Not sure how it is done but your first suggestion [[quote=“Yoichi, post:3, topic:771087”]
Set something datetime value which cannot be in actual such as 2199-1-1
Then replace it to blank. In this case, it’s necessary to add a new column then copy theses values.
[/quote]

] looks like viable. It’d be great if you could show me how that’s done using the expressions - Also

[Sisay_Dinku (Sisay YDG)]

Hi @Kancharla_Mahija

Sorry I am not very much familiar with this, but do you want me to have them in invoke code subsequently. I guess my question is how it is implemented?

Thanks a lot,

[Yoichi]

Hi,

How about the following?

io_dt_GlobalDataTable = Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(in_qi_TransactionItem.SpecificContent("JsonStringIs").ToString.Replace("""Date"": """"", """Date"": ""2199-01-01T00:00:00"""))

CDate(CurrentRow("Date")).Date=New Date(2199,1,1)

Main.xaml (11.1 KB)

Regards,

[Sisay_Dinku (Sisay YDG)]

Hi @Yoichi

Since I am using REFramework (there is no currentrow)
I changed the expression to Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)( in_qi_TransactionItem.SpecificContent(“JsonStringIs”).ToString.Replace(“”“Date”“: “””“”, “”“Date”": ““2199-01-01T00:00:00"””)) and I still see the error

[Yoichi]

HI,

For now, can you assign the following expression into string type variable then check it if “” after Date is replaced with “2199-01-01T00:00:00”?

varStr = in_qi_TransactionItem.SpecificContent(“JsonStringIs”).ToString.Replace(“”“Date”“: “””“”, “”“Date”": ““2199-01-01T00:00:00"””)

OR

There might be extra white space after : character . So can you also try the following expression?

Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(in_qi_TransactionItem.SpecificContent("JsonStringIs").ToString.Replace("""Date"":""""", """Date"":""2199-01-01T00:00:00"""))

Regards,

[Sisay_Dinku (Sisay YDG)]

Hi @Yoichi
I did correct the whitespace in the expression unfortunately I still see the same error.
Thanks!
Sisay

[Sisay_Dinku (Sisay YDG)]

Hi @Yoichi and team,
I’ve another option to consider - which is replacing the date column with the row that has empty or null or “” replace with the current date (MM/dd/yyyy HH:mm:ss format). I used the following expression,
Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(in_qi_TransactionItem.SpecificContent(“JsonStringIs”).ToString.Replace(“”“Date”“: “””“”, “”“Date”“: “”” & Now.ToString(“MM/dd/yyyy HH:mm:ss”) & “”“”))
but I still see the issue not being resolved.
Just FYI - Not sure if it makes any difference but I have the assign activity of the above expression at the beginning of the Process Transaction of REFramework -

Thanks for your help!
Sisay

[Yoichi]

HI,

If possible, can you share your json string as file using WrtieTextFile activity? It’s no problem, even if remove sensitive data or use dummy data.

Regards,

[Sisay_Dinku (Sisay YDG)]

@Yoichi

Let me know if you received the attached txt file.

[Yoichi]

HI,

Can you try the following sample? Please put the json file (Json_0018.txt) under the project folder when run this project.

Sample20241004-1.zip (3.1 KB)

Regards,

[Sisay_Dinku (Sisay YDG)]

Hi @Yoichi

I ran and it works and types back to excel file. However, I have follow up questions.

1. Why do the remainder columns got cut off? As I need the sequence of the columns to be intact.
2. When I have to get the json from the queue item how do I have to do the breakdown you did? Using the initial expression we used Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(in_qi_TransactionItem.SpecificContent(“JsonStringIs”).ToString.Replace(“”“Date”“:”“”“”, “”“Date”“:”“2199-01-01T00:00:00"”"))
   Sorry for the multiple back and Forths - just wanted to fully understand and wanted to test my REFramework
   Sample20241004-1.zip (151.0 KB)

[Yoichi]

@Sisay_Dinku

Why do the remainder columns got cut off? As I need the sequence of the columns to be intact.

Because just added new data column in the sample. DataColumn.SetOrdinal method will solve it. Can you try the following sample?

Sample20241004-1 (2).zip (3.2 KB)

When I have to get the json from the queue item how do I have to do the breakdown you did? Using the initial expression we used Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(in_qi_TransactionItem.SpecificContent(“JsonStringIs”).ToString.Replace(“”“Date”“:”“”“”, “”“Date”“:”“2199-01-01T00:00:00"”"))

To isolate cause, can you try to separate the expression then chek it step by step?

strJson = in_qi_TransactionItem.SpecificContent(“JsonStringIs”).ToString

strJson = strJson.Replace(.....

Then, check if the strJson doesn’t empty string value for Date key. If exists, investigate reason not replaced by the above expression. (e.g.extra white space etc.) Or share the part as file, if possible?

Regards,

[Sisay_Dinku (Sisay YDG)]

Hi @Yoichi
Thank you so much for the great support as always - Yes, I am able to accomplish what I wanted. Much appreciate it.

Sisay