Import text file

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

Originally Posted by norhaya

Hi BigMack

Thank you I'll have a look when m back in office. fyi I've a large file and not possible to transfer all into excel 2003. That's why I would like to select relevant information only. I prefer to select relevant which u have given some ideas. My issues here is what the best solution to have the data for item number and description. I tried to go into an option to delete blank rows 1st and then concatenate the two lines. Another problem is, the "item number and description" will be on 1st header in the page of text file, that's why I'm a bit blank here.

I'll come back n post my code here and let you review and give me feedback

Hi

Below is my code, please review and comments, however, I am still not able to call up the part number and description. Any ideas?

Sub ImportText()

Dim i As Long, n As Long

Dim tmpString As String
Dim tmpAUC As String 'AUC = Average Unit Cost
Dim Fname As String
Dim tmpCAT As String 'CAT = Category
Dim tmpPICK As String ' PICK = Picking Sequence
Dim tmpUOM As String ' UOM = Unit of Measure
Dim tmpPN As String ' PN = Part Number
Dim tmpDesc As String ' Desc = Description
Dim tmpQOH As Variant ' QOH = Qty on Hand


Fname = "C:\Import\Accpac.txt"
Open Fname For Input As #1
Cells.Clear

Range("A1:I1").Value = Array("Part No", "Desc", "Date Last Shipment", "Date Last Receipt", "Average Unit Cost", "UOM", "Qty On Hand", "Location", "Remarks")
tmpPN1 = InStr(tmpString, "") & InStr(tmpString, " ") & InStr(tmpString, "category")
i = 2
n = 1
Do While Not EOF(1)
Line Input #1, tmpString
If (InStr(tmpString, "Category")) > 0 Then
tmpCAT = Right$(tmpString, 12)
Sheet1.Range("C" & i).Value = tmpCAT

ElseIf (InStr(tmpString, "Picking")) > 0 Then
tmpPICK = Right$(tmpString, 12)
Sheet1.Range("D" & i).Value = tmpPICK
ElseIf (InStr(tmpString, "Average unit cost")) > 0 Then
tmpUOM = Right$(tmpString, 16)
tmpAUC = Mid(tmpString, 44, 14)
Sheet1.Range("F" & i).Value = Trim(tmpUOM)
Sheet1.Range("E" & i).Value = tmpAUC

i = i + 1

End If

Loop
Close #1
Worksheets("Sheet1").Range("A1:I1").Columns.AutoFit

End Sub

Originally Posted by norhaya

Hi

Below is my code, please review and comments, however, I am still not able to call up the part number and description. Any ideas?

Sub ImportText()

Dim i As Long, n As Long

Dim tmpString As String
Dim tmpAUC As String 'AUC = Average Unit Cost
Dim Fname As String
Dim tmpCAT As String 'CAT = Category
Dim tmpPICK As String ' PICK = Picking Sequence
Dim tmpUOM As String ' UOM = Unit of Measure
Dim tmpPN As String ' PN = Part Number
Dim tmpDesc As String ' Desc = Description
Dim tmpQOH As Variant ' QOH = Qty on Hand


Fname = "C:\Import\Accpac.txt"
Open Fname For Input As #1
Cells.Clear

Range("A1:I1").Value = Array("Part No", "Desc", "Date Last Shipment", "Date Last Receipt", "Average Unit Cost", "UOM", "Qty On Hand", "Location", "Remarks")
tmpPN1 = InStr(tmpString, "") & InStr(tmpString, " ") & InStr(tmpString, "category")
i = 2
n = 1
Do While Not EOF(1)
Line Input #1, tmpString
If (InStr(tmpString, "Category")) > 0 Then
tmpCAT = Right$(tmpString, 12)
Sheet1.Range("C" & i).Value = tmpCAT

ElseIf (InStr(tmpString, "Picking")) > 0 Then
tmpPICK = Right$(tmpString, 12)
Sheet1.Range("D" & i).Value = tmpPICK
ElseIf (InStr(tmpString, "Average unit cost")) > 0 Then
tmpUOM = Right$(tmpString, 16)
tmpAUC = Mid(tmpString, 44, 14)
Sheet1.Range("F" & i).Value = Trim(tmpUOM)
Sheet1.Range("E" & i).Value = tmpAUC

i = i + 1

End If

Loop
Close #1
Worksheets("Sheet1").Range("A1:I1").Columns.AutoFit

End Sub

Hi Bigmack

After going thru all the text, if I could join the line before line with "catergory" so it would be 1 string, then I could parse it easily. Can you guide/show me on the code? If this works then I can complete this task.

Appreciate your assistance.

Thanks, norhaya

Norhaya, please follow the rules--simply edit your post and use <code> tags otherwise I cannot even post a hint for you.