Extracting Nested JSON data to SQL Server 2014

Question

I have thousands of JSON files and I need those files to import to SQL Server tables. We are using SQL Server 2014. I have given the sample data from the json file. It would be great if someone can help me in flattening the data into csv or xlsx.

The challenge here is, I need to parse the JSON data into tables with column name and values. It has to loop through n number of times , if the file has n number of nodes

Sample Jason Data:

{
    "RRC-TAPE-RECORD-ID": "01",
    "WB-API-CNTY": "003",
    "WB-API-UNIQUE": "39808",
    "WB-NXT-AVAIL-SUFFIX": "0",
    "WB-NXT-AVAIL-HOLE-CHGE-NBR": "0",
    "WB-FIELD-DISTRICT": "0",
    "WB-RES-CNTY-CODE": "3",
    "WB-ORIG-COMPL-CC": "",
    "WB-ORIG-COMPL-CENT": "0",
    "WB-ORIG-COMPL-YY": "0",
    "WB-ORIG-COMPL-MM": "0",
    "WB-ORIG-COMPL-DD": "0",
    "WB-TOTAL-DEPTH": "0",
    "WB-VALID-FLUID-LEVEL": "0",
    "WB-CERT-REVOKED-CC": "0",
    "WB-CERT-REVOKED-YY": "0",
    "WB-CERT-REVOKED-MM": "0",
    "WB-CERT-REVOKED-DD": "0",
    "WB-CERTIFICATION-DENIAL-CC": "0",
    "WB-CERTIFICATION-DENIAL-YY": "0",
    "WB-CERTIFICATION-DENIAL-MM": "0",
    "WB-CERTIFICATION-DENIAL-DD": "0",
    "WB-DENIAL-REASON-FLAG": "",
    "WB-ERROR-API-ASSIGN-CODE": "",
    "WB-REFER-CORRECT-API-NBR": "0",
    "WB-DUMMY-API-NUMBER": "339808",
    "WB-DATE-DUMMY-REPLACED": "0",
    "WB-NEWEST-DRL-PMT-NBR": "613876",
    "WB-CANCEL-EXPIRE-CODE": "",
    "WB-EXCEPT-13-A": "N",
    "WB-FRESH-WATER-FLAG": "N",
    "WB-PLUG-FLAG": "N",
    "WB-PREVIOUS-API-NBR": "0",
    "WB-COMPLETION-DATA-IND": "N",
    "WB-HIST-DATE-SOURCE-FLAG": "0",
    "WB-EX14B2-COUNT": "0",
    "WB-DESIGNATION-HB-1975-FLAG": "0",
    "WB-DESIGNATION-EFFEC-CC": "0",
    "WB-DESIGNATION-EFFEC-YY": "0",
    "WB-DESIGNATION-EFFEC-MM": "0",
    "WB-DESIGNATION-REVISED-CC": "0",
    "WB-DESIGNATION-REVISED-YY": "0",
    "WB-DESIGNATION-REVISED-MM": "0",
    "WB-DESIGNATION-LETTER-CC": "0",
    "WB-DESIGNATION-LETTER-YY": "0",
    "WB-DESIGNATION-LETTER-MM": "0",
    "WB-DESIGNATION-LETTER-DD": "0",
    "WB-CERTIFICATION-EFFEC-CC": "0",
    "WB-CERTIFICATION-EFFEC-YY": "0",
    "WB-CERTIFICATION-EFFEC-MM": "0",
    "WB-WATER-LAND-CODE": "L",
    "WB-TOTAL-BONDED-DEPTH": "0",
    "WB-OVERRIDE-EST-PLUG-COST": "0",
    "WB-SHUT-IN-DATE": "0",
    "WB-SHUT-IN-YEAR": "0",
    "WB-SHUT-IN-MONTH": "0",
    "WB-OVERRIDE-BONDED-DEPTH": "0",
    "WB-SUBJ-TO-14B2-FLAG": "N",
    "WB-PEND-REMOVAL-14B2-FLAG": "N",
    "WB-ORPHAN-WELL-HOLD-FLAG": "0",
    "RRC-TAPE-FILLER": ""
}

Thanks a lot in advance

Answer 1

Quite simple:

from csv import DictWriter
import json

# Let's assume 'data.json' contains a list of the item you gave as an example
with open('data.json', 'r') as fp:
    data = json.load(fp)

if data:
    with open('some_file.csv', 'w') as fp:
        writer = DictWriter(fp, fieldnames=list(data[0].keys()))
        writer.writeheader()
        for d in data:
            writer.writerow(d)

Answer 2

I found this question very interesting and experimented it with my C# skills. Hope it will benefit you in python as well.

using System;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using System.IO;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Microsoft.Office.Interop.Excel;

    private void cmdJSONDataView_Click(object sender, EventArgs e)
    {
        // 
        String o1 = (File.ReadAllText(@"C:\Temp\data.json"));
        JObject JObj = JsonConvert.DeserializeObject<JObject>(o1);

        DataTable DT = new DataTable();
        DT.TableName = "DataT";
// Add a blank Row
        DT.Rows.Add();
// Add Columns and Values into DataTable
        foreach (var Item in JObj)
        {
            //Console.WriteLine(item.Key + "  " + item.Value);
            DT.Columns.Add(Item.Key);
            DT.Rows[0][Item.Key.ToString()] = Item.Value;
        } // End foreach

        // Display data in a C# WindowsFormsApplication dataGridView if needed else ignore this line.
        dataGridView2.DataSource = DT;

        // Create a xml file if needed else ignore this line.
        DT.WriteXml(@"c:\Temp\DataTable.xml", true);

        // https://stackoverflow.com/questions/17649886/c-sharp-xml-to-xlsx-how by Gun, July 2013
        // Create an excel object
        Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
        // Create a workbook object to "create a new"/"overwrite an existing" .xlsx file
        var wb = objExcel.Workbooks.Add();
        wb.SaveAs(@"C:\Temp\DataTable.xlsx"); // Note: It prompts if a file already exists
        wb.Close();

        string str = @"C:\Temp\DataTable.xlsx";
        // Create a workbook object
        Microsoft.Office.Interop.Excel.Workbook objWorkbook = objExcel.Workbooks.Open(Filename: str);
        // Create a worksheet object
        Microsoft.Office.Interop.Excel.Worksheet objWorksheet = objWorkbook.ActiveSheet;
        // Column Headings
        int iColumn = 0;
        foreach (DataColumn c in DT.Columns)
        {
            iColumn++;
            objExcel.Cells[1, iColumn] = c.ColumnName;
        } // End foreach

        // Row Data
        int iRow = objWorksheet.UsedRange.Rows.Count - 1;

        foreach (DataRow dr in DT.Rows)
        {
            iRow++;

            // Row's Cell Data
            iColumn = 0;
            foreach (DataColumn c in DT.Columns)
            {
                iColumn++;
                objExcel.Cells[iRow + 1, iColumn] = dr[c.ColumnName];
            } // End foreach
        } // End foreach

        objWorksheet.Activate();
        //Save the workbook
        objWorkbook.Save();
        //Close the Workbook
        objWorkbook.Close();
        // Finally Quit the Application
        //((Microsoft.Office.Interop.Excel._Application)objExcel).Quit();
        objExcel.Quit();
    }