4

I have a file like this

103710:v2HAbAFH029324:[email protected]:localhost:Sent
103821:CCFE5609E3:[email protected]:localhost:bounced
103922:DFF19609E2:[email protected]:localhost:Deferred

I need to change this to 

{"randomId":{"s":"103710"},"id":{"s":"v2HAbAFH029324"},"userId":{"s":"[email protected]"},"dns":{"s":"localhost"},"status":{"s":"Sent"}}
{"randomId":{"s":"103821"},"id":{"s":"CCFE5609E3"},"userId":{"s":"[email protected]"},"dns":{"s":"localhost"},"status":{"s":"bounced"}}
{"randomId":{"s":"103922"},"id":{"s":"DFF19609E2"},"userId":{"s":"[email protected]"},"dns":{"s":"localhost"},"status":{"s":"Deferred"}}

I am thinking the code like this 

while read line
do     
        sed -i 's/^/{"randomId":{"s":"/' test
        echo $line

        echo $line | grep -q ":"
        [ $? -eq 0 ] && echo "/"{"id":{"s":/"
        [ $? -eq 1 ] && echo "/",{"userId":{"s":/"
        [ $? -eq 2 ] && echo "/",{"host":{"s":/"
        [ $? -eq 3 ] && echo "/",{"status":{"s":/"
        echo "$line | " ";
done < test

Adding for first occurence of : {"id":{"s": and then second occurence adding {"userId":{"s":

Improve this question
2
  • please format your question: see unix.stackexchange.com/editing-help Commented Mar 24, 2017 at 15:43
  • Shouldn't that be echo -n, to stop it from inserting newlines? Commented Oct 22, 2024 at 21:13

6 Answers 6

Reset to default
5

That grep/echo block isn't going to do anything useful; $? is going to be set once—it's not going to iterate through the fields.

Thankfully, there appears to be a much easier way to do this: just split the fields apart into variables. Thankfully, read can do this for you:

while IFS=':' read -r randomid id userid dns status; do
    printf '{"randomId":{"s":"%s"},"id":{"s":"%s"},"userId":{"s":"%s"},"dns":{"s":"%s"},"status":{"s":"%s"}}\n' \
           "$randomid" "$id" "$userid" "$dns" "$status"
done

Using printf instead of the more-familiar echo avoids all the \"-sequences echo would require. Do note the backslash at the end of the line to split it.

BTW: The format you're producing is called JSON, and there might be tools to help generate it (for example, jq). Also, it can require its own escaping if, e.g., your fields can contain double-quotes.

Improve this answer
3

With perl:

perl -MJSON -F: -ple '@A = qw/randomId id userId dns status/; $_ = encode_json({map { shift @A => { "s" => $_ } } @F } )' input.csv
Improve this answer
1
  • 1
    This is a nice alternative because it actually uses a JSON generator, so will work even if there is a quote (etc.) in the input field. But you really ought to explain it better, and also note the caveat that it doesn't exactly generate the requested output (fields are in a different order), but that it's equivalent by the rules of JSON. Commented Mar 24, 2017 at 18:11
1

Because your data is delimited and easy to read there are a few ways to do this. Sed can parse your data in one line and output the changes:

sed -r -i 's/^(.*):(.*):(.*):(.*):(.*)$/{"randomId":{"s":"\1"},"id":{"s":"\2"},"userId":{"s":"\3"},"dns":{"s":"\4"},"status":{"s":"\5"}}/' input.txt

You use capture groups to capture everything between the beginning of the file, your delimiter, and the end of the file and then just manipulate text around those groups. Every capture group is referenced by "\#" where # is the number of the capture group starting at one and incrementing by one for each group.

As already mentioned you can also set your own delimiter. Bash has a builtin variable called IFS (internal field separator). IFS defaults to whitespace but you have the ability to change it. I won't show the bash example as its already been given and would just be a copy of it.

Improve this answer
0
1 
perl -F: -pale '
   @A = qw/randomId id userId dns status/;
   ($k, $_) = (0, "{" . join(",", map qq/"$A[$k++]":{"s":"$_"}/, @F) . "}");
' yourfile

Explanation

The @F holds the fields split on : which are then sewn together with appropriate massaging {"s":"fieldI"} and prefixed with the corresponding element from the array @A. All these elements are brought together by a join on , and enclosed in "{" ... "}". And you're done.

Improve this answer
1

Using Miller (mlr) for reading the data as colon-separated fields, adding the required key to each field, and then pushing the values down into sub-objects with an s key:

mlr --n2l --ifs colon --jvquoteall \
    label randomId,id,userId,dns,status then \
    put 'for (k in $*) { $[k] = { "s": $[k] } }' file

Here, the --n2l option is a shortcut for --inidx --ojsonl, --ifs sets the delimiter, and --jvquoteall makes Miller output all values as strings (numerical data would otherwise not be quoted). The label verb sets the names of the fields to the given list in order, and the put expression iterates over each field and moves the data down into a sub-object with an s key.

Output:

{"randomId": {"s": "103710"}, "id": {"s": "v2HAbAFH029324"}, "userId": {"s": "[email protected]"}, "dns": {"s": "localhost"}, "status": {"s": "Sent"}}
{"randomId": {"s": "103821"}, "id": {"s": "CCFE5609E3"}, "userId": {"s": "[email protected]"}, "dns": {"s": "localhost"}, "status": {"s": "bounced"}}
{"randomId": {"s": "103922"}, "id": {"s": "DFF19609E2"}, "userId": {"s": "[email protected]"}, "dns": {"s": "localhost"}, "status": {"s": "Deferred"}}

Another approach is to use jq to read the input as raw text lines, split each line into an array of separate strings on the colons, and then create separate JSON objects with an s key as the only key and the string as that key's value. The array of objects is then converted into an object with keys taken from the --args command line argument.

jq -R -c 'split(":") | map({ s: . }) | with_entries(.key = $ARGS.positional[.key])' file --args randomId id userId dns status

Result:

{"randomId":{"s":"103710"},"id":{"s":"v2HAbAFH029324"},"userId":{"s":"[email protected]"},"dns":{"s":"localhost"},"status":{"s":"Sent"}}
{"randomId":{"s":"103821"},"id":{"s":"CCFE5609E3"},"userId":{"s":"[email protected]"},"dns":{"s":"localhost"},"status":{"s":"bounced"}}
{"randomId":{"s":"103922"},"id":{"s":"DFF19609E2"},"userId":{"s":"[email protected]"},"dns":{"s":"localhost"},"status":{"s":"Deferred"}}
Improve this answer
2
  • 1
    See mlr's --jvquoteall for an output closer to what the OP's requesting. Commented Oct 20, 2024 at 14:31
  • @StéphaneChazelas Thank you! I did not initially see that the numerical data was to be treated as strings. Commented Oct 20, 2024 at 15:02
-1

You may find csvjson useful. Please refer to the documentation.

Sample Command

cat data.csv | csvjson -d :

Output

[{"103710": 103821.0, "v2HAbAFH029324": "CCFE5609E3", "[email protected]": "[email protected]", "localhost": "localhost", "Sent": "bounced"}, {"103710": 103922.0, "v2HAbAFH029324": "DFF19609E2", "[email protected]": "[email protected]", "localhost": "localhost", "Sent": "Deferred"}]

Installation

pip install csvkit

Please refer to the documentation for updated information.

Improve this answer
2
  • Note that it’s not a straight conversation of the data. You also need to insert new keys and the original data should be stored in sub-object with an s key. Commented Oct 20, 2024 at 12:54
  • I did not say this is the exact answer to the problem, but I told that @kumar babu may find it useful. And I am certain that many users who end up on this page will find this useful. Commented Oct 22, 2024 at 18:54

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.