Trying to take in JSON and duplicate tables based on result

[Sydney Brea]

Good afternoon!

I have a question regarding if it’s possible to essentially both do a mailmerge with a document (eample-mailmergeplustable.docx), duplicate a table and add rows and fill them in to a table at the same time.
 

I am taking in a json like the following. The format is the same, but there can be one or more entries that have the same states. So for example in the json, I have two states – ME and NY.

{

        "intendedSource": "Applied BDE",

        "actualSource": "Applied BDE",

        "payload": [

            {

                "state": "NY",

                "locationNumber": "1",

                "classCode": "2",

                "classification": "3",

                "renewalEstPayroll": "4",

                "renewalRate": "5",

                "annualPremium": "6"

            },

            {

                "state": "ME",

                "locationNumber": "A",

                "classCode": "B",

                "classification": "C",

                "renewalEstPayroll": "D",

                "renewalRate": "E",

                "annualPremium": "F"

            },

            {

                "state": "NY",

                "locationNumber": "100",

                "classCode": "200",

                "classification": "300",

                "renewalEstPayroll": "400",

                "renewalRate": "500",

                "annualPremium": "600"

            }

        ]

    }

I am trying to replace the values in each table.

Exposures

STATE: 
ME

Class Code	Classification	Renewal Est. Payroll	Renewal Rate	Expiring Payroll	Expiring Rate	Estimated Annual Premium
B	C	D	E	F

STATE: 
NY

Class Code	Classification	Renewal Est. Payroll	Renewal Rate	Expiring Payroll	Expiring Rate	Estimated Annual Premium
2	3	4	5	6
200	300	400	500	600

I included a zip file that has sample json (samplejson.txt), a sample of the document before (example-mailmergeplustable.docx) and the intended result (End Result.docx) after along with code that I tried to work with. If you can offer any help or suggestions, it would be greatly appreciated! Thank you in advance and Happy Holidays.

Attachment: syncfusionmailmergeplustablemodification_52e9f8f2.zip

[Sindhu Ramesh]

Hi Sydney Brea,
Based on the details provided, we understand that your requirement is to “Execute a mail merge using a given JSON file”. Your final requirement is to group states with multiple entries. To achieve this, we need to maintain a nested group. Therefore, we have modified the document and the corresponding JSON. Kindly refer to the complete sample attached.

In this sample, we have done below things:
  1. Create and open template document using WordDocument Instance.
  2. Retrieve JSON data as a list of dictionary items.
  3. Create a mail merge DataTable from the list of JSON data.
  4. Execute the NestedGroup mailmerge.
  5. Save the resultant document.

For the detailed information about NestedGroup mailmerge, kindly refer the UG
Mail merge for nested groups in C# | DocIO | Syncfusion®

Regards,
Sindhu Ramesh.

Attachment: NestedGroupMailMergewithJSON_afbf6175.zip

[Adrian Scott]

To duplicate tables dynamically in ASP.NET Core based on a JSON input, you can follow these steps:

1. Parse the JSON: Use System.Text.Json or Newtonsoft.Json to deserialize the JSON data into a C# object.

2. Define the Table Model: Create a model class representing the structure of the table.

3. Create a Method to Duplicate Tables: Use Entity Framework Core (EF Core) or raw SQL to create and populate duplicate tables.

Here’s an example:

using System.Text.Json; // or use Newtonsoft.Json

using Microsoft.EntityFrameworkCore;

using System.Linq;

public class DynamicTableService

{

    private readonly YourDbContext _context;

    public DynamicTableService(YourDbContext context)

    {

        _context = context;

    }

    public async Task DuplicateTablesFromJson(string jsonInput)

    {

        // Step 1: Parse JSON

        var data = JsonSerializer.Deserialize<List<TableData>>(jsonInput); // Adjust type if necessary

        if (data == null || !data.Any()) throw new Exception("Invalid or empty JSON input");

        foreach (var item in data)

        {

            // Step 2: Generate a new table name

            var newTableName = $"{item.BaseTableName}_Copy";

            // Step 3: Duplicate the table schema and data

            await DuplicateTableSchemaAndData(item.BaseTableName, newTableName);

        }

    }

    private async Task DuplicateTableSchemaAndData(string baseTableName, string newTableName)

    {

        var sql = $@"

            CREATE TABLE {newTableName} AS

            SELECT * FROM {baseTableName};

        ";

        await _context.Database.ExecuteSqlRawAsync(sql);

    }

}

// Example model representing table data from JSON

public class TableData

{

    public string BaseTableName { get; set; }

}

// Example usage

public class YourService

{

    private readonly DynamicTableService _dynamicTableService;

    public YourService(DynamicTableService dynamicTableService)

    {

        _dynamicTableService = dynamicTableService;

    }

    public async Task ProcessJsonAndDuplicateTables(string jsonInput)

    {

        await _dynamicTableService.DuplicateTablesFromJson(jsonInput);

    }

}

JSON Input Example

[

    { "BaseTableName": "Users" },

    { "BaseTableName": "Orders" }

]

Explanation:

1. Input Parsing:

   • The JSON input is deserialized into a list of objects representing the tables to duplicate.

2. Table Duplication:

   • For each table, a new table name is generated, and a raw SQL query duplicates the table structure and data.
   • Modify the SQL query as needed to fit your database dialect (e.g., PostgreSQL, MySQL, SQL Server).

3. Execution:

   • You call the DuplicateTablesFromJson method with the JSON input.

4. Error Handling:

   • Implement proper error handling and validation to ensure table names and input data are valid.

This example assumes you have a proper EF Core DbContext setup. Adjust table creation logic if you’re not using EF Core.

[Sindhu Ramesh]

Hi Adrian Scott,
Thank you for sharing your suggestion! Your solution is a great example of how to dynamically manage database tables based on JSON input. However, our requirement focuses on processing JSON data to populate a table in a Word document using mail merge.

For this, we recommend the solution we provided, which executes a mail merge operation to populate the table.

If you'd like to explore this functionality, feel free to check our documentation or ask for further assistance!

Regards,
Sindhu Ramesh.

[Kajal Suthar]

To duplicate tables based on JSON data:

1. Parse JSON: Convert the JSON data into a usable structure.
2. Iterate through tables: Loop over the JSON data to extract table names and columns.
3. Duplicate tables: Create new table names (e.g., by appending "_copy") and duplicate their columns.
4. Handle data: Optionally, duplicate the data within each table by replicating rows.

Example in Python:

import json

# Sample JSON

json_data = '{"tables": [{"table_name": "users", "columns": ["id", "name"]}, {"table_name": "products", "columns": ["product_id", "name"]}]}'

data = json.loads(json_data)

# Duplicate tables

new_tables = [{"table_name": f"{table['table_name']}_copy", "columns": table["columns"]} for table in data["tables"]]

# Output

print(new_tables)

[Sindhu Ramesh]

Hi Kajal Suthar,
Thank you for sharing your suggestion! Your solution is an example of how to convert JSON to a table and prints in console. However, our requirement focuses on processing JSON data to populate a table in a Word document using mail merge.

For this, we recommend the solution update we provided, which executes a mail merge operation to populate the table using Syncfusion Word library.

If you'd like to explore this functionality, feel free to check our documentation or ask for further assistance!

Regards,
Sindhu Ramesh.