Automate text import

[Ben]

Hi,

I've wrtten a batch file to audit all our servers, what
I'd now like to do though is come up with a way excel can
load these text files into one worksheet.

Can anyone please help?

Below is an example of the data gathered - the text files
are named after the computer.

Thanks

Ben

Computername: TEST
Kernel version: Microsoft Windows 2000,
Uniprocessor Free
Product type: Professional
Service pack: 3
IE version: 5.0100
System root: C:\WINNT
Processors: 1
Processor speed: 650 MHz
Processor type: Intel Pentium III
Physical memory: 256 MB
Volume Type Format Label
Size Free Free
C: Fixed NTFS 11.2
GB 9.4 GB 83%
D: CD-
ROM
0%
OS Hot Fix Installed
KB817606 06/10/2003
KB819696 06/10/2003
KB822679 06/10/2003
KB823559 06/10/2003
KB824105 06/10/2003
KB824146 06/10/2003
Q147222 06/10/2003
Q323255 06/10/2003
Q324380 06/10/2003
Q326830 06/10/2003
Q326886 06/10/2003
Q328310 06/10/2003
Q329115 06/10/2003
Q329170 06/10/2003
Q329834 06/10/2003
Q331953 06/10/2003
Q810833 06/10/2003
Q811493 06/10/2003
Q816093 06/10/2003
Q323255 06/10/2003
Q324380 06/10/2003
Q326830 06/10/2003
Q326886 06/10/2003
Q328310 06/10/2003
Q329115 06/10/2003
Q329170 06/10/2003
Q329834 06/10/2003
Q331953 06/10/2003
Q810833 06/10/2003
Q811493 06/10/2003
Q816093 06/10/2003

 

[Dave Peterson]

All in one worksheet??

I think I'd go back to my old DOS days and merge the data there.

Put all the files into a separate folder.
(I'm gonna guess that the names of the files look like: xxxxx.TXT)

Then get to a command prompt
traverse to that drive/folder

Copy *.txt allfiles.ALL

(Don't use the same extension for the "to" file. DOS Copy will try to include
it in the list of "From" files if you do. And that won't be good.)

Then back to excel and File|Open
Point at allfiles.all and you'll see the import wizard open up.

Then insert a new column A
put this in A1:
=TRIM(MID(B1,14,255))
(it'll skip over the "computername:" and return TEST--assuming that this is all
one cell.)

Then in B2, put this:
=IF(LEFT(B2,13)="computername:",TRIM(MID(B2,14,255)),A1)
and drag down.

Now column A will contain the name of the computer for each row. (Convert to
values if you want)