TechQA.

How to combine different DataSet into single JSON Object in C#

677 views Asked by At

I have an excel with couple of sheets. From them in the two sheets, there are 100's of rows available. I am trying to read the file and convert the two tables of data into JSON. I am using the ExcelDataReader NuGet package to get data from the Excel file. I am successfully able to access the data, let's say there are 10 sheets, and out of those 10, I am getting data from two similar sheets. The expectation is to get the two DataSet and convert them into single JSON Object.

Sample Data from Sheet Downhole Logging Data:

[
  {
    "Column0": null,
    "Hole Size (Enter Hole Size)": "No",
    "Hole Size (Enter Hole Size)_1": "Yes",
    "Hole Size (Enter Hole Size)_2": "Yes",
    "Hole Size (Enter Hole Size)_3": "Yes",
    "Hole Size (Enter Hole Size)_4": "Yes",
    "Hole Size (Enter Hole Size)_5": "No",
    "Hole Size (Enter Hole Size)_6": "No",
    "Hole Size (Enter Hole Size)_7": "No",
    "Template Trace Label\n(PDT Label)": "ABG Comb Gamma Ray BC RT",
    "Tool": "ABG",
    "InSite Record": "ABG RT",
    "Insite Variable Curve Label": "Gamma Ray KclBh",
    "InSite Mnemonic\nUse for Halvue Header": "BGCGC-T",
    "Wellbase Depth Descriptor": "Desc Run",
    "Wellbase Time Descriptor": "Desc Run",
    "Wellbase Depth Descriptor Name": "MWD-T",
    "Wellbase Time Descriptor Name": "MWD-T",
    "Time": "X",
    "Depth": "X",
    "IA": "X",
    "WITSML": "X",
    "Default Units": "API",
    "Min": 0.0,
    "Max": 150.0,
    "Trace Notes": null,
    "Data frequency": 1.21,
    "No": null,
    "No_1": null,
    "No_2": null,
    "No_3": null,
    "No_4": null,
    "No_5": null,
    "No_6": null,
    "No_7": null,
    "ROP Avg": null,
    "Surface Data": null,
    "ROP Avg_1": null,
    "ROPAVG": null,
    "Desc Run": null,
    "Desc Run_1": null,
    "0 | Desc Run Depth | Logging | Logging | ROP Avg |  |": null,
    "0 | Desc Run Time | SDL | ROP | ROP Avg |  |": null,
    "X": null,
    "X_1": null,
    "X_2": null,
    "X_3": null,
    "m/h": null,
    "Column22": null,
    "Column23": null,
    "Column24": null,
    "5.34": null
  },
  {
    "Column0": null,
    "Hole Size (Enter Hole Size)": "No",
    "Hole Size (Enter Hole Size)_1": "No",
    "Hole Size (Enter Hole Size)_2": "No",
    "Hole Size (Enter Hole Size)_3": "No",
    "Hole Size (Enter Hole Size)_4": "No",
    "Hole Size (Enter Hole Size)_5": "No",
    "Hole Size (Enter Hole Size)_6": "No",
    "Hole Size (Enter Hole Size)_7": "No",
    "Template Trace Label\n(PDT Label)": "Gamma Ray Image RT",
    "Tool": "ABG",
    "InSite Record": "ABG RT",
    "Insite Variable Curve Label": "ABG Comb Gamma Azi HS BC RT",
    "InSite Mnemonic\nUse for Halvue Header": "BGCGHC-T",
    "Wellbase Depth Descriptor": "Desc Run",
    "Wellbase Time Descriptor": "Desc Run",
    "Wellbase Depth Descriptor Name": "MWD-T",
    "Wellbase Time Descriptor Name": "MWD-T",
    "Time": "X",
    "Depth": "X",
    "IA": "X",
    "WITSML": "X",
    "Default Units": "API",
    "Min": 0.0,
    "Max": 150.0,
    "Trace Notes": null,
    "Data frequency": 2.45,
    "No": null,
    "No_1": null,
    "No_2": null,
    "No_3": null,
    "No_4": null,
    "No_5": null,
    "No_6": null,
    "No_7": null,
    "ROP Avg": null,
    "Surface Data": null,
    "ROP Avg_1": null,
    "ROPAVG": null,
    "Desc Run": null,
    "Desc Run_1": null,
    "0 | Desc Run Depth | Logging | Logging | ROP Avg |  |": null,
    "0 | Desc Run Time | SDL | ROP | ROP Avg |  |": null,
    "X": null,
    "X_1": null,
    "X_2": null,
    "X_3": null,
    "m/h": null,
    "Column22": null,
    "Column23": null,
    "Column24": null,
    "5.34": null
  }
]

