CSV to nested JSON objects

Question

I have a CSV containing a website's text in different languages.

Example of the CSV:

Group,Key,EN,NL
Global,yes,yes,ja
Global,no,no,neen
Menu,openMenu,Open menu,Open menu
Menu,signUp,Sign up,Registreer

From the CSV I want to easily convert it to a separate JSON file for each language. For Dutch (NL), I would need JSON as such:

{
    "global": {
        "yes": "ja",
        "no": "nee"
    },
    "menu": {
        "openMenu": "Open menu",
        "signUp": "Registreer"
    }
}

I'm looking for a solution where the only manual work would be changing which language output I'd need (EN or NL in this case).

I've tried writing a custom template using a JSON converter tool, but either I get duplicate keys (the group value):

{
  "Global": {
     "yes":"yes"
    },
  "Global": {
     "no":"no"
    },
  "Menu": {
     "openMenu":"Open menu"
    },
  "Menu": {
     "signUp":"Sign up"
    }
}

Or when I used their 'CVS to keyed JSON' template, the object structure is not exactly what is required:

{
   "Global": [
      {
         "Key": "yes",
         "EN": "yes",
         "NL": "ja"
      },
      {
         "Key": "no",
         "EN": "no",
         "NL": "neen"
      }
   ],
   "Menu": [
      {
         "Key": "openMenu",
         "EN": "Open menu",
         "NL": "Open menu"
      },
      {
         "Key": "signUp",
         "EN": "Sign up",
         "NL": "Registreer"
      }
   ]
}

I then tried researching JMESPath options, but I don't want to manually input each key, as in reality the CSV will be way larger. I'm not sure if JMESPath is possible in this case; I haven't figured it out yet.

Most solutions to similar questions I've found are using Python. I have 0 experience in Python to be fair, but if that's a good solution I'd be willing to look in this further!

What would be the best way to either convert my original CSV to the JSON file I require, or further format my JSON-file from the converter tool, to the JSON format I require?

Answer 1

Here's a simple Python code to create separate JSON files for each language.

Create a file and for instance, name it csv-to-json.py:

import csv
import json


def csv_to_json(csv_file):
    data = {}
    with open(csv_file, mode='r', encoding='utf-8') as file:
        reader = csv.DictReader(file)
        for row in reader:
            group = row['Group'].lower()
            key = row['Key']
            for lang in row.keys():
                if lang not in ['Group', 'Key']:
                    if lang not in data:
                        data[lang] = {}
                    if group not in data[lang]:
                        data[lang][group] = {}
                    data[lang][group][key] = row[lang]

    for lang, lang_data in data.items():
        output_file = f"{lang}.json"
        with open(output_file, mode='w', encoding='utf-8') as f:
            json.dump(lang_data, f, indent=4, ensure_ascii=False)


if __name__ == "__main__":
    csv_file = "your-csv-file.csv"
    csv_to_json(csv_file)

You can run the code in the terminal by executing python3 csv-to-json.py Make sure to change the your-csv-file.csv with your CSV file name.