JsonSerializationException while parsing JSON to DataTable

Question

I am trying to parse JSON data to a DataTable in C#. But while parsing I am seeing a Newtonsoft.Json.JsonSerializationException.

Error converting value "" to type 'System.DateTime'. Path '[1]['Created Date']', line 8, position 20.

Here is the code which I used to parse the data:

DataTable dtData = JArray.Parse(jsonString).ToObject<DataTable>();

Sample JSON data:

[
  {
    "rno": 1,
    "Name": "XYZ",
    "Created Date": "2014-04-30T14:39:12.2397769Z"
  },
  {
    "rno": 2,
    "Name": "ABC",
    "Created Date": ""
  }
]

Can someone tell me how to manage this type of scenario. I just want to prevent a data type change while adding data into the data table. Instead of DateTime, the column data type should be set as string.

If you take a look at the value of created date in the second entry, do you notice anything? — devsmn
– devsmn, Commented Apr 15, 2020 at 7:19
Also, take a step back and explain why you want to convert JSON to a DataTable. That is odd. Wouldn't you rather it be converted into a list of a concrete type? Why specifically do you want DataTable? — mjwills
– mjwills, Commented Apr 15, 2020 at 7:20
Use app.quicktype.io?share=wq0HmxtiEUoJoDaIEOmQ to parse it into standard C# types. Then generate the DataTable manually and populate it manually. — mjwills
– mjwills, Commented Apr 15, 2020 at 7:58
Your problem is that the "Created Date" is being recognized as a DateTime string -- but "" is not a valid DateTime string. To disable automatic DateTime recognition, set DateParseHandling = DateParseHandling.None as shown in Json.NET Disable the deserialization on DateTime. now your DataTable will be created with a string column rather than a DateTime column. You can postprocess it after deserialization to convert the column to DateTime if necessary. — dbc
– dbc, Commented Apr 15, 2020 at 18:56

Brian Rogers · Accepted Answer · 2020-05-04 19:55:54Z

0

When deserializing JSON to a DataTable, Json.Net automatically tries to determine the data type of each column based on the property values of the first item in the array. In this case, the first item has a date string for the Created Date property, so that column's data type is being set to DateTime. The error occurs on the second item when the parser encounters an empty string for the Created Date property. An empty string cannot be converted to a DateTime, so that results in a JsonSerializationException.

If you want the Created Date column to always be parsed as a string instead of a DateTime, you can set DateParseHandling to None. That will prevent the error you are seeing.

Change this code:

DataTable dtData = JArray.Parse(jsonString).ToObject<DataTable>();

To this:

var settings = new JsonSerializerSettings { DateParseHandling = DateParseHandling.None };
DataTable dtData = JsonConvert.DeserializeObject<DataTable>(jsonString, settings);

Fiddle: https://dotnetfiddle.net/EWWGT0

edited May 4, 2020 at 19:55

answered May 4, 2020 at 19:44

Brian Rogers

130k31 gold badges315 silver badges315 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PBo Over a year ago

This doesnt work in .net 6 with the latest newtonsoft Json.Net package

Brian Rogers Over a year ago

@PBo I beg to differ. dotnetfiddle.net/Etwl4Q

PBo Over a year ago

You are right Brian Rogers - While we updated to .net 6 we did not update the nuget packages. the newtonsoft version we had was 9.0.0 which suddenly stopped working and I wrote some work around code for that. Once I saw your comment, I discovered we are not using the latest version of newtonsoft json.net package. once we upgraded to the latest version, I reverted what I did and it worked. Quite unexpected. I wish I had the time to figure out why it happened

Collectives™ on Stack Overflow

JsonSerializationException while parsing JSON to DataTable

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related