Import text file

[norhaya]

Hi

I've a large text file which I need to import selected information only.

Below is the sample text file which showing profile for 3 item. Any sample code or ideas would be appreciated.

Date: 25 Sep 09   2:58pm                                                                    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                                    
 Category           [CON]     Tax status  [0]        Markup factor  [  1.00 ]        Last shipment [         ]
 Picking sequence   [    ]    Stock item  [Y]        Serial numbers [0]              Last receipt  [07 Mar 08]
 Report group       [   ]     Unit weight [          0.00 ]           
 Avg.days between ship.[             0 ]     Average units shipped [          0.00 ] EA        
 -- 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              
 Category           [CON]     Tax status  [0]        Markup factor  [  1.00 ]        Last shipment [         ]
 Picking sequence   [    ]    Stock item  [Y]        Serial numbers [0]              Last receipt  [         ]
 Report group       [   ]     Unit weight [          0.00 ]           
 Avg.days between ship.[             0 ]     Average units shipped [          0.00 ] EA        
 
Date: 25 Sep 09   2:58pm  Page:     2
Item Status Report

Sorted by item number from [                ] to [ZZZZZZZZZZZZZZZZ]

Item Number            Description

              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        
 -- Additional item information ------------------------------------------------------------------------------
 [K5504    ]
 [MS15570-63                     ]
 [F0N9-4101-1                    ]
 [         ]

              C4       CORE              
 Category           [CON]     Tax status  [0]        Markup factor  [  1.00 ]        Last shipment [26 Jul 04]
 Picking sequence   [    ]    Stock item  [Y]        Serial numbers [0]              Last receipt  [31 Dec 02]
 Report group       [   ]     Unit weight [          0.00 ]           
 Avg.days between ship.[           159 ]     Average units shipped [          5.00 ] EA        
 -- 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 ]    
 
Date: 25 Sep 09   2:58pm  Page:     3
Item Status Report

Sorted by item number from [                ] to [ZZZZZZZZZZZZZZZZ]

Item Number            Description

              C4       CORE              
 -- 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             
 Category           [CON]     Tax status  [0]        Markup factor  [  1.00 ]        Last shipment [         ]
 Picking sequence   [    ]    Stock item  [Y]        Serial numbers [0]              Last receipt  [31 Dec 02]
 Report group       [   ]     Unit weight [          0.00 ]           
 Avg.days between ship.[             0 ]     Average units shipped [          0.00 ] EA        
 -- 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              
 -- 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                    ]
 [         ]
 [         ]
 [         ]

[Leith Ross]

Hello norhaya,

Welcome to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

Use the Bulletin Board Code Tags
[code] at the start of the first line,
[/code] at the end of the last line.


As a member, You have agreed to follow the forum posting rules. Please take so time to familiarize yourself with the Do's and Don'ts here in the Forum, just click on the link below...

Forum Rules

[norhaya]

Thank you and noted

[bigmack]

Hi,

you can use the Open statement:

Open "your directory path\file name.txt" for input as #1

then read through each line within a loop until EOF. What part(s) of the text file are you specifically looking for?

[norhaya]

Hi,

you can use the Open statement:

Open "your directory path\file name.txt" for input as #1

then read through each line within a loop until EOF. What part(s) of the text file are you specifically looking for?

I need information as follows and data to be entered as 1 row

Header
Item Number, Description, last shipment, last receipt, Average Unit Cost, Stocking Unit, Qty on Hand, additional Information.

Data
47, lamp, dd/mmm/yy, dd/mmm/yy, ###,000.00, EA, 50.00, K5501

I'm self-taught myself in vba, I understand loop code if i work on spreadsheet. however for this I cannot figure out how to capture data in a text file.

any ideas or example with some explanation will be helpful for me.

[bigmack]

Hi Norhaya,

The header is the easy part. You don't need a macro for that! Here's a basic recipe--create the headers in sheet1. Now, you can begin importing/parsing data from a text file.

dim i as Long
dim tmpString as String
dim tmpAverageUnitCost as String

i = 2 
' Remember your headers are on Row 1, naturally, you want be begin on 2

Open "c:\your directory here\" & "Filename.txt" For Input As #1
Do While Not EOF(1) ' read entire text file until end of file
  Line Input #1, tmpString ' read each line and begin parsing

' let's go for an easy one, "Average unit cost" - after this, you should be able
' to come up with if-scenarios to parse out the remaining fields. The first 2 
' fields Item Number and Description you may have to just go with counting 
' the number of spaces

  if (InStr(tmpString, "Average unit cost")) > 0 Then
    tmpAverageUnitCost = Mid(tmpString, 20, 14)
' use instr to go after the fields
' use a string function like 'mid' to extract needed info--beginning on the
' 20th char and 14 chars long will get you 0.47. Now, to display this data 
' on to the Excel spreadsheet under the correct column (Average Unit
' Cost ... column E)

     Sheet1.Range("E" & i).Value = tmpAverageUnitCost
  End If

  i = i + 1  

Loop
Close #1

[royUK]

Have you read all the rules, if so you should see that your title does not comply