Sample Data from Sheet Surface Logging Data:

[
  {
    "Column0": null,
    "Hole Size (Enter Hole Size)": null,
    "Hole Size (Enter Hole Size)_1": null,
    "Hole Size (Enter Hole Size)_2": null,
    "Hole Size (Enter Hole Size)_3": null,
    "Hole Size (Enter Hole Size)_4": null,
    "Hole Size (Enter Hole Size)_5": null,
    "Hole Size (Enter Hole Size)_6": null,
    "Hole Size (Enter Hole Size)_7": null,
    "Template Trace Label\n(PDT Label)": null,
    "Tool": null,
    "InSite Record": null,
    "Insite Variable Curve Label": null,
    "InSite Mnemonic\nUse for Halvue Header": null,
    "Wellbase Depth Descriptor": null,
    "Wellbase Time Descriptor": null,
    "Wellbase Depth Descriptor Name": null,
    "Wellbase Time Descriptor Name": null,
    "Time": null,
    "Depth": null,
    "IA": null,
    "WITSML": null,
    "Default Units": null,
    "Min": null,
    "Max": null,
    "Trace Notes": null,
    "Data frequency": null,
    "No": "No",
    "No_1": "No",
    "No_2": "No",
    "No_3": "No",
    "No_4": "No",
    "No_5": "No",
    "No_6": "No",
    "No_7": "No",
    "ROP Avg": "Fill in Label",
    "Surface Data": "Type of Data ",
    "ROP Avg_1": "Fill in Label",
    "ROPAVG": "Fill in Record",
    "Desc Run": "Fill in Variable",
    "Desc Run_1": "Mnemonic",
    "0 | Desc Run Depth | Logging | Logging | ROP Avg |  |": "Fill in Descriptor",
    "0 | Desc Run Time | SDL | ROP | ROP Avg |  |": "Fill in Name",
    "X": "-",
    "X_1": "-",
    "X_2": "-",
    "X_3": "-",
    "m/h": "-",
    "Column22": "-",
    "Column23": "-",
    "Column24": "-",
    "5.34": null
  },
  {
    "Column0": null,
    "Hole Size (Enter Hole Size)": null,
    "Hole Size (Enter Hole Size)_1": null,
    "Hole Size (Enter Hole Size)_2": null,
    "Hole Size (Enter Hole Size)_3": null,
    "Hole Size (Enter Hole Size)_4": null,
    "Hole Size (Enter Hole Size)_5": null,
    "Hole Size (Enter Hole Size)_6": null,
    "Hole Size (Enter Hole Size)_7": null,
    "Template Trace Label\n(PDT Label)": null,
    "Tool": null,
    "InSite Record": null,
    "Insite Variable Curve Label": null,
    "InSite Mnemonic\nUse for Halvue Header": null,
    "Wellbase Depth Descriptor": null,
    "Wellbase Time Descriptor": null,
    "Wellbase Depth Descriptor Name": null,
    "Wellbase Time Descriptor Name": null,
    "Time": null,
    "Depth": null,
    "IA": null,
    "WITSML": null,
    "Default Units": null,
    "Min": null,
    "Max": null,
    "Trace Notes": null,
    "Data frequency": null,
    "No": "No",
    "No_1": "No",
    "No_2": "No",
    "No_3": "No",
    "No_4": "No",
    "No_5": "No",
    "No_6": "No",
    "No_7": "No",
    "ROP Avg": "Fill in Label",
    "Surface Data": "Type of Data ",
    "ROP Avg_1": "Fill in Label",
    "ROPAVG": "Fill in Record",
    "Desc Run": "Fill in Variable",
    "Desc Run_1": "Mnemonic",
    "0 | Desc Run Depth | Logging | Logging | ROP Avg |  |": "Fill in Descriptor",
    "0 | Desc Run Time | SDL | ROP | ROP Avg |  |": "Fill in Name",
    "X": "-",
    "X_1": "-",
    "X_2": "-",
    "X_3": "-",
    "m/h": "-",
    "Column22": "-",
    "Column23": "-",
    "Column24": "-",
    "5.34": null
  }
]

I'm using the below code to get the DataSets and merge them into a single DataSet. After that I convert that into Json.

private DataTable GetDownholeLoggingData(IFormFile file)
        {

            DataTable wellAndContact;

            using (var stream = file.OpenReadStream())
            {
                using (var memstrm = new MemoryStream())
                {
                    System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
                    stream.CopyTo(memstrm);

                    var reader = ExcelReaderFactory.CreateReader(stream);
                    var workSheet = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                        {
                            UseHeaderRow = true,
                            ReadHeaderRow = (rowReader) => {
                                for(int i=0; i<9; i++)
                                {
                                    rowReader.Read();
                                }
                            },
                          
                        }
                    });

                    wellAndContact = workSheet.Tables[2];
                    wellAndContact.TableName = workSheet.Tables[2].TableName;
                    var newV = workSheet.Tables[3];
                    newV.TableName = workSheet.Tables[3].TableName;
                    wellAndContact.Merge(workSheet.Tables[3]);
                }
            }

            return wellAndContact;
                    
        }

But my expectation is to get the two sheet of data combined as different Json Class in a single Object, like Below:

{
    "Downhole Logging Data": [
        {
          "Column0": null,
          "Hole Size (Enter Hole Size)": "No",
          "Hole Size (Enter Hole Size)_1": "Yes",
          "Hole Size (Enter Hole Size)_2": "Yes",
          "Hole Size (Enter Hole Size)_3": "Yes",
          "Hole Size (Enter Hole Size)_4": "Yes",
          "Hole Size (Enter Hole Size)_5": "No",
          "Hole Size (Enter Hole Size)_6": "No",
          "Hole Size (Enter Hole Size)_7": "No",
          "Template Trace Label\n(PDT Label)": "ABG Comb Gamma Ray BC RT",
          "Tool": "ABG",
          "InSite Record": "ABG RT",
          "Insite Variable Curve Label": "Gamma Ray KclBh",
          "InSite Mnemonic\nUse for Halvue Header": "BGCGC-T",
          "Wellbase Depth Descriptor": "Desc Run",
          "Wellbase Time Descriptor": "Desc Run",
          "Wellbase Depth Descriptor Name": "MWD-T",
          "Wellbase Time Descriptor Name": "MWD-T",
          "Time": "X",
          "Depth": "X",
          "IA": "X",
          "WITSML": "X",
          "Default Units": "API",
          "Min": 0.0,
          "Max": 150.0,
          "Trace Notes": null,
          "Data frequency": 1.21
        },
        {
          "Column0": null,
          "Hole Size (Enter Hole Size)": "No",
          "Hole Size (Enter Hole Size)_1": "No",
          "Hole Size (Enter Hole Size)_2": "No",
          "Hole Size (Enter Hole Size)_3": "No",
          "Hole Size (Enter Hole Size)_4": "No",
          "Hole Size (Enter Hole Size)_5": "No",
          "Hole Size (Enter Hole Size)_6": "No",
          "Hole Size (Enter Hole Size)_7": "No",
          "Template Trace Label\n(PDT Label)": "Gamma Ray Image RT",
          "Tool": "ABG",
          "InSite Record": "ABG RT",
          "Insite Variable Curve Label": "ABG Comb Gamma Azi HS BC RT",
          "InSite Mnemonic\nUse for Halvue Header": "BGCGHC-T",
          "Wellbase Depth Descriptor": "Desc Run",
          "Wellbase Time Descriptor": "Desc Run",
          "Wellbase Depth Descriptor Name": "MWD-T",
          "Wellbase Time Descriptor Name": "MWD-T",
          "Time": "X",
          "Depth": "X",
          "IA": "X",
          "WITSML": "X",
          "Default Units": "API",
          "Min": 0.0,
          "Max": 150.0,
          "Trace Notes": null,
          "Data frequency": 2.45
        }
    ],
    "Surface Logging Data": [
        {
    "Column0": null,
    "Hole Size (Enter Hole Size)": "No",
    "Hole Size (Enter Hole Size)_1": "No",
    "Hole Size (Enter Hole Size)_2": "No",
    "Hole Size (Enter Hole Size)_3": "No",
    "Hole Size (Enter Hole Size)_4": "No",
    "Hole Size (Enter Hole Size)_5": "No",
    "Hole Size (Enter Hole Size)_6": "No",
    "Hole Size (Enter Hole Size)_7": "No",
    "Template Trace Label\n(PDT Label)": "XBAT Comp Slowness 1 RT",
    "Tool": "XBAT",
    "InSite Record": "XBAT RT",
    "Insite Variable Curve Label": "DTC1",
    "InSite Mnemonic\nUse for Halvue Header": "XBC1-T",
    "Wellbase Depth Descriptor": "Desc Run",
    "Wellbase Time Descriptor": "Desc Run",
    "Wellbase Depth Descriptor Name": "MWD-T",
    "Wellbase Time Descriptor Name": "MWD-T",
    "Time": "X",
    "Depth": "X",
    "IA": "X",
    "WITSML": "X",
    "Default Units": "-",
    "Min": "-",
    "Max": "-",
    "Trace Notes": "-",
    "Data frequency": 753.89,
    "No": null,
    "No_1": null,
    "No_2": null,
    "No_3": null,
    "No_4": null,
    "No_5": null,
    "No_6": null,
    "No_7": null,
    "ROP Avg": null,
    "Surface Data": null,
    "ROP Avg_1": null,
    "ROPAVG": null,
    "Desc Run": null,
    "Desc Run_1": null,
    "0 | Desc Run Depth | Logging | Logging | ROP Avg |  |": null,
    "0 | Desc Run Time | SDL | ROP | ROP Avg |  |": null,
    "X": null,
    "X_1": null,
    "X_2": null,
    "X_3": null,
    "m/h": null,
    "Column22": null,
    "Column23": null,
    "Column24": null,
    "5.34": null
  },
  {
    "Column0": null,
    "Hole Size (Enter Hole Size)": "No",
    "Hole Size (Enter Hole Size)_1": "No",
    "Hole Size (Enter Hole Size)_2": "No",
    "Hole Size (Enter Hole Size)_3": "No",
    "Hole Size (Enter Hole Size)_4": "No",
    "Hole Size (Enter Hole Size)_5": "No",
    "Hole Size (Enter Hole Size)_6": "No",
    "Hole Size (Enter Hole Size)_7": "No",
    "Template Trace Label\n(PDT Label)": "XBAT Comp Slowness 2 RT",
    "Tool": "XBAT",
    "InSite Record": "XBAT RT",
    "Insite Variable Curve Label": "DTC2",
    "InSite Mnemonic\nUse for Halvue Header": "XBC2-T",
    "Wellbase Depth Descriptor": "Desc Run",
    "Wellbase Time Descriptor": "Desc Run",
    "Wellbase Depth Descriptor Name": "MWD-T",
    "Wellbase Time Descriptor Name": "MWD-T",
    "Time": "X",
    "Depth": "X",
    "IA": "X",
    "WITSML": "X",
    "Default Units": "-",
    "Min": "-",
    "Max": "-",
    "Trace Notes": "-",
    "Data frequency": 755.13,
    "No": null,
    "No_1": null,
    "No_2": null,
    "No_3": null,
    "No_4": null,
    "No_5": null,
    "No_6": null,
    "No_7": null,
    "ROP Avg": null,
    "Surface Data": null,
    "ROP Avg_1": null,
    "ROPAVG": null,
    "Desc Run": null,
    "Desc Run_1": null,
    "0 | Desc Run Depth | Logging | Logging | ROP Avg |  |": null,
    "0 | Desc Run Time | SDL | ROP | ROP Avg |  |": null,
    "X": null,
    "X_1": null,
    "X_2": null,
    "X_3": null,
    "m/h": null,
    "Column22": null,
    "Column23": null,
    "Column24": null,
    "5.34": null
  }
]
}

