Text File Importing

**MWheeelerFDWT** · 08-15-2011, 12:23 PM

Hi All,

First off, glad to be on the forums here.

I have a question regarding importing text files into a spreadsheet using a Macro in Excel 2007. Below is my code:

Sub ReadTextFile()
Dim A As Single, B As Single, C As Single, D As Single, E As Single, F As Single
  Dim iRow As Long
  Dim Fname As Variant
  Fname = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
           "Select Text Data File")
  If Fname = False Then Exit Sub
  Open Fname For Input As #1
  iRow = 3
  Do Until iRow = 3003
  Input #1, A, B, C, D, E, F
     Cells(iRow, 1) = A
     Cells(iRow, 2) = B
     Cells(iRow, 3) = C
     Cells(iRow, 4) = D
     Cells(iRow, 5) = E
     Cells(iRow, 6) = F
     iRow = iRow + 1
     Loop
  
  Cells(iRow, 8) = Fname
  Close 1
End Sub

This reads in text from a 6 column text file (.txt) into 6 corresponding columns in a worksheet. My question is about how to skip a line. In each text file, the first line is a blank, then the rest of the 3000 lines are comma-delimited into 6 columns. What could I add to the above code to skip the first line (which is essentially a single character since it has no commas), and properly import the rest of the data.

With the blank, every cell gets offset by one, and my data is imported incorrectly. I have gotten around this by deleting the space in each text file prior to reading it in, but I think i can account for it in the code.

Thanks for the help, and sorry for the long-winded question.

**tigeravatar** · 08-15-2011, 01:43 PM

MWheeelerFDWT,

Give the following a try:

Sub ReadTextFile2()
    
    Dim sPath As String: sPath = Application.GetOpenFilename("Text Files, *.txt")
    If sPath = "False" Then Exit Sub
    
    Dim sLine As String, aData() As Variant, DataIndex As Long
    Open sPath For Input As #1
    
    While Not EOF(1)
        DataIndex = DataIndex + 1
        Line Input #1, sLine
        If Trim(sLine) = vbNullString Then
            DataIndex = DataIndex - 1
        Else
            ReDim Preserve aData(0 To 5, 1 To DataIndex)
            aData(0, DataIndex) = Trim(Split(sLine, ",")(0))
            aData(1, DataIndex) = Trim(Split(sLine, ",")(1))
            aData(2, DataIndex) = Trim(Split(sLine, ",")(2))
            aData(3, DataIndex) = Trim(Split(sLine, ",")(3))
            aData(4, DataIndex) = Trim(Split(sLine, ",")(4))
            aData(5, DataIndex) = Trim(Split(sLine, ",")(5))
        End If
    Wend
    Close #1
    
    If DataIndex > 0 Then [A3].Resize(DataIndex, 6).Value = WorksheetFunction.Transpose(aData)
    
End Sub

Hope that helps,
~tigeravatar