Automate i18n Management with a One‑Command Node Script

Preface

Hello, I’m Nadeli. Over the past year I worked on the international ticketing business, dealing with i18n daily. I dislike repetitive, clumsy tasks, so I started thinking about optimizing our multilingual management model.

Pain Point Background

In the ticketing project we maintain two platforms (H5 and PC) using an online Excel file:

One Excel file with separate sheets for H5 and PC.

Each update requires exporting the Excel, manually running a script to generate language files, and copying them into the project.

As the project grew, several issues emerged:

Key naming chaos

Mixed capitalizations, camelCase, PascalCase.

No unified rule, making modular management difficult.

Lack of modular support

Thousands of keys make searching, modifying, and maintaining painful.

Complex update process

Manually run node scripts.

Manually copy generated files into the project.

Each iteration of multilingual file updates felt like a nightmare, especially with AI‑generated translations that required frequent corrections.

New Project, New Opportunity

While the ticketing project cannot be refactored, we started a new hotel business project and decided to build a more efficient i18n management solution from scratch.

Goals:

Standardize key rules, support modularity, separate modules with dots and content with underscores.

Automate JSON generation and integrate directly into the project.

Update with a single command, eliminating manual copying.

Script Implementation

The core logic is: Read Excel → Convert to JSON → Output to the project’s i18n directory .

Added a scripts folder and created excel-to-json.js. In package.json added the command:

{
  "scripts": {
    "i18n:excel-to-json": "node scripts/excel-to-json.js"
  }
}

Now the whole workflow can be executed with: pnpm i18n:excel-to-json This eliminates the need to locate script paths or copy files manually, boosting efficiency dramatically.

Script Details

The script reads the Excel file, maps language columns to standard language codes, clears the output directory, generates nested JSON objects based on dot‑separated keys, writes .json files, and logs missing translations.

import fs from 'node:fs'
import os from 'node:os'
import path from 'node:path'
import XLSX from 'xlsx'

// Language map: Excel header → language code
const languageMap = {
  'English': 'en',
  '简中': 'zh-CN',
  'Chinese (Traditional)': 'zh-TW',
  'Korean': 'ko',
  'Spanish': 'es',
  'German Edited': 'de',
  'Italian': 'it',
  'Norwegian': 'no',
  'French': 'fr',
  'Arabic': 'ar',
  'Thailandese': 'th',
  'Malay': 'ms'
}

function readExcel(filePath) {
  if (!fs.existsSync(filePath)) {
    throw new Error(`❌ Excel file not found: ${filePath}`)
  }
  const workbook = XLSX.readFile(filePath)
  const sheet = workbook.Sheets[workbook.SheetNames[0]]
  return XLSX.utils.sheet_to_json(sheet)
}

function clearOutputDir(dirPath) {
  if (fs.existsSync(dirPath)) {
    fs.readdirSync(dirPath).forEach(file => fs.unlinkSync(path.join(dirPath, file)))
    console.log(`🧹 Cleared directory: ${dirPath}`)
  } else {
    fs.mkdirSync(dirPath, { recursive: true })
    console.log(`📂 Created directory: ${dirPath}`)
  }
}

function generateLocales(rows, outputDir) {
  const locales = {}
  rows.forEach(row => {
    const key = row.Key
    if (!key) return
    Object.entries(languageMap).forEach(([columnName, langCode]) => {
      if (!locales[langCode]) locales[langCode] = {}
      const value = row[columnName] || ''
      const keys = key.split('.')
      let current = locales[langCode]
      keys.forEach((k, idx) => {
        if (idx === keys.length - 1) {
          current[k] = value
        } else {
          current[k] = current[k] || {}
          current = current[k]
        }
      })
    })
  })
  Object.entries(locales).forEach(([lang, data]) => {
    const filePath = path.join(outputDir, `${lang}.json`)
    fs.writeFileSync(filePath, JSON.stringify(data, null, 2), 'utf-8')
    console.log(`✅ Generated file: ${filePath}`)
  })
}

function detectMissingTranslations(rows) {
  const missing = []
  rows.forEach(row => {
    const key = row.Key
    if (!key) return
    Object.entries(languageMap).forEach(([columnName, langCode]) => {
      const value = row[columnName]
      if (!value?.trim()) {
        missing.push({ key, lang: langCode })
      }
    })
  })
  return missing
}

function logMissingTranslations(missingList) {
  if (missingList.length === 0) {
    console.log('
🎉 All keys have complete translations!')
    return
  }
  console.warn('
⚠️ Missing translations:')
  missingList.forEach(item => {
    console.warn(`  - key: "${item.key}" missing language: ${item.lang}`)
  })
}

function main() {
  const desktopPath = path.join(os.homedir(), 'Desktop', 'hotel多语言.xlsx')
  const outputDir = path.resolve('src/i18n/locales')
  const rows = readExcel(desktopPath)
  clearOutputDir(outputDir)
  generateLocales(rows, outputDir)
  logMissingTranslations(detectMissingTranslations(rows))
}

main()

Results

The new workflow simplifies operations:

Operation

Old Process

New Process

Run script

Manually locate script path pnpm i18n:excel-to-json File generation location

Manual copy after generation

Automatically output to project

Missing translation detection

None

Automatic warnings

Key naming management

No unified rule

Modular and standardized

The mechanism works well in the hotel project, receiving positive feedback from the team.

Conclusion

“Old projects are hard to overhaul, but new projects should get the architecture right from the start.”

By optimizing i18n handling we solved maintenance pain points and boosted overall development efficiency, and the solution can be smoothly migrated to the ticketing project if a refactor ever occurs.