Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-30-2013, 03:21 PM
omahadivision omahadivision is offline How to import a text file but skip the first line regardless of characters? Windows 7 32bit How to import a text file but skip the first line regardless of characters? Office 2007
Novice
How to import a text file but skip the first line regardless of characters?
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default How to import a text file but skip the first line regardless of characters?


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?
Reply With Quote
  #2  
Old 02-01-2013, 01:54 AM
macropod's Avatar
macropod macropod is offline How to import a text file but skip the first line regardless of characters? Windows 7 64bit How to import a text file but skip the first line regardless of characters? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,953
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Try:
If iRow > 0 Then Cells(iRow, 2) = B ' This line puts the imported values into the excel sheet
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-01-2013, 03:35 PM
omahadivision omahadivision is offline How to import a text file but skip the first line regardless of characters? Windows 7 32bit How to import a text file but skip the first line regardless of characters? Office 2007
Novice
How to import a text file but skip the first line regardless of characters?
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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 "?
Reply With Quote
  #4  
Old 02-01-2013, 03:50 PM
macropod's Avatar
macropod macropod is offline How to import a text file but skip the first line regardless of characters? Windows 7 64bit How to import a text file but skip the first line regardless of characters? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,953
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 02-01-2013, 04:08 PM
omahadivision omahadivision is offline How to import a text file but skip the first line regardless of characters? Windows 7 32bit How to import a text file but skip the first line regardless of characters? Office 2007
Novice
How to import a text file but skip the first line regardless of characters?
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default

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 View Post
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.
Reply With Quote
  #6  
Old 02-01-2013, 06:07 PM
omahadivision omahadivision is offline How to import a text file but skip the first line regardless of characters? Windows 7 32bit How to import a text file but skip the first line regardless of characters? Office 2007
Novice
How to import a text file but skip the first line regardless of characters?
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
Reply With Quote
  #7  
Old 02-01-2013, 07:15 PM
macropod's Avatar
macropod macropod is offline How to import a text file but skip the first line regardless of characters? Windows 7 64bit How to import a text file but skip the first line regardless of characters? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,953
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 02-01-2013, 08:30 PM
omahadivision omahadivision is offline How to import a text file but skip the first line regardless of characters? Windows 7 32bit How to import a text file but skip the first line regardless of characters? Office 2007
Novice
How to import a text file but skip the first line regardless of characters?
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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!
Reply With Quote
Reply

Thread Tools
Display Modes


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
How to import a text file but skip the first line regardless of characters? 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
How to import a text file but skip the first line regardless of characters? Import Text from File - Line Spacing marshalx Word 2 10-28-2009 02:37 AM
How to import a text file but skip the first line regardless of characters? Help please: Keeping spaced characters on one line EtienneOBrien Word 3 12-24-2008 07:05 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:09 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft