Formatting a Text File into 'straight data'

[JoeGKushner]

ppptrp03.p 1+ 3.6.13 Inventory Valuation Report Date: 17/09/13
Page: 1 Federal-Mogul Sealing Div Time: 19:56:21

Product Line: CHEM CHEMICALS

Item Number ABC Site Qty on Hand UM GL Cost Ext GL Cost Current Cost Ext Current Cost Var Pc
-------------------------- --- -------- ------------- -- ---------------- ---------------- --------------- ---------------- --------
7B0106A A 710456 207.34 KG 205.00 42,504.70 253.00 52,457.02 23.4%
WACKER RT685 A&B MIX 9 T
O 1
---------------- ---------------- --------
Item Total: 42,504.70 52,457.02 23.4%

That's some mess coming out of some 3rd party software. It goes about 66K lines. The Product Line changes, not frequently but enough. It's all one cell unless I break it up in the old text to columns.

Ideally they want it in something like the below where the top line is the 'heading' line and the item below it the data for that field.

Product Line: Item Number ABC Site Qty on Hand UM GL Cost Ext GL Cost Current Cost Ext Current Cost Var Pc ?
CHEM 7B0106A A 710456 207.34 KG 205 42,504.70 253 52,457.02 23.40% WACKER RT685A&B MIX 9TO 1

I'm familiar with doing simple macros to do things like formatting a few lines at a time not chunking out huge records like this. Any one have any advice on how to approach it?

 

[James Snyder]

With 66k lines, looping is going to take a lot of time, so I would recommend loading it into either a QueryTable, or using ADO to pull it in as a recordset. Both ways are going to require a lot of learning. Better see if some of the long time experts will answer...

 

[JoeGKushner]

With 66k lines, looping is going to take a lot of time, so I would recommend loading it into either a QueryTable, or using ADO to pull it in as a recordset. Both ways are going to require a lot of learning. Better see if some of the long time experts will answer...

I was afraid of something like that. I inherited another one similar but much smaller that already has some code written but looking it over and comparing the two this one looks more detailed and potentially complex with the grouping level coming in at odd intervals.