Two Input Lines Into Single Output Line (CSV)


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Two Input Lines Into Single Output Line (CSV)
# 1  
Old 07-23-2011
Two Input Lines Into Single Output Line (CSV)

Hi all,

My search karate must be weak because I'm about certain something very like this has been asked and answered here many times. I'll give you the exact scenario I've wasted a few hours of my Saturday on: Smilie

I'm trying to read through a very large number (~200) of router and switch configuration .txt files. My end-state goal is the have a spreadsheet that will show each switch name (it's embedded in the .txt file name, so no need to match the "hostname" line in the configs) and list the NTP servers it has configured. Some may have none configured (I still want an output line for these devices), most will have only one server, and some will have two.

So let's say I have a folder containing these three files:

router1.txt
router2.txt
switch1.txt

In this example, router1 has no NTP server. router2 has 'ntp server 1.2.3.4' in its configuration. switch1 has two lines as follows:

ntp server 1.2.3.4 prefer
ntp server 4.5.6.7

I want to create as output a .csv that looks as follows:

router1.txt,
router2.txt,ntp server 1.2.3.4
switch1.txt,ntp server 1.2.3.4 prefer,ntp server 4.5.6.7

I will then do "text to columns" in the spreadsheet, set the delimiter to ",", and then do a global "find and replace for ".txt" to "" (to remove the input file extension and then I basically have the device name in the left-most column). I have figured out how to get either grep or awk to print the input file name. And obviously I know how to match "ntp server" as a string. What I've been failing at so far is getting a single line of output per input file, comma-separated, regardless of the number of individual lines (including zero!) with the matching string found in said input files.

Can anybody help with that? I would be very grateful to you!!

Edit: Each input file contains a "hostname" line and that value is equal to the input file name (obviously less the .txt extension), so if it's difficult to print the input file name even if there isn't a match for "ntp server," then it would be just fine to also match on the hostname, thereby ensuring that I get an output line per input file, even when I don't hit on an ntp server line in the config. Hope that makes sense. Thanks again!

Last edited by svermill; 07-23-2011 at 11:17 PM.. Reason: Edited to potentially lessen complexity
# 2  
Old 07-23-2011
If you don't mind comma at the end of line:
Code:
for i in *; do awk 'NR==1{printf FILENAME","}/ntp server/{printf $0","};END{printf "\n"}' $i;done > out.csv

This User Gave Thanks to bartus11 For This Post:
# 3  
Old 07-23-2011
Thanks bartus11! I think you've gotten me to within a character or two of the solution. Here's what I'm seeing:


router1.txt,ntp server 10.40.40.1 prefer
,ntp server 10.40.40.2
,


Your help is very, very much appreciated!

Edit: The comma at the end doesn't bother me a bit (so long as it's at the end of a single line)!

Last edited by svermill; 07-23-2011 at 10:15 PM.. Reason: To clarify
# 4  
Old 07-23-2011
A slight twist that doesn't need to invoke awk for every file (more efficient), writes a trailing comma only if there are not any ntp records (as per the example), and translates the filename (without the need for an extra sed pass over the file).

Code:
ls *.txt | xargs awk '
FILENAME != last {
        fn = FILENAME;
        gsub( ".txt", "", fn );            
        printf( "%s%s%s", n ? "\n" : "", fn, k ? "" : "," );
        last = FILENAME;
        n = 1;     # signal need for newline before fn (prevent lead newline)
        k = 0;     # when set, signals need for comma after fn
}

/ntp server/ {
    printf( ",%s", $0 );
    k = 1;
}

END {
    printf( "%s\n", k ? "" : "," );
}'  >out.csv

Hope this helps, or gives you some more ideas.
This User Gave Thanks to agama For This Post:
# 5  
Old 07-23-2011
Thanks also be to agama! I must say that some of your code is a tad over my head (I will take the time to understand it, though!), so I'm unable to properly break it down in my own mind. All that I can tell you is that I'm getting out.csv with the name of the very first device in the folder - nothing else follows that. Again, it seems as though this has got to be within a character or two of being a workable solution...

Edit: Oh, I guess I probably just assumed that a trailing comma would follow the filename in the case of zero "ntp server" matches. It's certainly not necessary; the the spreadsheet text-to-columns feature will not care one way or the other and nor would I...

Last edited by svermill; 07-23-2011 at 11:25 PM.. Reason: To add a thought
# 6  
Old 07-23-2011
Hummm. For me it's doing what I expect. If you run it this way, without xargs, is anything different? (I'm assuming that you don't have many .txt files to run it this way.) I don't expect there to be any difference, but it never hurts to try.

Code:
awk '
FILENAME != last {
        fn = FILENAME;   
        gsub( ".txt", "", fn );      
        printf( "%s%s%s", n ? "\n" : "", fn, k ? "" : "," );
        last = FILENAME;
        n = 1;     # signal need for newline before fn (prevent lead newline)
        k = 0;     # when set, signals need for comma after fn
}

/ntp server/ {
    printf( ",%s", $0 );
    k = 1;
}

END {
    printf( "%s\n", k ? "" : "," );
}'  *.txt >out.csv

I also assume that if you execute ls *txt from the command line it lists all of the files you expect the awk to parse.

I'd guess that this line is tough to understand:
Code:
printf( "%s%s%s", n ? "\n" : "", fn, k ? "" : "," );

Each %s is replaced with a string from the parameter list. The parameters are an optional leading comma, the filename (without .txt) and an optional trailing comma. The tertiary operator has the form:

Code:
expression ?  true-action : false-action

So for n ? "\n" : "" the string with the newline (\n) is added to the printf string at the first %s when the variable n is non-zero. When n is zero, then a null-string (no comma) is added.

---------- Post updated at 21:42 ---------- Previous update was at 21:38 ----------

It did just occur to me that if you are running on a Sun, you need to use nawk rather than awk. I don't have a Sun box to test, but the symptom sure seems like the FILENAME variable isn't functioning as we'd expect and that does suggest a different version of awk.
# 7  
Old 07-24-2011
Well I'm feeling very sheepish now because I thought to - and then failed to - mention that this is Mac OS X BSD. I know that sometimes things aren't fully supported in this case, and in retrospect that should've been clearly flagged by the OP (yours truly!). Very sorry 'bout that all!

OK, with that said, I tried your latest proposed code and things are looking very promising. A couple of details that I suspect you'll understand at a glance (example):


router1
router2,,ntp server 10.10.250.1
switch1,ntp server 10.10.250.1
switch2,ntp server 10.10.250.2
,ntp server 10.10.250.1 prefer

The double comma won't be too difficult to deal with in the spreadsheet. However, the last two lines above put me back to the original problem of needing to manually manipulate a lot of stuff (although I originally said ~200 devices, this is something that I'm going to need to repeat often, so the workload would be high).

If it's in any way helpful, I can post sanitized configs of these devices. However, these are all Cisco routers and switches, so you basically have an area of the config where zero, one, or two (or conceivably more but not in this specific case) instances of 'ntp server x.y.z.n' exist in succession...

And thanks once again!

Edit: I don't know whether or not this is of interest, but in the case of "switch2" above, I noticed in the config the following:

ntp server 10.10.250.2
ntp server 10.10.250.1 prefer

So the order is neither by lowest IP nor "prefer" first. Presumably, then, it's by chronological order of entry. Something I've never noticed in many years of working with Cisco configs but then again this is the first time I've tried parsing them in this way...

Further edit: Strangeness abounds. Another detail I just noticed is that router2 above doesn't actually have an 'ntp server' command at all in its config; rather, it has an 'ntp peer' command. Something I hadn't considered at first and need to account for. <<<NOT TRUE!! It actually turns out to be the case that it has both an 'ntp server' and an 'ntp peer' command. Not sure what I screwed up in my search that led to that misunderstanding on my part?? In any event, it is not the case as far as I can tell in reviewing things that there is any kind of misalignment of this sort. If no 'ntp' command of any sort exists, I'm only seeing the filename/hostname, as desired...

Further, further edit: Just noticed that in the relatively few cases where I see a double-comma as in the case of router2 above, it's always preceded by a router1 (no 'ntp server' command in the config). You're obviously highly observant of such details and so probably didn't need me to point this out. However, I just noticed it as a consistent pattern, so I thought I'd once again edit this post (sorry!)...

---------- Post updated at 08:51 PM ---------- Previous update was at 08:14 PM ----------

OK, enough edits to the above have already been published, so a totally new post here...

Again, not sure if such a detail is anywhere remotely significant, but I thought to add that Cisco automatically adds additional NTP commands to the config that no user has ever entered on the keyboard. Typically, you will see:

'ntp clock-period 1234567890'

immediately prior to the 'ntp server' commands (this is not a "stanza," per se; they are all at the main level of the config - totally to the left). This is an internal/proprietary that that Cisco has never bothered to explain. "Just is what it is" type of thing...

---------- Post updated at 09:04 PM ---------- Previous update was at 08:51 PM ----------

Lastly (I promise for tonight!), I should perhaps mention that NTP is the very last section of the config of most (all?) devices and so the .txt file looks something like this:


<tons of output omitted>

router1#show run

<output omitted>

ntp clock-period 1234567890
ntp server 1.2.3.4 prefer
ntp server 4.5.6.7
end

router1#show blahblah

<tons of output omitted>

Last edited by svermill; 07-24-2011 at 01:25 AM.. Reason: To add a new detail.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Get an output of lines in pattern 1st line then 10th line then 11th line then 20th line and so on.

Input file: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 (6 Replies)
Discussion started by: Sagar Singh
6 Replies

2. Shell Programming and Scripting

ksh - Read input from file and output CSV i same row

Hello I have the following output and want the output to look: FROM: GigabitEthernet0/0 is up, line protocol is up 1 input errors, 0 CRC, 0 frame, 1 overrun, 0 ignored 275 output errors, 0 collisions, 3 interface resets GigabitEthernet0/1 is up, line protocol is up 0... (4 Replies)
Discussion started by: JayJay2018
4 Replies

3. Shell Programming and Scripting

How do I split a single-line input into five lines?

Example input: John:Shepherd:770-767-4040:U.S.A:New York Mo Jo:Jo Jo: 666-666-6666:U.S.A:Townsville Expected Output: First Name: John Last Name: Shepherd Phone Number: 770-767-4040 Country: U.S.A State: New York First Name: Mo Jo Last Name: Jo Jo Phone Number: 666-666-6666... (10 Replies)
Discussion started by: Camrikron
10 Replies

4. UNIX for Dummies Questions & Answers

Need help combining txt files w/ multiple lines into csv single cell - also need data merge

:confused:Hello -- i just joined the forums. I am a complete noob -- only about 1 week into learning how to program anything... and starting with linux. I am working in Linux terminal. I have a folder with a bunch of txt files. Each file has several lines of html code. I want to combine... (2 Replies)
Discussion started by: jetsetter
2 Replies

5. Shell Programming and Scripting

turning output of two lines into one CSV line

Hi, I am attempting to use sed on linux to do something trivial. I am also too embarassed to show you what I have tried so far! What I am trying to do should be trivial, if I knew what I was doing, but I don't. Would someone please help me? Here is my problemI have a ASCII file that has the... (4 Replies)
Discussion started by: Jon8987z
4 Replies

6. Shell Programming and Scripting

Input two variable on single line

Can we input two variable on single line that separate by space example user input "list jpg" it will list all jpg files in current directory (3 Replies)
Discussion started by: guidely
3 Replies

7. Shell Programming and Scripting

Multiple lines in a single column to be merged as a single line for a record

Hi, I have a requirement with, No~Dt~Notes 1~2011/08/1~"aaa bbb ccc ddd eee fff ggg hhh" Single column alone got splitted into multiple lines. I require the output as No~Dt~Notes 1~2011/08/1~"aaa<>bbb<>ccc<>ddd<>eee<>fff<>ggg<>hhh" mean to say those new lines to be... (1 Reply)
Discussion started by: Bhuvaneswari
1 Replies

8. Shell Programming and Scripting

Break lines up into single lines after each space in every line

It sounds a bit confusing but what I have is a text file like the example below (without the Line1, Line2, Line3 etc. of course) and I want to move every group of characters into a new line after each space. Example of text file; line1 .digg-widget-theme2 ul { background: rgb(0, 0, 0) none... (7 Replies)
Discussion started by: lewk
7 Replies

9. Shell Programming and Scripting

single line input to multiple line output with sed

hey gents, I'm working on something that will use snmpwalk to query the devices on my network and retreive the device name, device IP, device model and device serial. I'm using Nmap for the enumeration and sed to clean up the results for use by snmpwalk. Once i get all the data organized I'm... (8 Replies)
Discussion started by: mitch
8 Replies

10. Shell Programming and Scripting

Need output in different lines not in one single line

I am getting the coutput like this as show below in one single line, where as the command is executed is several lines and the output should also be requied in several lines, not in one single line. Anyone any idea? p4 opened -a | grep *locked* | awk '{ printf $8 }' >/tmp/aa $ cat... (1 Reply)
Discussion started by: csaha
1 Replies
Login or Register to Ask a Question