2

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.

0

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.

| improve this answer | |
  • Hey Cas. thanks so much for this suggestion, will test it out now to see if it works on the actual data. I did come up with a solution using 3 one liners last night and stripping out non-essential strings to leave me with the core elements but this looks perfect. Will post a comment when test. Much appreciated. – markizy Jul 3 '16 at 14:42
  • It doesn't exactly match what was in your question, but i figured the output format i chose was better :). You can easily change what the s/// commands do, and exactly what gets printed. It's meant as an example to modify and build upon. – cas Jul 3 '16 at 14:46
  • BTW, the perl built-in variable $. is the current line number, so the script avoids printing when zone name is on the first line. I should have explained that in my answer since it's not exactly obvious to someone who isn't already familiar with perl. – cas Jul 3 '16 at 14:49
  • thanks again Cas, really appreciated the script and explanations. tested this morning and perfect for what i need on import to Excel. Just had to sort the ^I with search and replace and it's awesome. you are a star! – markizy Jul 4 '16 at 9:41
  • the pipe to cat -T was only to show you that the output had 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. – cas Jul 4 '16 at 10:28
0

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:

enter image description here

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

| improve this answer | |
  • hi bashBedlam thanks for your thought. The main purpose is not so much replacing the values but getting them columnized so i can create new strings and manipulate them and with the current placement of them even when pasted into Excel it's all over the place based on the values entries being less or more each time. I can use Awk to get all fields on a new row so could quite possibly work this and cut certain non-required text but it's still a struggle for me. – markizy Jul 2 '16 at 8:12
0

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.

| improve this answer | |

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.