CSV To Json file in c#

Question

Hi,
I am trying to convert in one excel sheet data in JSON on some specific format .but i am not getting any solution to change dataset format .

My CSV data is in below format (CSV.png file)

I need data of JSON in below format only

{

"Map":{
"University": "VBU",
"Dept1":{
"Dept": "MCA",
"Data1":
{

"Name ": "Waugh, Timothy",
"Age": 10,
"Country": "UK"
},
"Data2":
{

"Name ": "Freeman, Neil",
"Age": 20,
"Country": "USA"
},
"Data3":
{

"Name ": "Andy, Robert",
"Age": 30,
"Country": "Poland"
}

},
"Dept2":{
"Dept": "BCA",
"Data1":
{

"Name ": "Timothy",
"Age": 10,
"Country": "UK"
},
"Data2":
{

"Name ": "Neil",
"Age": 21,
"Country": "USA"
}

}
}
}

I have tried to convert using Microsoft.Office.Interop.Excel.Application and Newsoft lib but i am not getting output as i expected .it is showing all data and not categories my data as per dept.

Below code sample to convert CSV to JSON

public void ExcelFileToJson(string excelpath)

    {  


        DataTable dt_excel_content= READExcel(excelpath);  

        DataSet excelData = new DataSet();   
        excelData.Tables.Add(dt_excel_content);  
        string spreadsheetjson = "";  

        spreadsheetjson= SheetTolson(excelData, dt_excel_content.TableName);   
        string fileName = dt_excel_content.TableName.Replace("", string.Empty);  
        System.IO.File.WriteAllText(fileName + ".json", spreadsheetjson);  
    }  

    public string SheetTolson(DataSet excelDataSet, string sheetName)  
    {  

        DataTable dataTable= excelDataSet.Tables[sheetName];  

        return JsonConvert.SerializeObject(dataTable);  
    }  


    public DataTable READExcel(string path)  
    {  
        Microsoft.Office.Interop.Excel.Application objXL = null;  
        Microsoft.Office.Interop.Excel.Workbook objWB = null;  
        objXL = new Microsoft.Office.Interop.Excel.Application();  
        objWB = objXL.Workbooks.Open(path);  
        Microsoft.Office.Interop.Excel.Worksheet objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets["Sheet1"];   

        int rows = objSHT.UsedRange.Rows.Count;  
        int cols = objSHT.UsedRange.Columns.Count;  
        DataTable dt = new DataTable();  
        int noofrow = 1;  

        for (int c = 1; c <= cols; c++)  
        {  
            string colname = objSHT.Cells[1, c].ToString();  
            dt.Columns.Add(colname);  
            noofrow = 2;  
        }  

        for (int r = noofrow; r <= rows; r++)  
        {  
            DataRow dr = dt.NewRow();  
            for (int c = 1; c <= cols; c++)  
            {  
                dr[c - 1] = objSHT.Cells[r, c].ToString();  
            }  

            dt.Rows.Add(dr);  
        }  

        objWB.Close();  
        objXL.Quit();  
        return dt;  
    }  

Answer 1

If this is a personal project, consider using EPPlus and if not you can't use it.

Here is a simple example.

Full source

At start of the project add the following line ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

Sample code to export a sheet

public static void ExportToJson()  
{  
    /*  
     * Points to a folder named ExcelFiles under bin\Debug in this case  
     */  
    var filePath = FileUtil.GetFileInfo(_excelBaseFolder, "Customers.xlsx").FullName;  
    FileInfo existingFile = new(filePath);  
    using ExcelPackage package = new(existingFile);  
  
    var dataTable = ExcelPackageToDataTable(package);  
      
    // uses json.net NuGet package  
    string jsonString = JsonConvert.SerializeObject(dataTable, Formatting.Indented);  
    // write to json in the bin\Debug folder  
    File.WriteAllText("Exported1.json", jsonString);  
}  

Answer 2

Here is another option which uses a free library and companion library and Json.net

public class ExcelOperations  
{  
    public static void ToJson(string excelFileName, string jsonFileName)  
    {  
        using (var stream = File.Open(excelFileName, FileMode.Open, FileAccess.Read))  
        {  
            using (var reader = ExcelReaderFactory.CreateReader(stream))  
            {  
                var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration()  
                {  
                    UseColumnDataType = true,  
                    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()  
                    {  
                        UseHeaderRow = true  
                    }  
                });  
  
                File.WriteAllText(  
                    jsonFileName,   
                    JsonConvert.SerializeObject(dataSet.Tables[0],   
                        Formatting.Indented));  
            }  
        }  
    }  
}  

Sample usage ExcelOperations.ToJson( "Orders.xlsx", "Orders.json");