0

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.

Brian Rogers
  • 125,747
  • 31
  • 299
  • 300
Vish
  • 91
  • 1
  • 9
  • 2
    If you take a look at the value of ```created date``` in the second entry, do you notice anything? – devsmn Apr 15 '20 at 07: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 Apr 15 '20 at 07:20
  • Do `DataTable` support `Nullable` @Eldar? – mjwills Apr 15 '20 at 07:21
  • Well if you can't control JSON data itself you can try the solution mentioned [here](https://stackoverflow.com/a/39862088/12354911). Which converts empty strings into null values in the JSON string. – Eldar Apr 15 '20 at 07:33
  • @mjwills I'm calling third party library's function, which only support data in the DataTable format. Due to this I don't have any alternative way to parse data. – Vish Apr 15 '20 at 07:52
  • @Shawn Thanks for your attention. Yes! I got the root cause of it. But can't able to fund any solution to prevent error. – Vish Apr 15 '20 at 07:54
  • @Eldar Thanks for providing link but may be it's impact on performance as it's traverse each data. And our JSON data contain minimum 10K records. – Vish Apr 15 '20 at 07:56
  • 2
    Use https://app.quicktype.io?share=wq0HmxtiEUoJoDaIEOmQ to parse it into standard C# types. Then generate the `DataTable` manually and populate it manually. – mjwills Apr 15 '20 at 07:58
  • 2
    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](https://stackoverflow.com/q/11856694/3744182). 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 Apr 15 '20 at 18:56

1 Answers1

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

Brian Rogers
  • 125,747
  • 31
  • 299
  • 300
  • This doesnt work in .net 6 with the latest newtonsoft Json.Net package – PBo Sep 27 '22 at 13:23
  • 1
    @PBo I beg to differ. https://dotnetfiddle.net/Etwl4Q – Brian Rogers Sep 28 '22 at 06:29
  • 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 – PBo Oct 12 '22 at 12:44