Json file to Excel - with sub arrays

[Zazhir 0]

I've need to convert, using delphi, a huge *json file into Excel file. Tried to use this library, that is made specific to work with json's files, but as I'am pretty new on delphi development, my code doen'st work as espected...

The json struct is just like this: 

[
  {
    "cod_iof": 1,
    "cod_etd": null,
    "cod_tes": null,
    "owners": [
      {
        "name_own": "MAX ALTER BEST",
        "cod_id": "1444471",
        "cod_rec": "4558522"
      },
      {
        "name_own": "MESTER ALTER BEST",
        "cod_id": "1444471",
        "cod_rec": "4558522"
      }
    ],
    "address": [
      {
        "cod_end": 4444444,
        "is_main": "Y"
      },
      {
        "cod_end": 4444444,
        "is_main": "N"
      }
    ],
    "info": [
      {
        "cod": 0
      },
      {
        "cod": 1
      },
      {
        "cod": 2
      },
      {
        "cod": 3
      }
    ],
    "values": [
      {
        "cod_est": 0,
        "cod_uyr": null
      }
    ],
    "builds": [
      {
        "cod_tes": 1,
        "info": [
          {
            "cod": 0
          },
          {
            "cod": 1
          },
          {
            "cod": 2
          },
          {
            "cod": 3
          }
        ]
      }
    ],
    "fronts": [
      {
        "cod_iuy": 1,
        "iuo_oo": 0
      }
    ],
    "checks": [
      {
        "cod_io": 1,
        "cod_oue": 1
      }
    ]
  },
  {
    //new list...
  }
  //....
]

This is not the real data, is just to show how my Json file, looks like. Is worth to say, that the real data, can pass more than 170.000k of lines. Is a pretty extense. 

 

The code that I' tried to adapt to my case, is this bellow:

uses 
	XSuperObject;

procedure TfrmDadosPref.FormCreate(Sender: TObject);
var
  F : TextFile;
  Arquivo,Buffer : String;
  i, x: integer;
begin
  qryPesq.Open;

   //alteração para pesquisa - Sabino
   for i := 0 to qryPesq.Fields.Count - 1 do
   begin
      CmbConsulta.Items.Add( qryPesq.Fields[i].DisplayLabel);
   end;
end;

procedure TfrmMyForm.SpeedButton1Click(Sender: TObject);
var
  JSONData: ISuperObject;
  DataArray, ProprietariosArray, EnderecosArray, CaracteristicasArray, ValorVenalArray, EdificadasArray, TestadasArray, CobrancasArray: TSuperArray;
  Proprietario, Endereco, Caracteristica, ValorVenal, Edificada, Testada, Cobranca: ISuperObject;
  I, J: Integer;
  Y: TSuperArray;
begin
// carregue seu arquivo JSON
JSONData := TSuperObject.ParseFile('\path to data\data.json', False);

// acesse a propriedade "data" do objeto JSON
DataArray := JSONData.A['data'];

for I := 0 to DataArray.Length - 1 do
begin
  // acesse as sub-listas do objeto JSON
  ProprietariosArray := DataArray.O[I].A['owners'];
  EnderecosArray := DataArray.O[I].A['address'];
  CaracteristicasArray := DataArray.O[I].A['info'];
  ValorVenalArray := DataArray.O[I].A['values'];
  EdificadasArray := DataArray.O[I].A['builds'];
  TestadasArray := DataArray.O[I].A['fronts'];
  CobrancasArray := DataArray.O[I].A['checks'];

  for J := 0 to ProprietariosArray.Length - 1 do
  begin
    Proprietario := ProprietariosArray.O[J];
  end;

  for J := 0 to EnderecosArray.Length - 1 do
  begin
    Endereco := EnderecosArray.O[J];
  end;

  //... and then goes...
end;

//save the file next

end;

 

This is what I've made so far... but i belive there a better and easy way to do that. I just don't know how.

I'am using Delphi XE6, I don't know if that can cause some divergency.

 

Edited February 8, 2023 by Zazhir

[Geoffrey Smith 45]

Use this project to create some delphi objects that will make it easy to read the file

 

https://github.com/PKGeorgiev/Delphi-JsonToDelphiClass

[programmerdelphi2k 237]

you can try some like this:

• your JSON have 3 levels, then, you can see the values to store on table fields
• using the JSON class from Delphi
• of course, the "RECURSIVE" procedure would help here!

{$R *.dfm}

uses
  System.Generics.Collections,
  System.JSON;

