Combine json file with CSV- similar to vlookup
<p>In plain terms, I'm trying to combine two sets of data. I'm open to use grep/bash or python.</p>

<ol>
<li><p>Read directory /mediaid</p></li>
<li><p>Read the .json files' filename</p></li>
<li><p>if the .json file name matches a row in the .csv, copy the contents of the json file in that row (if not, just skip)</p></li>
</ol>

<p>INPUT DATA</p>

<p><strong>File1.csv</strong></p>

<pre><code>testentry, 1234
testentry1, 6789
</code></pre>

<p>INPUT DATA (the filename is the MEDIAID to check)</p>

<p><strong>1234.json</strong></p>

<pre><code>[
{"id":"1", "text":"Nice man!"},
{"id":"2", "text":"Good job"}
]
</code></pre>

<p><strong>6789.json</strong></p>

<pre><code>[
{"id":"1", "text":"Test1"},
{"id":"2", "text":"Test2"}
]
</code></pre>

<p><strong>DESIRED OUTPUT DATA .csv</strong></p>

<pre><code>testentry, 1234, Nice man!, Good job
testentry1, 6789, Test1, Test2
</code></pre>

<p>My attempt using GREP is in progress, but I can't get the json file names to be checked and pass the data from them. </p>

<pre><code>#!/usr/bin/env bash

indir="$HOME/indir"
outdir="$HOME/outdir"

cd "$indir" || exit
mkdir -p "$outdir" || exit
for f in *.csv; do
    [[ -f $f ]] || continue
    lines=()
    while IFS=, read -ra cols; do
        if (( ${#cols[@]} != 2 )); then
            echo "Sorry buddy, you'll have to use a real CSV parser to handle: $f" &gt;&amp;2
            exit 1
        fi
        # Does the basename match the contents of the first column?
        if [[ ${cols[0]} == "${f%.*}" ]]; then
            echo "Match found in $f"
        fi
        lines+=("${cols[0]},${cols[1]}")
    done &lt;"$f"
    # something with JQ to read the json filename, and pass its data into the row
    printf '%s\n' "${lines[@]}" &gt; "$outdir/$f" || exit
done
</code></pre>

<p>A failed, but slightly better attempt in Python:</p>

<pre><code>import csv
import json

path_to_json = 'somedir/'

json_files = [pos_json for pos_json in os.listdir(path_to_json) if pos_json.endswith('.json')]

print json_files  # 

with open(json_files) as lookuplist:
    # IT NEEDS to match the mediaID from the json FILENAME
    with open('file1.csv', "r") as csvinput:
        with open('VlookupOut','w') as output:

            reader = csv.reader(lookuplist)
            reader2 = csv.reader(csvinput)
            writer = csv.writer(output)

            d = {}
            for xl in reader2:
                d[xl[2]] = xl[3:]

            for i in reader:
                if i[4] in d:
                    i.append(d[i[4]])
                writer.writerow(i)
</code></pre>

<p>This provides your required output:</p>

<pre class="lang-bsh prettyprint-override"><code>for file in /mediaid/*; do
    while read -r entry fileid; do 
        jsonfile="$fileid.json"
        if [[ -f "$jsonfile" ]]; then 
            text=$(jq -r 'map(.text) | join(", ")' "$jsonfile")
            echo "$entry $fileid, $text"
        fi
    done &lt; "$file"
done &gt; output.csv
</code></pre>

<p>Uses <a href="/questions/tagged/jq" class="post-tag" title="show questions tagged &#39;jq&#39;" rel="tag">jq</a> to parse the JSON files</p>

python json bash csv grep