| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ] [ ] [ ] [ ] |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ] |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Joel - Importing multiple text files to 1 spreadsheet, now importing from excel files | Excel Programming | |||
| importing text file data into excel | Excel Discussion (Misc queries) | |||
| Need to reformat text data before or after importing to Excel | Excel Discussion (Misc queries) | |||
| Importing Text File Data into Excel where records span several row | Excel Discussion (Misc queries) | |||
| Importing Text Data into Excel | Excel Programming | |||
Linear Mode
