Processing Text Data for importing into Excel

[norhaya]

Hi

I would like to process following data for import into excel. The
following text is in profile format for each Part.

Example:

Data I need is

Item No, Desc. Most Recent Cost, Date Change, Stock Unit, Qty On Hand,
Additional Information

Below is the example of my text file.

Date: 17 Sep 09 9:51am Fokker Services Asia
Pte Ltd Page: 1
Item Status Report

Items selected with the control account set (All account
sets ).
Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ]

Item Number Description

47 LAMP
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [29/11/05] Cost 1
[ 0.00 ]
Most recent cost [ 0.47 ]
[ 0.00 ] [07/03/08] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 23.50 ]
Average unit cost [ 0.47 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

50.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------

[K5501 ]

[MS15571-2 ]

[ ]

[ ]

63 BULB
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 2.15 ]
[ 0.00 ] [ / / ] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA

Date: 17 Sep 09 9:51am Fokker Services Asia
Pte Ltd Page: 2
Item Status Report

Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ]

Item Number Description

63 BULB
-- Additional item information
------------------------------------------------------------------------------

[K5504 ]

[MS15570-63 ]

[F0N9-4101-1 ]

[ ]

C4 CORE
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 2.54 ]
[ 0.00 ] [ / / ] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------

[OBS ]

[ ]

[ ]

[ ]

R1 STRAP
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 12.06 ]
[ 0.00 ] [ / / ] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA

Date: 17 Sep 09 9:51am Fokker Services Asia
Pte Ltd Page: 3
Item Status Report

Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ]

Item Number Description

R1 STRAP
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------
[6501
Hangar ]

[ ]

[ ]

[ ]

R2 STRAP
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 8.01 ]
[ 0.00 ] [ / / ] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------
[6501
Hangar ]

[ ]

[ ]

[ ]


Date: 17 Sep 09 9:51am Fokker Services Asia
Pte Ltd Page: 4
Item Status Report

Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ]

Item Number Description

107 STOP
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [06/07/05] Cost 1
[ 0.00 ]
Most recent cost [ 0.25 ]
[ 0.00 ] [22/07/05] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.75 ]
Average unit cost [ 0.25 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

3.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------

[K1008 ]

[ ]

[ ]

[ ]

133 CONNECTOR
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 183.33 ]
[ 0.00 ] [ / / ] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA

Date: 17 Sep 09 9:51am Fokker Services Asia
Pte Ltd Page: 5
Item Status Report

Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ]

Item Number Description

133 CONNECTOR
-- Additional item information
------------------------------------------------------------------------------

[OBS ]

[ ]

[ ]

[ ]

2-2 DISCHARGER
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 70.45 ]
[ 0.00 ] [ / / ] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------

[OBS ]

[ ]

[ ]

[ ]

220 SCREW
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [10/06/08] Cost 1
[ 0.00 ]
Most recent cost [ 6.34 ]
[ 0.00 ] [14/06/08] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 120.46 ]
Average unit cost [ 6.34 ]
EA

Date: 17 Sep 09 9:51am Fokker Services Asia
Pte Ltd Page: 6
Item Status Report

Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ]

Item Number Description

220 SCREW
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

19.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------

[K1002 ]

[ ]

[ ]

[ ]

266 BATTERY
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 45.77 ]
[ 42.21 ] [16/02/09] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------

[XC704 ]

[ ]

[ ]

[ ]

 

[Patrick Molloy]

if each part was in teh same 'template' then you could write code to read and
convert, but looking at waht you have, this is a major task. Can you get the
data in any other text format?

what I mean is this. If yuor code reads the file, it can pick up key code
owrds, such as "Date" to identify teh start of a new item, then it would
have to read 'item; then 'description' at whic point there might follow two
more words, say '47' and 'lamp'
but you'd pretty much have top code to look for eny '[' and so on. Very
difficult.
If yuo could see waht other alternatives you have for extracting the data,
then you might stand a chance.

Hi

I would like to process following data for import into excel. The
following text is in profile format for each Part.

Example:

Data I need is

Item No, Desc. Most Recent Cost, Date Change, Stock Unit, Qty On Hand,
Additional Information

Below is the example of my text file.

Date: 17 Sep 09 9:51am Fokker Services Asia
Pte Ltd Page: 1
Item Status Report

Items selected with the control account set (All account
sets ).
Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ]

Item Number Description

47 LAMP
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [29/11/05] Cost 1
[ 0.00 ]
Most recent cost [ 0.47 ]
[ 0.00 ] [07/03/08] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 23.50 ]
Average unit cost [ 0.47 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

50.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------

[K5501 ]

[MS15571-2 ]

[ ]

[ ]

63 BULB
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 2.15 ]
[ 0.00 ] [ / / ] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA

Date: 17 Sep 09 9:51am Fokker Services Asia
Pte Ltd Page: 2
Item Status Report

Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ]

Item Number Description

63 BULB
-- Additional item information
------------------------------------------------------------------------------

[K5504 ]

[MS15570-63 ]

[F0N9-4101-1 ]

[ ]

C4 CORE
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 2.54 ]
[ 0.00 ] [ / / ] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------

[OBS ]

[ ]

[ ]

[ ]

R1 STRAP
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 12.06 ]
[ 0.00 ] [ / / ] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA

Date: 17 Sep 09 9:51am Fokker Services Asia
Pte Ltd Page: 3
Item Status Report

Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ]

Item Number Description

R1 STRAP
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------
[6501
Hangar ]

[ ]

[ ]

[ ]

R2 STRAP
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 8.01 ]
[ 0.00 ] [ / / ] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.00 ]
Average unit cost [ 0.00 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

0.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------
[6501
Hangar ]

[ ]

[ ]

[ ]


Date: 17 Sep 09 9:51am Fokker Services Asia
Pte Ltd Page: 4
Item Status Report

Sorted by item number from [ ] to [ZZZZZZZZZZZZZZZZ]

Item Number Description

107 STOP
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [06/07/05] Cost 1
[ 0.00 ]
Most recent cost [ 0.25 ]
[ 0.00 ] [22/07/05] Cost 2
[ 0.00 ]
Account set
(Consumables )
Costing method (Moving
average )
Total cost
[ 0.75 ]
Average unit cost [ 0.25 ]
EA
-- Units of measure
-----------------------------------------------------------------------------------------
Stocking unit [EA ] Pricing
unit: (EA ) Costing unit: (EA )
Alternate units [ ]
[ ] [ ] [ ]
Conversion factors [ 1.0000 ]
[ 1.0000 ] [ 1.0000 ] [ 1.0000 ]
-- Total quantities ---- Qty on hand ------ Qty
on P/O ------ Qty on S/O ------------------------------------

3.00 0.00 0.00 EA
-- Additional item information
------------------------------------------------------------------------------

[K1008 ]

[ ]

[ ]

[ ]

133 CONNECTOR
-- Cost per EA -------------- Previous
cost ---- Date of change -------------------------------------
Standard cost [ 0.00 ]
[ 0.00 ] [ / / ] Cost 1
[ 0.00 ]
Most recent cost [ 183.33 ]