Excel/UNIX help

[WPKenny]

I'm a little stuck with excel.

Basically I have a file that has been created from a directory listing of several machines.
It's a single column. Each machine's directory listing is headed by the machine name and it's IP. At the end is a double line break.

What I'd like is some UNIX script (it's in Mac OSX) that will turn the file into two columns. In the left column I want the name of the machine repeated alongside each file on that machine. The filenames would be in the second column.

The other way I can think of doing it is importing the plain text into excel into a single column and writing a formula that will pull out the machine name and put it into the next column and paste it all the way down till it hits a blank cell on the left of it.
This could use a unique string that only appears in the machine name such as the start of the IP address which will be the same for each machine.

I hope some of that makes sense or perhaps someone out there knows of another way to create a comprehensive software audit using Apple Remote Desktop?

Cheers.


PS. I forgot to say why I needed it that way I explained. Basically I want to see what software is on who's machine. So I could use the auto filter function in Excel and it would show all the occurrences of Photoshop.app (for example) besides the machine name it was found on.

 

[phlash]

#! /bin/sh
machine=""
while read line
do
if [ -z "$line" ]; then
# New machine
machine=""
else
if [ -z "$machine" ]; then
machine="$line"
else
echo "$machine, $line"
fi
fi
done
--------8<--------8<--------

Not tested the above but it should read the file format you describe and output a suitable CSV for loading into Excel. It won't be fast mind you...

HTH,
Phil

 

[WPKenny]

Excellent. Thanks very much.

I haven't got the file fully prepared to run the script on yet (auditing = yawn) but it should be complete in a few days so I'll let you know.

 

[WPKenny]

I've got a guy here who I sent the script to to make it fit the file I have but he's tied up with other work right now.

Is anyone able to tell me the VB macro syntax I need to do the following keyboard command and how I can get it to repeat until the end of the document? (the = is me pressing the equals sign in excel)

=
left
return
=
up
return
up
autofill
left
CTRL+down
CTRL+down



PS I had a poke around and apparently KeyPress doesn't work on Macs. Which is what I'm running this on.

 

[MYstIC G]

I've got a guy here who I sent the script to to make it fit the file I have but he's tied up with other work right now.

Is anyone able to tell me the VB macro syntax I need to do the following keyboard command and how I can get it to repeat until the end of the document? (the = is me pressing the equals sign in excel)

=
left
return
=
up
return
up
autofill
left
CTRL+down
CTRL+down



PS I had a poke around and apparently KeyPress doesn't work on Macs. Which is what I'm running this on.

Easiest way is to record a crappy macro doing the actual keypresses mate, then pick it apart.

 

[WPKenny]

Easiest way is to record a crappy macro doing the actual keypresses mate, then pick it apart.

Tried that but the help files and the intarweb don't seem to give me the right answers. It seems autofill is only available through a macro that needs a specific range. I've not idea how to get it to just run an autofill on the current cell it's in as if you'd double clicked the little box in the corner of the cell.

The trouble is the file I want to run the macro on is too long to set it all up by hand and it will also be different every time so it will make the macro invalid.

I guess I'll have to pester my colleague tomorrow to find some time to finalise the script.

The only person at work who knew vb/excel macros died a couple of years ago. :/