[SOLVED] Manipulate text file to allow import to excel

dimothy · 06-11-2010, 07:52 AM

Dear All,

Have managed to create a simple script to poll the various printers we have at our company for a page count via SNMP. At the moment the script echos the printer name to a text file and then the snmp output is appended to the next line. In my rush I thought by using sed to add a comma to the end of each line would mean it would import in to excel nicely. So rather than having:

Printer 1,
45536,
Printer 2,
28765,

I need:

Printer 1, 45536,
Printer 2, 28765,

Is there a way to do this easily via sed? Or is there a way to get the command line to add the echo and the command output on to the same line of the text file?

currently the script looks like this:

Code:

#!/bin/bash
echo Accounts > printers.txt
snmpget -v 1 -c public **.**.**.** SNMPv2-SMI::mib-2.43.10.2.1.4.1.1 >> printers.txt
echo Accounts Colour >> printers.txt
snmpget -v 1 -c public **.**.**.** SNMPv2-SMI::mib-2.43.10.2.1.4.1.1 >> printers.txt

Thanks in advance!

IW2B · 06-11-2010, 09:46 AM

Hi,

You can try adding -n option to echo.

or to fix using sed

Code:

sed 'N;s/\n/ /' printers.txt

Both tested on Fedora 13. This is assuming a format off:

Quote:

Printer 1,
45536,
Printer 2,
28765,

I do not know where your text off "Accounts" and "Accounts Colour" would appear in that file, but it will break that sed option. I'm sure that someone will post a better sed option that does some check on actual text and not just blindly join 2 lines.

If I have more time later I'm try and find a better solution.

Regards
Ian

Elv13 · 06-11-2010, 02:22 PM

More complex than sed, but a solution would be to use a counter and the modulo of the current iteration to add ,\n

something like this:

Code:

RESULT="Accounts\n"

RESULT=$RESULT`snmpget -v 1 -c public **.**.**.** SNMPv2-SMI::mib-2.43.10.2.1.4.1.1`
RESULT=$RESULT"Accounts Colour"
RESULT=$RESULT`snmpget -v 1 -c public **.**.**.** SNMPv2-SMI::mib-2.43.10.2.1.4.1.1`

ITERATOR=0
for LINE in $RESULT;do
   OUTPUT=${OUTPUT}${LINE}
   if [ `expr $ITERATOR % 2` -gt 0 ]; then
      OUTPUT=$OUTPUT"\n"
   else
      OUTPUT=$OUTPUT","
   fi
   let ITERATOR=$ITERATOR+1
done

echo -e"$OUTPUT" > printers.csv

It will take a little time to debug before it work, but the principals should work. You may have to split both sections (Accounts and color) to make it work fine, or add an extra empty line after section title

dimothy · 06-14-2010, 07:50 AM

Thanks for that. Your command IW2B works like a charm. unfortunately not all my snmp commands have been answered so there is no output in the text file. this makes all the lines get muddled up! Think I am going to have to go and learn some if - for -else scripting and try and fiddle a null input for no response etc. knew it would turn out more complicated then i thought it would be originally.