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 CSV 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 CSV 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 get_date_time(utimestamp):
    ts = datetime.fromtimestamp(utimestamp // 1000000)
    return ts.date(), ts.time()
 
 
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].copy()
        headers["time_date"], headers["time_time"] = None, None
        del headers["time_usec"]
        print(";".join(headers), file=csv_file, flush=True)
        for elm in json_load:
            elm["time_date"], elm["time_time"] = get_date_time(elm["time_usec"])
            del elm["time_usec"]
            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.1794407500.txt.gz · Dernière modification : de Jean-Baptiste

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki