JSON file to datatable(s)

Question

0.00/5 (No votes)

See more:

Hi Experts,

I know this might been asked many times before and has many solutions out there but none works for me. I have a JSON file need to convert to datatable(s).
I have tried to use JsonConvert.DeserializeObject<class>(JSONFILE)
Also tried JsonConvert.DeserializeAnonymousType
and tried to convert to object / array
all above methods return errors. Please see below the JSON file and the code I have tried.

Thanks in advance!!
Samira

What I have tried:

JSON File

{
  "vehicle-make": {
    "id": "kia",
    "name": "Kia",
    "path": "p"
  },
  "vehicle-year": {
    "id": "2016-std-key",
    "name": "2016 Std. Key",
    "path": "p1"
  },
  "vehicle-model": {
    "id": "rio",
    "name": "Rio",
    "path": "p2"
  },
  "product": {
    "id": "orbit000a",
    "name": "ORBIT000A",
    "path": "/v1/catalog/orbit000a",
    "description": "description",
    "category": "kit"
  },
  "coverage": {
    "t-harness-support": false,
    "\"track-pack\"-gauges": false,
    "2-way-serial-data-communication": false,
    "3xlock-start-from-oem-remote": false,
    "3xlock-start-w-aftermarket-starter": false    
  },
  "accessories": []
}

Different ways to convert JSON to DT

HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(url);
            httpWebRequest.Headers.Add(headerKey, headerVal);
            httpWebRequest.Method = WebRequestMethods.Http.Get;
            httpWebRequest.Accept = "application/json; charset=utf-8";
            string JSONfile;
            var response = (HttpWebResponse)httpWebRequest.GetResponse();
            using (var sr = new StreamReader(response.GetResponseStream()))
            { JSONfile = sr.ReadToEnd(); }
            JSONfile = JSONfile.Replace("vehicle-make", "vehiclemake"); 
            JSONfile = JSONfile.Replace("vehicle-year", "vehicleyear");
            JSONfile = JSONfile.Replace("vehicle-model", "vehiclemodel");

            //****method(1)**** error unexpected end of data
            var obj = JObject.Parse(JSONfile);
            var array = new JArray(obj["vehiclemake"].Values());
            var dt = array.ToObject<DataTable>();
         //**** method(2) return null object
         clsMakes make = JsonConvert.DeserializeObject<clsMakes>(JSONfile);
        //also tried with list 
//Cannot deserialize the current JSON object (e.g. {"name":"value"}) into type 'System.Collections.Generic.List`1[ApiConnect.clsMakes]' because the type requires a JSON array (e.g. [1,2,3]) to deserialize correctly
       var make = JsonConvert.DeserializeObject<List<clsMakes>>(JSONfile);
      //class clsMakes
    class clsMakes
    {
        
        public string id {get;set;}
        public string name { get; set; }
        public string path { get; set; }
    }
//method(3)
//Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path 'vehiclemake'
var makeTB = JsonConvert.DeserializeAnonymousType(JSONfile, new { vehiclemake = default(DataTable) }).vehiclemake;

Posted 15-Mar-17 7:24am

Samira Radwan

Updated 15-Mar-17 14:16pm

Add a Solution

Comments

j snooze 15-Mar-17 17:17pm

Are you trying to only get the vehicle make out of this, or do you need each separate object(vehicle year, model, product etc...)?

Samira Radwan 16-Mar-17 8:33am

I nedd all the object, using vehicle make as an example

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Graeme_Grant · Accepted Answer · 2017-03-15T14:16:00

The problem that you are having is that you have complex node names that don't directly translate to .Net variable names. So Dynamic object creation is failing.

Examples of this are:

vehicle-model
\"track-pack\"-gauges
3xlock-start-from-oem-remote

All are invalid names .Net names but valid Json Node names.

This does not mean that it is not possible to do, it means that you have to do it manually.

C#

using Newtonsoft.Json;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;

namespace JsonConverter2
{
    class Program
    {
        static void Main(string[] args)
        {
            string raw = File.ReadAllText("data.json");
            var data = JsonHelper.ToClass<Response>(raw);
            Debugger.Break();
        }
    }

    public class VehicleMake
    {

        [JsonProperty("id")]
        public string Id { get; set; }

        [JsonProperty("name")]
        public string Name { get; set; }

        [JsonProperty("path")]
        public string Path { get; set; }
    }

    public class VehicleYear
    {

        [JsonProperty("id")]
        public string Id { get; set; }

        [JsonProperty("name")]
        public string Name { get; set; }

        [JsonProperty("path")]
        public string Path { get; set; }
    }

    public class VehicleModel
    {

        [JsonProperty("id")]
        public string Id { get; set; }

        [JsonProperty("name")]
        public string Name { get; set; }

        [JsonProperty("path")]
        public string Path { get; set; }
    }

    public class Product
    {

        [JsonProperty("id")]
        public string Id { get; set; }

        [JsonProperty("name")]
        public string Name { get; set; }

        [JsonProperty("path")]
        public string Path { get; set; }

        [JsonProperty("description")]
        public string Description { get; set; }

        [JsonProperty("category")]
        public string Category { get; set; }
    }

    public class Coverage
    {

        [JsonProperty("t-harness-support")]
        public bool THarnessSupport { get; set; }

        [JsonProperty("\"track-pack\"-gauges")]
        public bool TrackPackGauges { get; set; }

        [JsonProperty("2-way-serial-data-communication")]
        public bool AA2WaySerialDataCommunication { get; set; }

        [JsonProperty("3xlock-start-from-oem-remote")]
        public bool AA3xlockStartFromOemRemote { get; set; }

        [JsonProperty("3xlock-start-w-aftermarket-starter")]
        public bool AA3xlockStartWAftermarketStarter { get; set; }
    }

    public class Response
    {

        [JsonProperty("vehicle-make")]
        public VehicleMake VehicleMake { get; set; }

        [JsonProperty("vehicle-year")]
        public VehicleYear VehicleYear { get; set; }

        [JsonProperty("vehicle-model")]
        public VehicleModel VehicleModel { get; set; }

        [JsonProperty("product")]
        public Product Product { get; set; }

        [JsonProperty("coverage")]
        public Coverage Coverage { get; set; }

        [JsonProperty("accessories")]
        public IList<object> Accessories { get; set; }
    }

    public static class JsonHelper
    {
        public static T ToClass<T>(string data, JsonSerializerSettings jsonSettings = null)
        {
            var response = default(T);

            if (!string.IsNullOrEmpty(data))
                response = jsonSettings == null
                    ? JsonConvert.DeserializeObject<T>(data)
                    : JsonConvert.DeserializeObject<T>(data, jsonSettings);

            return response;
        }
    }
}