Outils pour utilisateurs

Outils du site


tech:script_python_conversion_json_vers_csv

Ceci est une ancienne révision du document !


Script Python conversion JSON vers CSV

Exemple 1 - 2 colonnes - clefs / valeurs

json2csv_kv.py

#! /usr/bin/env python3
 
import sys
import json
import argparse
 
parser = argparse.ArgumentParser(
    epilog="""Convert JSON file to CSV.""")
 
parser.add_argument("-i", help="Input JSON file")
parser.add_argument("-o", help="Output XLS file")
args = parser.parse_args()
 
def concat(a, b):
    a=str(a)
    b=str(b)
    return a+ "_" + b
 
def quote_string(thing):
    if isinstance(thing, str):
        return '"{}"'.format(thing)
    else:
        return thing
 
def recursiv_if_json_dict(k, v):
    if isinstance(v, dict):
        for k2, v2 in v.items():
            recursiv_if_json_dict(concat(k, k2), v2)
    else: 
        write(k, v)
 
def recursiv_if_json_list(thing):
    new_list = []
    if isinstance(l, list):
        for elm in thing:
            if isinstance(elm, list):
                recursiv_if_json_list(elm)
            else:
                new_list.append(elm)
    return new_list
 
def convert_json2csv():
    with open(args.i, "r") as json_file:
        json_load = json.load(json_file)
 
        if isinstance(json_load, dict):
            for k, v in json_load.items():
                recursiv_if_json_dict(k, v)
        elif isinstance(json_load, list):
            for elm in json_load:
                recursiv_if_json_list
 
def write(k, v):
    with open(args.o, "a") as csv_file:
        print(quote_string(k), quote_string(v), sep=';', file=csv_file, flush=True)
 
def truncate():
    with open(args.o, "a+") as csv_file:
        csv_file.truncate(0)
 
def main():
    if args.i and args.o:
        truncate()
        convert_json2csv()
        sys.exit(0)
 
 
if __name__ == "__main__":
    main()
cat /proc/meminfo | jc --proc-meminfo > meminfo.json
./json2csv_kv.py -i meminfo.json -o meminfo.csv

Exemple 2 - liste de dictionnaires

Filtrage préliminaire du JSON

cat Takeout/Chrome/Historique.json | jq '."Browser History"' > Historique2.json

json2csv_list_of_dicts.py

#! /usr/bin/env python3
 
import sys
import json
import argparse
from datetime import datetime
 
parser = argparse.ArgumentParser(
    epilog="""Convert JSON file to CSV.""")
 
parser.add_argument("-i", help="Input JSON file")
parser.add_argument("-o", help="Output XLS file")
args = parser.parse_args()
 
def clean_field(field):
    """
    Clean each fields. 
    CSV use ';' as field separator and so all ';' characters must be removed
    CSV use line break as line separator and so all '\n' and '\r' characters must be replaced
    """
    if field is None or field == "Undefined":
        ret = ""
    else:
        ret = str(field).strip().replace(";", "").replace("\n", ",").replace("\r", ",")
    return ret
 
def quote_string(thing):
    if isinstance(thing, str):
        return '"{}"'.format(thing)
    else:
        return thing
 
def convert_json2csv():
    with open(args.i, "r") as json_file:
        json_load = json.load(json_file)
    with open(args.o, "a+") as csv_file:
        headers = json_load[0]
        print(';'.join(headers), file=csv_file, flush=True)
        for elm in json_load:
          newtime = elm['time_usec'] / 1000000
          newtime = datetime.fromtimestamp(newtime)
          elm['time_usec'] = newtime
          for v in elm.values():
            print(quote_string(clean_field(v)), end=';', file=csv_file, flush=True)
          print(file=csv_file, flush=True)
 
def truncate():
    with open(args.o, "a+") as csv_file:
        csv_file.truncate(0)
 
def main():
    if args.i and args.o:
        truncate()
        convert_json2csv()
        sys.exit(0)
 
 
if __name__ == "__main__":
    main()
./json2csv_list_of_dicts.py -i Historique2.json -o Historique.csv

Exemple 2b

Bon, il y avait plus simple

echo '"favicon_url","page_transition_qualifier","title","url","time_usec","client_id"' > Historique.csv
 
jq --raw-output '
  ."Browser History".[]
  | [.favicon_url, .page_transition_qualifier, .title, .url, .time_usec, .client_id]
  | @csv
' Historique.json >> Historique.csv 

Autres

Conversion CSV vers JSON

man jc

...
            --csv  CSV file parser

            --csv-ih
                   CSV implicit header file parser

            --csv-s
                   CSV file streaming parser

            --csv-ih-s
                   CSV implicit header file streaming parser
...
cat Historique.csv | jc --csv
tech/script_python_conversion_json_vers_csv.1794089233.txt.gz · Dernière modification : de Jean-Baptiste

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki