Text file to fields (columns) every nth occurrence of a string

Question

I am creating an Excel spreadsheet of Cisco fabric config and want to get the format right into fields / columns for importing.

Here is the format, with amended info of course:

zone name Zone1_HOSTNAME01 vsan XXX
  fcalias name STORAGEPORT_0 vsan XXX
    pwwn xx:xx:xx:xx:xx

  fcalias name STORAGEPORT_1 vsan XXX
    pwwn xx:xx:xx:xx:xx

  fcalias name STORAGEPORT_2 vsan XXX
    pwwn xx:xx:xx:xx:xx

zone name Zone2_HOSTNAME02 vsan XXX
  fcalias name STORAGEPORT_3 vsan XXX
    pwwn xx:xx:xx:xx:xx

  fcalias name STORAGEPORT_4 vsan XXX
    pwwn xx:xx:xx:xx:xx

  fcalias name HOSTNAME02 vsan XXX
    pwwn xx:xx:xx:xx:xx

So what I want to do is to have everything in the Zone name ZONE NAME up to the " vsan" space in 1 field, and then until the next occurrence of start of line with "zone name" put each string into its own field which I can then "cut" using delimiters to get what I want. So in essence what I want to have in the end is:

"zone name Zone1_HOSTNAME01" "vsan" "XXX" "fcalias name" "STORAGEPORT_0 vsan XXX" "pwwn xx:xx:xx:xx:xx" "fcalias name" "STORAGEPORT_1 vsan XXX" "pwwn xx:xx:xx:xx:xx" "fcalias name" "STORAGEPORT_2 vsan XXX" "pwwn xx:xx:xx:xx:xx"

or something like that. Each white space can be in it's own field as I can then manipulate the columns afterwards far easier.

The text file has over 800 lines and some may be larger but it's unknown right now. The biggest issue is that the text that comes after the initial line starting with "zone name...." can be varying so I just need to translate them into their own fields regardless of what comes next.

Answer 1

The following perl script outputs your input file (markizy.txt) in tab-delimited format because there are spaces inside the fields.

#!/usr/bin/perl

while(<>) {
  chomp;
  s/ +(vsan|fcalias|pwwn) */\t$1 /g ;
  s/ +\t/\t/;

  if ($. > 1 && m/^zone name/) {
    print $l,"\n";
    $l = $_;
  } elsif (eof) {
    $l .= $_;
    print $l,"\n";
  } else {
    $l .= $_;
  };
};

The perl built-in variable $. is the current line number, so the script avoids printing (an empty line) when zone name is on the first line of the input. See man perlvar for details about this and many other variables (and their long-winded aliases like $INPUT_LINE_NUMBER for $.).

Save it to a file, make it executable with chmod +x, and run it. e.g. with cat -T to show the tabs (^I):

$ ./markizy.pl markizy.txt  | cat -T
zone name Zone1_HOSTNAME01^Ivsan XXX^Ifcalias name STORAGEPORT_0^Ivsan XXX^Ipwwn xx:xx:xx:xx:xx^Ifcalias name STORAGEPORT_1^Ivsan XXX^Ipwwn xx:xx:xx:xx:xx^Ifcalias name STORAGEPORT_2^Ivsan XXX^Ipwwn xx:xx:xx:xx:xx
zone name Zone2_HOSTNAME02^Ivsan XXX^Ifcalias name STORAGEPORT_3^Ivsan XXX^Ipwwn xx:xx:xx:xx:xx^Ifcalias name STORAGEPORT_4^Ivsan XXX^Ipwwn xx:xx:xx:xx:xx^Ifcalias name HOSTNAME02^Ivsan XXX^Ipwwn xx:xx:xx:xx:xx

The pipe to cat -T is only there to show you that the output has tab-separated fields (because they don't look much different to spaces, otherwise). Don't use it when running it for real, just redirect to a file. Excel (or gnumeric or Libre Office Calc or almost any other spreadsheet) should have no difficulty importing a TAB-separated text file - it's been a standard capability for almost as long as I can remember.

Run it for real as:

./markizy.pl markizy.txt > markizy.csv

You may have to tell Excel that the data is tab-separated rather than comma-separated on import, or it may be able to detect that fact itself.

Alternatively, if you're absolutely certain that none of the data fields will contain commas, replace all the \ts in the script with commas and you'll have comma-separated.

Answer 2

It might be easier in the long run to do the whole job in Excel. I cut and pasted your example into a text file and opened it into Excel and got the following:

From there, you can use the global search and replace command to make any modifications that you might want.

Answer 3

It seemed obvious certain fields could be omitted as these would be accounted for in the strings i would create in excel after importing the sorted data. There are i'm sure far better options but this took all my output, put all values in order on a new line and then stripped out the non-required fields for the vsan|pwwn|'zone name'|fcalias and left me with just the zone and member aliases along with pwwn entries. As all zones began with upper case Z this made it simpler also.

The code i used in a one liner was:

grep -oP '\S+' switch01-zones-20160711 | grep -Ev 'name|vsan|^01|^02|fcalias|pwwn|zone' | awk '{printf "%s%s", (/^Zone/?rs:FS), $0; rs=RS} END{print ""}' >to-import.csv

this left me with a nice single row for each Zone and the member alias with the connected www device too and imported into Excel for string building and all in a matter of moments.