importing flat text files

[boots]

I have many text files to import into EXCEL. They are flat files and the data I need is in columns 1-3, 10-12 and 365-371. The files also have header records that jumble up the columns 1-3, 4-12 and 12-25.

I also need a way to load the files as part of the MACRO so the users will have less problems.

[Echooff3]

Can you post a small sample of the file?

In the example can you denote where the header is jumbling the column.

[hermitking]

this might work take itto access and then in to exscel this will only work if
that should put it in to excell

[Killer42]

Another option would be to have your VB/VBA code read the text file and write it back out in CSV format, then import it to Excel. On the other hand, if you're going to go to that much effort, I suppose you could just have your code read the text file and place the data into the worksheet.

[boots]

Can you post a small sample of the file?

In the example can you denote where the header is jumbling the column.

The header doesn't exactly jumble the column. The fields do not line up with the rest of the document.

header record:
HDRSH0590460106122720061227PROD2007010410053803

I need data from 1-3, 4-8, 9-19 in the header records. Some files have multiple header records.

I need data from 1-3, 10-12, 365-371.
this is a record that I need to extract data from

DET0000001739THIS NDC IS FOR A DRUG THAT IS USUALLY COVERED UNDER PART B. IF PLAN DETERMINES THAT THIS DRUG IS PART B COVERED, SUBMIT DELETION RECORD. 0000002100{

[boots]

Another option would be to have your VB/VBA code read the text file and write it back out in CSV format, then import it to Excel. On the other hand, if you're going to go to that much effort, I suppose you could just have your code read the text file and place the data into the worksheet.

Could this be done within the parameters that I described? I have a massive amount of files and any automation that gets me what I want would be worth it.

[Echooff3]

The fields do not line up with the rest of the document.

I'm not sure what you mean by that?

Are all the records the same length? Do the have and delimiters?

[Killer42]

How about something along these lines...

Private Sub ConvertFile()
  Dim Text As String, Field(1 To 3) As String
  Open "OldFile.Txt" For Input Access Read Shared As #1
  ' Open "NewFile.Txt" For Output Access Write Lock Write As #2
  Do Until EOF(1)
    Line Input #1, Text
    SplitText Text, Field()
    Print #2, Field(1); vbTab; Field(2); vbTab; Field(3)
  Loop
  Close
End Sub
 
Private Sub SplitText(ByVal Src As String, Dest() As String)
 
  If Left$(Src, 3) = "HDR" Then
    Dest(1) = Left$(Src, 3)
    Dest(2) = Mid$(Src, 4, 5)
    Dest(3) = Mid$(Src, 365, 7)
  Else
    ' Populate Dest() array for detail record here.
  End If
 
End Sub

This is just the guts of the routine, to give you some ideas. You will need to fill some gaps (like the Else clause in the SplitText routine) and work out exactly how you will make use of it. For instance, this will read the data file and write out the three fields separated by tabs. You might want to write it in CSV format instead, or load the info directly into your worksheet instead of writing it to a second file, or all sorts of variations.

[boots]

I'm not sure what you mean by that?

Are all the records the same length? Do the have and delimiters?

All the records are the same length and there are no delimiters.

[Echooff3]

Killer's example should work for you. With a minor tweak to shoot it into Excel.

Private Sub ConvertFile()
  Dim Text As String, Field(1 To 3) As String
  Open "OldFile.Txt" For Input Access Read Shared As #1
  Do Until EOF(1)
    Line Input #1, Text
    SplitText Text, Field()
    ActiveCell.Value = Field(1)
    ActiveCell.Offset(,1).Activate 'move the active cell over one column
    ActiveCell.Value = Field(2)
    ActiveCell.Offset(,1).Activate
    ActiveCell.Value = Field(3)
    ActiveCell.Offset(1,-3).Activate 'move the active cell down one row and back 3 columns
  Loop
  Close
End Sub
 
Private Sub SplitText(ByVal Src As String, Dest() As String)
 
  If Left$(Src, 3) = "HDR" Then
    Dest(1) = Left$(Src, 3)
    Dest(2) = Mid$(Src, 4, 5)
    Dest(3) = Mid$(Src, 365, 7)
  Else
    ' Populate Dest() array for detail record here.
  End If
 
End Sub

That will throw the results into Excel starting with the first active cell.

So just replace "OldFile.Txt" with the path and file name of your file and you should be good to go.

[boots]

thanks, that works very well!!

[Killer42]

thanks, that works very well!!

Excellent! :)

Nice teamwork, people.

Echooff3, hope you don't mind - I've just made a small correction in your code (and in mine that it was copied from). Changed all Field() references in the SplitText routine to Dest().

[Echooff3]

Rock on! Good eye Killer!