json - 使用 BASH 将 CSV 转换为 JSON

Question

我正在尝试将下面的 csv 转换为 json 格式。

Africa,Kenya,NAI,281
Africa,Kenya,NAI,281
Asia,India,NSI,100
Asia,India,BSE,160
Asia,Pakistan,ISE,100
Asia,Pakistan,ANO,100
European Union,United Kingdom,LSE,100

这是所需的 json 格式，但我无法创建它。我将在此下方发布我正在进行的工作。任何帮助或指导将不胜感激......

  {"name":"Africa",
      "children":[
      {"name":"Kenya",
          "children":[
          {"name":"NAI","size":"109"},
          {"name":"NAA","size":"160"}]}]},
  {"name":"Asia",
      "children":[
      {"name":"India",
          "children":[
          {"name":"NSI","size":"100"},
          {"name":"BSE","size":"60"}]},
  {"name":"Pakistan",
      "children":[
      {"name":"ISE","size":"120"},
      {"name":"ANO","size":"433"}]}]},
  {"name":"European Union",
        "children":[
        {"name":"United Kingdom",
            "children":[
            {"name":"LSE","size":"550"},
            {"name":"PLU","size":"123"}]}]}

进行中。

$1 是上面粘贴了 csv 值的文件。

#!/bin/bash

pcountry=$(head -1 $1 | cut -d, -f2)

cat $1 | while read line ; do 

region=$(echo $line|cut -d, -f1)
country=$(echo $line|cut -d, -f2)
code=$(echo $line|cut -d, -f3-)
size=$(echo $line|cut -d, -f4)

if test "$pcountry" == "$country" ;
  then 
  echo -e {\"name\":\"$region\", '\n' \"children\": [ '\n'{\"name\":\"$country\",'\n'\"children\": [ '\n' \{\"name\":\"NAI\",\"size\":\"$size\"\}
  else
      if test "$pregion" == "$region"
      then :
      else 
          echo -e ,'\n'{\"name\":\""$region\", '\n' \"children\": [ '\n'{\"name\":\"$country\",'\n'\"children\": [ '\n' \{\"name\":\"NAI\",\"size\":\"$size\"\},


pcountry=$country
pregion=$region

fi ; done

问题是我似乎无法找到一种方法来确定国家/地区值何时结束。

Answer 1

正如许多评论者所说，使用 shell 进行这种转换是一个糟糕的想法。而且，仅使用 bash 内置函数几乎是不可能的；和 shell 脚本用于组合标准的 unix 命令，如 sed、awk、cut 等。您应该选择一种更好的语言来解决您的问题，这种语言是为这种迭代解析/处理而构建的。

但是，因为已经很晚了，而且我喝了太多咖啡，所以我拼凑了一个 bash 脚本(加上一些 sed 用于解析帮助)，它采用您拥有的示例 .csv 数据，并以您记下的格式输出 JSON。这是脚本:

#! /bin/bash 
# Initial input file format:
#
#         Africa,Kenya,NAI,281
#         Africa,Kenya,NAA,281
#         Asia,India,NSI,100
#         Asia,India,BSE,160
#         Asia,Pakistan,ISE,100
#         Asia,Pakistan,ANO,100
#         European Union,United Kingdom,LSE,100
#
# Intermediate file format for parsing to JSON:
#
#         Africa|Kenya:NAI=281
#         Asia|India:BSE=160&NSI=100|Pakistan:ISE=100&ANO=100
#         European Union|United Kingdom:LSE=100
#
# Call as:
#
#   $ ./script INPUTFILE.csv >OUTPUTFILE.json
#


# temporary files for output/parsing
TMP="./tmp.dat"
TMP2="./tmp2.dat"
>$TMP
>$TMP2

# read through initial file and output intermediate format
while read line
do
    region=$(echo $line | cut -d, -f1)
    country=$(echo $line | cut -d, -f2)
    code=$(echo $line | cut -d, -f3)
    size=$(echo $line | cut -d, -f4)

    # region record already started
    if grep "^$region" $TMP 2>&1 >/dev/null ;then
        >$TMP2 
        while read rec
        do
            if echo $rec | grep "^$region" 2>&1 >/dev/null
            then
                if echo "$rec" | grep "\|$country:" 2>&1 >/dev/null
                then
                    echo "$rec" | sed -e 's/\('"$country"':[^\|][^\|]*\)/\1\&'"$code"'='"$size"'/' >>$TMP2
                else
                    echo "$rec|$country:$code=$size" >>$TMP2
                fi
            else
                echo $rec >>$TMP2
            fi
        done < $TMP
        mv $TMP2 $TMP
    else
    # new region
        echo "$region|$country:$code=$size" >>$TMP
    fi

done < $1

# Parse through our intermediary format and output JSON to standard out
echo "["
country_count=$(cat $TMP | wc -l)
while read line
do
    country=$(echo $line | cut -d\| -f1)
    echo "{ \"name\": \"$country\", "
    echo "  \"children\": ["
    region_count=$(echo $line | cut -d\| -f2- | sed -e 's/|/\n/g' | wc -l)
    echo $line | cut -d\| -f2- | sed -e 's/|/\n/g' | 
    while read region
    do
        name=$(echo $region | cut -d: -f1)
        echo "    { \"name\": \"$name\", "
        echo "      \"children\": ["
            code_count=$(echo $region | sed -e 's/^'"$name"'://' -e 's/&/\n/g'  | wc -l)
            echo $region | sed -e 's/^'"$name"'://' -e 's/&/\n/g'  |
            while read code_size
            do
                code=$(echo $code_size | cut -d= -f1)
                size=$(echo $code_size | cut -d= -f2)
                code_count=$((code_count - 1))
                COMMA=""
                if [ $code_count -gt 0 ]; then
                  COMMA=","
                fi
                echo "        { \"name\": \"$code\", \"size\": \"$size\" }$COMMA " 
            done
        echo "      ]"
        region_count=$((region_count - 1))
        if [ $region_count -gt 0 ]; then
            echo "    },"
        else
            echo "    }"
        fi
    done 
    echo "  ]"
    country_count=$((country_count - 1))
    COMMA=""
    if [ $country_count -gt 0 ]; then
        COMMA=","
    fi    
    echo "}$COMMA"

done < $TMP
echo "]"

exit 0

并且，这是上述脚本的结果输出:

[
{ "name": "Africa",
  "children": [
    { "name": "Kenya",
      "children": [
        { "name": "NAI", "size": "281" },
        { "name": "NAA", "size": "281" }
      ]
    }
  ]
},
{ "name": "Asia",
  "children": [
    { "name": "India",
      "children": [
        { "name": "NSI", "size": "100" },
        { "name": "BSE", "size": "160" }
      ]
    },
    { "name": "Pakistan",
      "children": [
        { "name": "ISE", "size": "100" },
        { "name": "ANO", "size": "100" }
      ]
    }
  ]
},
{ "name": "European Union",
  "children": [
    { "name": "United Kingdom",
      "children": [
        { "name": "LSE", "size": "100" }
      ]
    }
  ]
}
]

请不要在任何生产环境中使用上述代码。