importing vertical data - 1 record per text file

[brundleFly]

I wrote a WMI script that gathers PC inventory data (HW specs,
installed SW, user info...) & writes it to a text file. Each file
contains 1 PC's complete profile.

My thought was to use Access to format the data with reports. In my
haste to get the WMI script done, I forgot that the import wizard wants
each record's data all on one line. Oy.

I write all the single value data at the beginning of the file (field
name <tab> value) & then write subsequent multivalue data after it.
The multivalue stuff is columnar with a header line containing the
field names.

I wasn't sure if I was going to cut the multivalue stuff to separate
files later as that could just be imported using the import wizard.
Since it looks as though i have to write something to walk the file and
grab the single value stuff, i am hoping that i can also grab the
multivalue info in the same routine too.

Any help would be greatly appreciated!!!

Here is a sample of my data:
site: Rochester
invDate: 5/17/2006
pcName: BIG_CAHUNA
make: Dell Computer Corporation
model: Dimension 8200
serialNumPC: GMJK311
Primary User/Function: DevBox
roomNbr: mancave
procDesc: Intel(R) Pentium(R) 4 CPU 1.80GHz
clockSpeed: 1794
biosMan: Dell Computer Corporation
biosVer: A09
biosRelDate: 9/13/2002
OS: Microsoft Windows XP Professional
OSbuild: 2600
Service Pack: 2.0
OSregUser: Jim
OSserialNum: 76487-OEM-0011903-xxxxx
totalRAM: 535834624
monMake: Dell Computer Corp.
monModel: Dell M991
screenRes: 1024 x 768
monSize: 20
monQuality: acceptable
genComment: still kickin


RAMslot: RAMsize:
RIMM1 134217728
RIMM2 134217728
RIMM3 134217728
RIMM4 134217728


driveLetter: fileSystem: driveSize: freeSpace:
C: NTFS 80015491072 58563694592
D: FAT32 80004153344 42744086528

..... more multivalue sections... you get the idea ; )

 

[John Nurick]

I think there are two ways of going about it (not counting starting
again with a revised WMI script)<g>. (1) If you want to use Access's
report engine, write code that reads all your existing files into a
normalised structure of however many tables are needed:

tblPCs
site*
invDate*
pcName*
make
etc.

It sounds as if site, invDate and pcName will work as the primary key
(indicated by the asterisks). Or if every file has a unique name you
could use that.

tblRam
site*
invDate*
pcName*
ramSlot*
ramSize

And so on. I'd probably use a Perl script to create a CSV or
tab-delimited file for each table, then import those into Access tables:
but there are many ways of skinning this cat. Once the data's in Access,
start designing the report (with a subreport for each of the subsidiary
tables).

(2) Another approach is to forget about Access and write code in your
favourite scripting language to read the data files and create a
formatted report as a text file. If you need more than a neatly laid out
text file, have your script output HTML code; then the HTML file with
Word for printing.

Hope this helps. From the way you write about WMI I assume you're
reasonably fluent in VBScript or some other scripting language.