Formatting the output of a sql query and creating a csv

Question

The current output that i have from a sql query is

database_name   database_size     unallocated_space   reserved      data          index_size    unused
-------------- ------------------ ------------------  ------------- ------------- ------------- ------------- 
 TEST            22200.00 MB            3160.41 MB      9256544 KB    6597072 KB    1559384 KB    1100088 KB

Trying to create a csv in the format

database_name,database_size,unallocated_space,reserved,data,index_size,unused
Test,22200.00 MB,3160.41 MB,9256544 KB,6597072 KB,1559384 KB,1100088 KB

Output that I am currently getting

database_name,database_size,unallocated_space
Test,22200.00,MB,3160.41,MB,9256544,KB,6597072,KB,1559384,KB,1100088,KB

with

echo "database_name,database_size,unallocated_space" > $outfile
awk -F " " '{print $1","$2","$3",$4","$5","$6","$7}' $readFile  >> $outfile

where readfile is the file containing the sql query output and outfile is the csv.

Kindly do wrap your samples and codes in CODE TAGS in your question and let us know then. — RavinderSingh13
– RavinderSingh13, Commented Aug 27, 2020 at 16:02
You could probably modify this answer by @EdMorton to suit your needs: stackoverflow.com/a/31947741/4162356 Which database, are you using, BTW? — James Brown
– James Brown, Commented Aug 27, 2020 at 16:13

Dominique · Accepted Answer · 2020-08-27 16:19:46Z

1

Can't you just change your SQL query, somethin like:

Select concat(database_name, ",", database_size, ",", ...) ...

Like this, you don't need to do any reformatting afterwards.

Beware, I have written "concat", I'm not sure about the correct name of the concatenation function.

answered Aug 27, 2020 at 16:19

Dominique

17.6k20 gold badges81 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

thanasisp · Accepted Answer · 2020-08-27 20:01:04Z

0

You can use this awk, first condition prints the header, second one prints the fields with numbers and MB together.

> awk -v OFS=, 'NR==1{$1=$1;print;next} NF==5{print $1, $2" "$3, $4" "$5}' file
database_name,database_size,unallocated_space
Test,100.00 MB,200 MB

Or an alternative awk, from @Cyrus (based on the number of spaces in your input).

> awk 'NR!=2{$1=$1; print}' FS='  +' OFS=',' file
database_name,database_size,unallocated_space,
Test,100.00 MB,200 MB

Note: in both commands, $1=$1 is an idiom to force awk split to fields and recalculate the output, so it prints using the new field separator.

Modified for the updated input:

awk -OFS, 'NR==1{$1=$1;print;next} NF==13{print $1,$2" "$3,...<etc until 12-13> }' file

edited Aug 27, 2020 at 20:01

answered Aug 27, 2020 at 16:12

thanasisp

5,9953 gold badges17 silver badges32 bronze badges

4 Comments

Cyrus Over a year ago

or alternatively with GNU awk: awk 'NR!=2{$1=$1; print}' FS=' +' OFS=',' file. I assume that in JSS's question the many spaces on the right are not part of the output.

JSS Over a year ago

The above works just fine but if we have another row that has is appended to the sql output the above is not able to fetch that

database_name   database_size      unallocated_space -------------- ------------------ ------------------ TEST            22200.00 MB            3160.41 MB reserved      data          index_size    unused ------------- ------------- ------------- ------------- 9256544 KB    6597072 KB    1559384 KB    1100088 KB

Is it possible to tweak the above somehow?

Ed Morton Over a year ago

@JSS Chameon questions are strongly discouraged. You asked a question, got some answers, and accepted an answer. If you need something additional then just ask a new followup question.

thanasisp Over a year ago

@Ed Morton perhaps you should be awarded a badge for chameleon hunting, also I have to train myself on that.

Collectives™ on Stack Overflow

Formatting the output of a sql query and creating a csv

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related