The question is how to rearrange the column along with values in desired order.



Expected output should be in a,b,c,d and their corresponding values.

  • The _idx column in your script is $1 and not $2 (could it be that simple ? ) – Samusz May 10 '19 at 4:31
  • (1) Could you possibly present your problem using input that isn’t soooo loooong that it needs to be scrolled horizontally?  (2) Please give an example of the desired output.  (3) Please explain the logic of your attempt(s).  For example, I see a sed command that is inserting newlines into the data immediately followed by a tr command that is removing newlines.  That doesn’t make any sense.  Please do not respond in comments; edit your question to make it clearer and more complete. – G-Man Says 'Reinstate Monica' May 10 '19 at 5:53
  • May you please make it clear if double quotes have to be stripped in the final output? Also, are your columns allowed to contain the <space>, column separator, e.g. "idxg_name":"something ,else"? – fra-san May 10 '19 at 8:07
  • Now you have apparently left out the sample of your input data (it looks like you are showing the expected output (thanks!) and then only the data at an intermediate step). And please try to stop changing the format of your desired output, it makes harder for others to work on an answer. – fra-san May 10 '19 at 9:02
  • 2
    could you add a stable sample of input/desired ouput/and current output. Finally if it is json, please use json tool for it. – Kiwy May 10 '19 at 10:01

Since your question evolved significantly over time, I'll try to address three distinct points.

Your attempt1

Your awk command is trying to split your lines on occurrences of admin:. Even if it made sense you would only be able to reference fields $1 and $2, since you only have one occurrence of admin: in each of your lines.

You may be looking for something as:

printf '%s\n' '"_id":"asc" ,"name":"enygren" ,"admin":[] ,"creat":"date3"' |
  sed 's/"//g' |
  awk -F' ,' -v OFS='|' '{if ($2~/name:/){print $1,$3,$4,$2} else {$1=$1; print $0}}'

Which, of course, may happen not to be a good idea: /name:/ matches anything that contains name:, not just the exact label name:.

Anyway, this looks like an XY problem.

Rearranging columns

Here is an awk solution that you can customize to select and reorder your columns, assuming they are from a delimited text file.

It makes the assumption that the fields in your input data cannot contain any " or ,. This sounded reasonable based on the code you posted1, but actually appears not to be the case. You should resort to some tool specialized in manipulation of structured data (see below), e.g. csvkit for CSV or jq for JSON (thanks to Kiwi for the hint).

Given the script prog_file:

                        # Create an array of labels for the fileds you want
                        # to keep, in the order you want to print them
    labels[1] = "\"_id\""
    labels[2] = "\"admin\""
    labels[3] = "\"creat\""
    labels[4] = "\"name\""
                        # Split any field on ":" and make an array of
                        # full fields indexed by their label.
                        # This assumes labels DO NOT CONTAIN any ":"
    for ( i=1; i<=NF; i++ ) {
        split($i, chunks, ":")
        fields[chunks[1]] = $i
                        # Reset the record
    $0 = ""
                        # Re-build the record with only the fields
                        # whose labels are in the array we defined in
                        # the BEGIN block.
                        # Explicitly use "4" as the upper bound because
                        # POSIX does not specify the order in which
                        # "for (var in array)" assigns indexes to var
    for ( i=1; i<=4; i++ ) {
        $i = fields[labels[i]]
                        # Strip any double quote
    print $0

and the input2:

"_id":"123" ,"admin":[src] ,"creat":"date1" ,"name":"dedu"
"_id":"2w3" ,"admin":[analise] ,"creat":"date2" ,"name":"csv"
"_id":"asc" ,"name":"enygren" ,"admin":[] ,"creat":"date3"
"_id":"scd" ,"admin":[] ,"creat":"date4" ,"name":"tzpi"

the invocation:

awk -v FS=' ,' -v OFS='|' -f prog_file input_file



Dealing with data formats

The last sample of input data you edited into your question does not seem to be from a delimited text file. It looks like a list of JSON objects.
Despite being human-readable, JSON is a data format and requires a different approach—indeed the above awk solution will not work with that input.

Adding a bit structure, your sample can be turned (back?) into valid JSON:

$ cat file

(Note that I assumed that a missing " in "d":["val2","val32] was a typo, and used "d":["val2","val32"] instead).

$ sed 's/^/{/; s/$/},/; 1 s/^/[/; $ s/,$/]/' file >tmpfile
$ cat tmpfile 

Then, the safe approach would be to use a JSON processor as jq to filter and reorder your data:

$ jq -r '.[] | {a: .a, b: .b, c: .c, d: .d} | @text' tmpfile

Removing the remaining opening and closing curly braces is trivial and safe, while it will not be safe to blindly remove double quotes (") or replace commas with vertical bars (,|) to fully match your sample output.

1 From question's revisions n° 4 to n° 7.
2 Inferred from the last part of question's revision n° 6.
3 From question's revision n° 6.

|improve this answer|||||
  • 2
    I applaud your patience (chasing the moving target through smoke and fog). If the question had ever stabilized enough for me to consider it to be answerable, I probably would have written something similar to your awk answer (extracting the desired values into an associative array, and then outputting them in the desired order). – G-Man Says 'Reinstate Monica' May 11 '19 at 19:17

Sorry, if I ignore your attempt. It looks too complicated to me, piping through many scripts and tools.

As I understand, the columns are in correct order, except for the idxg_name, which should be placed at the end. So I suggest to simply do:

sed 's/"//g;s/\(,idxg_name:[^,]*\)\(.*\)/\2\1/' yourfile
  • The s/"//g part removes the " like you already did
  • ,idxg_name:[^,]* matches the idxg_name field, starting at the comma and including everything before the next comma (note that this will fail, if the name includes a comma! If this can happen, it will make things more complicated, considering, whether the comma is inside "")
  • The .* matches the rest of the line and
  • the replacement \2\1 changes the order of both parts inside those \(\), thus placing the name field at the end of the line. Done.
|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.