How to import a text file but skip the first line regardless of characters?

omahadivision · #1 01-30-2013, 03:21 PM

Hello,

So I have a VBA code that imports a two-column text file into an Excel table. This would be very easy except that the top line isn't part of the data. It can be in any format, including letters, and numbers, and it can contain punctuation and have an odd or even number of things. I've wracked my brain trying to figure out how to skip it, or even better yet import it to a different spot on my spreadsheet. Here is my code:

Code:

On Error Resume Next
  Dim A    As Double, B  As Integer
  Dim iRow    As Long
  Dim Fname   As Variant
  Fname = f.SelectedItems(i)' This is the filename of the textfile, found from previous coding
  If Fname = False Then Exit Sub
  Open Fname For Input As #1
  iRow = 0
  Do While Not EOF(1)
     Input #1, A, B
       Cells(iRow, 2) = B ' This line puts the imported values into the excel sheet
     iRow = iRow + 1
       Loop
  Close 1

It may be handy to show what the data I am importing looks like:

Code:

Anything! 100 can, go on this1line
10.0     99
10.01    111
10.02    114
10.03    113
10.04    127
10.05    122
10.06    136
10.07    131
10.08    123
10.09    111

The first column is always the same and always starts with 10.0 followed by four spaces, which is why I don't even bother importing it. Any ideas?

macropod · #2 02-01-2013, 01:54 AM

Try:
If iRow > 0 Then Cells(iRow, 2) = B ' This line puts the imported values into the excel sheet

omahadivision · #3 02-01-2013, 03:35 PM

Quote:

Originally Posted by macropod

Try:
If iRow > 0 Then Cells(iRow, 2) = B ' This line puts the imported values into the excel sheet

Thanks! Unfortunately, the first thing in line 1 can also be a number at times, and can even be 10. Is there some way to specify that it starts importing when the line equals "10.0 "?

macropod · #4 02-01-2013, 03:50 PM

With the modification I proposed, the first line in your data file will always be skipped, regardless of its content. Isn't that what you wanted? Unless your data file can have multiple unwanted 'header' lines, I can't see what would be achieved by the additional testing.

omahadivision · #5 02-01-2013, 04:08 PM

I see what you mean. I was thinking that it meant the value had to be greater than 0 but now I see it is the line. I will try that. Thanks!

Quote:

Originally Posted by macropod

With the modification I proposed, the first line in your data file will always be skipped, regardless of its content. Isn't that what you wanted? Unless your data file can have multiple unwanted 'header' lines, I can't see what would be achieved by the additional testing.

omahadivision · #6 02-01-2013, 06:07 PM

Quote:

Originally Posted by macropod

With the modification I proposed, the first line in your data file will always be skipped, regardless of its content. Isn't that what you wanted? Unless your data file can have multiple unwanted 'header' lines, I can't see what would be achieved by the additional testing.

The code seems to work great as long as there are two things in the first line. Otherwise, it will actually view the third thing as the beginning of the second line. I assume that this has something to do with the Input #1, A, B line of code.

macropod · #7 02-01-2013, 07:15 PM

Try:

Code:

  Open Fname For Input As #1
  iRow = 0
  Do While Not EOF(1)
    Line Input #1, StrData
    While InStr(StrData, "  ") > 0
      StrData = Replace(StrData, "  ", " ")
    Wend
      If iRow > 0 Then Cells(iRow, 2) = Split(StrData, " ")(1) ' This line puts the imported values into the excel sheet
     iRow = iRow + 1
  Loop
  Close 1

omahadivision · #8 02-01-2013, 08:30 PM

Quote:

Originally Posted by macropod

Try:

Code:

  Open Fname For Input As #1
  iRow = 0
  Do While Not EOF(1)
    Line Input #1, StrData
    While InStr(StrData, "  ") > 0
      StrData = Replace(StrData, "  ", " ")
    Wend
      If iRow > 0 Then Cells(iRow, 2) = Split(StrData, " ")(1) ' This line puts the imported values into the excel sheet
     iRow = iRow + 1
  Loop
  Close 1

Brilliant. Thanks!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to import multiple text file data into word	mizankabir	Word VBA	7	09-15-2023 10:27 PM
macro, data import from the ONLY text file in current folder	ue418	Excel Programming	5	10-28-2017 12:52 PM
Junk characters (box-like characters) in Word file	Sashikala	Word	1	04-20-2010 02:03 PM
Import Text from File - Line Spacing	marshalx	Word	2	10-28-2009 02:37 AM
Help please: Keeping spaced characters on one line	EtienneOBrien	Word	3	12-24-2008 07:05 AM