Converting JSON data into dataTable

Question

I am trying to convert the nested Json array string into a dataTable. My code is working fine, creating fine Data Table. But now customer requirement changes and I am struggling to find a way out to get the below structure.

The requirement is whenever the Json Value is an array the data should come as seperate row.

Any help is really appreciated !!

Sample JSON structure:

{ "A": "A0" , "B" : { "B2" : "B2- Val", "B3" : [{"B30" : "B30 - Val1" ,"B31" : "B31 - Val1"}]}, "C": ["C0", "C1"]}

Current DataTable output structure:

Required DataTable Structure:

Original C# code:

public DataTable JsonStringToDataTable(string jsonString)
    {
        LoggingUtil.LogMessage("GetReceiver :: JsonStringToDataTable :: Enters", LogLevel.Debug);

        DataTable dt = new DataTable();
        List<string> lstColumnName = new List<string>();
        List<string> lstRowData = new List<string>();
        try
        {
            lstColumnName = ConvertJsonToList(jsonString, false);
            foreach (string AddColumnName in lstColumnName)
            {
                DataColumnCollection columns = dt.Columns;
                string colName = AddColumnName.ToLower();
                if (!columns.Contains(colName))
                 {
                     dt.Columns.Add(colName);
                 }                
            }
            lstRowData = ConvertJsonToList(jsonString, true);
            DataRow nr = dt.NewRow();
            for (int i = 0; i < lstRowData.Count; i++)
            {
                try
                {
                    string RowColumns = lstColumnName[i];
                    string RowDataString = lstRowData[i];
                    nr[RowColumns] = RowDataString;
                }
                catch (Exception ex)
                {
                    //continue;
                    throw ex;
                }
            }
            dt.Rows.Add(nr);
        }
        catch (Exception ex)
        {
            LoggingUtil.LogMessage("GetReceiver :: JsonStringToDataTable :: Error while creating datatable from JSON string :: " + ex.Message, LogLevel.Debug);
            throw ex;
        }
        finally
        {
            LoggingUtil.LogMessage("GetReceiver :: JsonStringToDataTable :: Exits", LogLevel.Debug);
        }
        return dt;
    }
    public static List<string> ConvertJsonToList(string jsonString, bool isValue)
    {
        LoggingUtil.LogMessage("GetReceiver :: ConvertJsonToList :: Enters", LogLevel.Debug);
        DataTable dt = new DataTable();
        var jObj = JObject.Parse(jsonString);
        List<string> lstData = new List<string>();

        try
        {
            if (isValue)
            {
                lstData = AddJsonObjects(jObj, "JSON", lstData, true, false);
            }
            else
            {
                lstData = AddJsonObjects(jObj, "JSON", lstData, false, false);
            }
        }
        catch (Exception ex)
        {
            LoggingUtil.LogMessage("GetReceiver :: ConvertJsonToList :: Error :: " + ex.Message, LogLevel.Debug);
            throw ex;
        }
        finally
        {
            LoggingUtil.LogMessage("GetReceiver :: ConvertJsonToList :: Exits", LogLevel.Debug);
        }
        return lstData;
    }
    public static List<string> AddJsonObjects(JObject jObj, string name, List<string> ColumnsName, bool isValue, bool isArrayObject)
    {
        foreach (var property in jObj.Properties())
        {
            string strName = name + "." + property.Name;
            if (isArrayObject && !isValue)
            {
                ColumnsName = AddTokenValues(property.Value, strName, ColumnsName, isValue, true);
            }
            else
            {
                ColumnsName = AddTokenValues(property.Value, property.Name, ColumnsName, isValue, false);
            }
        }
        return ColumnsName;
    }
    public static List<string> AddTokenValues(JToken token, string name, List<string> ColumnsName, bool isValue, bool isArrayObject)
    {
        if (token is JValue)
        {
            if (isValue)
            {
                string value = string.Empty;
                if (token.Type != JTokenType.Null)
                {
                    value = ((JValue)token).Value.ToString();
                }
                ColumnsName.Add(value);
            }
            else
            {
                ColumnsName.Add(name);
            }
        }
        else if (token is JArray)
        {
            ColumnsName = AddArrayValues((JArray)token, name, ColumnsName, isValue);
        }
        else if (token is JObject)
        {
            ColumnsName = AddJsonObjects((JObject)token, name, ColumnsName, isValue, true);
        }
        return ColumnsName;
    }
    public static List<string> AddArrayValues(JArray array, string name, List<string> dataList, bool isValue)
    {
        for (var i = 0; i < array.Count; i++)
        {
            dataList = AddTokenValues(array[i], string.Format("[{0}]", name + "[" + i.ToString() + "]"), dataList, isValue, true);
        }
        return dataList;
    }

Murray Foxcroft · Accepted Answer · 2016-04-27 13:45:32Z

Here you go - its not pretty and needs further testing and clean up (e.g. separate concerns in to classes and get rid of those globals!) but it gives you what you are after. Paste the code below in to a new console application (paste over the content of Program.cs) and add System.Web.Extensions as a reference.

Good luck!

using System;
using System.Collections.Generic;
using System.Data;
using System.Web.Script.Serialization;

namespace ConsoleApplication1
{
    class Program
    {
        private static DataTable dt;
        private static Dictionary<string, int> columnRowManager;

