Click here to Skip to main content
15,038,345 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
i have json file and i have to convert that json file into csv and store it some where,
and json has nested attributes.
{"AlertID":20515,"AlertIDSpecified":true,"AlertSentTimeStamp":"\/Date(-2208992400000)\/","AlertSentTimeStampSpecified":true,"AlertedUsers":[],"AttributeDetails":{"Abbreviation":"Status","DataType":"string","DefaultOperator":"=","DefaultStatus":4,"DefaultStatusSpecified":true,"DisplayName":"Status","EffectiveStatus":4,"EffectiveStatusSpecified":true,"HelpText":"Indicates the status of the check execution","ID":10400,"IDSpecified":true,"IsQualifier}}
like the above one.
how can i convert that json file into csv?

C#
string text = System.IO.File.ReadAllText("input.txt");
            XmlNode xml = JsonConvert.DeserializeXmlNode("{records:{record:" + text + "}}"); 
            XmlDocument xmldoc = new XmlDocument();
            //Create XmlDoc Object
            xmldoc.LoadXml(xml.InnerXml);
            //Create XML Steam 
            var xmlReader = new XmlNodeReader(xmldoc);
            DataSet dataSet = new DataSet();
            //Load Dataset with Xml
            dataSet.ReadXml(xmlReader);
            //return single table inside of dataset
            var csv = dataSet.Tables[0].getCSV(",");
        }
        public static string getCSV(this DataTable table,string delimitor)
      {
            var result = new StringBuilder();
         for (int i = 0; i < table.Columns.Count; i++)
         {
            result.Append(table.Columns[i].ColumnName);
             result.Append(i == table.Columns.Count - 1 ? "\n" : delimitor);
            }
        foreach (DataRow row in table.Rows)
        {
    for (int i = 0; i < table.Columns.Count; i++)
         {
            result.Append(row[i].ToString());
        result.Append(i == table.Columns.Count - 1 ? "\n" : delimitor);
        }
        }
        return result.ToString().TrimEnd(new char[] { '\r', '\n' });
        //return result.ToString();
      }

i got this code some where but it is not working.
can any one help me out in doing this?

Thanks in Advance
Suresh gaddam
Posted
Comments
Ralf Meier 15-Jul-15 0:17am
   
How should your CSV-File (the structure of the content) look like ?
F-ES Sitecore 9-Aug-15 10:03am
   
CSV files are flat but your data is hierarchical, so at a basic level you can't convert that data to CSV. You'll have to think about what the resulting CSV file is going to look like then go from there.

1 solution

I have tried something like this , Please alter according to your usage:
C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Newtonsoft.Json;
using System.IO;


namespace WindowsFormsApplication1
{
    public class AlteredUsers
    {
        public int UserID;
        public string userName;
    }
    public class AttributeDetails
    {
        public string Abbreviation;
        public string DataType;
        public string DefaultOperator;
        public int DefaultStatus;
        public bool DefaultStatusSpecified;
        public string DisplayName;
        public int EffectiveStatus;
        public bool EffectiveStatusSpecified;
        public string HelpText;
        public int ID;
        public bool IDSpecified;
        public bool IsQualifier;

    }
    public class MyJSONClass
    {
        public int AlertID;
        public bool AlertIDSpecified;
        public string AlertSentTimeStamp;
        public bool AlertSentTimeStampSpecified;
        public List<AlteredUsers> Altereduser;
        public AttributeDetails Attributedetails;
        
    }
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnConvertToCsv_Click(object sender, EventArgs e)
        {
            MyJSONClass objMyJSONClass = new MyJSONClass();
            objMyJSONClass.Altereduser = new List<AlteredUsers>();
            objMyJSONClass = ConvertJSONToObject(textBox1.Text);
            if (null != objMyJSONClass)
            {
                SaveFileDialog saveFileDialog1 = new SaveFileDialog();
                saveFileDialog1.Filter = "CSV File|*.csv";
                saveFileDialog1.Title = "Save CSV File";
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    SaveToCSV(saveFileDialog1.FileName, objMyJSONClass);
                }
            }
        }

        private void SaveToCSV(string csvfileName, MyJSONClass objMyJSONClass)
        {
            string csvString = objMyJSONClass.AlertID+","+
                objMyJSONClass.AlertIDSpecified+","+
                objMyJSONClass.AlertSentTimeStamp+","+
                objMyJSONClass.AlertSentTimeStampSpecified;

            if (null != objMyJSONClass.Altereduser)
            {
                foreach (AlteredUsers user in objMyJSONClass.Altereduser)
                {
                    csvString = "," + user.UserID.ToString() + "," + user.userName;
                }
            }

            csvString = csvString + "," + objMyJSONClass.Attributedetails.Abbreviation + "," +
                        objMyJSONClass.Attributedetails.DataType + "," +
                        objMyJSONClass.Attributedetails.DefaultOperator + "," +
                        objMyJSONClass.Attributedetails.DefaultStatus.ToString() + "," +
                        objMyJSONClass.Attributedetails.DefaultStatusSpecified.ToString() + "," +
                        objMyJSONClass.Attributedetails.DisplayName + "," +
                        objMyJSONClass.Attributedetails.EffectiveStatus.ToString() + "," +
                        objMyJSONClass.Attributedetails.EffectiveStatusSpecified.ToString() + "," +
                        objMyJSONClass.Attributedetails.HelpText + "," +
                        objMyJSONClass.Attributedetails.ID.ToString() + "," +
                        objMyJSONClass.Attributedetails.IDSpecified.ToString() + "," +
                        objMyJSONClass.Attributedetails.IsQualifier.ToString();


            File.WriteAllText(csvfileName, csvString);
        }

        private MyJSONClass ConvertJSONToObject(string InputJSONString)
        {
            MyJSONClass objMyJSONClass = new MyJSONClass();
            objMyJSONClass.Altereduser = new List<AlteredUsers>();

            try
            {
                objMyJSONClass = (MyJSONClass)JsonConvert.DeserializeObject(InputJSONString, objMyJSONClass.GetType());

                return objMyJSONClass;
            }
            catch (Exception ex)
            {

                return null;
            }
            
        }
    }
}


please correct your JSON Like this :


{
"AlertID":20515,
"AlertIDSpecified":true,
"AlertSentTimeStamp":"Date(-2208992400000)",
"AlertSentTimeStampSpecified":true,
"AlertedUsers":[],
"AttributeDetails":
{
"Abbreviation":"Status",
"DataType":"string",
"DefaultOperator":"=",
"DefaultStatus":4,
"DefaultStatusSpecified":true,
"DisplayName":"Status",
"EffectiveStatus":4,
"EffectiveStatusSpecified":true,
"HelpText":"Indicates the status of the check execution",
"ID":10400,
"IDSpecified":true,
"IsQualifier":true
}
}
   
v3

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900