[SOLVED] How to format gcloud compute instances list output to excel format

Issue

Tried various approaches, but nearest to working one:

1. Replace multiple spaces with single one
2. Replace commas(,) in INTERNAL_IP column with Pipe(|)
3. Remove 4th cloumn (PREEMPTIBLE) as it was causing IPs in INTERNAL_IP cloumn shift under it.
4. Replace space with comma(,) to prepare a csv file.

But did not work. Gets messed up at PREEMPTIBLE cloumn.

    gcloud compute instances list > file1 
    
    tr -s " " < file1 > file2 // to replace multiple spaces with single one
    
    sed s/\,/\|/g file2 > file3 // to replace , with pipe
    
    awk '{$4=""; print $0}' file3 // to remove 4th column
    
    sed -e 's/\s\+/,/g' file3 > final.csv

Output of gcloud compute instances list command:

Expected format:

Any help or suggestion is appreciated. Thank you in advance.

Edit: Attached sample input and expected output files:

sample_input.txt

expected_output.xlsx

Solution

csv format is supported in gcloud CLI so everything you are doing can be done without sed/awk maybe with | tail -n +2 if you want to skip the column header :

gcloud compute instances list --format="csv(NAME,ZONE,MACHINE_TYPE,PREEMPTIBLE,INTERNAL_IP,EXTERNAL_IP,STATUS)" > final.csv

Or if you wanted to do something with the data in your bash script:

while IFS="," read -r NAME ZONE MACHINE_TYPE PREMPTIBLE INTERNAL_IP EXTERNAL_IP STATUS
do
    echo "NAME=$NAME ZONE=$ZONE MACHINE_TYPE=$MACHINE_TYPE PREMPTIBLE=$PREMPTIBLE INTERNAL_IP=$INTERNAL_IP EXTERNAL_IP=$EXTERNAL_IP STATUS=$STATUS"
done < <(gcloud compute instances list --format="csv(NAME,ZONE,MACHINE_TYPE,PREEMPTIBLE,INTERNAL_IP,EXTERNAL_IP,STATUS)" | tail -n +2 | awk ' BEGIN {print "NAME,ZONE,MACHINE_TYPE,PREMPTIBLE,INTERNAL_IP,EXTERNAL_IP,STATUS"} {print $1","$2","$3","" "","$4","" "","$5}' )

Answered By - JulienG