Can anyone help on how to achieve the above result. Thanks in advance.

c# arrays json asp.net-core exceldatareader
Original Q&A
1

There are 1 answers

0
Luuk On

I did not read your code, because I did not recognize this code as creating a json (sorry, my fault!)

With the code, as shown below, this output is created:

json1: {"Column0":null,"MyProperty1":"one","MyProperty2":"two"}
json2: {"Column0":null,"MyProperty1":"11","MyProperty2":"22"}
jsonTotal1: [{"Column0":null,"MyProperty1":"one","MyProperty2":"two"},{"Column0":null,"MyProperty1":"11","MyProperty2":"22"}]
jsonTotal2: {"sheet1":{"Column0":null,"MyProperty1":"one","MyProperty2":"two"},"sheet2":{"Column0":null,"MyProperty1":"11","MyProperty2":"22"}}

With this code jsonTotal1 look about the same as you final JSON, and jsonTotal2 has named sheets.

NOTE: I know my data is not the same as your data! (also structure is different, because I left out the rows that you have in a sheet)

Code:

Program.cs

using Newtonsoft.Json;

ConsoleApp21.Sheet sheet1 = new ConsoleApp21.Sheet()
{
    Column0 = null,
    MyProperty1 = "one",
    MyProperty2 = "two"
};

