How to convert a JSON data from web API to a DataTable?

Vikash Kumar

10y10.1kGeneral

Hi,

Here's a sample code and a json data

https://dev.twitter.com/rest/reference/get/followers/list

Please take a look at the json data in the above link

I have tried this

var postBody = "screen_name=" + Uri.EscapeDataString(screen_name);//

resource_url += "?" + postBody;

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(resource_url);

request.Headers.Add("Authorization", authHeader);

request.Method = "GET";

request.ContentType = "application/x-www-form-urlencoded";

var response = (HttpWebResponse)request.GetResponse();

var reader = new StreamReader(response.GetResponseStream());

var objText = reader.ReadToEnd();

myDiv.InnerHtml = objText;/*s*/

DataTable dt = new DataTable();

dt.Columns.Add("id", typeof(string));

dt.Columns.Add("id_str", typeof(string));

dt.Columns.Add("name", typeof(string));

dt.Columns.Add("screen_name", typeof(string));

dt.Columns.Add("location", typeof(string));

dt.Columns.Add("description", typeof(string));

dt.Columns.Add("url", typeof(string));

dt.Columns.Add("followers_count", typeof(string));

dt.Columns.Add("friends_count", typeof(string));

dt.Columns.Add("listed_count", typeof(string));

dt.Columns.Add("created_at", typeof(string));

dt.Columns.Add("favourites_count", typeof(string));

JArray jsonDat = JArray.Parse(objText);

using (var ms = new MemoryStream(Encoding.Unicode.GetBytes(jsonDat)))

foreach (var datas in jsonDat.users)

{

dt.Rows.Add(datas.users.id, datas.users.id_str, datas.users.name, datas.users.screen_name, datas.users.location, datas.users.description, datas.users.url, datas.users.followers_count, datas.users.friends_count, datas.users.listed_count, datas.users.created_at, datas.users.favourites_count);

}

Also i have created a class named users

public class users

{

[JsonProperty("id")]

public string Id { get; set; }

[JsonProperty("id_str")]

public string id_str { get; set; }

[JsonProperty("name")]

public string name { get; set; }

[JsonProperty("screen_name")]

public string screen_name { get; set; }

[JsonProperty("location")]

public string location { get; set; }

[JsonProperty("description")]

public string description { get; set; }

[JsonProperty("url")]

public string url { get; set; }

[JsonProperty("followers_count")]

public string followers_count { get; set; }

[JsonProperty("friends_count")]

public string friends_count { get; set; }

[JsonProperty("listed_count")]

public string listed_count { get; set; }

[JsonProperty("created_at")]

public string created_at { get; set; }

[JsonProperty("favourites_count")]

public string favourites_count { get; set; }

}

Need help on this

Thanks

17 Replies

Know the answer? Post it — somebody with the same question will find it here.

Salman Beg10y ago
Hi Vikash,
when you will get the jsondata from web apithen use like this,
DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));
Thanks.
Midhun Tp10y ago
Hi Vikash,
Just check below API for that. I don't know whether you are using the same -
https://dev.twitter.com/rest/reference/get/followers/list
If you want another method, just post your question as separate one in the forum so that other members will share their ideas.
Vikash Kumar10y ago
Does someone know how to get twitter follower lists. I tried this way but didn't work. Does someone have a link or library to share?
Vikash Kumar10y ago
What if i want some data to be replaced
Can i try like this?
Replace*("protected":"").Replace*("id_str":"")
These datas are in the json file i attached earlier and i don't want these.
Midhun Tp10y ago
Hi,
As per my knowledge, there is no automated way for correcting json files, you may need to correct it manually. Also looking at your json file, I don't think it is possible to convert it into an datatable as it is a very complicated nested json.

Vikash Kumar10y ago

Error: Parse error on line 3: ... [{		"id": 16320192~"id_str": "16320192 ----------------------^ Expecting 'EOF', '}', ',', ']', got 'undefined'

Now how do i go about fixing it?

Midhun Tp10y ago
Hi,

Please validate your json data by pasting it in below link. There may be some errors with json data -
https://jsonformatter.curiousconcept.com
Vikash Kumar10y ago
Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1.
```
 dt = JsonConvert.DeserializeObject(json);//Error
```
```
Sir,
```
```
Please check out my json data
```
```
It looks too complicated 
```
Midhun Tp10y ago
Hi,
It will be much easier, if you use a dll file for this - Newtonsoft.Json.dll
You can convert json to datatable with below code -
public void DerializeDataTable()
{
DataTable dt = new DataTable();
string json = @"[{""Name"":""AAA"",""Age"":""22"",""Job"":""PPP""},"
+ @"{""Name"":""BBB"",""Age"":""25"",""Job"":""QQQ""},"
+ @"{""Name"":""CCC"",""Age"":""38"",""Job"":""RRR""}]";
dt = JsonConvert.DeserializeObject(json);
}
Vikash Kumar10y ago
try
{
string[] spliting = rowData.Split(':');
int idx = 0;
string v;
if (spliting.Length > 2)
{
v = spliting[spliting.Length - 1].Replace("\"", "").Replace("~", ",");
}
else
{
idx = rowData.IndexOf(":");
v = rowData.Substring(idx + 1).Replace("\"", "").Replace("~", ",");
}
nr[lopcnt] = v;
lopcnt++;
}
catch (Exception ex) //Error:Cannot find cloumn1
{
continue;
}
}
dt.Rows.Add(nr)
Midhun Tp10y ago
Hi,
Please try below code -
if (ds.Tables.Contains(dt.TableName))
{
dt.TableName = dt.TableName + z;
}
ds.Tables.Add(dt);
Vikash Kumar10y ago
Thank you sir,
I get this error now
ds.Tables.Add(dt);//Error
DataTable already belongs to this DataSet.
Midhun Tp10y ago
Hi,
A temporary work around to overcome that error is below -
int i = 0;
foreach (string c in dtColumns)
{
if (dt.Columns.Contains(c))
{
dt.Columns.Add(c + i.ToString());
i++;
}
else
dt.Columns.Add(c);
}
Please note that the blog deals only with a simple json file and not a much complicated one.

Vikash Kumar10y ago

http://www.c-sharpcorner.com/blogs/importing-json-file-to-dataset-without-using-external-libraries

I am getting this error

foreach (string c in dtColumns)

{

dt.Columns.Add(c);//Error

}

DuplicateNameException: A column named 'http' already belongs to this DataTable

Vikash Kumar10y ago
Hi,
thank you for your help but the links you all provided doesn't work for me, breaking my head on this json data since a week and no result.
Krishna Rajput Singh10y ago
below links helpful for you....
http://www.c-sharpcorner.com/uploadfile/9bff34/3-ways-to-convert-datatable-to-json-string-in-asp-net-c-sharp/
http://www.advancesharp.com/blog/1082/convert-datatable-to-json-in-c
http://www.aspdotnet-suresh.com/2013/05/c-convert-datatable-to-json-string-in-c.html
Midhun Tp10y ago
Hi,

Please go through below links -
http://www.c-sharpcorner.com/blogs/importing-json-file-to-dataset-without-using-external-libraries
http://www.c-sharpcorner.com/blogs/convert-json-string-to-datatable-in-asp-net1

How to convert a JSON data from web API to a DataTable?

Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1.

DataTable already belongs to this DataSet.