Python script to convert excel file (xlsx) into languages multiple files (JSON), use the files on i18next library - Languages: en-US, es-ES and pt-BR

main.py

import pandas as pd
import re
import json
import openpyxl
import os
import codecs

df = pd.read_excel('./translations.xlsx', sheet_name = 0)

titles = []
data = {}
j = 0

for val in df[df.columns[1]]:
  titles.append(re.sub('[^\w]+', '', val.lower().replace(" ", "_")))

for col in df.columns:
  i = 0

  if j < 3:
    for i in range(len(titles)):
      data[titles[i]] = df[col][i]
      i+=1

    colTitle = col + ".json"
    with codecs.open(colTitle, 'w', encoding='utf8') as outfile:
      json.dump(data, outfile, ensure_ascii=False, indent=2, sort_keys=True)
    data = {}
    j+=1

translations.xlsx

Your Excel file must have 4 fields, for the 3 languages and the last one for variables names used on i18next library.

Example:

PT    EN      ES    VARIABLES

Olá   Hello   Hola    hello

Mundo World   Mundo   world

It will convert the excel into 3 files:

EN.json:
{
  "hello": "Hello",
  "world": "World"
}

ES.json:
{
  "hello": "Hola",
  "world": "Mundo"
}

PT.json:
{
  "hello": "Olá",
  "world": "Mundo"
}

Hello my buddy. I used your code 😄

but I used in Linux enviroment, then I had to change something in code:

import pandas as pd
import json
import xlrd
import os
import codecs

os.system('clear')

df = pd.read_excel('./translations.xlsx', sheet_name = 0)

titles = []
data = {}

for val in df[df.columns[3]]:
  titles.append(val.lower().replace(" ", "_"))

columns = [str(k) for k in df.columns]

for col in columns:
  i = 0

  if col != 'VARIABLES':
    for i in range(len(titles)):
      data[titles[i]] = df[col][i]
      i+=1

    colTitle = col + ".json"
    with codecs.open(colTitle, 'w', encoding='utf8') as outfile:
      json.dump(data, outfile, ensure_ascii=False, indent=2, sort_keys=True)
    data = {}

It's works