Processing Text Data for importing into Excel
 

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 ]

[ ]

[ ]

[ ]

Processing Text Data for importing into Excel
 

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.



"norhaya" wrote:

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 ]