procedure TForm1.Button1Click(Sender: TObject);
var
  LJSvalueLvl1: TJSONValue;
  LJSvalueLvl2: TJSONValue;
  LJSvalueLvl3: TJSONValue;
  LJSarrayLvl1: TJSONArray;
  LJSarrayLvl2: TJSONArray;
  LJSarrayLvl3: TJSONArray;
  LJSobjLvl1  : TJSONObject;
  LJSobjLvl2  : TJSONObject;
  LJSobjLvl3  : TJSONObject;
begin
  // considering your JSON with 3 levels = 3 arrays!
  // 3 levels = Master-Details tables!
  //
  // LJSobjLvlXXX.Pairs[ xxx ].JsonString = field-names
  // LJSobjLvlXXX.Pairs[ xxx ].JsonValue  = field-values
  //
  // TableXXX.FieldByName( LJSobjLvlXXX.Pairs[ xxx ].JsonString.ToString ).AsXXXXX := valueXXXX
  // valueXXXX  =  LJSobjLvlXXX.Pairs[ xxx ].JsonValue.ToString / "asType<T>" = "value-as-type-XXXX"
  //
  LJSvalueLvl1 := TJSONObject.ParseJSONValue(Memo1.Text, true, true); // if well-formed go ahead...
  //
  if LJSvalueLvl1 is TJSONArray then
    begin
      LJSarrayLvl1 := TJSONArray(LJSvalueLvl1);
      //
      for var I: integer := 0 to LJSarrayLvl1.Count - 1 do
        begin
          LJSvalueLvl1 := TJSONValue(LJSarrayLvl1[I]);
          //
          if LJSvalueLvl1 is TJSONObject then
            begin
              LJSobjLvl1 := TJSONObject(LJSvalueLvl1);
              //
              for var j: integer := 0 to (LJSobjLvl1.Count - 1) do
                begin
                  Memo2.Lines.Add('Lvl 1 = ' + LJSobjLvl1.Pairs[j].JsonString.ToString);
                  //
                  if LJSobjLvl1.Pairs[j].JsonValue is TJSONArray then
                    begin
                      LJSarrayLvl2 := TJSONArray(LJSobjLvl1.Pairs[j].JsonValue);
                      //
                      for var k: integer := 0 to (LJSarrayLvl2.Count - 1) do
                        begin
                          LJSvalueLvl2 := TJSONValue(LJSarrayLvl2[I]);
                          //
                          if LJSvalueLvl2 is TJSONObject then
                            begin
                              LJSobjLvl2 := TJSONObject(LJSvalueLvl2);
                              //
                              for var l: integer := 0 to (LJSobjLvl2.Count - 1) do
                                begin
                                  Memo2.Lines.Add('______Lvl 2 = ' + LJSobjLvl2.Pairs[l].JsonString.ToString);
                                  //
                                  if LJSobjLvl2.Pairs[l].JsonValue is TJSONArray then
                                    begin
                                      LJSarrayLvl3 := TJSONArray(LJSobjLvl2.Pairs[l].JsonValue);
                                      //
                                      for var m: integer := 0 to (LJSarrayLvl3.Count - 1) do
                                        begin
                                          LJSvalueLvl3 := TJSONValue(LJSarrayLvl3[m]);
                                          //
                                          if LJSvalueLvl3 is TJSONObject then
                                            begin
                                              LJSobjLvl3 := TJSONObject(LJSvalueLvl3);
                                              //
                                              for var n: integer := 0 to (LJSobjLvl3.Count - 1) do
                                                begin
                                                  // TJSONObject = values from Lvl3
                                                  Memo2.Lines.Add('____________Lvl 3 = ' + LJSobjLvl3.Pairs[n].JsonString.ToString + '=' + LJSobjLvl3.Pairs[n].JsonValue.ToString);
                                                end;
                                            end;
                                        end;
                                    end
                                  else // TJSONObject = values from Lvl2
                                    begin
                                      LJSobjLvl3 := TJSONObject(LJSobjLvl2.Pairs[l].JsonValue);
                                      Memo2.Lines.Add('_________Lvl 2 = value = ' + LJSobjLvl3.ToString);
                                    end;
                                end;
                            end;
                        end;
                    end
                  else // TJSONObject = values from Lvl1
                    Memo2.Lines.Add('___Lvl 1 = value = ' + LJSobjLvl1.Pairs[j].JsonValue.ToString);
                end;
            end;
        end;
    end;
end;

end.

     

Edited February 9, 2023 by programmerdelphi2k