ConsoleApp21.Sheet sheet2 = new ConsoleApp21.Sheet()
{
    Column0 = null,
    MyProperty1 = "11",
    MyProperty2 = "22"
};

string json1 = JsonConvert.SerializeObject(sheet1);
string json2 = JsonConvert.SerializeObject(sheet2);

// This will create the same as yours:
string jsonTotal = JsonConvert.SerializeObject(new object[] { sheet1, sheet2 });

Console.WriteLine($"json1: {json1}");
Console.WriteLine($"json2: {json2}");
Console.WriteLine($"jsonTotal1: {jsonTotal}");

ConsoleApp21.workbook wb = new ConsoleApp21.workbook();
wb.sheet1 = sheet1;
wb.sheet2 = sheet2;

// Now the sheets have a name:
jsonTotal = JsonConvert.SerializeObject(wb);
Console.WriteLine($"jsonTotal2: {jsonTotal}");

Sheet.cs

namespace ConsoleApp21
{
    public class Sheet
    {
        public int? Column0 { get; set; }
        public string MyProperty1 { get; set; }
        public string MyProperty2 { get; set; }

    }

    public class workbook
    {
        public Sheet sheet1 { get; set; }
        public Sheet sheet2 { get; set; }
    }
}

Related Questions in C#

Related Questions in ARRAYS

Related Questions in JSON

Related Questions in ASP.NET-CORE

Related Questions in EXCELDATAREADER

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Trending Questions