        static void Main(string[] args)
        {
            //var json = "[{'firstName':'John', 'lastName':'Doe'},{'firstName':'Anna', 'lastName':'Smith'},{'firstName':'Peter','lastName': 'Jones'} ]";
            //var json = "{ 'glossary': { 'title': 'example glossary','GlossDiv': { 'title': 'S','GlossList': { 'GlossEntry': { 'ID': 'SGML','SortAs': 'SGML','GlossTerm': 'Standard Generalized Markup Language','Acronym': 'SGML','Abbrev': 'ISO 8879:1986','GlossDef': { 'para': 'A meta-markup language, used to create markup languages such as DocBook.','GlossSeeAlso': ['GML', 'XML'] },'GlossSee': 'markup' } } } } }";
            var json = "{ 'A': 'A0' , 'B' : { 'B2' : 'B2 - Val', 'B3' : [{'B30' : 'B30 - Val1' ,'B31' : 'B31 - Val1'}]}, 'C': ['C0', 'C1']}";
            var jss = new JavaScriptSerializer();
            dt = new DataTable();
            columnRowManager = new Dictionary<string, int>();

            try
            {
                // Deal with an object root
                var dict = jss.Deserialize<Dictionary<string, object>>(json);
                GetColumnsAndRowsFromJsonDictionary(dict);
            }
            catch (InvalidOperationException ioX)
            {
                // Deal with an Array Root
                var dictionaries = jss.Deserialize<Dictionary<string, object>[]>(json);
                foreach (var dict in dictionaries)
                {
                    GetColumnsAndRowsFromJsonDictionary(dict);
                }
            }

            DumpTableToConsole();
        }

        private static void DumpTableToConsole()
        {
            WriteColumnsToConsole();
            WriteRowsToConsole();
            Console.ReadKey();
        }

        private static void WriteRowsToConsole()
        {

            // Write out the Rows
            foreach (DataRow row in dt.Rows)
            {
                foreach (DataColumn col in dt.Columns)
                {
                    Console.Write(row[col.ColumnName].ToString().PadRight(12) + ",");
                }
                Console.WriteLine();
            }
        }

        private static void WriteColumnsToConsole()
        {
            foreach (DataColumn col in dt.Columns)
            {
                Console.Write(col.ColumnName.PadRight(12) + ",");
            }
            Console.WriteLine();
            Console.WriteLine("-------------------------------------------------------------------------------");
        }

        private static void AddDataToTable(string column, string cellValue)
        {
            AddColumnIfNew(column);
            int targetRowPosition = DetermineTargetRow(column);
            AddRowIfRequired(targetRowPosition);
            dt.Rows[targetRowPosition - 1][column] = cellValue;
        }

        private static void AddRowIfRequired(int targetRowPosition)
        {
            if (dt.Rows.Count < targetRowPosition)
            {
                dt.Rows.Add();
            }
        }

        private static int DetermineTargetRow(string column)
        {
            int targetRowPosition;
            columnRowManager.TryGetValue(column, out targetRowPosition);
            targetRowPosition++;
            columnRowManager[column] = targetRowPosition;
            return targetRowPosition;
        }

        private static void AddColumnIfNew(string column)
        {
            if (!dt.Columns.Contains(column))
            {
                dt.Columns.Add(new DataColumn(column, typeof(String)));
                columnRowManager.Add(column, 0);
            }
        }

        private static void GetColumnsAndRowsFromJsonDictionary(Dictionary<string, object> dictionary)
        {
            // Catch the curse of recursion - null is your friend (enemy!) 
            if (dictionary == null) return;

            foreach (var kvp in dictionary)
            {
                if (kvp.Value.GetType() == typeof(Dictionary<string, object>))
                {
                    // Process an embedded dictionary (hierarchy)
                    var subDictionary = kvp.Value as Dictionary<string, object>;
                    GetColumnsAndRowsFromJsonDictionary(subDictionary);
                }
                else if (kvp.Value.GetType() == typeof(System.Collections.ArrayList))
                {
                    ProcessArrayList(kvp);
                }
                else if (kvp.Value.GetType() == typeof(String))
                {
                    AddDataToTable(kvp.Key, kvp.Value.ToString());
                }
                else
                {
                    throw new NotSupportedException(string.Format("Err2: Type '{0}' not supported", kvp.Value.GetType().ToString()));
                }
            }
        }

        private static void ProcessArrayList(KeyValuePair<string, object> kvp)
        {
            // Process each independant item in the array list 
            foreach (var arrItem in kvp.Value as System.Collections.ArrayList)
            {
                if (arrItem.GetType() == typeof(String))
                {
                    AddDataToTable(kvp.Key, arrItem.ToString());
                }
                else if (arrItem.GetType() == typeof(Dictionary<string, object>))
                {
                    var subArrDictionary = arrItem as Dictionary<string, object>;
                    GetColumnsAndRowsFromJsonDictionary(subArrDictionary);
                }
                else
                {
                    throw new NotSupportedException(string.Format("Err1: Type '{0}' not supported", arrItem.GetType().ToString()));
                }
            }
        }
    }
}

Sorry.. Caught up with another high priority work, so currently, had this conversion changes as pending. Thank you so much for the idea. Will check this out and update you ASAP..!!!

Collectives™ on Stack Overflow

Converting JSON data into 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