Transform plain data into json file

Question

everyone. I am quite new with linux and I am learning a lot with all of you reading this site. I am into a bit of trouble with my accounting program and that's why I am recurring to you. I want to move my data from plain text to json, what I believe will give me better results and more flexibility.

I have a folder containing info in separated files. The files are like this:

20170404
pago
80051442-4
002-001-0080057
310000


310000
si
2017-06-05

I want to copy this info in new files where each line is a new json field. Something like this:

{
"field1":"20170404",
"field2":"pago",
"field3":"80051442-4",
"field4":"002-001-0080057",
"field5":"310000"
"field6":"",
"field7":"";
"field8":"310000"
"field9":"si"
"field10":"2017-06-05"
}

I am looking for a script to read the whole directory and build these new files: oldfile.json.

And after that I want to tab results of several of these files in different columns:

file1    field1    field2    field3   field4    field5    field6    field7
file2    field1    field2    field3   field4    field5    field6    field7
file3    field1    field2    field3   field4    field5    field6    field7
file4    field1    field2    field3   field4    field5    field6    field7
file5    field1    field2    field3   field4    field5    field6    field7

Thank you all for your time.

Answer 1

I would solve this using jq tool (a proper JSON parsing/manipulating tool):

for f in *; do
    jq -R -s 'rtrimstr("\n") | split("\n") | to_entries | reduce .[] as $o ({}; .["field" + ($o.key+1|tostring)] |= $o.value )' "$f" > "$f".json; 
done

---

• -R(--raw-input) combined with -s(--slurp) - pass the entire input to the filter as a single long string

Answer 2

for file in *; do
    awk 'BEGIN { print "{" }  END { print "\n}" } { printf( "%s",newrec); printf( "\"field%d\": \"%s\"", NR, $0); newrec=",\n" }' "$file" > "${file}.json"
done

NR is the Number of Records (or lines) in the file that have been processed so far. It's 1 when working on line one, 2 on line two, and so forth.

$0 is the entire unmodified input record (or line).

If the names of the fields are the same, you can define an array (e. g. labels[]) in the BEGIN stanza, and then refer to them by index (e. g. labels[NR]).

Answer 3

Answer to the second part - converting json files to table.

Usage: ./json_to_table.sh *.json

#!/bin/bash

for i in "$@"; do
    file_content=$(sed -n 's/".*":"\(.*\)",\?/\1/p' "$i" | tr '\n' ' ')
    printf "%s %s\n" "$i" "$file_content"
done | column -t

If you don't need pretty table format, then you may remove | column -t part (in the bottom, after done). Then row's fields will be separated by one space, therefore, each will have own width. It is good for parsing by programs, like awk, sed, but inconvenient for humans reading.

Note: after each record in the json file, except last, should be comma. What I mean:

"field8":"310000", <- here
"field9":"si", <-here
"field10":"2017-06-05" <- not here. Last record without comma.
}

Answer 4

So final script for the first part is:

for file in facturas/*; do
    awk 'BEGIN {
        split("fechae tpago ruc nfactura total iva10 iva5 iva0 archivado fechap",campos)
        print "{" 
    }
    END {
        print "\n}" 
    }
    {
        printf( "%s",newrec)
        printf( "\"%s\":\"%s\"", campos[NR], $0)
        newrec=",\n"
    }' "$file" > "${file}.json"
done

And final result for second part:

#!/bin/bash

for i in "$@"; do
    printf "%s " "$i"
    sed -n 's/".*":"\(.*\)"/\1/p' "$i" | tr ',\n' ' ' 
    echo
done | column -t

Thank you everyone for your interest.