LINUX - Shell Scripting - Generate map(key value pair) from flat file

user_8872217 0
Viewed 109

I have a flat file which is similar to JSON(not exactly same). Final motive is to convert it into CSV (in specific order, not same as input JSON File ) and dump in database. now since the input file is not always in same order AWK, cut etc wont be useful. Need something like HASHMAP. Attaching sample Input and Expected out. Suggestions please.

Input JSON file sample (I have like 1000 files, 50K rows each, 200 columns each)

{"Field1":{"string":"Value,123"},"Field2":{"string":"564243"},"Field3":{"string":"SWCHP0001155"},"LOCATION":null,"OWNERUID":{"string":"655,,34"}}
{"Field1":{"string":"Value,456"},"Field2":{"string":"89565655"},"Field3":{"string":"SWCHP0001166"},"LOCATION":{"string":"BEACH,"},"OWNERUID":{"string":"65534"}}
........

Output CSV File Expected in specific order, lets say in order (OWNERUID, LOCATION, Field1, Field2, Field3)

655;;34,null,Value;123,564243,SWCHP0001155
65534,BEACH;,Value;456,89565655,SWCHP0001166

My idea so far is to do some string manipulation change eveything a standard format like below, transfer Data into hashmap, fetch in required and dump in CSV

Intermediate Standard format : (can be changed, please suggest)

Field1?Value;123,Field2?564243,Field3?SWCHP0001155,LOCATION?null,OWNERUID?655;;34
Field1?Value;456,Field2?89565655,Field3?SWCHP0001166,LOCATION?BEACH;,OWNERUID?65534
........

note:

  1. values contain , and other special characters
  2. I can change values a bit like converting commas to semicolon (like above)
  3. There are NULL values to be handled
csv shell linux parsing
1 Answers

jq is a C tool that aims at processing JSON strings. It acts as a filter (or a chain of filters) that processes one input at a time and has a fairly nice documentation. It may be available in your distribution, or pretends to be built from source with no to little dependancies beyond a C compiler. In your example, this should be enough:

$ cat file.json |  jq '[.OWNERUID.string, .LOCATION.string?, .Field1.string?, .Field2.string?, .Field3.string?]|join(";")'
"655,,34;;Value,123;564243;SWCHP0001155"
"65534;BEACH,;Value,456;89565655;SWCHP0001166"
$

Or if you want to get rid of the enclosing quotes, a sed filter can remove them:

$ cat ess.txt |  jq '[.OWNERUID.string, .LOCATION.string?, .Field1.string?, .Field2.string?, .Field3.string?]|join(";")' | sed -e 's/^"\(.*\)"$/\1/'
655,,34;;Value,123;564243;SWCHP0001155
65534;BEACH,;Value,456;89565655;SWCHP0001166
$
user_35452730
Related