1

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
  • 1
    Given that the Expires field has a comma, I don't think you want comma-delimited fields. – Andy Dalton Aug 10 '20 at 0:50
  • Yes Andy, I want date "Aug 4, 2021" to be in new column. – pavan Aug 10 '20 at 1:08
0

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.

0

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..

0

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
  • You can cope with multi-word column names, and do this in one awk process. Where FNR == 2, if FNR == NR, construct FIELDWIDTHS from the length of each input field (the hyphens), then nextfile. Name the input file twice in the awk args so reads it a second time, and skip FNR == 2 again. – Paul_Pedant Aug 10 '20 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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