Convert shell results into Excel File in columns

Question

I am running a shell script that gives the below result, and I want to store the result in Excel in different columns (HOST, Status, Expires, Days). How can I convert into Excel?

Host                                            Status       Expires      Days
----------------------------------------------- ------------ ------------ ----
FILE:certs/dnscert.crt                          Valid        Aug  4, 2021  359

Answer 1

Given a modification of your sample input:

$ cat input
Host                                            Status       Expires      Days
----------------------------------------------- ------------ ------------ ----
FILE:certs/dnscert1.crt                         Valid        Aug  1, 2020  7
FILE:certs/dnscert2.crt                         Invalid      Aug  4, 2021  359
FILE:certs/dnscert3.crt                         Valid        Aug  4, 2021  359

Then, if I apply the following awk command, I get:

$ awk 'NR > 2 { print $1 "," $2 ",\"" $3, $4, $5 "\"," $6}' input
FILE:certs/dnscert1.crt,Valid,"Aug 1, 2020",7
FILE:certs/dnscert2.crt,Invalid,"Aug 4, 2021",359
FILE:certs/dnscert3.crt,Valid,"Aug 4, 2021",359

The assumes the date field is always 3 tokens: month, day, and year.

The NR > 2 skips the first two lines: the header and delimiter. The rest just prints the fields with commas between then, taking care to quote the date fields.

Answer 2

Assuming the shell script is providing the data with spaces to layout, you can use awk to convert to tab-delmited. Excel will open a tab-delimited file (perhaps complaining about format mismatch), and allow the user to save as xlsx if needed.

$my_script | awk -v OFS='\t' '{$1=$1}1' > myexcel.xls

Which uses a trick to force OFS to be used, and then print. Or the less cryptic:

$my_script | '{print $1 "\t" $2 "\t" $3 "\t" $4}' > myexcel.xls

There are modules for your favorite scripting languages that will produce a "real" xlsx file (which is just a zipped directory of XML), but that gets more involved. If you just need something users can click and open in Excel, tab-delimited with an xls extension works fine..

Answer 3

The fields are themselves comprising spaces like the date field, so we have to devise another strategy.

Using the header(assuming no spaces in field names) get the field widths of each field and plug that in the FIELDWIDTHS built-in variable of awk, in a second pass over the input file.

$ fw=$(< file \
   awk -v FPAT='[^[:space:]]+([[:space:]]+|$)' '  NR>1{exit}
{for (i=1; i<=NF; i++) $i=length($i)}1')

$ awk \
  -v q=\" -v OFS=,     \
  -v FIELDWIDTHS="$fw" \
'
function trim(a,  l, t) {
  l = "^[[:space:]]+"
  t = "[[:space:]]+$" 
  re = l "|" t
  gsub(re, "", a)
  return a
}
function quote(a,  r, nondigit) {
  r = "[^[:digit:]]"
  nondigit = a ~ r
  return nondigit ? q a q : a
}
NR==2{next} 
{
  for (i=1; i<=NF; i++) {
    t = $i
    gsub(/"/, "\\&", t)
    t = trim(t)
    $i = quote(t)
  }
}1' file

"Host","Status","Expires","Days"
"FILE:certs/dnscert.crt","Valid","Aug  4, 2021",359