Convert Word Non-tabular Data to Excel Row/Column

[johnmeyer]

I want to convert an alumni database, in the Word format shown below, into a row/column Excel workbook. Here is sample of what two records look like in Word:

Name: Diane L. Smith
Student Name: Diane L. Smith
Job Title: Computer Operator Tech.
Firm Name: Loyola Univ. Med. Ctr.
Degree(s) Earned from Institution(s) Attended: LISC, Beautician-State of IL Cert., Childcare-State of IL
Home Address: 138 W Center Dr., Palatine, Illinois
Home Phone Number: 708 33-1234
E-Mail Address: smith@aol.com
---------------------------
Name: Mrs. Elizabeth H. Doe
Student Name: Betsy Doe
Job Title: Physical Educ. Tchr.
Firm Name: AcmeISD
Home Address: 123 Main Dr. Galveston, Texas 12345
Home Phone Number: 817 333-1234
Spouse’s Name: David
Children’s Names: Clint, Cort

The break between records shown by the dashed line is actually a hard page break in word. Thus each "record" is on a separate page. The "field names" are all identical, are always boldfaced, and always end in a colon.

What I want to do is a little like the Excel Transpose command, but the problem is that not every record contains all possible fields, and also I have to somehow iterate through each "record" in Word.

I can program a macro, either in Word or Excel, to extract this information, but that is going to take awhile for me to get it all working. I assume something like this may be commercially available, and I'm more than happy to pay a little if there is a conversion program that does this sort of thing.

Here's my question: what search words do I use to describe the type of "database" shown above? Also, can anyone point me to any prior art that might get me going?

Thanks in advance for any pointers you might be able to provide.

 

[sandy666]

"computer language,"

if you take a free time, here is another language called M-code
this is solution 4U if you'll get whenever much higher Excel version

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Source, each ([Column1] <> null and [Column1] <> "---------------------------")),
    Split = Table.SplitColumn(Filter, "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false)),
    Group = Table.AddColumn(Table.Group(Split, {"Column1.1"}, {{"Count", each _, type table}}), "Custom", each Table.Column([Count],"Column1.2")),
    Extract = Table.TransformColumns(Group, {"Custom", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    SplitAny = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByAnyDelimiter({"="}, QuoteStyle.Csv)),
    Result = Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(SplitAny,{"Count"})), [PromoteAllScalars=true])
in
    Result

good luck

 

[johnmeyer]

Thanks!

I did find one line of code which, while I understand what it does, I can't find documentation that hints that what you created could possibly work.

Here's the line:

d(ary(0)) = Empty

The "d" array will eventually hold the column headings, after you've walked through all the data. It is dimensioned as a CreateObject("scripting.dictionary") structure, something that I haven't used before, but which I think I understand. And, walking through the code I find that the line above adds another member, but only if that member isn't already contained in that object.

But here's what puzzles me: how can "Empty" return anything? All the VBA documentation makes it sound like it does what the word means: it should return a null value. In this case, it seems to only return Empty when the identical member already exists.

However, the way it works solves one of the other problems I knew I had to deal with which is how to extract the column headers from the field names which precede the colon, but without creating redundant entries.

As for M-code, that looks like a whole different animal. It reminds me, for those of you who've been with Office since the beginning, of when Microsoft switched from WordBasic to VBA. I had Word all tricked out with WordBasic macros (so it would operate like Wordstar!!) and had to re-write.

And yes, to Sandy the Devil, I still use all the 1981 Wordstar shortcuts in Word every day.

Heck, I am so retro that I still play 78 rpm records, own five cassette tape decks, a Beta VCR, 1950s "Sound of the Theater" Altec speakers, and lots more.

 

[sandy666]

I am so retro that I still play 78 rpm records, own five cassette tape decks, a Beta VCR, 1950s "Sound of the Theater" Altec speakers, and lots more

I can add that tube devices work much better than today on microchips.

 

[johnmeyer]

I can add that tube devices work much better than today on microchips.

I actually have tubes and microchips working together. Right next to where I'm sitting is a Nixie tube digital clock that I designed and built in 1972. It's been running 24/7 ever since then. It uses "jelly bean" TTL logic chips ("microchip") to run it.

It is sitting on a Hallicrafters SX-100 shortwave radio (all tubes) that I've had since 1960. Still works great and, much to my amazement, even today there are people out there talking to each other using single-sideband, and some are even "talking" in Morse code.

 

[sandy666]

Welcome to the jungle
I used TS-830 for years but now I have handy Yaesu for local talk

 

[johnmeyer]

Welcome to the jungle
I used TS-830 for years but now I have handy Yaesu for local talk

Ah, a 2-meter ham.

My brother and dad were the hams. I was younger and was merely a SWL. Dad had a hand-held Yaesu 2-meter rig as well. It irritated me when, at family gatherings, he'd often talk to strangers on the radio rather than talk to family.

Dad upgraded to Kenwood gear, similar to yours, back in the 80s or 90s (I forget). That is how I ended up with the old Hallicrafters. I put a dipole antenna in the attic and occasionally run through the 15-20-40 bands, just to hear what's going on.

When dad died in 2004, I was going to give away the Kenwood in a garage sale until I checked eBay and found out it was still worth a lot of money. I sold it for something like $700 to a ham in Denver (my parent's house was in Chicago). I was glad that it found a "good home."

 

[sandy666]

2-meter ham

right now, yes
for SSB I have no condition in place I live now for extended antennas like Yagi or something bigger